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
-~----------~----~----~----~------~----~------~--~---
