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