sqlalchemy accessing column types from query results

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) |
   + -- -- -- -- -- -- -- -- - + -- -- -- -- -- + -- -- -- -- -- -- -- -- -- +

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

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

Suggestion : 2

Last Updated : 28 Feb, 2022,Geeks-Premier-League-2022

SQLAlchemy is an awesome and easy-to-use python module that is used to connect python and SQL databases together increasing the powers of any programmer. To install SQLAlchemy, run the following command in your terminal:

pip install sqlalchemy pymysql

Let us fetch all the names using SQLAlchemy and see the result obtained. The SQL query will look like this:

SELECT name FROM student;

The SQL query will look like this:

SELECT section FROM student WHERE sno IN(1, 2, 6);

Suggestion : 3

It adds one or more column expressions to the list of result columns to be returned.,It adds a mapped entity to the list of result columns to be returned.,It applies one or more ORDER BY criterion to the query and returns the newly resulting Query.,It applies one or more GROUP BY criterion to the query and return the newly resulting Query

Query objects are initially generated using the query() method of the Session as follows −

q = session.query(mapped class)

Following statement is also equivalent to the above given statement −

q = Query(mappedClass, session)

The query object has all() method which returns a resultset in the form of list of objects. If we execute it on our customers table −

result = session.query(Customers).all()

The result object can be traversed using For loop as below to obtain all records in underlying customers table. Here is the complete code to display all records in Customers table −

from sqlalchemy
import Column, Integer, String
from sqlalchemy
import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative
import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)

address = Column(String)
email = Column(String)

from sqlalchemy.orm
import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).all()

for row in result:
   print("Name: ", row.name, "Address:", row.address, "Email:", row.email)

Python console shows list of records as below −

Name: Ravi Kumar Address: Station Road Nanded Email: ravi @gmail.com
Name: Komal Pande Address: Koti, Hyderabad Email: komal @gmail.com
Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath @gmail.com
Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk @gmail.com

Suggestion : 4

We’ll be using the basic functionality of SQLAlchemy which is the SQL Expression Language to create some metadata that will contain a number of related modules (or objects) that define our new book database table:,At the top we define metadata, then we pass that into the Table() method, where we give our table the name book. Within this, we define each column, along with important attributes like data type and primary_key.,With the engine created, we now need to use the .create_all() method of our metadata object and pass the engine connection to it, which will automatically cause SQLAlchemy to generate our table for us, as seen above.,Once our table(s) are defined and associated with our metadata object, we need to create a database engine with which we can connect. This is accomplished using the create_engine function.

import sqlalchemy
from sqlalchemy
import create_engine
from sqlalchemy
import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy
import inspect

print sqlalchemy.__version__
Out[ * ]: 1.0 .9

metadata = MetaData()
books = Table('book', metadata,
   Column('id', Integer, primary_key = True),
   Column('title', String),
   Column('primary_author', String),
)

engine = create_engine('sqlite:///bookstore.db')
metadata.create_all(engine)

engine = create_engine('sqlite:///bookstore.db')

engine = create_engine('postgresql://user:password@host/database')

inspector = inspect(engine)
inspector.get_columns('book')
Out[ * ]: [{
      'autoincrement': True,
      'default': None,
      'name': u 'id',
      'nullable': False,
      'primary_key': 1,
      'type': INTEGER()
   },
   {
      'autoincrement': True,
      'default': None,
      'name': u 'title',
      'nullable': True,
      'primary_key': 0,
      'type': VARCHAR()
   },
   {
      'autoincrement': True,
      'default': None,
      'name': u 'primary_author',
      'nullable': True,
      'primary_key': 0,
      'type': VARCHAR()
   }
]