pyodbc: Memory Error using fast_executemany with TEXT / NTEXT columns

Question

I'm having an issue with inserting rows into a database. Just wondering if anyone has any ideas why this is happening? It works when I avoid using fast_executemany but then inserts become very slow.

driver = 'ODBC Driver 17 for SQL Server'
conn = pyodbc.connect('DRIVER=' + driver + ';SERVER=' + server+ \
                      ';UID=' + user+ ';PWD=' + password)
cursor = conn.cursor()
cursor.fast_executemany = True

insert_sql = """
INSERT INTO table (a, b, c)
VALUES (?, ?, ?)
"""

cursor.executemany(insert_sql, insert_params)

---------------------------------------------------------------------------
MemoryError                               Traceback (most recent call last)
<ipython-input-12-e7e82e4d8c2d> in <module>
      2 start_time = time.time()
      3 
----> 4 cursor.executemany(insert_sql, insert_params)

MemoryError:

Answer 1

There is a known issue with fast_executemany when working with TEXT or NTEXT columns, as described on GitHub here.

The problem is that when pyodbc queries the database metadata to determine the maximum size of the column the driver returns 2 GB (instead of 0, as would be returned for a [n]varchar(max) column).

pyodbc allocates 2 GB of memory for each [N]TEXT element in the parameter array, and the Python app quickly runs out of memory.

The workaround is to use cursor.setinputsizes([(pyodbc.SQL_WVARCHAR, 0, 0)]) (as described here) to coax pyodbc into treating [N]TEXT columns like [n]varchar(max) columns.

(Given that [N]TEXT is a deprecated column type for SQL Server it is unlikely that there will be a formal fix for this issue.)