Repository navigation
mssql-python 1.7.1 and 1.8.0 both fail on executemany #609
Description
Activity
Hi Ravi Krishna (@sravikrishna), thank you for opening this issue!
Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.
If you have additional information to share, please feel free to update the issue.
Thank you for your patience!
Ravi Krishna (@sravikrishna) - This may unblock you while we get to the bottom of your issue.
Quickstart: Bulk copy with the mssql-python driver for Python
Thanks David.
Yes I am aware of bulk_copy and I am using it for file copy purposes.
But this is direct table to table copy.Update: I have an old version of the script which uses pyodbc. I was very happy with pyodbc fast_executemany feature. But pyodbc can not take datetime offset and few other variables and throws out -155 error. mssql-python is an improvement there.
Anyhow this table does not contain any data type which can be -155 issue. So I copied the same using the pyodbc version. Two things:
- It is noticeably faster than mssql-python. There is no difference in the code except the connection driver.
- I didn't get any error and the copy completed successfully.
This rules out any issue with the table.
here is the relevant code, which is same for pyodbc and mssql-python.
t_cursor.fast_executemany = True
s_cursor.execute(sql_stmt)
while rows := s_cursor.fetchmany(5000) :
t_cursor.executemany(ins_sql,rows)
t_cxn.commit()
rows_copied += len(rows)
write_log(str(rows_copied) + " rows committed")Thanks David.
Yes I am aware of bulk_copy and I am using it for file copy purposes. But this is direct table to table copy.
Update: I have an old version of the script which uses pyodbc. I was very happy with pyodbc fast_executemany feature. But pyodbc can not take datetime offset and few other variables and throws out -155 error. mssql-python is an improvement there.
Anyhow this table does not contain any data type which can be -155 issue. So I copied the same using the pyodbc version. Two things:
- It is noticeably faster than mssql-python. There is no difference in the code except the connection driver.
- I didn't get any error and the copy completed successfully.
This rules out any issue with the table.
here is the relevant code, which is same for pyodbc and mssql-python.
t_cursor.fast_executemany = True s_cursor.execute(sql_stmt) while rows := s_cursor.fetchmany(5000) : t_cursor.executemany(ins_sql,rows) t_cxn.commit() rows_copied += len(rows) write_log(str(rows_copied) + " rows committed")
We've definitely got an issue to dig into and we'll do that.
I'm saying you could do something like this for now:
s_cursor.execute(sql_stmt) def source_rows(): while rows := s_cursor.fetchmany(5000): yield from rows result = t_cursor.bulkcopy( "dbo.MyTargetTable", source_rows(), batch_size=5000, # commit boundary; mirrors the old per-fetch commit table_lock=True, # enables minimal logging where eligible ) rows_copied = result["rows_copied"] write_log(f"{rows_copied} rows committed (batches={result['batch_count']}, " f"elapsed={result['elapsed_time']}s)")
It may even end up being faster than executemany, depending on how many rows you're dealing with. If you bump up the batch size, you'll probably see it pick up even more speed.
- Oh I did not know that bulk_copy accepts rows. Even 1.71.1 did not accept rows insisting on only tuple format. Let me try. Thanks.…-- Sent from phone. On Sat, May 30, 2026 at 10:42 AM, David ***@***.***> wrote: dlevy-msft-sql left a comment (microsoft/mssql-python#609) Thanks David. Yes I am aware of bulk_copy and I am using it for file copy purposes. But this is direct table to table copy. Update: I have an old version of the script which uses pyodbc. I was very happy with pyodbc fast_executemany feature. But pyodbc can not take datetime offset and few other variables and throws out -155 error. mssql-python is an improvement there. Anyhow this table does not contain any data type which can be -155 issue. So I copied the same using the pyodbc version. Two things: - It is noticeably faster than mssql-python. There is no difference in the code except the connection driver. - I didn't get any error and the copy completed successfully. This rules out any issue with the table. here is the relevant code, which is same for pyodbc and mssql-python. t_cursor.fast_executemany = True s_cursor.execute(sql_stmt) while rows := s_cursor.fetchmany(5000) : t_cursor.executemany(ins_sql,rows) t_cxn.commit() rows_copied += len(rows) write_log(str(rows_copied) + " rows committed") We've definitely got an issue to dig into and we'll do that. I'm saying you could do something like this for now: s_cursor.execute(sql_stmt) def source_rows(): while rows := s_cursor.fetchmany(5000): yield from rows result = t_cursor.bulkcopy( "dbo.MyTargetTable", source_rows(), batch_size=5000, # commit boundary; mirrors the old per-fetch commit table_lock=True, # enables minimal logging where eligible ) rows_copied = result["rows_copied"] write_log(f"{rows_copied} rows committed (batches={result['batch_count']}, " f"elapsed={result['elapsed_time']}s)") It may even end up being faster than executemany, depending on how many rows you're dealing with. If you bump up the batch size, you'll probably see it pick up even more speed. — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications, keep track of coding agent tasks and review pull requests on the go with GitHub Mobile for iOS and Android. Download it today! You are receiving this because you were mentioned.Message ID: ***@***.***>
as expected it did not work.
File "D:\py3.13.12\Lib\site-packages\mssql_python\cursor.py", line 3036, in bulkcopy
raise type(e)(str(e)) from NoneValueError: Expected tuple, got: 'Row' object cannot be cast as 'tuple'
as expected it did not work.
File "D:\py3.13.12\Lib\site-packages\mssql_python\cursor.py", line 3036, in bulkcopy
raise type(e)(str(e)) from NoneValueError: Expected tuple, got: 'Row' object cannot be cast as 'tuple'
Oops...sorry about that! It looked like it was working so I didn't check the table. It was exiting on an error and not printing it.
from mssql_python import connect CONNECTION_STRING = ( "Server=<server>;" "Database=AdventureWorks2025;" "Encrypt=yes;" "TrustServerCertificate=yes;" "Uid=<uid>;" "Pwd=<pwd>;" ) sql_stmt = "SELECT * FROM Person.Person" s_cxn = connect(CONNECTION_STRING) t_cxn = connect(CONNECTION_STRING) s_cursor = s_cxn.cursor() t_cursor = t_cxn.cursor() t_cursor.execute(""" IF OBJECT_ID('Person.Person_copy', 'U') IS NULL SELECT top 0 * INTO Person.Person_copy FROM Person.Person; ELSE TRUNCATE TABLE Person.Person_copy; """) t_cxn.commit() s_cursor.execute(sql_stmt) def source_rows(): while rows := s_cursor.fetchmany(5000): for row in rows: yield tuple(row) column_names = [d[0] for d in s_cursor.description] result = t_cursor.bulkcopy( "Person.Person_copy", source_rows(), batch_size=5000, # commit boundary; mirrors the old per-fetch commit table_lock=True, # enables minimal logging where eligible column_mappings=column_names, keep_identity=True, ) rows_copied = result["rows_copied"] print( f"{rows_copied} rows committed (batches={result['batch_count']}, " f"elapsed={result['elapsed_time']}s)" )
19972 rows committed (batches=4, elapsed=2.8929122s)Ravi Krishna (@sravikrishna) Thanks for the detailed report, we've been able to reproduce this on our end. There's a type mismatch in the executemany auto-detection path for NUMERIC columns that surfaces when a batch contains values outside the internal MONEY range. We're prioritizing a fix and will update this issue once it's available.
- added 4 commits that reference this issue
on Jun 1, 2026 The PR has been merged this will ne available in our next release
Thank you for raising issue and trying out our driver!- added a commit that references this issue
on Jun 5, 2026 I tested it with the new 1.9.0 and I see that the bug has been fixed. However, the performance is very disappointing. I have the same code where I can pick the driver as an option. pyodbc seems to be about 75 times faster. pyodbc takes 3 seconds to read 5000 rows and write to a different instance, followed by commit. mssql-python is taking 2min or more.
python 3.13.13 on Windows
mssql-python 1.7.1/1.8.0
I have a python script to copy a table from one env to another. The script makes two connections, one to the source database and one to the target database. Reads rows on the source as fetchmany(5000) and then writes on the target side as one executemany with fast_executemany set to True.
Works great most of the time. However I am seeing this issue with one table.
After copying about 923,000 rows, it errors out
ret = ddbc_bindings.SQLExecuteMany(
self.hstmt, operation, columnwise_params, parameters_type, row_count, encoding_settings
)
RuntimeError: Parameter's object type does not match parameter's C type. paramIndex - 10, C type - SQL_C_NUMERIC
Seem to happen at the same row.
I understand this information may not be enuf.
Pls advise what information should I provide for the support team to find a resolution.