Python-批量插入

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb
import traceback
import time

# 测试环境脚本 - 批量从Mysql主表导出数据到Mycat子表

# Mysql: 打开数据库连接
mysqlDB = MySQLdb.connect(host="47.91", port=3306, user="s", passwd="s", db="s", charset='utf8' )
mysqlCursor = mysqlDB.cursor()
# Mycat: 打开数据库连接
mycatDB = MySQLdb.connect(host="47.89", port=8066, user="s", passwd="s", db="s", charset='utf8' )
mycatCursor = mycatDB.cursor()

# 截止时间
endTime = "2018-06-20 15:20:00"
# 页大小
pageSize = 500

try:
    # 循环分页获取时间线数据
    page = 1
    while (page > 0):

        # 分页查询
        offset = (page - 1) * pageSize
        sql = "SELECT * FROM t_contacts_timeline WHERE main_user_id IS NOT NULL AND create_time < '%s' ORDER BY create_time ASC LIMIT %s,%s " % (endTime, offset, pageSize)
        fetchCount = mysqlCursor.execute(sql)

        # 如果未查到数据, 中断执行
        if fetchCount == 0:
            break
        mysqlResults = mysqlCursor.fetchall()

        # 拼装导入SQL域名
        inserIntoSql = 'INSERT ignore INTO t_contacts_timeline ' \
                       '(id, user_id, contact_id, contact_type, create_time, social_type, social_content, refer_id, top_flag, update_time, read_flag, delete_tag, home_page, browse_end_time, msg_time, page_action_logs, parent_id, main_user_id) VALUES ' \
                       '(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        valuesList = []
        for row in mysqlResults:
            valueTup = (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16],row[17])
            valuesList.append(valueTup)

        # 批量导入,发生异常,中断执行
        try:
            mycatCursor.executemany(inserIntoSql, valuesList)
            mycatDB.commit()
        except Exception, e:
            mycatDB.rollback()
            print u"[批量导入-中断] valuesList=%s" % (valuesList[0])
            print u"[批量导入-异常] msg=%s" % traceback.print_exc()
            break
        page = page + 1

except Exception,e:
    print u"[执行发生异常] msg=%s" % traceback.print_exc()

# 关闭数据库连接
mysqlCursor.close()
mysqlDB.close()
mycatCursor.close()
mycatDB.close()
print u"执行完成!"

 

转载于:https://www.cnblogs.com/wanhua-wu/p/9227438.html