django直接操作MySQL，中文乱码

 

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)