you're ordering the Member and Gender relation()s by a column in the parent table, which is producing the error. The order_by expression should be local to the Member or Gender entity.
On Feb 5, 2009, at 11:54 AM, Gloria W wrote: > > Hi All, > I have three classes, all using the same declarative_base() instance, > as follows: > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > In a config file: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > global Base > Base = None > > def initBase(): > global Base > if not Base: > Base = declarative_base() > > return Base > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > First class: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > sys.path.append('../config') > import config > > Base = config.initBase() > > class Member(Base): > __tablename__ = 'members' > > memberID = Column(Integer, primary_key=True) > question = Column(String) > answer = Column(String) > lockoutflag = Column(Boolean) > deleteflag = Column(Boolean) > firstname = Column(String) > lastname = Column(String) > middleinitial = Column(String) > alert = Column(String) > activeflag = Column(Boolean) > crmleadID = Column(String) > password = Column(String,name='pass') > insuranceflag = Column(Boolean) > applicationstatusID = Column(Integer) > identifier = Column(String) > email = Column(String) > > def __init__(self, member_id, question, answer, lockoutflag, > deleteflag, etc.. > def __repr__(self): > return "<Member('%s','%s',etc ... > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Second: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > sys.path.append('../config') > import config > > Base = config.initBase() > > class Gender(Base): > __tablename__ = 'member_gender' > genderID = Column(Integer, primary_key=True) > gender = Column(String,name='description') > gender_activeflag = Column(Boolean,name='activeflag') > gender_orderindex = Column(Float,name='orderindex') > > def __init__(self, genderID, description, activeflag, orderindex): > self.genderID = genderID > self.gender = description > self.gender_activeflag = activeflag > self.gender_orderindex = orderindex > > def __repr__(self): > return "<Gender('%s','%s','%s','%s')>" % (self.genderID, > self.gender, > self.activeflag, self.orderindex) > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Third Class, using foreign keys and relations into the first two > classes: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > sys.path.append('../config') > import config > > from Members import * > from Gender import * > > Base = config.initBase() > > class MemberProfile(Base): > __tablename__ = 'member_profiles' > > memberID = Column(Integer, ForeignKey('members.memberID'), > primary_key=True) > SSN = Column(String) > DOB = Column(Date) > industryID = Column(Integer) > primarysectorID = Column(Integer) > address1 = Column(String) > address2 = Column(String) > city = Column(String) > state = Column(String) > zip = Column(String) > howhearID = Column(Integer) > affiliationID = Column(Integer) > incomeID = Column(Integer) > worksituationID = Column(Integer) > currentinsuranceID = Column(Integer) > genderID = Column(Integer,ForeignKey('member_gender.genderID')) > referemail = Column(String) > occupation = Column(String) > phonehome = Column(String) > phonework = Column(String) > phonecell = Column(String) > phonefax = Column(String) > occupationID = Column(Integer) > occupationother = Column(String) > billing_address1 = Column(String) > billing_address2 = Column(String) > billing_city = Column(String) > billing_state = Column(String) > billing_zip = Column(String) > > member = relation(Member,lazy=False,backref=backref > ('members',order_by=memberID),cascade="all, delete") > > gender = relation(Gender,lazy=False,backref=backref > ('member_gender'),order_by=genderID) > > > def __init__(self, memberID, SSN=None, etc... > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > My unit test is here: > > Base = config.initBase() > metdata = Base.metadata > engine = create_engine(config.db_conn) > Session = sessionmaker(bind=engine) > session = Session() > > memberProfile = session.query(MemberProfile).filter_by > (memberID=81087).first() > print "\nOriginal record:" > print memberProfile.__dict__ > > > The error I get is here: > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > python MemberProfiles.py > Traceback (most recent call last): > File "MemberProfiles.py", line 108, in ? > memberProfile = session.query(MemberProfile).filter_by > (memberID=81087).first() > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/orm/query.py", line 1027, in first > ret = list(self[0:1]) > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/orm/query.py", line 952, in __getitem__ > return list(res) > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/orm/query.py", line 1088, in __iter__ > return self._execute_and_instances(context) > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/orm/query.py", line 1091, in _execute_and_instances > result = self.session.execute(querycontext.statement, > params=self._params, mapper=self._mapper_zero_or_none(), > _state=self._refresh_state) > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/orm/session.py", line 749, in execute > return self.__connection(engine, close_with_result=True).execute( > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/engine/base.py", line 806, in execute > return Connection.executors[c](self, object, multiparams, params) > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/engine/base.py", line 856, in execute_clauseelement > return self.__execute_context(context) > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/engine/base.py", line 878, in __execute_context > self._cursor_execute(context.cursor, context.statement, > context.parameters[0], context=context) > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/engine/base.py", line 927, in _cursor_execute > self._handle_dbapi_exception(e, statement, parameters, cursor) > File "/usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/ > sqlalchemy/engine/base.py", line 909, in _handle_dbapi_exception > raise exc.DBAPIError.instance(statement, parameters, e, > connection_invalidated=is_disconnect) > sqlalchemy.exc.ProgrammingError: (ProgrammingError) missing FROM- > clause entry for table "member_profiles" at character 5151 > 'SELECT anon_1."member_profiles_memberID" AS > "anon_1_member_profiles_memberID", anon_1."member_profiles_genderID" > AS "anon_1_member_profiles_genderID", anon_1."member_profiles_SSN" AS > "anon_1_member_profiles_SSN", anon_1."member_profiles_DOB" AS > "anon_1_member_profiles_DOB", anon_1."member_profiles_industryID" AS > "anon_1_member_profiles_industryID", > anon_1."member_profiles_primarysectorID" AS > "anon_1_member_profiles_primarysectorID", > anon_1.member_profiles_address1 AS anon_1_member_profiles_address1, > anon_1.member_profiles_address2 AS anon_1_member_profiles_address2, > anon_1.member_profiles_city AS anon_1_member_profiles_city, > anon_1.member_profiles_state AS anon_1_member_profiles_state, > anon_1.member_profiles_zip AS anon_1_member_profiles_zip, > anon_1."member_profiles_howhearID" AS > "anon_1_member_profiles_howhearID", > anon_1."member_profiles_affiliationID" AS > "anon_1_member_profiles_affiliationID", > anon_1."member_profiles_incomeID" AS > "anon_1_member_profiles_incomeID", > anon_1."member_profiles_worksituationID" AS > "anon_1_member_profiles_worksituationID", > anon_1."member_profiles_currentinsuranceID" AS > "anon_1_member_profiles_currentinsuranceID", > anon_1.member_profiles_referemail AS > anon_1_member_profiles_referemail, anon_1.member_profiles_occupation > AS anon_1_member_profiles_occupation, anon_1.member_profiles_phonehome > AS anon_1_member_profiles_phonehome, anon_1.member_profiles_phonework > AS anon_1_member_profiles_phonework, anon_1.member_profiles_phonecell > AS anon_1_member_profiles_phonecell, anon_1.member_profiles_phonefax > AS anon_1_member_profiles_phonefax, > anon_1."member_profiles_occupationID" AS > "anon_1_member_profiles_occupationID", > anon_1.member_profiles_occupationother AS > anon_1_member_profiles_occupationother, > anon_1.member_profiles_billing_address1 AS > anon_1_member_profiles_billing_address1, > anon_1.member_profiles_billing_address2 AS > anon_1_member_profiles_billing_address2, > anon_1.member_profiles_billing_city AS > anon_1_member_profiles_billing_city, > anon_1.member_profiles_billing_state AS > anon_1_member_profiles_billing_state, > anon_1.member_profiles_billing_zip AS > anon_1_member_profiles_billing_zip, members_1.pass AS members_1_pass, > members_1."memberID" AS "members_1_memberID", members_1.question AS > members_1_question, members_1.answer AS members_1_answer, > members_1.lockoutflag AS members_1_lockoutflag, members_1.deleteflag > AS members_1_deleteflag, members_1.firstname AS members_1_firstname, > members_1.lastname AS members_1_lastname, members_1.middleinitial AS > members_1_middleinitial, members_1.alert AS members_1_alert, > members_1.activeflag AS members_1_activeflag, members_1."crmleadID" AS > "members_1_crmleadID", members_1.insuranceflag AS > members_1_insuranceflag, members_1."applicationstatusID" AS > "members_1_applicationstatusID", members_1.identifier AS > members_1_identifier, members_1.email AS members_1_email, > member_gender_1.description AS member_gender_1_description, > member_gender_1.activeflag AS member_gender_1_activeflag, > member_gender_1.orderindex AS member_gender_1_orderindex, > member_gender_1."genderID" AS "member_gender_1_genderID" \nFROM > (SELECT member_profiles."memberID" AS "member_profiles_memberID", > member_profiles."genderID" AS "member_profiles_genderID", > member_profiles."SSN" AS "member_profiles_SSN", member_profiles."DOB" > AS "member_profiles_DOB", member_profiles."industryID" AS > "member_profiles_industryID", member_profiles."primarysectorID" AS > "member_profiles_primarysectorID", member_profiles.address1 AS > member_profiles_address1, member_profiles.address2 AS > member_profiles_address2, member_profiles.city AS > member_profiles_city, member_profiles.state AS member_profiles_state, > member_profiles.zip AS member_profiles_zip, > member_profiles."howhearID" AS "member_profiles_howhearID", > member_profiles."affiliationID" AS "member_profiles_affiliationID", > member_profiles."incomeID" AS "member_profiles_incomeID", > member_profiles."worksituationID" AS > "member_profiles_worksituationID", > member_profiles."currentinsuranceID" AS > "member_profiles_currentinsuranceID", member_profiles.referemail AS > member_profiles_referemail, member_profiles.occupation AS > member_profiles_occupation, member_profiles.phonehome AS > member_profiles_phonehome, member_profiles.phonework AS > member_profiles_phonework, member_profiles.phonecell AS > member_profiles_phonecell, member_profiles.phonefax AS > member_profiles_phonefax, member_profiles."occupationID" AS > "member_profiles_occupationID", member_profiles.occupationother AS > member_profiles_occupationother, member_profiles.billing_address1 AS > member_profiles_billing_address1, member_profiles.billing_address2 AS > member_profiles_billing_address2, member_profiles.billing_city AS > member_profiles_billing_city, member_profiles.billing_state AS > member_profiles_billing_state, member_profiles.billing_zip AS > member_profiles_billing_zip \nFROM member_profiles \nWHERE > member_profiles."memberID" = %(memberID_1)s \n LIMIT 1 OFFSET 0) AS > anon_1 LEFT OUTER JOIN members AS members_1 ON members_1."memberID" = > anon_1."member_profiles_memberID" LEFT OUTER JOIN member_gender AS > member_gender_1 ON member_gender_1."genderID" = > anon_1."member_profiles_genderID" ORDER BY > member_profiles."genderID"' {'memberID_1': 81087} > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > Looks like an error in the outer join of the SQL statement. Am I > missing something, or is this a bug? > > Many thank-yous in advance, and sorry for the length of this. > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---