[sqlalchemy] Re: Create database and transactional blocks strangeness
Wolodja Wentland wrote:
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
there's nothing about the above code that guarantees the connection on
which you called set_isolation_level() is the one used by your
session.execute(). I think you mean to call execute(CREATE DATABASE)
on the connection returned by self._admin_engine.connect().
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Create database and transactional blocks strangeness
On Tue, Oct 27, 2009 at 16:22 -0400, Michael Bayer wrote:
Wolodja Wentland wrote:
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
there's nothing about the above code that guarantees the connection on
which you called set_isolation_level() is the one used by your
session.execute(). I think you mean to call execute(CREATE DATABASE)
on the connection returned by self._admin_engine.connect().
You are right! I changed the code to this:
--- snip ---
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
conn = self._admin_engine.connect()
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
conn.execute('CREATE DATABASE %s'%(self.name))
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_READ_COMMITTED)
--- snip ---
and it works like a charm.
But i still have some little questions...
* Is there an even better way to do this? ;-)
* Is it necessary to set the isolation level to the value it had
before I set it to ISOLATION_LEVEL_AUTOCOMMIT to make sure that no
connection uses ISOLATION_LEVEL_AUTOCOMMIT in the future without
explicitly setting that?
(I will change the code so it remembers the value of isolation_level
and use that instead of it to ISOLATION_LEVEL_READ_COMMITTED
explicitly)
* Why the .connection.connection ? I remember that I had to write just
one .connection in the past.
And one more word... This is the fastest mailing list I have ever used.
Thank you so much for reacting so fast on this ML, thank you very much
for SA and thanks for the solution to my problem!
have a great afternoon
Wolodja Wentland
signature.asc
Description: Digital signature
[sqlalchemy] Re: Create database and transactional blocks strangeness
Wolodja Wentland wrote:
On Tue, Oct 27, 2009 at 16:22 -0400, Michael Bayer wrote:
Wolodja Wentland wrote:
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
self._admin_engine.connect().connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
self.admin_session.execute('CREATE DATABASE %s'%(self.name))
there's nothing about the above code that guarantees the connection on
which you called set_isolation_level() is the one used by your
session.execute(). I think you mean to call execute(CREATE DATABASE)
on the connection returned by self._admin_engine.connect().
You are right! I changed the code to this:
--- snip ---
def create(self):
Create this database
# set isolation level to AUTOCOMMIT
# postgres can't CREATE databases within a transaction
conn = self._admin_engine.connect()
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_AUTOCOMMIT)
conn.execute('CREATE DATABASE %s'%(self.name))
conn.connection.connection.set_isolation_level(
ISOLATION_LEVEL_READ_COMMITTED)
--- snip ---
and it works like a charm.
But i still have some little questions...
* Is there an even better way to do this? ;-)
create_engine() for PG supports an isolation_level parameter. But it
only does the four levels PG provides, it doesn't yet have a hook for
Psycopg2's autocommit mode.
* Is it necessary to set the isolation level to the value it had
before I set it to ISOLATION_LEVEL_AUTOCOMMIT to make sure that no
connection uses ISOLATION_LEVEL_AUTOCOMMIT in the future without
explicitly setting that?
the way you have it, yes. Alternatively, you can call detach() on the
connection you returned and it will be de-associated from the connection
pool. Otherwise I would absolutely use try/finally above so that the
isolation level is returned to normal if the CREATE DATABASE fails.
* Why the .connection.connection ? I remember that I had to write just
one .connection in the past.
it should only need to be conn.connection. That returns a wrapper that
will pass all method calls down to the psycopg2 connection. would be
interested to know the error otherwise.
And one more word... This is the fastest mailing list I have ever used.
Thank you so much for reacting so fast on this ML, thank you very much
for SA and thanks for the solution to my problem!
youre welcome
have a great afternoon
Wolodja Wentland
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
