SQLAlchemy mengakses tipe kolom dari hasil query

Question

Saya menyambung ke database SQL Server menggunakan SQLAlchemy (dengan 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

yang mengakibatkan:

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

Sesuai PEP 249 ( .descriptionatribut kursor ):

Dua item pertama (name dan type_code) adalah wajib, lima lainnya opsional dan disetel ke None jika tidak ada nilai yang berarti dapat diberikan.

Saya mengasumsikan integer (1, 1, 4, 3, 3)adalah tipe kolom.

Dua pertanyaan saya:

1. Bagaimana cara memetakan bilangan bulat ini ke tipe data (seperti char, integer, dll.)?
2. Apakah tipe data SQL ini? Jika tidak, apakah mungkin untuk mendapatkan tipe data SQL?

FWIW, saya mendapatkan hasil yang sama saat menggunakan, raw_connection()bukan connect().

Menemukan tiga pertanyaan dengan garis yang serupa (yang tidak menjawab pertanyaan khusus ini). Saya perlu menggunakan pendekatan connect()+ execute().

• SQLAlchemy mendapatkan tipe data kolom dari hasil kueri
• Bagaimana cara mendapatkan tipe sql kolom yang dikueri oleh sqlalchemy
• Konversi mudah antara tipe kolom SQLAlchemy dan tipe data python?

Answer 1

Jika tidak, apakah mungkin untuk mendapatkan tipe data SQL?

Fungsi SQL Server sys.dm_exec_describe_first_result_set dapat digunakan untuk mendapatkan tipe data kolom SQL secara langsung untuk kueri yang disediakan:

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

Dalam contoh Anda:

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

Untuk:

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 <> demo biola

Answer 2

Melihat PEP249 : type_codetidak terlihat sama melalui tipe DB yang berbeda.

Jadi jawaban ini akan difokuskan pada MS SQL Server.

1. Bagaimana cara memetakan bilangan bulat ini ke tipe data (seperti char, integer, dll.)?

Anda dapat membuat dikt type_codeuntuk type_objectmenggunakan kode berikut:

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

Ini akan menghasilkan dikt berikut:

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

Sayangnya, saya tidak memiliki akses ke contoh berjalan dari MS SQL Server. Jadi saya tidak dapat memeriksa apakah hasil jenis cocok dengan contoh Anda.

1. Apakah tipe data SQL ini? Jika tidak, apakah mungkin untuk mendapatkan tipe data SQL?

Melihat PEP dan hasil ini: bidang ini bukan tipe data SQL. Ini adalah "Objek Tipe".

DB API tampaknya tidak menyediakan metode / fungsi untuk memeriksa metadata hasil kueri. API hanya menyediakan cara untuk mengikat tipe data dari SQL ke tipe python.

Jika Anda perlu mendapatkan tipe data SQL yang tepat, Anda harus menulis kueri SQL khusus server.

Answer 3

Mungkin pengemudi itu sendiri. Di bawah ini saya memiliki kode yang hampir sama dengan milik Anda, hanya menggunakan driver pyodbc di AdventureWorks. Saya memilih tabel dengan banyak tipe data berbeda dan semuanya ditampilkan.

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)

Keluaran:

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

Apakah Anda dapat mencoba driver ini sebagai perbandingan?