SQLAlchemy支持H2DB吗？

Question

SQLAlchemy支持H2数据库吗？我正在使用Pyramid框架，想要连接到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

如果有人再次遇到这个问题，我尝试让这个东西运行起来（作为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")

但是这段代码会抛出一个异常： from sqlalchemy_utils import 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()']

Answer 2

据我所知，目前没有官方支持HSQLDB的方言或者原生H2的方言。如果你用H2数据库却选择了Postgres的方言，这肯定会导致你遇到的错误。

你可以试试 sqlalchemy-jython，看看能否使用H2的方言，这样可能会更顺利。