There are various approaches to copying metadata from one database to another using SQLAlchemy. Here's one approach that involves creating a separate engine for the source database and target database, and then using the MetaData object to copy the schema from one to the other:
from sqlalchemy import create_engine
source_engine = create_engine('postgres://user:password@source_host/source_db')
target_engine = create_engine('mysql://user:password@target_host/target_db')
from sqlalchemy import MetaData
metadata = MetaData(bind= source_engine, reflect=True)
metadata.create_all(bind= target_engine)
with source_engine.begin() as source_conn:
for table in metadata.sorted_tables:
target_engine.execute(f'DESC {table.name}')
for constraint in table._sorted_constraints:
target_engine.execute(str(constraint).replace("CREATE", "CREATE IF NOT EXISTS"))
This code loops through all the tables in the metadata object and fetches a description of each one for the target database in order to determine its columns and primary/foreign keys. Then, it executes the create statements for any other constraints in the source database.
Asked: 2022-07-01 11:00:00 +0000
Seen: 9 times
Last updated: Sep 15 '22