python保存字典数据到MySQL
import pymysql
import datetime
conn = pymysql.connect(host='localhost', user='root', password='root', database='exercise',
charset='utf8') # 链接,用户名,密码,数据库
cursor = conn.cursor()
def change_type(item):
"""
将保存的值改为字符串类型
:param item:
:return:
"""
for k, v in item.items():
item[k] = "CAST({} AS char)".format("'%s'" % v if v is not None else "null")
return item
def gen_c_sql(table_names, item):
"""
生成 create sql
:param table_names:表名
:param item:
:return:
"""
return "insert into {} ({}) values ({})".format(table_names, ",".join(item.keys()), ",".join(item.values()))
def save(table_names, data):
dats = change_type(data)
sql = gen_c_sql(table_names, dats)
cursor.execute(sql)
conn.commit()
if __name__ == '__main__':
item_type = 'didi'
access_token = '22'
refresh_token = '33'
dat = {
'access_token': access_token,
'refresh_token': refresh_token,
'insert_time': datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), # 当前时间
'data_type': item_type
}
table_name = 'jlyq_access_token'
save(table_name, dat)
本文是一个工具类,change_type,gen_c_sql和save方法可公用,只需要在数据库里建表,更改表名,需要保存的dat值就可以了。