从一个sqlalchemy
的连接对象和一个字符串形式的表名开始,我如何获得表属性,例如列名和数据类型.
例如,连接到sqlalchemy
中的数据库
from sqlalchemy import create_engine
conn = create_engine('mssql+pyodbc://...driver=ODBC+Driver+17+for+SQL+Server').connect()
则conn
是sqlalchemy
连接对象
In [1]: conn
Out[1]: <sqlalchemy.engine.base.Connection at 0x7feb6efef070>
How do I get table properties based on a table name as a string, eg 100?
这should起作用,但创建了一个空的DataFrame
from sqlalchemy import text
import pandas as pd
query = f"""SELECT * FROM information_schema.columns WHERE table_name='{table}'"""
df = pd.read_sql_query(text(query), conn)
In [2]: df
Out[2]:
Empty DataFrame
Columns: [TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME]
Index: []
versions:
sqlalchemy - 2.0.4
pandas - 1.5.3