SqlAlchemy Create Table Specifying Schema

Question

I want to copy a table from SQL Server to Sqlite via SqlAlchemy reflection.

from sqlalchemy import create_engine, MetaData, Table

source_engine = create_engine(...) # create sql server engine
source_connection = source_engine.connect()
source_metadata = MetaData() # (1)

destination_engine = create_engine('sqlite:///test.db', echo=True)
destination_connection = destination_engine.connect()

Table('table_name', source_metadata, autoload=True, autoload_with=source_engine) # (2)

source_metadata.create_all(destination_engine)

The above works with the default schema (dbo) but I want to specify the schema of the reflected tables in SQL Server.

When I change (1) to source_metadata = Metadata(schema='dbo') or (2) to Table(table_name, source_metadata, autoload=True, autoload_with=source_engine, schema='dbo') I get the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown database "dbo"

I know the issue is with sqlite. When I create a table in SQLite Studio like below, I get the same error:

CREATE TABLE dbo.table_name(...

but the following creates the table successfully:

CREATE TABLE [dbo.table_name](...

Finally, I have tried the below with no success:

destination_engine.execute("ATTACH DATABASE 'test.db' AS 'dbo'")

How do I create a table specifying the schema using SqlAlchemy?

user_99594 · Answer

First, sqlite does not support schemas as they are known to be in the other RDBMS. And the fact that [dbo.table_name] is created for you is just that - it is a full table name including the brackets.

Therefore, I assume you will be OK with just dropping it completely.

And to do that, just set it to None before you call create_all:

# either specifying `schema='xxx'` on the Table() or on `Metadata()` or both will work just fine
source_metadata = MetaData(schema='xxx')
table1 = Table('table_name', source_metadata, autoload=True, autoload_with=source_engine, schema='xxx')  

# unset schema
table1.schema = None

# or if you have multiple tables, you will just have to set `.schema = None` to all of them:
for tbl in src_metadata.tables.values():
    tbl.schema = None

# now create the tables on the destination engine
source_metadata.create_all(destination_engine)

Alternatively, if you cannot drop the schema, you can just change table names with the prefix:

for tbl in src_metadata.tables.values():
    tbl.name = tbl.schema + '_' + tbl.name
    tbl.schema = None

And in any event you might need to fix all the ForeignKeys as well... so it is much more work than just changing the schema ;)

And I am afraid the schema might not be the last problem whey copying the data from one type DB to another. Incompatible data types will probably follow. If they do, read Reflecting with Database-Agnostic Types. Also with renaming tables/schemas, you might need to take care of re-linking ForeignKeys, Index's etc.

user_15745129 · Answer

We can use the Inspector feature available in the SqlAlchemy API to create new tables. The schema, table, and respective constraints are taken care by the "Inspector API"-

from sqlalchemy import inspect,create_engine

engine = create_engine('....')

ins=inspect(engine)

Refer the below doc for more info: https://docs.sqlalchemy.org/en/14/core/reflection.html?highlight=inspector#fine-grained-reflection-with-inspector

SqlAlchemy Create Table Specifying Schema

2 Answers