How do I copy all metadata from one database to another using a different engine in SQLAlchemy?

edit

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:

1. Create the source and target engine object using SQLAlchemy engine URL:

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

1. Load the metadata for the source database:

from sqlalchemy import MetaData

metadata = MetaData(bind= source_engine, reflect=True)

1. Create the schema in the target database using the metadata object:

metadata.create_all(bind= target_engine)

1. At this point, the tables themselves should be created with their respective columns and types, but any additional constraints such as indices, keys, and triggers will not have been created yet. You can copy these over via SQL statements, for example:

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.

1. Once this is complete, you should have a full copy of the schema and data from the source database in the target database.