pymysql batch data insertation

Insert a large amount of data into a table in batches.

For example, insert name into the tags table

sql ='INSERT INTO table name (field name) VALUES (%s,%s,%s)'

In python, the parameter in the sql statement must use %s as a placeholder. In web development, SQL injection can be avoided, and inexplicable errors encountered during development can be reduced.

Prepare the code

impor pymysql

conn = pymysql.connect(host='192.168.3.xxx',
               user='root',
               password='root',
               db='xxx',
               charset='utf8mb4',
               cursorclass=pymysql.cursors.DictCursor)

The general method of inserting data using pymsql:

param=[]
with conn.cursor() as cursor:
    for name in data:
        param.append([name])
    cursor.execute(sql, param)

conn.commit()

Use executemany command

param=[]

for name in data:
    param.append([name])

try:
    with conn.cursor() as cursor:
        cursor.executemany(sql, param)
        conn.commit()   
except Exception as e:
    print e
    conn.rollback() 

It is recommended to use executemany, because the speed of using executemany is about 10 times the speed of using execute.