creating a database through SQLAlchemy

[Travis Kriplean]

Hi all,

I'd like to use SQLAlchemy to connect to a db server, create a
database, and then start using it. However, it appears that the
SQLAlchemy api assumes the existence of a database to connect to. I'm
able to connect to the server without a database specified:

>>> con = 'postgres://postgres:[PASS]@[HOST]'
>>> m = MetaData(bind=con)
>>> m.get_engine()
<sqlalchemy.engine.base.Engine object at 0x00E6B470>

However, when I try to execute a query to create the database, it
fails because CREATE DATABASE "cannot run inside a transaction block":

>>> c = m.get_engine().connect()
>>> c.execute('CREATE DATABASE test_db')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "c:\python25\lib\site-packages\sqlalchemy-0.3.10-py2.5.egg
\sqlalchemy\eng
ine\base.py", line 517, in execute
return Connection.executors[c](self, object, *multiparams,
**params)
File "c:\python25\lib\site-packages\sqlalchemy-0.3.10-py2.5.egg
\sqlalchemy\eng
ine\base.py", line 532, in execute_text
self._execute_raw(context)
File "c:\python25\lib\site-packages\sqlalchemy-0.3.10-py2.5.egg
\sqlalchemy\eng
ine\base.py", line 581, in _execute_raw
self._execute(context)
File "c:\python25\lib\site-packages\sqlalchemy-0.3.10-py2.5.egg
\sqlalchemy\eng
ine\base.py", line 599, in _execute
raise exceptions.SQLError(context.statement, context.parameters,
e)
sqlalchemy.exceptions.SQLError: (ProgrammingError) CREATE DATABASE
cannot run in
side a transaction block
'CREATE DATABASE test_db' {}

My apologies if there is an easy solution through the API or if this
question has been answered before. I've have been unable to find
anything yet.

Thanks!
Travis

[Mike Orr]

That must be a Postgres-specific problem because it works with MySQL.

$ python
Python 2.5.1 (r251:54863, May 2 2007, 16:56:35)
[GCC 4.1.2 (Ubuntu 4.1.2-0ubuntu4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy as sa
>>> engine = sa.create_engine("mysql://root:PASSWORD@localhost")
>>> e = engine.connect().execute
>>> e("create database test2")
<sqlalchemy.engine.base.ResultProxy object at 0x83811ac>
>>> e("show databases").fetchall()
[('information_schema',), ('mysql',), ('rlink',), ('shields',), ('test2',)]
>>> e("show tables").fetchall()

Traceback (most recent call last):

...
raise exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.OperationalError: (OperationalError) (1046, 'No
database selected') 'show tables' ()
>>> e("use test2")
<sqlalchemy.engine.base.ResultProxy object at 0x846a36c>
>>> e("show tables").fetchall()
[]
>>> e("drop database test2")
<sqlalchemy.engine.base.ResultProxy object at 0x838124c>
>>> e("show databases").fetchall()
[('information_schema',), ('mysql',), ('rlink',), ('shields',)]

Obviously it's perilous to switch databases in an existing engine,
especially if it's bound to a session or metadata. MySQL
automatically commits the last transaction before running a
non-transactional command (which basically means any schema-changing
operation). This may confuse the hell out of your session if you
don't commit + clear first. I don't know if PostgreSQL does the same.

--
Mike Orr <slugg...@gmail.com>

[Travis Kriplean]

Thanks for the help!

I got around the problem by executing:
>>> c.execute('end;CREATE DATABASE test_db')

However, this seems a bit ugly. Is there a way to obtain a non-
transactional connection from an engine in 0.3.10? If not, is it
possible in 0.4?

Thanks,
Travis

On Aug 30, 6:40 pm, "Mike Orr" <sluggos...@gmail.com> wrote:

> Mike Orr <sluggos...@gmail.com>

[Michael Bayer]

all connections in DBAPI are "transactional". theres no begin()
method, only commit() and rollback(). some DBAPis support an
"autocommit" flag but this is not standardized. so SA has not much
choice but to issue its own "autocommit" behavior, which could be
expanded to detect "CREATE DATABASE". Just add a trac ticket if
you'd like to get this done.

[Marco Mariani]

Travis Kriplean ha scritto:

> However, this seems a bit ugly. Is there a way to obtain a non-
> transactional connection from an engine in 0.3.10? If not, is it
> possible in 0.4?
>

I use this with SA 0.3.10 and Postgres:

engine.raw_connection().set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

engine.text("CREATE DATABASE %s ENCODING = 'utf8'" % dbname).execute()

[Travis Kriplean]

Thanks everyone!

On 3 Sep, 01:13, Marco Mariani <marco.mari...@prometeia.it> wrote:
> TravisKriplean ha scritto: