I use sqlalchemy that uses psycopg2 for connecting to postgresql servers.
When I launch the following code:
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;' {}
When I try to use a url without specifying a database argument:
url = URL(drivername='postgresql', username='myname', password='mypasswd', host='localhost')
I 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 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
How can I fix the problem?
PostgreSQL supports sequences, and SQLAlchemy uses these as the default means of creating new primary key values for integer-based primary key columns.
Prerequisites. The current psycopg2 implementation supports: Python versions from 3.6 to 3.10. PostgreSQL server versions from 7.4 to 15.
Creating and Inserting Data into TablesBy passing the database which is not present, to the engine then sqlalchemy automatically creates a new database.
The psycopg2 is over 2x faster than SQLAlchemy on small table. This behavior is expected as psycopg2 is a database driver for postgresql while SQLAlchemy is general ORM library.
Same without using ORM Session:
conn = eng.connect()
conn.connection.connection.set_isolation_level(0)
conn.execute('create database test')
conn.connection.connection.set_isolation_level(1)
Surely there would be no reason to use ORM to set isolation level on plain DB connection, right?
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)
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_level
http://initd.org/psycopg/docs/extensions.html#isolation-level-constants
http://www.postgresql.org/docs/current/static/transaction-iso.html
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With