按日期绘制mysql数据库数据 [英] Plotting data of a mysql database by date

问题描述

我创建了一个mysql数据库来保存湿度，温度和其他带有时间戳的数据.我可以绘制数据，但x轴未标记.

I created a mysql-database to save the humidity, temperature and several other datas with a timestamp. I can plot the data, but the x-axis is not labeled.

我试图设置刻度线的标签，但没有正确标记它们.无法看到在哪个日期保存了哪些数据.日期类型为datetime.datetime()

I tried to set the label of the ticks, which did not labeled them correctly. It was not possible to see on which date which data was saved. The type of the dates is datetime.datetime()

result = cursor.fetchall()

for r in result:
    dates.append(r[1])
    humidity.append(r[2])
    temperature.append(r[3])
    pm25.append(r[4])
    pm10.append(r[5])


fig, ax = plt.subplots()

for tick in ax.get_xticklabels():
    tick.set_rotation(45)


ax.plot(dates, humidity, color = 'b')
ax.plot(dates, temperature, color = 'r')
ax.plot(dates, pm25, color = 'orange')
ax.plot(dates, pm10, color = 'g')

plt.show()

我希望日期标记为x轴，如果可能的话，可以用更大的刻度线标记每个新的一天.

I want the dates to label the x-axis and if it is possible to mark every new day with a bigger tick.

推荐答案

在没有数据示例的情况下，我无法重现您的问题，但是我使用我的代码编写了一些代码.我的数据库是sqlite3，但这并不重要.

I have not been able to reproduce your problem without an example of your data, but I wrote some code using mine. My database is sqlite3, but that doesn't really matter.

熊猫有一个read_sql_query方法，您可能会发现它很有用.我正在使用其parse_dates和index_col将数据直接读入带有日期时间索引的熊猫数据框中.

Pandas have a read_sql_query method which you might find useful. I'm using its parse_dates and index_col to read the data straight into a pandas dataframe with a datetime index.

# read_sql_query
with sqlite3.connect(my_db) as con:
    query = "SELECT humidity, ground_temp, ambient_temp, reading_timestamp from Measurements WHERE Measurements.stations_id = 591441"
    to_plot = pd.read_sql_query(sql=query, con=con, parse_dates=['reading_timestamp'], index_col='reading_timestamp')  

如果您喜欢fetchall()，我可以达到以下相同效果:

If you prefer fetchall() I can achieve the same result like this:

# fetchall
with sqlite3.connect(my_db) as con:
    query = "SELECT humidity, ground_temp, ambient_temp, reading_timestamp from Measurements WHERE Measurements.stations_id = 591441"
    to_plot = con.execute(query).fetchall()
    to_plot = pd.DataFrame(to_plot, columns=['humidity', 'ground_temp', 'ambient_temp', 'reading_timestamp']).set_index('reading_timestamp')

这是我的数据:

                           humidity  ground_temp  ambient_temp
reading_timestamp                                             
2019-05-21 14:55:02+00:00     70.66        14.31         16.33
2019-05-22 10:25:02+00:00     42.08        14.56         15.37
2019-05-23 12:25:02+00:00     55.07        15.75         17.49
2019-05-24 03:25:02+00:00     65.10        16.88         21.25
2019-05-27 13:55:02+00:00     57.46        18.50         25.12

索引是日期时间:

to_plot.index

DatetimeIndex(['2019-05-21 14:55:02+00:00', '2019-05-22 10:25:02+00:00',
               '2019-05-23 12:25:02+00:00', '2019-05-24 03:25:02+00:00',
               '2019-05-27 13:55:02+00:00'],
              dtype='datetime64[ns, UTC]', name='reading_timestamp', freq=None)

现在我有一系列的绘图方法.

Now I have a range of options how to plot.

最简单，最快，但可定制性较低的

The easiest and the quickest, but less customizable.

fig, ax = plt.subplots()
plt.plot(to_plot)
for tick in ax.get_xticklabels():
    tick.set_rotation(45)

更多控制项，自动分配标签，这样我就可以轻松添加图例.

More control, assigns labels automatically so I can easily add a legend.

fig, ax = plt.subplots()
ax.plot(to_plot['ambient_temp'], 'orange')
ax.plot(to_plot['ground_temp'], 'red')
ax.plot(to_plot['humidity'], 'blue')
for tick in ax.get_xticklabels():
    tick.set_rotation(45)
ax.legend()

但是在这个用例中我看不到任何好处.绘图系列使用更少的打字即可获得相同的结果.

But I don't see any benefit in this use case. Plotting Series gives the same result with less typing.

# Convert to lists
dates = list(to_plot.index)
ambient_temp = list(to_plot['ambient_temp'])
ground_temp = list(to_plot['ground_temp'])
humidity = list(to_plot['humidity'])
# Plot lists
fig, ax = plt.subplots()
ax.plot(dates, ambient_temp, 'orange', label='ambient_temp')
ax.plot(dates, ground_temp, 'red', label='ground_temp')
ax.plot(dates, humidity, 'blue', label='humidity')
for tick in ax.get_xticklabels():
    tick.set_rotation(45)
ax.legend()

字体较大的日子

现在要用更大的字体显示几天，我建议您使用matplotlib.dates将日期设置为主要刻度，然后对其进行格式化您想要的方式.

Days in a bigger font

Now to get days to display in a bigger font, I would suggest that you set days as major ticks using matplotlib.dates and then format them the way you want.

fig, ax = plt.subplots()
ax.plot(to_plot['ambient_temp'], 'orange')
ax.plot(to_plot['ground_temp'], 'red')
ax.plot(to_plot['humidity'], 'blue')
for tick in ax.get_xticklabels():
    tick.set_rotation(45)
ax.legend()

import matplotlib.dates as mdates
# mdates detects days
days = mdates.DayLocator()
# format for days
days_fmt = mdates.DateFormatter('%Y-%m-%d')
# days are major ticks
ax.xaxis.set_major_locator(days)
# format major ticks as days
ax.xaxis.set_major_formatter(days_fmt)
# give major ticks on x-axis a large font
ax.tick_params(axis='x', which='major', labelsize=13)

这篇关于按日期绘制mysql数据库数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！