sql_insert = '''insert into sinfors_hvvms  values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''    conn= MySQLdb.connect(        host='10.160.25.31',        port = 3306,        user='mgmtuser',        passwd='N53(R3#2.a2)mg',        db ='mgmt', 　　　　 charset='utf8' #插入数据时解决中文乱码问题        )    cu = conn.cursor()    datas=[]    i = 1    for csvf in os.listdir(csvpath):        if os.path.splitext(csvf)[0].startswith('queryresult-') and os.path.splitext(csvf)[1] == '.csv':            csvfile = os.path.join(csvpath,csvf)            #print csvfile            cf = open(csvfile)            cf.readline()                        for l in csv.reader(cf):                l.insert(0,i)                i=i+1                datas.append(tuple(l))            cf.close()            #print csvfile            os.remove(csvfile)    if len(datas) > 0:        cu.execute('delete from sinfors_hvvms')        conn.commit()        for data in datas:            #print sql_insert,data            cu.execute(sql_insert,data) #data是一个tuple：(171, '192.168.200.194', 'OPS-Linux', '', '8', '0.0', 'D:\\Hyper-V\\OP', 'Off', 'None', 'None', '1', 'Internet', 'None', 'Disabled', '1601/1/1 8:00:00')

conn.commit()        cu.execute('select * from sinfors_hvvms')        cu.close()

 

print sql_insert,data返回值：

insert into sinfors_hvvms values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) (171, '192.168.200.194', 'OPS-Linux', '', '8', '0.0', 'D:\\Hyper-V\\OP', 'Off', 'None', 'None', '1', 'Internet', 'None', 'Disa

bled', '1601/1/1 8:00:00')

 

自定义字段顺序插入值，查看表字段默认顺序：

desc tablename;

sql_insert = '''insert into sinfors_hvvms(id,PSComputerName,Name,IPAddress,ProcessorCount,MemorySize,Path,State,DelaySec,Description,Generation,VMSwitchName,VlanID,ReplicationState,udate_auto)  values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''

 

解决mySQL中文乱码问题：

1. Python文件设置编码 utf-8 （文件前面加上 #encoding=utf-8)

2. MySQL数据库charset=utf-8

3. Python连接MySQL是加上参数 charset=utf8

4. 设置Python的默认编码为 utf-8 (sys.setdefaultencoding(utf-8)