SQLAlchemy是否支持H2DB？

Question

SQLAlchemy是否支持H2 db？我正在使用金字塔，想要连接到H2数据库。如果使用postgres方言，我会得到如下错误：

File "/Users/homecamera/gotocamera/hcadmin/env/lib/python2.7/site-packages/SQLAlchemy-0.7.3-py2.7-macosx-10.4-x86_64.egg/sqlalchemy/dialects/postgresql/base.py", line 871, in initialize
super(PGDialect, self).initialize(connection)
File "/Users/homecamera/gotocamera/hcadmin/env/lib/python2.7/site-packages/SQLAlchemy-0.7.3-py2.7-macosx-10.4-x86_64.egg/sqlalchemy/engine/default.py", line 181, in initialize
self.get_isolation_level(connection.connection)
File "/Users/homecamera/gotocamera/hcadmin/env/lib/python2.7/site-packages/SQLAlchemy-0.7.3-py2.7-macosx-10.4-x86_64.egg/sqlalchemy/dialects/postgresql/base.py", line 910, in get_isolation_level
cursor.execute('show transaction isolation level')
ProgrammingError: Syntax error in SQL statement "SELECT "; expected "TOP, LIMIT, DISTINCT, ALL, *, NOT, EXISTS"; SQL statement:
show transaction isolation level [42001-140]
DETAIL:  org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT "; expected "TOP, LIMIT, DISTINCT, ALL, *, NOT, EXISTS"; SQL statement:
show transaction isolation level [42001-140]

Answer 1

AFAIK没有对HSQLDB方言或本地H2方言的官方支持。在H2中使用Postgres方言(不使用HSQLDB)肯定会导致出现错误。

您可能会更幸运地尝试使用sqlalchemy-jython并使用H2方言。

Answer 2

为了防止任何人再次遇到这个问题，我试图让它运行(作为sqlite的替代方案)，但它只能部分工作，唯一有效的驱动程序是pg8000。

使用以下命令运行服务器：

nohup java -cp /opt/h2/bin/h2*.jar org.h2.tools.Server -pg -pgAllowOthers -pgPort 5435 -baseDir /opt/h2-data &

下面的代码在sqlalchemy中有效：

from sqlalchemy import create_engine
engine = create_engine('postgresql+pg8000://sa:sa@localhost:5435/main')
engine.execute("SELECT 1")

然而，这段代码抛出了一个异常:从sqlalchemy_utils导入create_database create_database('postgresql+pg8000://sa:sa@localhost:5435/main')

例外：

sqlalchemy.exc.ProgrammingError: (pg8000.core.ProgrammingError) ('ERROR', 
'HY000', 'General error: "java.lang.IllegalStateException: output binary 
format is undefined" [50000-196]', 'org.h2.jdbc.JdbcSQLException: General
error: "java.lang.IllegalStateException: output binary format is undefined"
[50000-196]') [SQL: 'select version()']