SQLAlchemy python copying table identically and pasting it elsewhere

Question

I have a MySQL DB that has a table with a primary key which are VARCHAR(4) and contains 10 columns which contain FLOAT. I have attempted to copy the table and paste it elsewhere but when read_sql(), it changes the format of the table e.g. changing VARCHAR(4) and FLOAT to TEXT and changing the past primary key into a regular column. Lastly it added an index. My question is, how can I copy the table identically, make changes to the data, and write it to another data base that has a similar format.

Answer 1

Your question implies that you're looking for a SQLAlchemy-based table migration. Typically, I'd use a utility like mysqldump instead. I did a little searching and tested a SQLAlchemy-based solution successfully. Use this recipe from https://www.paulsprogrammingnotes.com/2014/01/clonecopy-table-schema-from-one.html

from sqlalchemy import create_engine, Table, Column, Integer, Unicode, MetaData, String, Text, update, and_, select, func, types

# create engine, reflect existing columns, and create table object for oldTable
srcEngine = create_engine('mysql+mysqldb://username:password@111.111.111.111/database') # change this for your source database
srcEngine._metadata = MetaData(bind=srcEngine)
srcEngine._metadata.reflect(srcEngine) # get columns from existing table
srcTable = Table('oldTable', srcEngine._metadata)

# create engine and table object for newTable
destEngine = create_engine('mysql+mysqldb://username:password@localhost/database') # change this for your destination database
destEngine._metadata = MetaData(bind=destEngine)
destTable = Table('newTable', destEngine._metadata)

# copy schema and create newTable from oldTable
for column in srcTable.columns:
    destTable.append_column(column.copy())
destTable.create()

You'll need to replace the connection strings with your own, and change the table names to your own.