title: Using SQLAlchemy to migrate databases (sqlite to Postgres CloudSQL)
body: <p>Trying to migrate the database for an early project of mine to the cloud.  Although the code that built everything is janky, the db structure and data itself are fairly sound.  I could probably figure out a dump method of migrating everything (pgdump, etc), but I still have much to learn about this stuff so I'd rather get the experience by doing it step-by-step.</p>
  
  <p><strong>Source:</strong> A ~1gb sqlite database file</p>
  
  <p><strong>Destination:</strong> Google CloudSQL running Postgres v9.6</p>
  
  <p>Already created the tables in the cloud db, using the same schema &amp; table names as the sqlite db.  Not worried about schema enforcement errors because I haven't yet defined foreign keys in the cloud.</p>
  
  <p><strong>The Plan:</strong> Create distinct, concurrent SQLAlchemy connections to each database, then read sqlite --> write to CloudSQL.  Went back and defined data structures for each table using SQLAlchemy.  Snippet from <strong>models.py</strong>:</p>
  
  <pre><code>from sqlalchemy import Column, Integer, String
  from sqlalchemy.ext.declarative import declarative_base, declared_attr
  
  Base = declarative_base()
  
  class PublicMixin(object):
      @declared_attr
      def __tablename__(cls):
          return cls.__name__.lower()
  
  class Category(PublicMixin, Base):
      id = Column(Integer, primary_key=True)
      name = Column(String)
  
  class Player(PublicMixin, Base):
      id = Column(Integer, primary_key=True)
      name = Column(String)
      username = Column(String)
      notes = Column(String)
  [...]
  </code></pre>
  
  <p>I then duplicated this file as <strong>models_lite.py</strong> so I could import each model without potential for interference.  Here's the <strong>migration.py</strong> file I tried to run as a proof of concept:</p>
  
  <pre><code>from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker
  from models import Base, Category, Player
  from models_sqlite import Base as Base_lite, Category as Category_lite, Player as Player_lite
  
  # SQLite db
  engine_lite = create_engine('sqlite:///mydb.sqlite')
  Base_lite.metadata.bind = engine_lite
  LiteSession = sessionmaker()
  LiteSession.bind = engine_lite
  session_lite = LiteSession()
  
  # CloudSQL, via local proxy
  engine_cloud = create_engine('postgresql+psycopg2://USER:PW@/DBNAME?host=/cloudsql/INSTANCE')
  Base.metadata.bind = engine_cloud
  CloudSession = sessionmaker()
  CloudSession.bind = engine_cloud
  session_cloud = CloudSession()
  
  category_lite = session_lite.query(Category_lite).all()
  category_cloud = Category()
  
  for c in category_lite:
      category_cloud = c
      session_cloud.add(category_cloud)
  
  session_cloud.commit()
  </code></pre>
  
  <p>Running this yields the following error:</p>
  
  <pre><code>File "postgres migration.py", line 68, in &lt;module&gt;
      session_cloud.add(category_cloud)
  [...]
  sqlalchemy.exc.InvalidRequestError: Object '&lt;Category at 0x11141b908&gt;' is already attached to session '1' (this is '2')
  </code></pre>
  
  <p>Explicitly setting each column inside the <code>for</code> loop works (i.e.: <code>category_cloud.id = c.id</code>), but there must be a way to avoid having to do this for every column in every table.  How should I approach this?</p>