Microsoft Python Driver for SQL Server

#1

Post by kermatt » Wed, Sep 17, 2025, 3:28 PM UTC

Microsoft Python Driver for SQL Server
github.com

Re: Microsoft Python Driver for SQL Server

#2

Post by zurfer » Wed, Sep 17, 2025, 4:02 PM UTC

This is really timely. I just needed to build a connector to Azure Fabric and it requires ODBC 18 which in turn requires openssl to allow deprecated and old versions of TLS. Now I can revert all of that and make it clean :)

Re: Microsoft Python Driver for SQL Server

#3

Post by abirch » Wed, Sep 17, 2025, 4:15 PM UTC

What my workself would love is to easily dump Pandas or Polar data frames to SQL Tables in SQL Server as fast as possible. I see this bcp, but I don't see an example of uploading a large panda dataframe to SQL Server.

Re: Microsoft Python Driver for SQL Server

#4

Post by A4ET8a8uTh0_v2 » Wed, Sep 17, 2025, 4:22 PM UTC

abirch wrote: post #3

What my workself would love is to easily dump Pandas or Polar data frames to SQL Tables in SQL Server as fast as possible. I see this bcp, but I don't see an example of uploading a large panda dataframe to SQL Server.

Honestly, what I find myself doing more often than not lately is not having problems with the actual data/code/schema whatever, but, instead, fighting with layers of bureaucracy, restrictions, data leakage prevention systems, specific file limitations imposed by the previously listed items...

There are times I miss being a kid and just doing things.

Re: Microsoft Python Driver for SQL Server

#5

Post by __mharrison__ » Wed, Sep 17, 2025, 4:29 PM UTC

Very cool. Used to be a huge pain to connect to sqlserver from Python (especially non Windows platforms).

Re: Microsoft Python Driver for SQL Server

#6

Post by qsort » Wed, Sep 17, 2025, 4:55 PM UTC

__mharrison__ wrote: post #5

Very cool. Used to be a huge pain to connect to sqlserver from Python (especially non Windows platforms).

I do expect this package to make connecting easier, but it was okay even before. ODBC connectivity via pyodbc has always worked quite well and it wasn't really any different when compared to any other ODBC source. I'm more on the data engineering side and I'm very picky about this kind of stuff, I don't expect the average user would even notice besides the initial pain of configuring ODBC from scratch.

Re: Microsoft Python Driver for SQL Server

#7

Post by sceadu » Wed, Sep 17, 2025, 4:58 PM UTC

abirch wrote: post #3

What my workself would love is to easily dump Pandas or Polar data frames to SQL Tables in SQL Server as fast as possible. I see this bcp, but I don't see an example of uploading a large panda dataframe to SQL Server.

You might be able to do it with ibis. Don't know about the performance though

Re: Microsoft Python Driver for SQL Server

#8

Post by qsort » Wed, Sep 17, 2025, 5:02 PM UTC

abirch wrote: post #3

What my workself would love is to easily dump Pandas or Polar data frames to SQL Tables in SQL Server as fast as possible. I see this bcp, but I don't see an example of uploading a large panda dataframe to SQL Server.

How large? In many cases dumping to file and bulk loading is good enough. SQL Server in particular has openrowsets that support bulk operations, which is especially handy if you're transferring data over the network.

Re: Microsoft Python Driver for SQL Server

#9

Post by abirch » Wed, Sep 17, 2025, 5:04 PM UTC

sceadu wrote: post #7

abirch wrote: post #3

What my workself would love is to easily dump Pandas or Polar data frames to SQL Tables in SQL Server as fast as possible. I see this bcp, but I don't see an example of uploading a large panda dataframe to SQL Server.

You might be able to do it with ibis. Don't know about the performance though

Thank you, I'll look into this. Yes performance is the main driver when some data frames have millions of rows.

Re: Microsoft Python Driver for SQL Server

#10

Post by abirch » Wed, Sep 17, 2025, 5:09 PM UTC

qsort wrote: post #8

abirch wrote: post #3

What my workself would love is to easily dump Pandas or Polar data frames to SQL Tables in SQL Server as fast as possible. I see this bcp, but I don't see an example of uploading a large panda dataframe to SQL Server.

How large? In many cases dumping to file and bulk loading is good enough. SQL Server in particular has openrowsets that support bulk operations, which is especially handy if you're transferring data over the network.

Millions of rows large. I tried doing the openrowsets but encountered permission issues with the shared directory. Using fast_executemany with sqlalchemy has helped, but sometimes it's a few minutes. I tried bcp as well locally but IT has not wanted to deploy it to production.