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 .
1条答案
按热度按时间3gtaxfhh1#
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!