Python и чтение из excel с записью в mysql как ускорить?

Question

[wideShift]

Задавал уже вопрос про оптимизацию. Придумал такой пример ради интереса:
прочитать 500 тыс.строк.(инфа о пользователях) из excel и сохранить в mysql.
Задача в данном случае вымышленная, но, думаю, такие возникают.
Скрипт отрабатывает за 260 сек.

import mysql.connector
import openpyxl
import time

start = time.time()
conn = mysql.connector.connect(host='localhost',
                                 user='root',
                                 password='',
                                 database='exceltest')
cursor = conn.cursor()
wb = openpyxl.load_workbook('kniga.xlsx')
sheet = wb.active
for i in range(2, 500002): #Читаем со 2-й строки (1-я заголовок)
    nm = sheet[f'A{i}'].value #id
    name = sheet[f'B{i}'].value #имя
    fam = sheet[f'C{i}'].value #фамилия
    otch = sheet[f'D{i}'].value #отчество
    vod_pr = sheet[f'E{i}'].value #Водительские права(Есть/Нет)
    sem_pol = sheet[f'F{i}'].value #Семейное положение(Да/Нет)
    prof = sheet[f'G{i}'].value #Профессия
    cursor.execute("""insert into table1(id, name, fam, otch, vod_pr, sem_pol, prof)
                  values(%s, %s, %s, %s, %s, %s, %s)""", (nm, name, fam, otch, vod_pr, sem_pol, prof))
conn.commit()
cursor.close()
conn.close()
end = time.time()
print(end-start)

Answer 1

[o5a]

Для массовой вставки строк лучше использовать executemany, а не делать execute в цикле.
https://dev.mysql.com/doc/connector-python/en/conn...
Т.е. сформировать вложенный список строк, затем массово его вставить, например так:

# формируем вложенный список всех строк данных
data = [[
    sheet[f'A{i}'].value, #id
    sheet[f'B{i}'].value, #имя
    sheet[f'C{i}'].value, #фамилия
    sheet[f'D{i}'].value, #отчество
    sheet[f'E{i}'].value, #Водительские права(Есть/Нет)
    sheet[f'F{i}'].value, #Семейное положение(Да/Нет)
    sheet[f'G{i}'].value] #Профессия
    for i in range (2, 500002)
    ]

# массово вставляем все данные
cursor.executemany("""insert into table1(id, name, fam, otch, vod_pr, sem_pol, prof)
              values(%s, %s, %s, %s, %s, %s, %s)""", data)

Но раз уж мы массово вытаскиваем данные, то лучше переделать выборку, например так:

# формируем вложенный список всех строк данных (тут подставить свои значения min/max row/column если потребуется)
data = list(sheet.iter_rows(min_row=2, max_row=500002, max_col=7, values_only=True))

# массово вставляем все данные
cursor.executemany("""insert into table1(id, name, fam, otch, vod_pr, sem_pol, prof)
              values(%s, %s, %s, %s, %s, %s, %s)""", data)

Хотя возможно есть более быстрый способ массово выбирать данные строк в openpyxl, я не знаю.

P.S. можно сразу почистить данные от пустых строк, например так
data = list(filter(lambda x: any(x), data))

Answer 2

[javedimka]

Преобразовать xlsx в csv, импортировать в mysql. Держи, ускорил, даже питон не нужен.

Comments

[wideShift]

А без преобразования варианты есть?

[javedimka]

wideShift, Есть, но зачем? Ты же вроде ускорить хочешь?