python MySQL 批量插入

记录下,python MySQL 批量插入 例

# coding:utf-8
import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')

# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, AGE, SEX) VALUES (%s,%s,%s)"
# 一个tuple或者list
T = (('xiaoming', 31, 'boy'), ('hong', 22, 'girl'), ('wang', 90, 'man'))

try:
    # 执行sql语句
    cursor.executemany(sql, T)
    # 提交到数据库执行
    db.commit()
except :
    # 如果发生错误则回滚
    db.rollback()
# 关闭游标
cursor.close()
# 关闭数据库连接
db.close()

操作

medias = []
for dl_id in range(1,10):
    ab = [cartoons_id,chapter_id, dl_id, escape_string(downAlt[dl_id]), arrinsert, ttime, ttime]
    medias.append(ab)

media_sql = 'INSERT INTO `db`.`cm` VALUES (NULL,%s, %s, "%s", %s,%s ,%s,%s)'
pysql.db.queryBatch(pysql, media_sql, medias)

SQL

    def queryBatch(self,sql,agrs=None):
        if self._connect and self._cursor:
            try:
                self._cursor.executemany(sql,agrs)
                self._connect.commit()
            except:
                self._connect.rollback()