Re: [sqlalchemy] Multi-column primary key with autoincrement?
Heyho! [multi-column primary key where one column is autoincrement int] On Wednesday 16 December 2009 05.29:54 Daniel Falk wrote: The true problem here is with sqlite, which tries to make a smart choice about whether to autoincrement or not. And it gets it wrong. SQLAlchemy is correct to not specify the id field in the INSERT statement. That's the cue to the db that it needs to supply that value on its own. Hmm. Closer inspection shows that * sqlite doesn't even support the scenario I want (autoincrement on one column with multi column primary key) and * sqlalchemy doesn't notice this and just creates a two column primary key without autoincrement at all. Is there any hope that sqlalchemy will start to simulate the feature on sqlite? (I'm using the python-pysqlite2 2.5.5-3 / libsqlite3-0 3.6.20-1 / python-sqlalchemy 0.5.6-1 packages from Debian squeeze, btw) I've opened #1642 now. PostgreSQL handles this in just the way I was expecting (no surprise since my expectation on autoincrement columns is derived from the way pg builds its serial data type :-), so I'll have to test if sqlalchemy will do the right thing here. Then I can at least use pg (I was planning to do so in production anyway; sqlite is convenient for development though.) cheers -- vbi -- featured product: GNU Privacy Guard - http://gnupg.org signature.asc Description: This is a digitally signed message part.
Re: [sqlalchemy] Multi-column primary key with autoincrement?
Adrian von Bidder wrote: Heyho! [multi-column primary key where one column is autoincrement int] On Wednesday 16 December 2009 05.29:54 Daniel Falk wrote: The true problem here is with sqlite, which tries to make a smart choice about whether to autoincrement or not. And it gets it wrong. SQLAlchemy is correct to not specify the id field in the INSERT statement. That's the cue to the db that it needs to supply that value on its own. Hmm. Closer inspection shows that * sqlite doesn't even support the scenario I want (autoincrement on one column with multi column primary key) and * sqlalchemy doesn't notice this and just creates a two column primary key without autoincrement at all. Is there any hope that sqlalchemy will start to simulate the feature on sqlite? (I'm using the python-pysqlite2 2.5.5-3 / libsqlite3-0 3.6.20-1 / python-sqlalchemy 0.5.6-1 packages from Debian squeeze, btw) I've opened #1642 now. Sorry, I've closed it. SQLite doesn't support autoincrement on composite PKs and theres no one-size-fits-all way to simulate this, so its up to the user. You need to either use the default keyword and specify a function or SQL expression that will generate new identifiers, or just set up the PK attributes on your new objects before adding them to the session. PostgreSQL handles this in just the way I was expecting (no surprise since my expectation on autoincrement columns is derived from the way pg builds its serial data type :-), so I'll have to test if sqlalchemy will do the right thing here. Then I can at least use pg (I was planning to do so in production anyway; sqlite is convenient for development though.) absolutely, PG uses sequences and has no issue here. the no-autoincrement-sqlite thing is an only-sqlite issue and we also have some unit tests that specifically skip sqlite for this reason. cheers -- vbi -- featured product: GNU Privacy Guard - http://gnupg.org -- 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] Multi-column primary key with autoincrement?
On Dec 16, 2009, at 09:32 , Adrian von Bidder wrote: sqlite is convenient for development though Except when it *adds* complexity? ;-) -- Alex Brasetvik -- 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] Multi-column primary key with autoincrement?
Heyho! On Wednesday 16 December 2009 16:36:10 Michael Bayer wrote: You need to either use the default keyword and specify a function or SQL expression that will generate new identifiers, or just set up the PK attributes on your new objects before adding them to the session. ... or just switch to pg for testing. I don't care to support other db anyway. Ok, thanks for clearing this up. cheers -- vbi -- If we can capitalize on something that did or did not happen in 1947 then it can help the entire state. -- Rep. Dan Foley on inventing the Extraterrestrial Culture Day signature.asc Description: This is a digitally signed message part.
Re: [sqlalchemy] Multi-column primary key with autoincrement?
Adrian von Bidder wrote: Heyho! My small blog-style web site should support article versioning, so: class Entry(DeclarativeBase): id = Column(Integer, autoincrement=True, primary_key=True) version = Column(Integer, primary_key=True, default=0) ... and more stuff (content, author, ...) it seems autoincrement is not supported in this case. (I'm working in a TurboGears 2 environment with SQLite; final deployment will probably be with PostgreSQL) At least, when trying to populate a db from TurboGear's pasteer set-up, I get an IntegrityError (id may not be NULL) and see that id is not set in the INSERT statement. Just removing the version column from the Entry class suffices to let it work as before. Sorry to be so late in responding to this, but hey. Maybe better late than never. Anyway, I've had the same problem. The true problem here is with sqlite, which tries to make a smart choice about whether to autoincrement or not. And it gets it wrong. SQLAlchemy is correct to not specify the id field in the INSERT statement. That's the cue to the db that it needs to supply that value on its own. -- 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] Multi-column primary key with autoincrement?
Heyho! My small blog-style web site should support article versioning, so: class Entry(DeclarativeBase): id = Column(Integer, autoincrement=True, primary_key=True) version = Column(Integer, primary_key=True, default=0) ... and more stuff (content, author, ...) it seems autoincrement is not supported in this case. (I'm working in a TurboGears 2 environment with SQLite; final deployment will probably be with PostgreSQL) At least, when trying to populate a db from TurboGear's pasteer set-up, I get an IntegrityError (id may not be NULL) and see that id is not set in the INSERT statement. Just removing the version column from the Entry class suffices to let it work as before. Additionally: to keep the queries simple and fast, I'll introduce a current flag which should be set to True for the latest version of any id. Are there hooks I can use to manage this? (something like: execute UPDATE ... set current = False where version myversion whenver an Entry is being added to the db with a version 0) And a third qestion: what is the easiest way to create a new version? copy my 'Entry' object element by element to a new instance, or does sqla provide a cloning mechanism? thanks a lot! cheers -- vbi -- SCO's lawsuit is a lost cause. The implications for Linux users are rather like the implications for passengers on an ocean liner of a seagull diving into the water nearby. -- Thomas Carey, Bromberg Sunstein, LLP, attorney signature.asc Description: This is a digitally signed message part.
