pymssql executemany вставить значение очень медленно

python-2.7.15, pymssql-2.1.4, SQL_Server-2018, Windows 10 Pro, MS-Office-2016

import time
import csv
import pymssql

db_settings = {
    "host" : "127.0.0.1",
    "port" : "1433",
    "user" : "sa",
    "password" : "********",
    "database" : "testdb",
    "charset" : "utf8"
               }
conn = pymssql.connect(**db_settings)
cursor = conn.cursor()
ff = csv.reader(open('base.csv', 'r'))
sql = """
    BEGIN
        INSERT INTO Base([name], [year], [update], [status], 
    [timeline], [language], [pic]) VALUES (%s, %s, %s, %s, %s, %s, %s)
    END
    """
now=time.strftime("%M:%S")
t = []
for i in ff:
    i = i[1:]
    if "year" in i:
        pass
    else:
        t.append((i[0], i[1], i[3], i[4], i[6], i[5], i[8]))
cursor.executemany(sql, t)
conn.commit()

end=time.strftime("%M:%S")

print(now+","+end)

Размер файла base.csv - 21,7 МБ, 30374 строки. Когда я выполню приведенный выше код, это займет 929 секунд. Это означает, что всего 32,7 строки в секунду, это слишком медленно. Кто может мне помочь выяснить причину? Большое спасибо. :-)

введите здесь описание изображения


python pymssql sql-server-2012 executemany
person xie    schedule 12.05.2019    source источник
comment
Можете ли вы вставить еще один отпечаток времени после проверки года циклом for? Я подозреваю, что так долго занимает не вставка, а сравнение строк ...   -  person sekky    schedule 12.05.2019
comment
Я пытаюсь удалить цикл for в коде, это занимает 928 секунд, цикл for просто хочет удалить первую строку в base.csv   -  person xie    schedule 12.05.2019
comment
Хм ... CSV конфиденциальна или вы можете поделиться этими данными здесь? Я хотел бы сам попробовать кое-что с кодом, но выполнение этого без исходных данных может привести к совсем другим результатам.   -  person sekky    schedule 12.05.2019
comment
Вы можете пропустить строку заголовка с помощью next(ff, None) перед входом в цикл for i in ff:. Таким образом, вам не нужно проверять каждую последующую строку. И вы действительно вставляете в экземпляр локальной базы данных (127.0.0.1)? Когда я делаю это для 100 000 строк x 4 столбца, я получаю около 695 строк в секунду с pymssql (и около 5000 строк в секунду с pyodbc, драйвером ODBC 17 для SQL Server и fast_executemany=True).   -  person Gord Thompson    schedule 12.05.2019
comment
Спасибо. Теперь я узнаю, как пропустить строку заголовка файла csv без условного оператора if.   -  person xie    schedule 12.05.2019
comment
(cc: @sekky) - Я просто снова попробовал использовать ваши 30 374 строки тестовых данных. У меня 2336 строк в секунду с pymssql и 7 594 строк в секунду с pyodbc + fast_executemany. В вашем окружении явно происходит что-то еще, что вы нам не показываете.   -  person Gord Thompson    schedule 13.05.2019
comment
Наконец, переустановите ОС и MSSQL, я получил 1977 строк в секунду с pymssql. Большое спасибо:-)   -  person xie    schedule 20.05.2019

Ответы (1)


arrow_upward
0
arrow_downward

Я уменьшил время execute_many в pymssql с 30 минут до 30 секунд вот так.

В sql вы можете создавать операторы вставки сразу с несколькими строками. Это выглядит как ниже

INSERT (col_name1, col_name2) 
INTO table_name 
VALUES 
(row1_val1, row1_val2), 
(row2_val1, row2_val2) ... 
(row1000_val1, row1000_val2)

Я реализовал функцию вставки, которая получает фрагменты данных и изменяет запрос для вставки нескольких значений за одно выполнение.

def insert(query, data, chunk=999):
    conn = get_connection()
    cursor = conn.cursor()
    query = query.lower()
    insert_q, values_q = query.split('values') # get part with the query and the parameters
    insert_q += 'values' # add values to make sql query correct after split
    for chunk_data in chunks(data, chunk):
        # chunk_data contains list of row parameters
        flat_list = [item for sublist in chunk_data for item in sublist] # we make it flat to use execute later instead execute_many
        chunk_query = insert_q + ','.join([values_q] * len(chunk_data)) # creating the query with multiple values insert
        cursor.execute(chunk_query, tuple(flat_list)
        conn.commit()

chunks можно реализовать так (спасибо одному из замечательных ответов с этого форума)

def chunks(lst, n):
    for i in range(0, len(lst), n):
        yield lst[i:i + n]

Пример использования

insert('INSERT (user_id, name, surname) INTO users VALUES (%s, %s, %s)',
       [(1, 'Jack', 'Kcaj'), (2, 'Andrew', 'Golara')]
person Konrad    schedule 20.05.2021