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" <[EMAIL PROTECTED]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
