import pandas as pd
import pymysql
import time

def makefile(name,q):
    f = open(name,'w')
    f.write('ls,num,id,pass,name\n')
    for i in range(q):
        txt = '%s,%s,id%s,pw%s,nm%s\n'%(i,i,i,i,i)
        f.write(txt)

def conn():
    db = pymysql.connect(host='localhost',port=3306,user='howoni',passwd='#',db='howoni')
    c = db.cursor()
    c.execute('truncate TABLES')
    return db

makefile('hi2.csv',500000)
df = pd.read_csv('hi2.csv',index_col='ls')

b = conn()
a = b.cursor()

s = time.time()
sql = 'insert into TABLES values '
data = []
for idx, i in enumerate(df.values):     
    sql += '(%s, %s, %s, %s),'
    for j in i:
        data.append(j)
    if idx % 20 == 0:
        sql = sql.rstrip(',')
        a.execute(sql, tuple(data))
        sql = 'insert into TABLES values '
        data = []
sql = sql.rstrip(',')
a.execute(sql,data)
print(time.time()-s)

s = time.time()
sql = 'insert into TABLES values (%s, %s, %s, %s)'
for i in df.values:
    a.execute(sql, tuple(i))
print(time.time()-s)

a.execute('select count(*) from TABLES')
r = a.fetchone()
print(r)

b.commit()
a.close()

1) "insert into [TABLES] values (%s, %s, %s, %s) * 20" * 25000 : 500000rows 소요시간 15초,

2) "insert into [TABLES] values (%s, %s, %s, %s) * 500000 : 500000rows 소요시간 120초

pymssql이용해서 bulk가 아닌방법으로 때려박으실때 참고하시면될거같아용

stackoverflow 에도 같은 질문 타래가 있네요. 다양한 답변이 달렸으니, 한번 테스트해 보시길.

https://stackoverflow.com/questions/29706278/python-pandas-to-sql-with-sqlalchemy-how-to-speed-up-exporting-to-ms-sql