Sqlalchemy - Copy Data From A Database To Another Database With Exactly Same Schema

I have 2 mysql database connect with sqlalchemy in 2 docker machine. They both have same schema and data inside. Now I want to copy data in 1 of the database to another to merge

Solution 1:

Yes, you can:

Run this on the database you want to copy over:

mysqldump --no-create-info {yourdbname} > {yourdbname}.sql

And then on the database you want to import this:

mysql {yourdbname} < {yourdbname}.sql

You might run into an issue with the primary keys and/or duplicate records. Sadly from that point on you will have to generate SQL to export your data with an altered primary key, while keeping your foreign key relations intact.

This second complex issue a procedure is required to query the information schema:

• Select the table names from the information schema
• Select the primary key using the information schema
• Select max(primary key) for every table.
• Use the information schema to create SELECT queries in which you add the previous max(primary key) to the primary key, and write the output to the data file.

On foreign keys related you will also have to add this same max(primary key) value from the related table. It will be a bit more code to write, but 50-80 lines stored procedure should do it.