pymysql建表+插入数据
需求:保存数据到MySQL安装pymysqlpip install pymysql保存数据模块from pymysql import connectfrom pymysql.cursors import DictCursor# 建立连接conn = connect(host="localhost",user="root",password="root",database="db_transmitt
·
需求:保存数据到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()
更多推荐
所有评论(0)