title: How do I migrate rows from a local SQLite DB to a remote MySQL DB using SQLAlchemy?
body: <p>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:</p>
  
  <p>SensorCollection.py</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Then I have another file that declares my Base, Session, and engine:</p>
  
  <p>base.py</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>And finally my actual code:</p>
  
  <pre><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()
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>Edit</strong>
  djkern helped me get the table created and I've updated my code. I still haven't gotten the data to append yet, though.</p>