Hah! When you said I'd have to wait for a patch, I didn't expect to get one same-day!
Thanks so much for your help with this Mike. After all this, I'll definitely be donating to SQLAlchemy when I get paid next Thursday :) Cameron Jackson Engineering Intern Air Operations Thales Australia Thales Australia Centre, WTC Northbank Wharf, Concourse Level, Siddeley Street, Melbourne, VIC 3005, Australia Tel: +61 3 8630 4591 cameron.jack...@thalesgroup.com.au<mailto:cameron.jack...@thalesgroup.com.au> | www.thalesgroup.com.au<http://www.thalesgroup.com.au> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Wednesday, 7 December 2011 1:56 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] SQLAlchemy emitting INTEGER instead of SERIAL- postgres sequence isn't created Use the latest tip: http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz and here's an example: from sqlalchemy import Integer, ForeignKey, String, \ Column, UniqueConstraint, ForeignKeyConstraint from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship Base = declarative_base() class Entry(Base): __tablename__ = 'entry' entry_id = Column(Integer, primary_key=True) widget_id = Column(Integer, ForeignKey('widget.widget_id')) name = Column(String(50)) __table_args__ = ( UniqueConstraint("entry_id", "widget_id"), ) class Widget(Base): __tablename__ = 'widget' widget_id = Column(Integer, autoincrement='ignore_fk', primary_key=True) favorite_entry_id = Column(Integer) name = Column(String(50)) __table_args__ = ( ForeignKeyConstraint( ["widget_id", "favorite_entry_id"], ["entry.widget_id", "entry.entry_id"], name="fk_favorite_entry", use_alter=True ), ) entries = relationship(Entry, primaryjoin= widget_id==Entry.widget_id, foreign_keys=Entry.widget_id) favorite_entry = relationship(Entry, primaryjoin= favorite_entry_id==Entry.entry_id, foreign_keys=favorite_entry_id, post_update=True) On Dec 6, 2011, at 8:50 PM, Jackson, Cameron wrote: Thought it might be something like that. For now I guess I'll just make the change manually. Thanks. Cameron Jackson Engineering Intern Air Operations Thales Australia Thales Australia Centre, WTC Northbank Wharf, Concourse Level, Siddeley Street, Melbourne, VIC 3005, Australia Tel: +61 3 8630 4591 cameron.jack...@thalesgroup.com.au<mailto:cameron.jack...@thalesgroup.com.au> | www.thalesgroup.com.au<http://www.thalesgroup.com.au/> From: sqlalchemy@googlegroups.com<mailto:sqlalchemy@googlegroups.com> [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Wednesday, 7 December 2011 12:46 PM To: sqlalchemy@googlegroups.com<mailto:sqlalchemy@googlegroups.com> Subject: Re: [sqlalchemy] SQLAlchemy emitting INTEGER instead of SERIAL- postgres sequence isn't created On Dec 6, 2011, at 8:02 PM, Jackson, Cameron wrote: For the background to the code I'm using, see this Stack Overflow question: http://stackoverflow.com/questions/8394177/complex-foreign-key-constraint-in-sqlalchemy/8408659 My declarative code looks like this: from MyGlobals import Base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, ForeignKeyConstraint from sqlalchemy.orm import relationship class SystemVariable(Base): __tablename__ = 'SystemVariables' id = Column(Integer, primary_key = True) name = Column(String) choice_id = Column(Integer) __table_args__ = (ForeignKeyConstraint(['choice_id', 'id'], ['VariableOptions.id', 'VariableOptions.variable_id'], name = 'SystemVariables_choice_id_fkey', use_alter = True), {}) options = relationship('VariableOption', backref = 'variable', cascade = 'all, delete, delete-orphan') choice = relationship('VariableOption', post_update = True) class VariableOption(Base): __tablename__ = 'VariableOptions' id = Column(Integer, primary_key = True) name = Column(String) variable_id = Column(Integer, ForeignKey('SystemVariables.id')) __table_args__ = (UniqueConstraint('id', 'variable_id'), {}) For some reason, SQLA emits id SERIAL NOT NULL for the second table (VariableOptions), but for the first one (SystemVariables) it's doing id INTEGER NOT NULL. Seeing as I'm using Postgres, this means that the sequence is not created for SystemVariables, which is a problem. I've tried adding autoincrement = True and Sequence('SystemVariables_id_seq') to the Column declaration, but neither helped. None of my other tables have this problem. Why is SQLA doing this for this one, and how can I fix it? yeah you might have to wait for a patch on this one. SQLAlchemy universally considers a PK col with an FK to not be autoincrementing. Thanks, Cameron Jackson Engineering Intern Air Operations Thales Australia Thales Australia Centre, WTC Northbank Wharf, Concourse Level, Siddeley Street, Melbourne, VIC 3005, Australia Tel: +61 3 8630 4591 cameron.jack...@thalesgroup.com.au<mailto:cameron.jack...@thalesgroup.com.au> | www.thalesgroup.com.au<http://www.thalesgroup.com.au/> ------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. ------------------------------------------------------------------------- -- 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<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto: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 sqlalchemy@googlegroups.com<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto:sqlalchemy+unsubscr...@googlegroups.com>. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. ------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. ------------------------------------------------------------------------- -- 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<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto: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 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. ------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. ------------------------------------------------------------------------- -- 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.
