2

I am trying to use pymysql.executemany to multi insert.

But the less of a space after VALUES in sql causes the running time to increase. Why is that?

Here is my code, check the two run function.

import pymysql.cursors

from functools import wraps
from datetime import datetime


def running_time(func):
    @wraps(func)
    def deco():
        t0 = datetime.now()
        res = func()
        t1 = datetime.now()
        print(t1 - t0)
        return res
    return deco


CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '',
    'db': 'test',
    'charset': 'utf8',
    'cursorclass': pymysql.cursors.DictCursor,
    'autocommit': True,
}
connection = pymysql.connect(**CONFIG)
cur = connection.cursor()


@running_time
def run():
    sql = """INSERT INTO table_name(c) VALUES (%s)"""
    for i in range(100):
        param = ['1'] * 2000
        cur.executemany(sql, param)
run()


@running_time
def run():
    sql = """INSERT INTO table_name(c) VALUES(%s)"""
    for i in range(100):
        param = ['1'] * 2000
        cur.executemany(sql, param)
run()

outputs:

0:00:02.765183

0:01:13.729428

Community
  • 1
  • 1
panda0
  • 31
  • 3
  • A similar question was posted before. Refer to this topic: [link](https://stackoverflow.com/questions/1017239/how-do-null-values-affect-performance-in-a-database-search) –  Sep 27 '17 at 03:31
  • I think this is not the same problem. I am confused the syntax of sql, not the value of data. – panda0 Sep 27 '17 at 03:38

1 Answers1

0

This is a bug of pymysql.

Check this: allow no whitespaces between VALUES and ( when do bulk insert/replace #597

panda0
  • 31
  • 3