Sqlalchemy是否可以与多个附加的SQLite数据库文件配合使用？ [英] Can Sqlalchemy work well with multiple attached SQLite database files?

问题描述

可以使用 ATTACH语句将多个SQLite数据库连接在一起并一起使用。可以使用特定于架构/文件的关键字引用每个SQLite文件中的表。这应该允许您通过按文件作用域来同时处理具有相同名称的多个表。我在这里仔细阅读了一个很好的教程：

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/

似乎我应该能够使用SQLAlchemy的Table'schema'关键字来区分到多个文件的连接。当我寻找一种方法来对通过ATTACH连接的SQLite数据库使用SQLAlchemy时，这是我发现的唯一示例。不幸的是，它已经过时，并且似乎不适用于当前版本。

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")' ()

我读到可以用Postgres模式完成这种事情。多个附加的SQLite数据库似乎很自然。我怀疑我是在做一些愚蠢的事情，还是错过了一些重要的观点。是否可以使用SQLAlchemy同时处理多个SQLite文件？如果是这样，最好的方法是什么？还有其他ORM使其比SQLAlchemy容易吗？

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?

谢谢！
Dan

Thanks! Dan

推荐答案

使用内存创建SQlite引擎，而不是附加不同的数据库文件

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())

这篇关于Sqlalchemy是否可以与多个附加的SQLite数据库文件配合使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！