SQL Server SQLAlchemy. Show tables in existing schema

ovfsdjhp 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(87)

I want to read a table from SQL Server using SQLAlchemy. This table already exists and has primary key. However, it's located in the schema 'my_schema'. And I can't reach this table.

On the contrary, using the following code I can reach a table from another database, which does not have schemas:

from sqlalchemy import create_engine, MetaData, Column, String, Table
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
no_schema_engine = create_engine(
"firebird+fdb://%(user)s:%(pwd)s@%(host)s:%(port)d/%(path)s?charset=%(charset)s" % insert_params,
encoding=insert_params['charset']) 
metadata = MetaData()  
my_table= Table('my_table_name',metadata, Column('id', String, primary_key=True), autoload=True, autoload_with=no_schema_engine )
session = Session(no_schema_engine)
sample_row = session.query(my_table).first()
print(sample_row)
>> (1, datetime.datetime(2020, 9, 8, 22, 58, 23, 947000))

When I change the engine to connect to SQL Server and to copy the same table (but now it has schema), it throws an error sqlalchemy.exc.NoSuchTableError: my_table_name . I use the same code as above, except I change the engine and the row

my_table= Table('my_table_name',metadata, Column('id', String, primary_key=True), autoload=True, autoload_with=schema_engine, schema='my_schema')

It's also important to note that SQLAlchemy actually can see all the schemas in SQL Server database but can't see tables:

from sqlalchemy import inspect
inspector = inspect(source_engine)
print(inspector.get_table_names())
>> []
schemas = inspector.get_schema_names()
print(schemas)
>> ['my_schema_1', 'my_schema_2', ...]

sql-server

来源：https://stackoverflow.com/questions/71149424/sqlalchemy-show-tables-in-existing-schema

1条答案

按热度按时间

ar5n3qh51#

I had a similar problem. I'm connecting to a SQL Server and manipulating with SQLAlchemy.

Tables with schema "dbo" were retrieving with method get_table_names() . Notice that 'dbo' is the default schema. Tables with other schema were not retrieved. I needed to specify the schema name as a parameter in the method ( get_table_names(schema=schema_name) .

I reproduce the steps that I use for retrieving my table names from SQL Server:

import urllib
from sqlalchemy import create_engine, inspect

# Params are defined in my environment
params = urllib.parse.quote_plus(
    f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};UID={USERNAME};PWD={PASSWORD};Trusted_Connection=yes;"

# Create URL with the params
url = "mssql+pyodbc://?odbc_connect={}".format(params)

# Create an Engine
engine = create_engine(url, echo=True)

# Create an inspector
inspector = inspect(engine)

# Retrieve tables with schema 'dbo'

tables_dbo = inspector.get_table_names()

# Retrieve tables with other schema
schema_name = "other_schema"
tables_other_schema = inspector.get_table_names(schema=schema_name)

As I can check from your examples, you could get your schema names and then loop the list to get the table names in each schema.

# Get schema names
schema_names = inspector.get_schema_names()
table_names = []

# Add tables names to a list
for schema in schema_names:
    table_names += inspector.get_table_names(schema=schema)

print(table_names)

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server SQLAlchemy. Show tables in existing schema

1条答案

相关问题

热门标签

最新问答