Qfyilyi

I have a database x with some amount of data filled in every tables. I want to create a copy of that database (with same schema and exact data). First I create a Declaritive Base class of x using automap_base.

from sqlalchemy.ext.automap import automap_base

from sqlalchemy.orm import Session as s



def name_for_scalar_relationship(base, local_cls, referred_cls, constraint):

    name = referred_cls.__name__.lower() + "_ref"

    return name



Base = automap_base()



# engine, refering to the original database

engine = create_engine("mysql+pymysql://root:password1@localhost:3306/x")



# reflect the tables

Base.prepare(engine, reflect=True, name_for_scalar_relationship=name_for_scalar_relationship)



Router = Base.classes.router

########check the data in Router table

session = s(engine)

r1 = session.query(Router).all()

for n in r1:

    print(n.name)   #This returns all the router names

Getting some help from here I use alembic to upgrade the database y located at different place mysql+pymysql://anum:Anum-6630@localhost:3306/y.

from sqlalchemy.orm import sessionmaker as sm

from sqlalchemy import create_engine

from alembic import op



# revision identifiers, used by Alembic.

revision = 'fae98f65a6ff'

down_revision = None

branch_labels = None

depends_on = None





def upgrade():

    bind = op.get_bind()

    session = sm(bind=bind)

    Base.metadata.create_all(bind=bind)



    # session._add_bind(session, bind=bind)

    session.add(Router(id=uuid.uuid().bytes, serial="Test1"))

    session.commit()

The line Base.metadata.create_all(bind=bind) actually add all the tables (including proper FK constraints) into database y, but all the tables are empty, except one entry in Router table which I added manually. I tried using create_all() but that didn't worked aswel.
Is there a way to copy all the data from x to y database ?

Since no one answered, here is my wild method that does the copying:
Because the tables needs to be created in order (to avoid FK constraints errors), I have to define an ordered-list containing each table

Slow and NOT reliable solution:

allTables = ["tableA", 

             "tableB", # <table B points to FK constraint of tableA>

             "tableC", # <table C points to FK constraint of tableB>

             ...]



def copyAllContent():

    global allTables

    s = Session(bind=origEngine)  # session bind to original table

    se = Session(bind=op.get_bind()) # session bind to cloned table (currently empty)

try:

    for table in allTables:

        # print(table)

        rows = s.query(Base.classes._data[table]).all()

        for row in rows:

            local_object = se.merge(row)  #merging both sessions

            se.add(local_object)

            se.commit()

except Exception as e:

    print(e)

The above method worked for most of the tables, but not all. e.g. table router existed in original database, but still I get errors in s.query(Base.classes._data[table]).all() no key exists with name router. Haven't got enough time to dig down to the solution for that.

FAST and Reliable solution:

Later on I found from here another FAST and quiet reliable solution using mysqldump

#copy sql dump from x database

mysqldump --column-statistics=0 -P 8000 -h localhost -u root -p --hex-blob x > x_dump.sql

The above command-line mysqldump command creates a sql dump file named x_dump.sql which contains all the necessary SQL scripts required to re-generate your database. Now All we need to do is apply this sql dump file to another database y

#clone the database contents into y database

mysql -P 3306 -h localhost -u anum -p y < x_dump.sql

Here is pythonic version of doing the same

import subprocess



#copy sql dump from x database - blocking call (use Popen for non-blocking)

print(subprocess.call(["mysqldump", "--column-statistics=0", '-P', '8000', '-h', 'localhost', '-u', '<user>', '-p<password>',

                        '--hex-blob', 'x', '>', 'x_dump.sql'], shell=True))



print("done taking dump.")



#clone the database contents into y database - blocking call

print(subprocess.call(["mysql", '-P', '3306', '-h', 'localhost', '-u', '<user>', '-p<password>',

                        'y', '<', 'x_dump.sql'], shell=True))



print("done cloning the sqlDump.")