SQLAlchemy accessing column types from query results

Question

I am connecting to a SQL Server database using SQLAlchemy (with the pymssql driver).

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

which results in:

(('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))

As per PEP 249 (cursor's .description attribute):

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.

I am assuming the integers (1, 1, 4, 3, 3) are column types.

My two questions:

1. How to map these integers to data types (like char, integer, etc.)?
2. Are these SQL data types? If no, is it possible to get the SQL data types?

FWIW, I get the same result when using raw_connection() instead of connect().

Came across three questions along similar lines (which do not answer this specific question). I need to use the connect() + execute() approach.

• 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?

Lisk

Answer 1

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

SQL Server function sys.dm_exec_describe_first_result_set could be used to get SQL column's data type directly for provided query:

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

In your example:

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

For:

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

Chavaree

Thanks, I was hoping that the answer would be available in the metadata somewhere, but this is the best I have. – Lisk

Note: Nov 2020 was around sqlalchemy 1.2 according to sqlalchemy.org/download.html – Given

Answer 2

It might be the driver itself. Below I have almost the same code as yours, just using the pyodbc driver on AdventureWorks. I picked a table with lots of different datatypes and they are all showing.

import sqlalchemy
conn_string = conn_string = f'mssql+pyodbc://{username}:{pwd}@{instance}/AdventureWorksLT2017?driver=ODBC+Driver+17+for+SQL+Server'
sql = 'SELECT TOP 10 * FROM SalesLT.Product;'

engine = sqlalchemy.create_engine(conn_string)
connection = engine.connect()

result = connection.execute(sql)
print(result.cursor.description)

Output:

(('ProductID', <class 'int'>, None, 10, 10, 0, False), ('Name', <class 'str'>, None, 50, 50, 0, False), ('ProductNumber', <class 'str'>, None, 25, 25, 0, False), ('Color', <class 'str'>, None, 15, 15, 0, True), ('StandardCost', <class 'decimal.Decimal'>, None, 19, 19, 4, False), ('ListPrice', <class 'decimal.Decimal'>, None, 19, 19, 4, False), ('Size', <class 'str'>, None, 5, 5, 0, True), ('Weight', <class 'decimal.Decimal'>, None, 8, 8, 2, True), ('ProductCategoryID', <class 'int'>, None, 10, 10, 0, True), ('ProductModelID', <class 'int'>, None, 10, 10, 0, True), ('SellStartDate', <class 'datetime.datetime'>, None, 23, 23, 3, False), ('SellEndDate', <class 'datetime.datetime'>, None, 23, 23, 3, True), ('DiscontinuedDate', <class 'datetime.datetime'>, None, 23, 23, 3, True), ('ThumbNailPhoto', <class 'bytearray'>, None, 0, 0, 0, True), ('ThumbnailPhotoFileName', <class 'str'>, None, 50, 50, 0, True), ('rowguid', <class 'str'>, None, 36, 36, 0, False), ('ModifiedDate', <class 'datetime.datetime'>, None, 23, 23, 3, False))

Are you able to try this driver as a comparison?

Favouritism

Thanks, this is helpful. But I need to use pymssql. – Lisk

Answer 3

Looking at PEP249: type_code does not look like to be the same through different DB type.

So this answer will be focus on MS SQL Server.

1. How to map these integers to data types (like char, integer, etc.)?

You can create a dict of type_code to type_object using the following code:

import inspect
import pymssql

code_map = {
    type_obj.value: (type_name, type_obj)
    for type_name, type_obj
    in inspect.getmembers(
        pymssql,
        predicate=lambda x: isinstance(x, pymssql.DBAPIType),
    )
}

This will produce following dict:

{2: ('BINARY', <DBAPIType 2>),
 4: ('DATETIME', <DBAPIType 4>),
 5: ('DECIMAL', <DBAPIType 5>),
 3: ('NUMBER', <DBAPIType 3>),
 1: ('STRING', <DBAPIType 1>)}

Unfortunately, I do not have access to a running instance of MS SQL Server. So I am not able to check if type results match with your example.

2. Are these SQL data types? If no, is it possible to get the SQL data types?

Looking at the PEP and this result: this fields are not SQL data types. This are "Type Object".

DB API does not look like to provide methods / functions to inspect query results metadata. The API just provide a way to bind data types from SQL to python types.

If you need to get exact SQL data type, then you must write server specific SQL query.

Timorous

Thank you, but it looks like this is just the mapping from the code to the data type. I am interested in the SQL data types specifically. – Lisk