2020-10-18 MySQL与Python

 

sudo apt-get install python-dev
sudo apt-get install libmysqlclient-dev

sudo pip install PyMySQL

 

MySQL建表

create table t_mqtt_temp(
     id int unsigned NOT NULL AUTO_INCREMENT,
     tempid varchar(64) DEFAULT NULL,
     topic varchar(255) NOT NULL,
     qos tinyint(1) NOT NULL DEFAULT '0',
     payload double,
     arrived datetime NOT NULL,
     PRIMARY KEY (id)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;

 

完整代码

import pymysql
import matplotlib.pyplot as plt
from matplotlib.pyplot import MultipleLocator
host = "localhost"
port = 3306
user = "root"
passwd = "admin"
charset = "utf8"
dbname = "test"
conn = None

    conn = pymysql.Connection(
        host = host,
        port = port,
        user = user,
        passwd = passwd,
        db = dbname,
        charset = charset
        )
    cur = conn.cursor(pymysql.cursors.DictCursor)
    cur.execute("select * from t_mqtt_temp;")
    rows = cur.fetchall()

xList = []
yList = []

for i in rows:
    xList.append(i[0])
    yList.append(i[4])

x_major_locator=MultipleLocator(1)
ax=plt.gca()
ax.xaxis.set_major_locator(x_major_locator)
 
plt.plot(xList,
         yList,
         'ro-',
         color='#4169E1',
         alpha=0.8,
         linewidth=1,
         label='I')

plt.show()