SQLAlchemy che accede ai tipi di colonna dai risultati della query

Question

Mi sto collegando a un database SQL Server utilizzando SQLAlchemy (con il pymssqldriver).

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

che si traduce 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))

Secondo PEP 249 ( .descriptionattributo del cursore ):

I primi due elementi (nome e tipo_codice) sono obbligatori, gli altri cinque sono facoltativi e sono impostati su Nessuno se non è possibile fornire valori significativi.

Suppongo che gli interi (1, 1, 4, 3, 3)siano tipi di colonna.

Le mie due domande:

1. Come mappare questi numeri interi ai tipi di dati (come char, integer, ecc.)?
2. Sono questi tipi di dati SQL? In caso negativo, è possibile ottenere i tipi di dati SQL?

FWIW, ottengo lo stesso risultato usando al raw_connection()posto di connect().

Mi sono imbattuto in tre domande lungo linee simili (che non rispondono a questa domanda specifica). Devo usare l' approccio connect()+ execute().

• SQLAlchemy ottiene i tipi di dati della colonna dei risultati della query
• Come ottenere il tipo sql delle colonne interrogato da sqlalchemy
• Facile conversione tra tipi di colonna SQLAlchemy e tipi di dati Python?

Answer 1

In caso negativo, è possibile ottenere i tipi di dati SQL?

La funzione SQL Server sys.dm_exec_describe_first_result_set può essere utilizzata per ottenere direttamente il tipo di dati della colonna SQL per la query fornita:

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

Nel tuo esempio:

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

Per:

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

Answer 2

Guardando PEP249 : type_codenon sembra essere lo stesso attraverso diversi tipi di DB.

Quindi questa risposta si concentrerà su MS SQL Server.

1. Come mappare questi numeri interi ai tipi di dati (come char, integer, ecc.)?

Puoi creare un dict di type_codeto type_objectutilizzando il codice seguente:

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),
    )
}

Questo produrrà il seguente dict:

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

Sfortunatamente, non ho accesso a un'istanza in esecuzione di MS SQL Server. Quindi non sono in grado di verificare se i risultati del tipo corrispondono al tuo esempio.

1. Sono questi tipi di dati SQL? In caso negativo, è possibile ottenere i tipi di dati SQL?

Guardando il PEP e questo risultato: questi campi non sono tipi di dati SQL. Questo è "Type Object".

L'API DB non sembra fornire metodi / funzioni per ispezionare i metadati dei risultati delle query. L'API fornisce solo un modo per associare i tipi di dati da SQL a tipi python.

Se è necessario ottenere il tipo di dati SQL esatto, è necessario scrivere una query SQL specifica del server.

Answer 3

Potrebbe essere il driver stesso. Di seguito ho quasi lo stesso codice del tuo, usando solo il driver pyodbc su AdventureWorks. Ho scelto un tavolo con molti tipi di dati diversi e sono tutti visualizzati.

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)

Produzione:

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

Riesci a provare questo driver come confronto?