如何使用mysql表中的信息创建BAR图表？

How can I create a plot using information from a database table(mysql)? So for the x axis I would like to use the id column and for the y axis I would like to use items in cart(number). You can use any library as you want if it gives the result that I would like to have. Now in my plot(I attached the photo) on the x label it gives an interval of 500 (0,500,1000 etc) but I would like to have the ids(1,2,3,4,...3024) and for the y label I would like to see the items in cart. I attached the code. I will appreciate any help.

如何使用数据库表（mysql）中的信息创建绘图？所以对于x轴我想使用id列，对于y轴我想使用购物车中的物品（数字）。如果它提供了我想要的结果，您可以根据需要使用任何库。现在我的情节（我附上照片）在x标签上，它给出了500（0,500,1000等）的间隔，但我想有ids（1,2,3,4，... 3024）和y标签我想看看购物车中的物品。我附上了代码。我将不胜感激任何帮助。

import pymysql
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np

conn = pymysql.connect(host='localhost', user='root', passwd='', db='amazon_cart')

cur = conn.cursor()
x = cur.execute("SELECT `id`,`items in cart(number)`,`product title` FROM `csv_9_05`")

plt.xlabel('Product Id')
plt.ylabel('Items in cart(number)')

rows = cur.fetchall()
df = pd.DataFrame([[xy for xy in x] for x in rows])

x=df[0]
y=df[1]

plt.bar(x,y)

plt.show()

cur.close()
conn.close()

SQL OF THE TABLE

表的SQL

DROP TABLE IF EXISTS `csv_9_05`;
CREATE TABLE IF NOT EXISTS `csv_9_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3025 DEFAULT CHARSET=latin1;

1 个解决方案

#1

Hm... I think restructuring your database is going to make a lot of things much easier for you. Given the schema you've provided here, I would recommend increasing the number of tables you have and doing some joins. Also, your data type for integer values (the number of items in a cart) should be int, not varchar. Your table fields shouldn't have spaces in their names, and I'm not sure why a product's id and the number of products in a cart are given a 1-to-1 relationship.

嗯......我认为重组你的数据库会让你的事情变得更容易。鉴于您在此提供的架构，我建议您增加表的数量并进行一些连接。此外，整数值的数据类型（购物车中的项目数）应为int，而不是varchar。您的表格字段名称中不应包含空格，我不确定为什么产品的ID和购物车中的产品数量为1对1关系。

But that's a separate issue. Just rebuilding this database is probably going to be more work than the specific task you're asking about. You really should reformat your DB, and if you have questions about how, please tell me. But for now I'll try to answer your question based on your current configuration.

但这是一个单独的问题。只是重建这个数据库可能比你要问的具体任务更多的工作。你真的应该重新格式化你的数据库，如果你对如何有疑问，请告诉我。但是现在我会尝试根据您当前的配置回答您的问题。

I'm not terribly well versed in Pandas, so I'll answer this without the use of that module.

我对Pandas并不是非常精通，所以我会在没有使用该模块的情况下回答这个问题。

If you declare your cursor like so:

如果你声明你的光标如下：

cursor = conn.cursor(pymysql.cursors.DictCursor)
x = cur.execute("SELECT `id`,`items in cart(number)`,`product title` FROM `csv_9_05`")

Then your rows will be returned as a list of 3024 dictionaries, i.e.:

然后你的行将作为3024字典的列表返回，即：

rows = cursor.fetchall()

# this will produce the following list:
# rows = [
#        {'id': 1, 'items in cart(number)': 12, 'product_title': 'hammer'},
#        {'id': 2, 'items in cart(number)': 5, 'product_title': 'nails'},
#        {...},
#        {'id': 3024, 'items in cart(number)': 31, 'product_title': 'watermelons'}
#    ]

Then, plotting becomes really easy.

然后，绘图变得非常容易。

plt.figure(1)
plt.bar([x['id'] for x in rows], [y['items in cart(number)'] for y in rows])
plt.xlabel('Product Id')
plt.ylabel('Items in cart(number)')
plt.show()
plt.close()

I think that should do it.

我认为应该这样做。

#1