Поле счетчика pyodbc to_sql неверно или синтаксическая ошибка

Question

Я загружаю данные Json с веб-сайта API и использую функции to_sql sqlalchemy, pyodbc и pandas для вставки этих данных в MSSQL-сервер.

Я могу загрузить до 10000 строк, однако я должен ограничить размер фрагмента 10, иначе я получаю следующую ошибку:

DBAPIError: (pyodbc.Error) ('07002', '[07002] [Microsoft] [Поле собственного клиента SQL Server 11.0] Неверное поле COUNT или синтаксическая ошибка (0) (SQLExecDirectW)') [SQL: 'INSERT INTO [TEMP_proroduction_entity_details]

Есть около 500 миллионов строк для загрузки, он просто ползет с такой скоростью. Любой совет об обходном пути?

Спасибо,

Answer 1

ОБНОВИТЬ:

Панды 0.23.1 отменили проблемные изменения, внесенные в 0.23.0. Тем не менее, лучшим решением для повышения производительности остается CSV -> bcp подход, как описано ниже.

ОБНОВИТЬ:

Панды 0.24.0, по-видимому, вновь представили проблему (ссылка: здесь)

---

(Оригинальный ответ)

До версии панд 0.23.0, to_sql сгенерирует отдельную INSERT для каждой строки в DataTable:

exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(6)',
    N'INSERT INTO df_to_sql_test (id, txt) VALUES (@P1, @P2)',
    0,N'row000'
exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(6)',
    N'INSERT INTO df_to_sql_test (id, txt) VALUES (@P1, @P2)',
    1,N'row001'
exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(6)',
    N'INSERT INTO df_to_sql_test (id, txt) VALUES (@P1, @P2)',
    2,N'row002'

Предположительно для повышения производительности pandas 0.23.0 теперь генерирует конструктор табличных значений для вставки нескольких строк за вызов

exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(6),@P3 int,@P4 nvarchar(6),@P5 int,@P6 nvarchar(6)',
    N'INSERT INTO df_to_sql_test (id, txt) VALUES (@P1, @P2), (@P3, @P4), (@P5, @P6)',
    0,N'row000',1,N'row001',2,N'row002'

Проблема в том, что хранимые процедуры SQL Server (включая системные хранимые процедуры, такие как sp_prepexec) ограничены 2100 параметрами, поэтому, если DataFrame имеет 100 столбцов, to_sql можно вставить только около 20 строк одновременно.

Мы можем рассчитать необходимый chunksize с помощью

# df is an existing DataFrame
#
# limit based on sp_prepexec parameter count
tsql_chunksize = 2097 // len(df.columns)
# cap at 1000 (limit for number of rows inserted by table-value constructor)
tsql_chunksize = 1000 if tsql_chunksize > 1000 else tsql_chunksize
#
df.to_sql('tablename', engine, if_exists='replace', index=False, chunksize=tsql_chunksize)

Тем не менее, наиболее быстрый подход, вероятно, будет:

• дамп DataFrame в файл CSV (или аналогичный), а затем

• заставить Python вызывать SQL Server bcp утилита для загрузки этого файла в таблицу.

Answer 2

For me solution was NOT TO USE:

      engine = create_engine(connection_uri, fast_executemany=True)

instead I just played with:

      df.to_sql('tablename', engine, index=False, if_exists='replace',
          method='multi', chunksize=100)

Here instead of chunksize=100 I've put chunksize=90 and it started to work. Obviously because previous table was smaller and for larger number of columns you might need smaller number here. Play around with it if you don't want to play with calculations which might be wrong for various reasons.

Answer 3

Сделано несколько модификаций, основанных на ответе Горда Томпсона. Это автоматически вычислит размер фрагмента и сохранит его до наименьшего ближайшего целочисленного значения, которое соответствует пределу 2100 параметров:

import math
df_num_of_cols=len(df.columns)
chunknum=math.floor(2100/df_num_of_cols)
df.to_sql('MY_TABLE',con=engine,schema='myschema',chunksize=chunknum,if_exists='append',method='multi',index=False )

Answer 4

У меня нет репутации, поэтому я не могу комментировать Амита С. Я просто попробовал этот способ, с chuknum, вычисленным с помощью метода, установленного на 'multi'. По-прежнему показывает мне ошибку:

[Microsoft][SQL Server Native Client 11.0][SQL Server]У входящего запроса слишком много параметров. Сервер поддерживает до 2100 параметров. Уменьшите количество параметров и повторно отправьте запрос

Итак, я просто изменил:

chunknum=math.floor(2100/df_num_of_cols) 

к

chunknum=math.floor(2100/df_num_of_cols) - 1

Кажется, теперь работает отлично. Думаю, должна быть какая-то краевая проблема...