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.
On Aug 31, 2007, at 4:00 PM, Travis Kriplean wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
