Hi all, I am having a problem getting database creation on PostgreSQL done correctly in an API that I am writing. I am using a svn checkout of SA trunk from yesterday if that is important.
I have use the following code to create the database:
--- snip ---
...
try:
import psycopg2.extensions as e
ISOLATION_LEVEL_AUTOCOMMIT = e.ISOLATION_LEVEL_AUTOCOMMIT
ISOLATION_LEVEL_READ_COMMITTED = e.ISOLATION_LEVEL_READ_COMMITTED
ISOLATION_LEVEL_SERIALIZABLE = e.ISOLATION_LEVEL_SERIALIZABLE
del e
except ImportError, imp_err:
ISOLATION_LEVEL_AUTOCOMMIT = 0
ISOLATION_LEVEL_READ_COMMITTED = 1
ISOLATION_LEVEL_SERIALIZABLE = 2
...
def __init__(....):
...
self._admin_engine = create_engine(
'%s+%s://%s:%...@%s/postgres'%(self.vendor, self.driver,
self.user,
self.password, self.host))
self._AdminSession = sessionmaker(bind=self._admin_engine)
...
@property
def admin_session(self):
if self._admin_session is None:
self._admin_session = self._AdminSession()
return self._admin_session
...
def create(self):
"""Create this database"""
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_READ_COMMITTED)
--- snip ---
I can create the database just fine within the interpreter:
--- snip ---
>>> import mwdb
>>> db = mwdb.orm.database.PostgreSQLDatabase('psycopg2', 'babilen',
>>> 'PASSWORD', 'localhost', 'test', 'zh')
>>> db.all_databases()
['template1', 'template0', 'postgres']
>>> db.create()
>>> db.all_databases()
['template1', 'template0', 'postgres', 'test']
>>> db.drop()
>>> db.all_databases()
['template1', 'template0', 'postgres']
--- snip ---
But this fails miserably when the API is used within a program:
--- snip ---
dump_db = mwdb.orm.database.PostgreSQLDatabase(
self.options.pg_driver,
self.options.pg_username,
self.options.password,
self.options.pg_host,
self._database_name(dump_info),
dump_info['language'])
if self._database_name(dump_info) not in dump_db.all_databases():
LOG.info('Create database: %s' % self._database_name(dump_info))
dump_db.create()
--- snip ---
Traceback:
--- snip ---
Traceback (most recent call last):
File "/home/babilen/.virtualenvs/wp-import/bin/wp-import", line 185, in
<module>
pg_importer.import_from_directory(ARGS[0])
File
"/home/babilen/.virtualenvs/wp-import/lib/python2.6/site-packages/wp_import/importer.py",
line 147, in import_from_directory
self._import_dump(dump_info)
File
"/home/babilen/.virtualenvs/wp-import/lib/python2.6/site-packages/wp_import/importer.py",
line 103, in _import_dump
dump_db.create()
File
"/home/babilen/.virtualenvs/wp-import/lib/python2.6/site-packages/mwdb/orm/database.py",
line 515, in create
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
File
"/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/orm/session.py",
line 739, in execute
clause, params or {})
File
"/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 975, in execute
return Connection.executors[c](self, object, multiparams, params)
File
"/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 1037, in _execute_clauseelement
return self.__execute_context(context)
File
"/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 1060, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
"/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 1122, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File
"/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/base.py",
line 1120, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File
"/home/babilen/.virtualenvs/wp-import/src/sqlalchemy/lib/sqlalchemy/engine/default.py",
line 181, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (InternalError) CREATE DATABASE cannot run inside
a transaction block
'CREATE DATABASE wp_zh_20091023' {}
--- snip ---
Do you have any idea why this is happening?
Is the .connection.connection.set_isolation_level() the right way to do this?
Why do I have to write connection.connection? This used to (?) be different.
signature.asc
Description: Digital signature
