On 8/30/07, Travis Kriplean <[EMAIL PROTECTED]> wrote:
> 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:[EMAIL PROTECTED]'
> >>> 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' {}
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:[EMAIL PROTECTED]")
>>> 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 <[EMAIL PROTECTED]>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
