Increasing buffer size for fast_executemany = True

[CarlaFernandez]

Hello,

I'm inserting data into an Azure SQL Database and I have a geography column which exceeds the default buffer size (8000) even for a single row.

My questions:

• Is there a way to increase the buffer size which is used by fast_executemany = True?
• Otherwise, is the only way to insert my data row-by-row or are there other (pyodbc) alternatives?

Thanks!

[gordthompson]

What ODBC driver are you using? Specifically, what does connection.getinfo(pyodbc.SQL_DRIVER_NAME) return?

[CarlaFernandez]

Hello, it outputs: libmsodbcsql-18.3.so.3.1

[v-makouz]

pyODBC should use DAE in this case. Did you try and it didn't work?

[CarlaFernandez]

I'm sorry, I don't see any documentation regarding this DAE. Could you share some links? Thanks!

[gordthompson]

Possibly related: #490 (comment)

[CarlaFernandez]

I have tried using setinputsizes as well as converting to binary format, and reducing the batch size, but I still see the error.

[gordthompson]

I dunno, this works for me:

import pyodbc

cnxn = pyodbc.connect(
    "DSN=msodbcsql18_199;UID=scott;PWD=tiger^5HHH",
    autocommit=True,
)
crsr = cnxn.cursor()

crsr.execute("DROP TABLE IF EXISTS disc_1356")
crsr.execute(
    "CREATE TABLE disc_1356 (id int IDENTITY PRIMARY KEY, geog_col geography)"
)

num_points = 1000
pts = [(0.000, 0.001 * x) for x in range(num_points)]
s = ", ".join([f"{p[0]:0.3f} {p[1]:0.3f}" for p in pts])
print(len(s))  # 12998

crsr.fast_executemany = True
crsr.setinputsizes([(pyodbc.SQL_WVARCHAR, 0)])
crsr.executemany(
    "INSERT INTO disc_1356 (geog_col) VALUES (geography::STGeomFromText(?, 4326))",
    [(f"LINESTRING({s})",) for x in range(2)],
)

[CarlaFernandez]

Indeed it is working now, thanks a lot Gord. I had built an incorrect list for the setinputsizes method and that's why I kept seeing the error.