Re: [sqlalchemy] Re: Transaction isolation level support?
Coincidentally I've been dealing with issue lately as I've tried to further integrate sqlalchemy into a legacy code base and replace our existing database abstraction. In MySQL at least, there is a concept of "session" isolation level and "transaction" isolation level. Meaning you can set the isolation level for just a specific transaction, as well as the default isolation level for the whole connection. Essentially I need to be able to do something like: with session.begin(isolation_level=LEVEL_SERIALIZABLE): ... do stuff ... Unfortunately there seems to be no way to pass any additional arguments into the dialect to get this behavior. I've been hacking around it by basically having a custom Session class that just assumes it's using MySQL only and issues extra SQL to set (and unset) the isolation level at transaction boundaries. This approach is of course completely impossible to pass upstream. Rhett On Tue, Dec 14, 2010 at 8:21 AM, Michael Bayer wrote: > There's really two concepts of "isolation level" at play. > > The "isolation_level" parameter in SQLA is usually linked to > database-specific SQL commands that we issue, in the case of PG its "SET > SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL", in sqlite its > "PRAMGA read_committed". > > But the psycopg2 and pysqlite DBAPIs also support ways to modify the > isolation characteristics via connection-specific commands, which also change > the DBAPIs behavior a bit. In sqlite its the "autocommit" flag, and > psycopg2 its the set_isolation_level() method. In the latter case, we have > to use psycopg2's API since psycopg2 itself works against the > connection-level settings if it hasn't been told about them (I think it > resets them on each command). > > On a per-connection basis, the difficulty there has always been that we > didn't have a nice extensible API with which to apply modifiers to Connection > objects. As of 0.6 we do, known as execution_options(), and isolation levels > are exactly the kind of function that would be available via > execution_options(). So at this point its an unimplemented feature with a > fairly clear path. I've placed an initial patch for this in > http://www.sqlalchemy.org/trac/ticket/2001 . > > > > > > On Dec 13, 2010, at 10:10 PM, Russell Warren wrote: > >>> Do other dialects support this, but just not indicate it in the >>> documentation? >> >> On further investigation I see this old ticket: >> >> http://www.sqlalchemy.org/trac/ticket/443 >> >> ... which implies it is, in fact, only for PostgreSQL and Sqlite at >> the moment. >> >> I'm also quite interested in setting the isolation level on a per >> session/connection basis. However - it seems like the isolation_level >> argument to create_engine() sets the isolation level for all managed >> connections. I recognize that there may (at least) be some issues >> with this based on the connection pooling scheme in use, but is there >> a way (even a hack) to do a one-time set of the isolation level to >> SERIALIZABLE (for example) for a created session? >> >> This thread makes it seem like per-session/connection isolation >> setting is not as simple as it might seem at first... >> http://groups.google.com/group/sqlalchemy-devel/browse_thread/thread/653b6b2f49a940b8 >> >> And this StackOverflow question makes it seem like it isn't easy to >> set the isolation level per session... >> http://bit.ly/eG5DUl >> >> Still digging... >> >> Russ >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalch...@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. >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Connection initialization
When we make a new connection to the database (MySQL in this case), it
appears that the dialect has some initialization functions than run
several queries:
connection duration rows query
primary_slave/tid=92761174.014ms 1'SELECT DATABASE()'
primary_slave/tid=92761171.868ms 1"SELECT CAST('test
unicode
returns' AS CHAR(60)) AS anon_1"
primary_slave/tid=92761178.586ms 8"SHOW VARIABLES LIKE
'character_set%'"
primary_slave/tid=92761174.056ms 1"SHOW VARIABLES LIKE
'lower_case_table_names'"
primary_slave/tid=92761178.084ms 126'SHOW COLLATION'
primary_slave/tid=9276117 30.873ms 1"SHOW VARIABLES LIKE
'sql_mode'"
Is there a way to eliminate the need to make these queries ? Though we
of course don't open new connections often, it does happen and adding
this overhead for information that probably shouldn't change seems
unnecessary.
(MySQL 5.0.x, sqlalchemy 6.0beta)
Rhett
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Custom ORM attributes to provide on-the-fly data conversion
>>
>> Oh right, sorry:
>>
>> class Advertiser(Base):
>> __tablename__ = "advertiser"
>> id, _id = build_id_column('id', primary_key=True)
>> salesperson_id, _salesperson_id =
>> build_id_column('salesperson_id', foreign_key=ForeignKey("%s.id" %
>> Salesperson.__tablename__))
>> salesperson, _salesperson = build_relation(Salesperson)
>
> sorry, that mapping doesn't really make any sense to me.
>
>> Where these build_* functions are returning tuple where the first
>> element is a synonym for the second, which some extra descriptor stuff
>> on it
>> to do the obfuscation.
>
> right but, you don't define synonyms that way. Its best to stick with the
> methods in the documentation.
>
Sorry if I'm not explaining myself quite clearly. build_id_column() is
doing exactly what the docs are saying to do,
just trying to abstract out that pattern since it will be used on
every single object in our system, often for multiple cols.
Rhett
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Custom ORM attributes to provide on-the-fly data conversion
On Wed, Apr 21, 2010 at 6:35 AM, Michael Bayer wrote:
> Rhett Garber wrote:
>> This would be much easier, I could potentially be what we go with. I
>> think this is similar to my 'original implementation'
>> I just found the syntax to be a bit bothersome since the person
>> creating the table has to know they are creating two
>> columns... or not using declarative. These ids are (and foreign keys)
>> mean you'll be doing something like this multiple
>> times on every table in our system, so I want it to be a streamline
>> and foolproof as possible.
>
> It hasn't gotten across to me what the "two columns" version of this is.
> an illustration of the table schema and mappings for that might make it
> clearer what you are attempting.
Oh right, sorry:
class Advertiser(Base):
__tablename__ = "advertiser"
id, _id = build_id_column('id', primary_key=True)
salesperson_id, _salesperson_id =
build_id_column('salesperson_id', foreign_key=ForeignKey("%s.id" %
Salesperson.__tablename__))
salesperson, _salesperson = build_relation(Salesperson)
Where these build_* functions are returning tuple where the first
element is a synonym for the second, which some extra descriptor stuff
on it
to do the obfuscation.
I think we've also got a version that uses a metaclass to cleanup the
syntax, where it goes through and redefines them for you.
But still, it's pretty complex.
I'm not quite sure I have my head around what's different than your
solution, except this is trying to use declarative syntax.
Perhaps that's the problem.
Rhett
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Custom ORM attributes to provide on-the-fly data conversion
On Tue, Apr 20, 2010 at 4:32 PM, Michael Bayer wrote:
>
> On Apr 20, 2010, at 7:06 PM, Rhett wrote:
>
>> I've run into some difficulty getting the ORM to fit into an existing
>> code base with some, I suppose, non-standard conventions.
>>
>> One of the conventions is to not allow primary keys (auto-incremented
>> integers) to be exposed on the front-end servlet or template
>> but to maintain the original integer values inside 'logic' or 'back-
>> end' code.
>>
>> We've been through a few methods of trying this, but they feel like
>> we're fighting sqlalchemy and must be missing something.
>
>
> In Python, the closest thing we have to a "private" attribute is that it
> begins with an underscore. This is extremely easy to do, just name the
> mapped attribute "_id" or whatever name you'd like. This is documented at
> http://www.sqlalchemy.org/docs/mappers.html#customizing-column-properties .
>
>> The plan is to flip a flag when the object passes through a pre-
>> defined barrier, converting these columns only when they are asked
>> for.
>>
>> The original implementation had us creating two columns, one of them
>> being a synonym which provides the encryption
>> behavior, but this means we have class that look like this:
>> class Foo(Base):
>> id, _id = build_id_column(Integer, primary_key=True)
>
> where does "encryption" come into play ? are you trying to just expose
> the "primary key" value as something else ? easy enough, just use a
> descriptor:
I probably should have said 'obfuscation'. You are correct, we're
trying to make the "primary key" not expose the actual
size/distribution of the underlying table.
>
> class MyClass(object):
> def encrypted_id(self):
> return encrypt_my_id(self._super_secret_id_attribute)
>
> def set_encrypted_id(self, id):
> self._super_secret_id_attribute = unencrypt_id(id)
>
> encrypted_id = property(encrypted_id, set_encrypted_id)
>
> mapper(MyClass, mytable, properties={
> '_super_secret_id_attribute':mytable.c.id
> })
>
> if you want MyClass.encrypted_id to be available in queries at the class
> level, this would require a SQL function that does your "encryption". See
> examples/derived_attributes/ for some techniques on that.
So the point is that this should be done outside of the core ORM as a
standard property as opposed to getting the ORM to
do it for me. The downside being that the syntax isn't quite as clean
as using declarative.
This would be much easier, I could potentially be what we go with. I
think this is similar to my 'original implementation'
I just found the syntax to be a bit bothersome since the person
creating the table has to know they are creating two
columns... or not using declarative. These ids are (and foreign keys)
mean you'll be doing something like this multiple
times on every table in our system, so I want it to be a streamline
and foolproof as possible.
Thanks for your help,
Rhett
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
