使用python,sqlalchemy和psycopg2创建PostgreSQL数据库时出错 [英] Error when creating a PostgreSQL database using python, sqlalchemy and psycopg2
本文介绍了使用python,sqlalchemy和psycopg2创建PostgreSQL数据库时出错的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用使用psycopg2连接到PostgreSQL服务器的sqlalchemy。
I use sqlalchemy that uses psycopg2 for connecting to postgresql servers.
当我启动以下代码时:
from sqlalchemy.engine.url import URL
from sqlalchemy.engine import create_engine
url = URL(drivername='postgresql', username='myname', password='mypasswd', host='localhost', database='template1')
eng = create_engine(url)
eng.execute('CREATE DATABASE new_db;')
我总是会遇到以下错误:
I always get the following error:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1788, in execute
return connection.execute(statement, *multiparams, **params)
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1191, in execute
params)
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1287, in _execute_text
return self.__execute_context(context)
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1302, in __execute_context
context.parameters[0], context=context)
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1401, in _cursor_execute
context)
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1394, in _cursor_execute
context)
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py", line 299, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (InternalError) CREATE DATABASE cannot run inside a transaction block
'CREATE DATABASE new_db;' {}
当我尝试使用未指定 database 参数的url时:
When I try to use a url without specifying a database argument:
url = URL(drivername='postgresql', username='myname', password='mypasswd', host='localhost')
我收到以下错误:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1787, in execute
connection = self.contextual_connect(close_with_result=True)
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/base.py", line 1829, in contextual_connect
self.pool.connect(),
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/pool.py", line 182, in connect
return _ConnectionFairy(self).checkout()
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/pool.py", line 369, in __init__
rec = self._connection_record = pool.get()
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/pool.py", line 213, in get
return self.do_get()
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/pool.py", line 732, in do_get
con = self.create_connection()
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/pool.py", line 147, in create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/pool.py", line 253, in __init__
self.connection = self.__connect()
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/pool.py", line 319, in __connect
connection = self.__pool._creator()
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/strategies.py", line 82, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/local/lib/python2.7/site-packages/SQLAlchemy-0.6.6-py2.7.egg/sqlalchemy/engine/default.py", line 249, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (OperationalError) FATAL: database "roma" does not exist
None None
如何解决此问题?
推荐答案
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('postgresql+psycopg2://USER:PASSWORD@127.0.0.1:5432/DB_OR_TEMPLATE')
session = sessionmaker(bind=engine)()
session.connection().connection.set_isolation_level(0)
session.execute('CREATE DATABASE test')
session.connection().connection.set_isolation_level(1)
如果没有任何数据库,则应使用 template1
If you don't have any databases, you should use template1
"""Isolation level values."""
ISOLATION_LEVEL_AUTOCOMMIT = 0
ISOLATION_LEVEL_READ_COMMITTED = 1
ISOLATION_LEVEL_SERIALIZABLE = 2
http:// initd。 org / psycopg / docs / connection.html#connection.set_isolation_级别
http://initd.org/psycopg/docs/extensions.html#isolation-level-constants
http://www.postgresql.org/docs/current/static/transaction-iso.html
这篇关于使用python,sqlalchemy和psycopg2创建PostgreSQL数据库时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文