python mysql插入语句使用dict快速

适用于

1、插入多个字段

2、字段名和字段值可以存储在字典中的键-属性。

conn = MySQLdb.connect(host='localhost',user='', passwd='', db='cai_test')
            cursor = conn.cursor()
            for line in data:
                sentence = line.replace('\n','').split('\t')
                provin = sentence[2].split(';')
                my_dict = {}
                my_dict['ip'] = sentence[0]
                my_dict['province_num'] = sentence[1]
                p_string= ""
                for i in range(1,len(provin)):
                    item = provin[i].split(':')
                    my_dict[item[0]] = item[1]
                print my_dict
                qmarks = ', '.join(['%s'] * len(my_dict))  #核心代码
                cols = ', '.join(my_dict.keys())#核心代码
                sql_str = "insert into ip_data (%s) values (%s)" % (cols,qmarks)
                cursor.execute(sql_str,my_dict.values())
            conn.commit()