Erreur lors de la création d'une base de données PostgreSQL en utilisant python, sqlalchemy et psycopg2

- Je utiliser sqlalchemy qui utilise psycopg2 pour la connexion à des serveurs postgresql.

Lorsque je lance le code suivant:

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;')

Je reçois toujours le message d'erreur suivant:

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;' {}

Lorsque j'essaie d'utiliser une url sans spécifier de base de données argument:

url = URL(drivername='postgresql', username='myname', password='mypasswd', host='localhost')

J'obtiens l'erreur suivante:

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

Comment puis-je résoudre le problème?

source d'informationauteur Roman Prykhodchenko

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('postgresql+psycopg2://USER:[email protected]: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)

Si vous n'avez pas de bases de données, vous devez utiliser 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_level

http://initd.org/psycopg/docs/extensions.html#isolation-level-constants

http://www.postgresql.org/docs/current/static/transaction-iso.html

17

Même sans l'aide de l'ORM Session:
```
conn = eng.connect()
conn.connection.connection.set_isolation_level(0)
conn.execute('create database test')
conn.connection.connection.set_isolation_level(1)
```
Sûrement, il n'y aurait pas de raison d'utiliser un ORM pour définir le niveau d'isolation sur la plaine de connexion DB, droit?

Vous devez vous connecter pour publier un commentaire.