python - SQLAlchemy 从查询结果中访问列类型

Question

我正在使用 SQLAlchemy(使用 pymssql 驱动程序)连接到 SQL Server 数据库。

import sqlalchemy

conn_string = f'mssql+pymssql://{uid}:{pwd}@{instance}/?database={db};charset=utf8'
sql = 'SELECT * FROM FAKETABLE;'

engine = sqlalchemy.create_engine(conn_string)
connection = engine.connect()
result = connection.execute(sql)
result.cursor.description

这导致:

(('col_1', 1, None, None, None, None, None),
 ('col_2', 1, None, None, None, None, None),
 ('col_3', 4, None, None, None, None, None),
 ('col_4', 3, None, None, None, None, None),
 ('col_5', 3, None, None, None, None, None))

根据 PEP 249 (光标的 .description 属性):

The first two items (name and type_code) are mandatory, the other five are optional and are set to None if no meaningful values can be provided.

我假设整数 (1, 1, 4, 3, 3)是列类型。
我的两个问题:

如何将这些整数映射到数据类型(如 char、整数等)？

这些是 SQL 数据类型吗？如果没有，是否可以获取 SQL 数据类型？

FWIW，我在使用 raw_connection() 时得到相同的结果而不是 connect() .
遇到了类似的三个问题(不回答这个特定问题)。我需要使用 connect() + execute()方法。

SQLAlchemy getting column data types of query results

How to get columns' sql type queried by sqlalchemy

Easy convert betwen SQLAlchemy column types and python data types?

Answer 1

If no, is it possible to get the SQL data types?

SQL Server 函数 sys.dm_exec_describe_first_result_set 可用于直接为提供的查询获取 SQL 列的数据类型:

SELECT column_ordinal, name, system_type_name, *
FROM sys.dm_exec_describe_first_result_set('here goes query', NULL, 0) ; 

在你的例子中:

sql = """SELECT column_ordinal, name, system_type_name 
    FROM sys.dm_exec_describe_first_result_set('SELECT * FROM FAKETABLE', NULL, 0) ;"""

为了:

CREATE TABLE FAKETABLE(id INT, d DATE, country NVARCHAR(10));

SELECT column_ordinal, name, system_type_name 
FROM sys.dm_exec_describe_first_result_set('SELECT * FROM FAKETABLE', NULL, 0) ;

+-----------------+----------+------------------+
| column_ordinal  |  name    | system_type_name |
+-----------------+----------+------------------+
|              1  | id       | int              |
|              2  | d        | date             |
|              3  | country  | nvarchar(10)     |
+-----------------+----------+------------------+

db<>fiddle demo