Insert into temp table with fast_executemany + executemany loses fraction of seconds

[zhiyongzhang87]

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.9.16
• pyodbc: 4.0.34
• OS: CentOS Linux release 7.7.1908
• DB: SQL Server 15.0.2000.5
• driver: ODBC Driver 17 for SQL Server (17.10.2.1)

Issue

import pyodbc, datetime, struct

def datetime_as_string(raw_bytes): tup = struct.unpack("<2l", raw_bytes) days_since_1900 = tup[0] partial_day = round(tup[1] / 300.0, 3) return datetime.datetime(1900, 1, 1) + datetime.timedelta(days=days_since_1900) + datetime.timedelta(seconds=partial_day)

cnx = pyodbc.connect('....') cnx.add_output_converter(pyodbc.SQL_TYPE_TIMESTAMP, datetime_as_string) cur = cnx.cursor() cur.fast_executemany = True cur.execute("CREATE TABLE #tmptest (dt DATETIME)") cur.execute("INSERT INTO #tmptest VALUES('2023-05-17 15:00:55.23')") cur.executemany('INSERT INTO #tmptest VALUES(?)', [[datetime.datetime(2023,5,17,15,0,55,230000)]]) cur.fast_executemany = False cur.executemany('INSERT INTO #tmptest VALUES(?)', [[datetime.datetime(2023,5,17,15,0,55,230000)]]) print(cur.execute('SELECT * FROM #tmptest').fetchall())

Output: [(datetime.datetime(2023, 5, 17, 15, 0, 55, 230000),), (datetime.datetime(2023, 5, 17, 15, 0, 55),), (datetime.datetime(2023, 5, 17, 15, 0, 55, 230000),)]

fast_executemany + executemany loses fraction of seconds

[v-chojas]

Does it only happen with a temp table, and not persistent tables? If so, try enabling the UseFMTONLY option. Otherwise, post an ODBC trace so we can inspect it to see what's going on.

[zhiyongzhang87]

@v-chojas UseFMTONLY fixes the issue! thank you!