英文:
How does data conversion work between pyodbc and sql server?
问题
我正在构建一个自动化脚本，其中我使用pyodbc将一些数据注入到SQL Server中，使用以下代码行（基本示例）：
cursor.execute(sql_query, data)
鉴于我已在本地创建/设计了一个SQL Server数据库和表，数据似乎会自动转换为表设计中指定的所需类型，当插入到db.table时。
我想知道这是如何工作的，以及是否安全不创建显式代码来转换数据类型？我在官方微软文档1上找到了相关信息，但我认为那里写的内容并没有明确解释如何从Python发送数据到SQL Server，因为它指出：
Python支持的数据类型数量有限，与SQL Server相比。因此，每当在Python脚本中使用来自SQL Server的数据时，SQL数据可能会被隐式转换为兼容的Python数据类型。然而，通常无法自动执行精确的转换，会返回错误。
这似乎是从SQL Server -> Python的角度来看，而不是从Python -> 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.
答案1
得分: 3
Pyodbc的GitHub维基有一页专门解释了如何将Python对象转换为ODBC数据类型以及反之的页面在这里。
每个Python原语以及具有直接SQL等效项（decimal、date、uuid）的标准库对象，在上传数据时会自动转换为其MS SQL对应项，因此您不需要自己进行任何转换。
在转换会导致数据丢失（例如int -> bit）或类型不兼容（bool -> date）的情况下，pyodbc会抛出错误，因此您会得到通知。
以下是从Python到SQL Server的转换表（使用MS SQL对应ODBC类型的名称）：
| Python数据类型 | 描述 | MS SQL数据类型 |
|---|---|---|
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 |
日期和时间 | datetime |
uuid.UUID |
UUID / GUID | uniqueidentifier |
*文档中还有一些关于如何转换nulls，字符串的编码以及二进制类型大小的额外说明。
希望这有所帮助！
英文:
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!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论