Can Sqlalchemy work well with multiple attached SQLite database files?

Question

It's possible to connect multiple SQLite databases together using the 'ATTACH' statement and work with them jointly. Tables in each SQLite file can be referenced using a schema/file specific keyword. This should allow you to simultaneously deal with multiple tables with the same name by scoping by file. I went through a very good tutorial on how to do this is here:

http://longweekendmobile.com/2010/05/29/how-to-attach-multiple-sqlite-databases-together/

It seemed like I should be able to use SQLAlchemy's Table 'schema' keyword to differentiate between connections to multiple files. When I went searching for a way to use SQLAlchemy with SQLite databases that had been connected via ATTACH, this was the only example I found. Unfortunately, it is out of date and does not seem to work with current versions.

https://groups.google.com/forum/#!topic/sqlalchemy/QXqs4M2MjbY

I tried to update that example using Declarative classes, etc. Here was my attempt:

from sqlalchemy import *
from sqlalchemy.orm import * 
from sqlalchemy.ext.declarative import *
#from sqlalchemy.pool import SingletonThreadPool

metadata = MetaData(object)
DeclarativeBase = declarative_base(metadata=metadata)

##########################################################################
# Classes
##########################################################################

class A(DeclarativeBase):
    __table__              = Table('A', DeclarativeBase.metadata,
                                   Column('id', Integer, primary_key=True, index=True, autoincrement=True),
                                   Column('col_a', Integer, index=True))

class B(DeclarativeBase):
    __table__              = Table('B', DeclarativeBase.metadata,
                                   Column('id', Integer, primary_key=True, index=True, autoincrement=True),
                                   Column('col_b', Integer, index=True),
                                   schema='database_b')

#engine = create_engine('sqlite:////tmp/database_a.sqlite',echo=True, poolclass=SingletonThreadPool)
engine = create_engine('sqlite:////tmp/database_a.sqlite',echo=True)
db     = engine.connect()
db.execute("ATTACH DATABASE '/tmp/database_b.sqlite' AS database_b")

DeclarativeBase.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.commit()

Unfortunately, I got the following output:

monster:tmp ladmin$ python sqliteattachtest2.py 
2014-04-12 18:04:58,845 INFO sqlalchemy.engine.base.Engine ATTACH DATABASE '/tmp/database_b.sqlite' AS database_b
2014-04-12 18:04:58,845 INFO sqlalchemy.engine.base.Engine ()
2014-04-12 18:04:58,846 INFO sqlalchemy.engine.base.Engine PRAGMA "database_b".table_info("B")
2014-04-12 18:04:58,846 INFO sqlalchemy.engine.base.Engine ()
2014-04-12 18:04:58,846 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "sqliteattachtest2.py", line 29, in <module>
    DeclarativeBase.metadata.create_all(engine)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/schema.py", line 2793, in create_all
    tables=tables)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1479, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1122, in _run_visitor
    **kwargs).traverse_single(element)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 111, in traverse_single
    return meth(obj, **kw)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 57, in visit_metadata
    if self._can_create_table(t)]
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 35, in _can_create_table
    table.name, schema=table.schema)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/dialects/sqlite/base.py", line 716, in has_table
    cursor = _pragma_cursor(connection.execute(statement))
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 805, in _execute_text
    statement, parameters
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 195, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) unknown database "database_b" 'PRAGMA "database_b".table_info("B")' ()

I read that this sort of thing can be done using Postgres schemas. Multiple attached SQLite databases seems like a natural counterpart. I suspect I'm either doing something stupid or missed some important point. Is it possible to use SQLAlchemy to work with multiple SQLite files at the same time? If so, what's the best way to do it? Are there other ORMs that make this easier than SQLAlchemy does?

Thanks! Dan

Answer 1

Create Engine of SQlite with inmemory than attach different database files

from sqlalchemy import create_engine, MetaData, Table,Column,Integer,select
from sqlalchemy.orm import mapper, sessionmaker
from sqlite3 import dbapi2 as sqlite
from sqlalchemy.engine.reflection import Inspector

class Bookmarks(object):
    pass

class BookmarksB(object):
    pass



def loadSession():
    engine = create_engine('sqlite://', echo=True)
    engine.execute("attach database 'database_b' as BB;")
    engine.execute("attach database 'database_a' as AA;")
    metadata = MetaData(engine)


    inspector = Inspector.from_engine(engine)
    print inspector.get_table_names()

    moz_bookmarks = Table('table_a', metadata,Column("id", Integer, primary_key=True),schema='AA', autoload=True)
    mapper(Bookmarks, moz_bookmarks)
    moz_bookmarksB = Table('table_b', metadata,Column("id", Integer, primary_key=True),schema='BB', autoload=True)
    mapper(BookmarksB, moz_bookmarksB)

    Session = sessionmaker(bind=engine)
    session = Session()
    return session

if __name__ == "__main__":
    session = loadSession()
    res = session.query(Bookmarks).all()
    for m in res:
        print m.msisdn,m.id

    #print list(select([moz_bookmarks, moz_bookmarksB], moz_bookmarks.c.b_id == moz_bookmarksB.c.id).execute())