[sqlalchemy] Re: MSSQL session.flush() doesn't always fetch primary keys
Oops, sorry about the garbage at the end
On 1/10/07, Rick Morrison [EMAIL PROTECTED] wrote:
A bit of background might be helpful here. The psuedo-sequence mechanism
that the MSSQL modules uses may seem a bit complex, but by leveraging the
same Sequence() mechanism that the PG and Oracle modules use, it allows a
single SA Table definition to correctly create() with the proper sequences
on PostgresSQL (via SERIAL, a kind of automatic named sequence), Oracle (via
named sequences), and MSSQL (via the weird IDENTITY stuff they took from
Sybase).
If you really need an implicit PK, I think best way to handle this is to
leave the Sequence stuff in place so that it continues to play that role,
and to put a hook into the Column initialization as Paul has suggested. Not
sure __init__ is the right place, but that's a nit. The MSSQL module could
override the hook method and implement the same Sequence logic as it does
for the SchemaGenerator to create implicit autoincrementing Integer PKs.
That, or simply use the Sequence() that's already in place. Instead of:
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True), # Sequence('paj')
Column('user_name, String))
use:
users = Table('users', metadata,
Column('user_id', Integer, Sequence('paj'), primary_key=True),
Column('user_name, String))
and all the @@IDENTITY machinery will jump into life as expected.
Rick
users = Table(*'users'*, metadata,
8http://www.sqlalchemy.org/trac/attachment/ticket/415/bug4.py#L8
Column(*'user_id'*, Integer, primary_key=True), *# Sequence('paj'), *
9http://www.sqlalchemy.org/trac/attachment/ticket/415/bug4.py#L9
Column(*'user_name'*, String))
The point was the particular app I was writing needed more or less
transparent access to PostgresSQL, MS-SQL Server and Oracle. Once I found
that SA had t
On 1/9/07, Michael Bayer [EMAIL PROTECTED] wrote:
the current behavior of SA is to utilize the databases implicit PK
generation feature *if one is available*. the three databases that I
know have this are sqlite (send None in to a PK INSERT, it gets
generated), MySQL (AUTOINCREMENT) and Postgres (SERIAL/BIGSERIAL,
which results in the creation of a sequence with a naming scheme).
the other database im familiar with, Oracle, does not have such a
feature. So with Oracle, you have to create an explicit Sequence
object if you want PKs to get generated automatically upon INSERT,
and SA will fire off that sequence. you can put a Sequence on any
Column, and it will still work on mysql/sqlite, since in those cases
its just ignored, and the normal autoincrementing behavior of those
databases is used instead.
so we dont have anything that is creating named sequences
automatically. im not sure what MS-SQL's needs are in this
department. but the autoincrement=True on Column is always like
that, it basically means use the database's auto-incrementing
feature, *if available*...if MS-SQL doesnt have that then you wont
get autoincrementing.
Now, if we want to say, SQLAlchemy should automatically create named
sequences for databases that use them, thats a change we can look
into making, and of course the question becomes what do we name the
sequences, and stuff like that. but at the moment thats not how its
designed, so thats probably why its not working.
On Jan 9, 2007, at 6:33 PM, Paul Johnston wrote:
Hi Rick,
I think we're actually agreeing about almost everything, although I
have
responded to your points individually. Lets see what Michael says
about
this. Actually reading over my post again, my preferred fix was a
little
hidden at the bottom. I will highlight it here:
The fix that seems logical to me is to put
the code to create an implicit sequence in Column.__init__. It's
down to
the individual engines how they implement this sequence, but its
existence or not is engine independent.
If all you're looking for is an implicit generation of an IDENTITY
column, you can use the autoincrement=True keyword to the Column
constructor.
Yep, that's true, but if I do that then session.flush() doesn't fetch
the pk, hence my bug report.
But be careful with the assumption of autoincrementing PKs. It's
perfectly valid to have a PK that is not autoincrementing. That's why
the pseudo-Sequence() mechanism is there in the first place, to
distinguish between the two (and to allow the specification of the
IDENTITY seed value).
Yep, hence the fairly complicated code in MSSQLSchemaGenerator
# install a IDENTITY Sequence if we have an implicit
IDENTITY column
if column.primary_key and column.autoincrement and
isinstance(column.type, sqltypes.Integer) and not column.foreign_key:
if column.default is None or (isinstance(column.default,
schema.Sequence ) and
[sqlalchemy] Re: MySQL Has Gone Away
Michael Bayer wrote: ok, dont change anything in turbogears yet. i think i have fixed the problem, please try out rev 2133. ticket #387 explains the issue. This issue seems very similar to one I found using the MySQL Zope database adapter. The symptom was the same (MySQL has gone away), and I think the underlying issue might be the same. Both Alchemy and Zope DA starting having this problem only after upgrading to MySQL 5. It seems that the Alchemy patch resolves the issue while closing the connection, but the real problem might be that the database closed the connection to the client, and the client was never made aware? The Zope DA uses a isConnected method to determine the validity of the client connection. The fix for it involved making this method a little more robust using the MySQL connection's ping, which (usually) automatically reconnects to the DB as necessary. If a MySQLdb.OperationalError is thrown while pinging, it closes the connection. Perhaps the issue is similar for Alchemy, it's just not manifesting itself until the connection is being closed. (Perhaps it is being closed *because* the connection is invalid?) I'm not sure if these issues are related, but they seem to be very similar. Just thought this information might give more ideas/another angle with which to view the problem. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] named cursor
server-side cursors (i.e. named) for all postgres functionality by default is currently in the trunk, and you can turn it off by saying client_side_cursors=True in create_engine(). however one user reported that table reflection breaks, which I cannot reproduce. so can some folks please try out the trunk with postgres and see if we can go with server side cursors by default? ive run the unit tests many times with both settings and i dont perceieve any performance hit from using server side cursors. id like this to be in the next release, but if theres some issue using server side cursors in all cases, then i have to break open the code and make it a lot more complex to detect the conditions where server-side cursors are appropriate. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL session.flush() doesn't always fetch primary keys
I myself don't need it, but Paul, if you want to take a swag at it, I'll be happy to review any patch. Rick On 1/10/07, Michael Bayer [EMAIL PROTECTED] wrote: the Column has the autoincrement=True flag on it, and you can detect that its a PRIMARY KEY column + Integer. if MS-SQL dialect wants to auto-create a sequence or whatever based on those conditions, its all yours. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] named cursor
Hi Mike, I've been lurking here in the shadows for a while...this particular topic looks very interesting to me and I'd love to upgrade to HEAD to try it out. However, I'm wondering what the risk factor is in doing that? Have there been any other major (potentially destabilizing) changes lately? I'm using r2168 right now, and it's been very stable. I've upgraded to HEAD quite a few times over the past year and I've always been impressed with (1) the overall stability of SA and (2) speed with which bugs were fixed when they are found. I'm just asking for a general idea of how stable you feel the trunk is right now. Don't worry, I'll take responsibility for my actions and will in no way hold you responsible for anything that may occur if I decide to upgrade right now--just looking for a general indicator. Thanks. ~ Daniel Michael Bayer wrote: server-side cursors (i.e. named) for all postgres functionality by default is currently in the trunk, and you can turn it off by saying client_side_cursors=True in create_engine(). however one user reported that table reflection breaks, which I cannot reproduce. so can some folks please try out the trunk with postgres and see if we can go with server side cursors by default? ive run the unit tests many times with both settings and i dont perceieve any performance hit from using server side cursors. id like this to be in the next release, but if theres some issue using server side cursors in all cases, then i have to break open the code and make it a lot more complex to detect the conditions where server-side cursors are appropriate. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] named cursor
it should be pretty stable, we havent had much dramatic going on. i moved some things around a bit in r2174 with regards to eager loading but i think those changes have ironed out pretty well. On Jan 10, 2007, at 10:39 PM, Daniel Miller wrote: Hi Mike, I've been lurking here in the shadows for a while...this particular topic looks very interesting to me and I'd love to upgrade to HEAD to try it out. However, I'm wondering what the risk factor is in doing that? Have there been any other major (potentially destabilizing) changes lately? I'm using r2168 right now, and it's been very stable. I've upgraded to HEAD quite a few times over the past year and I've always been impressed with (1) the overall stability of SA and (2) speed with which bugs were fixed when they are found. I'm just asking for a general idea of how stable you feel the trunk is right now. Don't worry, I'll take responsibility for my actions and will in no way hold you responsible for anything that may occur if I decide to upgrade right now--just looking for a general indicator. Thanks. ~ Daniel Michael Bayer wrote: server-side cursors (i.e. named) for all postgres functionality by default is currently in the trunk, and you can turn it off by saying client_side_cursors=True in create_engine(). however one user reported that table reflection breaks, which I cannot reproduce. so can some folks please try out the trunk with postgres and see if we can go with server side cursors by default? ive run the unit tests many times with both settings and i dont perceieve any performance hit from using server side cursors. id like this to be in the next release, but if theres some issue using server side cursors in all cases, then i have to break open the code and make it a lot more complex to detect the conditions where server-side cursors are appropriate. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: customize table creation
On 9 Jan., 22:59, Jonathan Ellis [EMAIL PROTECTED] wrote: You're trying to dynamically add columns to the table in the database, or just to the Table instance? Just to the Table instance, no database tables should exist at that point. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
