CODEWITHSUNDEEP
pythonsql-serverpyodbc
pragmatic learner
pragmatic learner

Reputation: 447

How does data conversion work between pyodbc and sql server?

I'm building an automated script where I inject some data into sql server using pyodbc, with this line (basic example):

cursor.execute(sql_query, data)

Given that I've created/designed a sql server database and table locally, the data seems to be converted automatically - "under the hood" - into the required types that are specified in the table design when inserted into db.table.

I'm wondering how this works and if it's safe not to create explicit code to convert data types? I found this official microsoft docs on it, but I don't think what is written there gives me clarity on sending data from python to sql server as it states:

Python supports a limited number of data types in comparison to SQL Server. As a result, whenever you use data from SQL Server in Python scripts, SQL data might be implicitly converted to a compatible Python data type. However, often an exact conversion cannot be performed automatically and an error is returned.

This seems like it's from SQL Server -> Python, rather than Python -> SQL Server.

Upvotes: 1

Views: 928

Answers (1)

Caleb Keller
Caleb Keller

Reputation: 694

Pyodbc's GitHub wiki has a page dedicated to explaining how it converts Python objects to ODBC data types and vice versa here.

Every Python primitive and the couple standard library objects that have direct SQL equivalents (decimal, date, uuid) are converted automatically into their MS SQL counterparts when you upload data, so it shouldn't be necessary for you to do anything yourself in order to convert them.

In cases where the conversion would cause a loss of data (int -> bit for example) or the types are incompatible (bool -> date), pyodbc just throws an error, so you will be aware if that happens.

Here's the table for conversions from Python -> SQL Server (using MS SQL's name for the corresponding ODBC type):

Python Datatype Description MS SQL Datatype
None null varies*
bool boolean bit
int integer bigint
float floating point float
decimal.Decimal decimal numeric
str UTF-16LE* varchar*
bytes, bytearray binary varbinary*
datetime.date date date
datetime.time time time
datetime.datetime date & time datetime
uuid.UUID UUID / GUID uniqueidentifier

*The documentation has some extra notes about how nulls are converted, the encoding used for strings, and the sizes of binary types.

Hope this helps!

Upvotes: 3

Related Questions