How do I migrate rows from a local SQLite DB to a remote MySQL DB using SQLAlchemy?

Question

I'm using SQLAlchemy to manage 2 databases. One is a local SQLite DB and the other is a remote MySQL DB on Google's cloud platform and they both share the same schema. I'm trying to use append the rows contained in the SQLite DB to the MySQL DB. I currently have one file that defines my class like so:

SensorCollection.py

class Readings(Base):
    __tablename__ = 'readings'

    time = Column(String(250), primary_key=True)
    box_name = Column(String(250))
    FS = Column(Numeric)
    IS = Column(Numeric)
    VS = Column(Numeric)
    CO = Column(Numeric)
    TVOC = Column(Numeric)
    cTemp = Column(Numeric)
    fTemp = Column(Numeric)
    humidity = Column(Numeric)
    pressure = Column(Numeric)

Then I have another file that declares my Base, Session, and engine:

base.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///%s' % 'db/db.db')
Session = sessionmaker(bind=engine)
Base = declarative_base()

And finally my actual code:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import Table
from SensorCollection import Readings
from sqlalchemy.sql.expression import insert

# uploads MySQL database to Google cloud server
# uses ORM to ensure the databases can communicate with each other
user = ***
password = ***
host = ***
port = ***
dbName = ***
instanceConnectionName = ***
# create the engine for the local db
engineLocal = create_engine('sqlite:///%s' % 'db/db.db')
BaseLocal = Readings(None, None, None, None, None, None, None, None, None, None, None)

# create the engine for the Google db
engineGoogle = create_engine('mysql+mysqldb://%s@%s:%s/%s?unix_socket=/cloudsql/%s' % (user, host, port, dbName, instanceConnectionName))
SessionGoogle = sessionmaker(bind=engineGoogle)
BaseGoogle = Readings(None, None, None, None, None, None, None, None, None, None, None)

# create the local db
BaseLocal.metadata.create_all(engineLocal)

# create the Google db and start the session
BaseGoogle.metadata.create_all(engineGoogle)
sessionGoogle = SessionGoogle()

# create table objects for each db
t1 = Table('readings', BaseLocal.metadata, autoload=True, autoload_with=engineLocal)
t2 = Table('readings', BaseGoogle.metadata, autoload=True, autoload_with=engineGoogle)

# the first subquery, select all ids from SOME_TABLE where some_field is not NULL
s1 = t1.select()

# the second subquery, select all ids from SOME_TABLE where some_field is NULL
s2 = t2.select()

# union s1 and s2 subqueries together and alias the result as "alias_name"
q = s1.union(s2)

insert(t2, s1)
sessionGoogle.query(q)
# sessionGoogle.add_all(s1)

# commit changes and close the session
sessionGoogle.commit()
sessionGoogle.close()

Currently, the SQLite database exists just fine and the remote server is set up, but the code doesn't even create the table when it connects to Google. It seems to connect properly because if I alter any server details, I get a MySQL error. It also doesn't give me any errors. It compiles and runs, but doesn't append data or even create the table. This is my first time taking a crack at any kind of DB so I'm sure it's riddled with mistakes and not elegant at all. Any help would be appreciated.

Edit djkern helped me get the table created and I've updated my code. I still haven't gotten the data to append yet, though.

Answer 1

In your code, you have:

# create the Google db and start the session
BaseLocal.metadata.create_all(engineLocal)
sessionGoogle = SessionGoogle()

Did you mean for this to be?

# create the Google db and start the session
BaseGoogle.metadata.create_all(engineGoogle)
sessionGoogle = SessionGoogle()

Answer 2

Two more classes need to be added,

class ReadingsLocal(BaseLocal):

and

class ReadingsGoogle(BaseGoogle):

They should be in the same file as and identical to

class Readings(Base):

New Base, engine, and Session variables must also be created. Then, the code should look like this:

# create the local db
BaseLocal.metadata.create_all(engineLocal)
sessionLocal = SessionLocal()

# create the Remote db and start the session
BaseGoogle.metadata.create_all(engineGoogle)
sessionGoogle = SessionGoogle()

for reading in sessionLocal.query(ReadingsLocal):
    # reading is already attached to a session, so a tmp variable must be created to avoid conflicts
    tmp = ReadingsGoogle(reading.time, reading.box_name, reading.FS, reading.IS, reading.VS, reading.CO, reading.TVOC,
               reading.cTemp, reading.fTemp, reading.humidity, reading.pressure)
    sessionGoogle.add(tmp)

# commit changes and close the session
sessionGoogle.commit()
sessionGoogle.close()