Fast load sql to db (By disable autocommit)

db_fast_load.py

#!/usr/bin/env python
#coding: utf-8

import pymysql as mdb

def read_statement(fd):
    lines = []
    while True:
        line = fd.readline()
        if not line:
            break
        line = line.strip()
        lines.append(line)
        if line.endswith(';'):
            break
    return '\r\n'.join(lines)


def mysql_exec(conn, stat):
    cursor = conn.cursor()
    cursor.execute(stat)
    cursor.close()


COUNTER = 0
CNT_LIMIT = 5000
def execute(conn, stat):
    global COUNTER
    is_insert = stat.startswith('INSERT INTO')
    if not is_insert and COUNTER > 0:
        print 'Commit insert ', COUNTER
        COUNTER = 0
        conn.commit()
    mysql_exec(conn, stat)
    if not is_insert :
        print stat
        print '='*80
        conn.commit()
    else:
        COUNTER += 1
        if COUNTER >= CNT_LIMIT:
            print 'Commit insert ', COUNTER
            COUNTER = 0
            conn.commit()


def execute_stats(dbname, filename):
    conn = mdb.connect(user="root", passwd="hello123",
                       database=dbname,charset="utf8")
    conn.autocommit(False)
    with open(filename, 'r') as fd:
        while True:
            stat = read_statement(fd)
            if stat.strip():
                execute(conn, stat)
            else:
                break
    conn.commit()
    conn.close()

def main(dbname):
    execute_stats(dbname, '{}.sql'.format(dbname))


if __name__ == '__main__':
    import sys
    main(sys.argv[1])