需求:保存数据到MySQL
安装pymysql
pip install pymysql
保存数据模块
from pymysql import connect
from pymysql.cursors import DictCursor
conn = connect(
host="localhost",
user="root",
password="root",
database="db_transmitter",
port=3306,
cursorclass=DictCursor,
)
c = conn.cursor()
def save_data(Transmitter_number, Date_time, Update_time, Pressure, Temperature, State):
sql = """
create table %s(
id int primary key auto_increment,
Date_time datetime not null,
Update_time datetime,
Pressure float,
Temperature float,
State varchar(200)
)
""" % Transmitter_number
items = {
'Date_time': Date_time,
'Update_time': Update_time,
'Pressure': Pressure,
'Temperature': Temperature,
'State': State
}
insert_values = ",".join(["%({})s".format(key) for key in items])
save_sql = 'insert into %s(Date_time, Update_time, Pressure, Temperature, State) values(%s)' % (
Transmitter_number, insert_values)
print(save_sql)
c.execute('show tables')
tb_list = c.fetchall()
print(tb_list)
if {'Tables_in_db_transmitter': Transmitter_number} not in tb_list:
c.execute(sql)
c.execute(save_sql, args=items)
conn.commit()
else:
c.execute(save_sql, args=items)
conn.commit()
c.close()
conn.close()