python批量导入MySQL的表头 、扩展插入(datetime)类型主键
目标:1.在EXCEL中编辑table name、数据格式、数据类型和备注等信息,把excel中col_1列的值作为表头,col_2列的值作为数据类型,col_3列的值作为约束类型,在mysql中创建table。2.以datetime类型为主键,先按一定顺序扩展主键列,然后更新数据。目的(起因):1.需要在创建一个table来存储数据,大约300列。通过敲create、alter来创建表,太累,容易出错,日后维护修改麻烦。2.更新时间序列数据,虽然有某些行无数据的情况,但想在tables中保留改行,以便分析数据缺失情况。在网上找了很多,各种出bug。下面分享下自己的解决办法。配置及工具:win10(LTSC)、python3.7.5、mysql(8.0)、pycharm、jupyter notebook、mysql.connector(我是初学者,随机选的这个)、其它库(pandas,datetime,numpy)方法:一、表头问题字符串拼接的方法拼接出sql,通过cursor.execute(sql),create、alter实现目标二、扩展datetime主键列pandas.date_range生成时间序列,用numpy.array().reshape(len(),1)把一维转置成二维,最后格式换成tuple组成的list,最后用cursor.executemany(sql),inset into命令扩展数据列。目标格式:[('-01-02 00:00:06',), ('-01-02 00:10:06',), ('-01-02 00:20:06',)],错误格式(['-01-02 00:00:06'], ['-01-02 00:10:06'], ['-01-02 00:20:06'])、[['-01-02 00:00:06'], ['-01-02 00:10:06'], ['-01-02 00:20:06']]、[('-01-02 00:00:06'), ('-01-02 00:10:06'), ('-01-02 00:20:06')],。为什么时候元组,我也不懂,别人的测试中,其它格式都行,我这里只有这个格式可以。视乎元组中那个逗号(",")很重要。很多东西还不懂,望指教。
import mysql.connectorimport pandasimport datetimeimport numpyconfig={'user':'root','password':'~~','database':'~~'}con=mysql.connector.connect(**config)#可变长变量**configcursor=con.cursor()#问题一解决办法path=r'~~'df_bi = pandas.read_excel(path)#sqa = 'CREATE TABLE test('sqb = 'date_time DateTime PRIMARY KEY'sqc = df_bi.loc[1]['col_1'] + ' ' + df_bi.loc[1]['col_2'] + ' ' + df_bi.loc[1]['col_3'] + ' 'sqn = ');'#col_1:表头,col_2:数据类型,col_3:约束类型cursor.execute(sql)mit()#问题二解决办法#创建时间序列ds = pandas.date_range(start='-01-02 0:0:6',periods =3,freq ='10T')#转换时间序列str=pandas.to_datetime(ds).strftime("%Y-%m-%d %H:%M:%S")#print(str)#Index(['-01-02 00:00:06', '-01-02 00:10:06', '-01-02 00:20:06'], dtype='object')val =numpy.array(str).reshape(len(str),1)val=val.tolist()for i in range(len(val)):val[i]=tuple(val[i])#print(val)#[('-01-02 00:00:06',), ('-01-02 00:10:06',), ('-01-02 00:20:06',)]sql= "INSERT INTO test(date_time) VALUES(%s);"cursor.executemany(sql,val)mit()cursor.close()con.close()
如果觉得《python批量导入MySQL的表头 扩展插入(datetime)类型主键》对你有帮助,请点赞、收藏,并留下你的观点哦!