[sqlalchemy] Re: New problem with synonym (getter/setter) use in 0.5.2
its not really a bug. use real column objects for your order by expression, i.e.: order_by=_username, this is because when using declarative, string arguments used in relation()/backref() where there are ordinarily class or SQL expression objects are interpreted to be part of the registry of declared classes. Even in previous versions of SQLA or without using declarative, you dont want to put order_by='somecolname' in relation()/backref() - it prevents SQLAlchemy from aliasing that order by in the case of any kind of aliased join, subquery, or eager load. On Feb 4, 2009, at 1:07 AM, Ken wrote: This seems to have just come up after upgrading from 0.5.0rc4. Create a MySQL database (may happen with other engines?) called test and run this simple script to reproduce: http://dpaste.com/116443/ One gets this traceback: http://dpaste.com/116444/ It's happening because in backref, I'm specifying an orderby of 'username'. I also tried '_username' but that also fails. Thoughts? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] getting columns name from query result
Hi,
Are there any approach to get columns names from query result ?
I'm executing a dynamic sql statemet using text function and execute.
s = 'select * from foo where foo.name like = :name'
txt = text(s, bind=database.metadata.bind)
p = {'name':u'some name'}
result = txt.execute(p).fetchall()
Now, I need something like...
result.get_columns_names()
Thanks in advance.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Need SqlAlchemy Model Advice for a table with many foreign keys.
Thanks for this response. I do need all of the data available at once.
Specifically, here is what I'm trying to do. I'm following this
example right from the docs:
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relation, backref
class Address(Base):
... __tablename__ = 'addresses'
... id = Column(Integer, primary_key=True)
... email_address = Column(String, nullable=False)
... user_id = Column(Integer, ForeignKey('users.id'))
...
... user = relation(User, backref=backref('addresses',
order_by=id))
...
... def __init__(self, email_address):
... self.email_address = email_address
...
... def __repr__(self):
... return Address('%s') % self.email_address
Just dealing with two foreign key tables for now, I have foreign keys
into Members, and Gender in this MemberProfiles class. I inherit from
the declarative_base, as shown:
import sys
import pprint
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *
sys.path.append('../config')
import config
from Members import *
from Gender import *
Base = declarative_base()
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_profiles'),order_by=genderID)
def __init__(self, memberID, ...
def __repr__(self):
return MemberProfile('%s','%s',...
I try to run this in the unit test:
if __name__ == __main__:
member_profile_table = MemberProfile.__table__
metdata = Base.metadata
engine = create_engine(config.db_conn)
Session = sessionmaker(bind=engine)
session = Session()
memberProfile = session.query(MemberProfile).filter_by
(memberID=81017).first()
print \nOriginal record:
print memberProfile.__dict__
memberProfile.phonework=11
memberProfile.question=who?
session.commit()
I get this error:
debian-etch:/var/www/ReST/models# python MemberProfiles.py
Traceback (most recent call last):
File MemberProfiles.py, line 108, in ?
memberProfile = session.query(MemberProfile).filter_by
(memberID=81017).first()
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/session.py, line 908, in query
return self._query_cls(entities, self, **kwargs)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py, line 95, in __init__
self.__setup_aliasizers(self._entities)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/query.py, line 109, in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/util.py, line 466, in _entity_info
mapper = class_mapper(entity, compile)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/util.py, line 543, in class_mapper
mapper = mapper.compile()
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/mapper.py, line 679, in compile
mapper._post_configure_properties()
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/mapper.py, line 701, in _post_configure_properties
prop.init(key, self)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/interfaces.py, line 404, in init
self.do_init()
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/properties.py, line 574, in do_init
self._determine_targets()
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/orm/properties.py, line 588, in _determine_targets
self.mapper = mapper.class_mapper(self.argument(), compile=False)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc4-py2.4.egg/
sqlalchemy/ext/declarative.py, line 387, in return_cls
raise
[sqlalchemy] Re: Need SqlAlchemy Model Advice for a table with many foreign keys.
Don't know if this will work in your case.. but to handle joins to so
many static tables.
Note.. I didn't need to update these tables, nor query from them back
to the data tables. (i.e. no need to call Gender.member_profiles())
I did something like the following:
class MemberProfile(Base):
gender_by_desc = make_descriptor_for_static_field(tablename,
id_fieldname, desc_fieldname)('genderID')
with:
def make_descriptor_for_static_field(tablename, id_fieldname,
desc_fieldname)
class C(object):
_cache = None
def __init__(self, data_fieldname):
self.data_fieldname = data_fieldname
t = Table(tablename, metadata, autoload=True)
id_field = getattr(t.c, id_fieldname)
desc_field = getattr(t.c, desc_fieldname)
s = select([id_field, name_field])
result = engine.execute(s)
self._cache = dict(list(result))
def __get__(self, obj, objtype):
v = getattr(obj, self.data_fieldname)
if v is None:
return None
else:
return self._cache[v]
return C
On Feb 4, 6:02 pm, Gloria W strang...@comcast.net wrote:
Thanks for this response. I do need all of the data available at once.
Specifically, here is what I'm trying to do. I'm following this
example right from the docs:
d.
...
...
This same example works if I inherit from my Member class, but I don't
want to do this, since I have to also join Gender, and 40 something
other tables to this class.
What am I missing to make this work like the example shown?
Thank you immensely,
Gloria
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: New problem with synonym (getter/setter) use in 0.5.2
On Feb 4, 6:53 am, Michael Bayer mike...@zzzcomputing.com wrote: its not really a bug. use real column objects for your order by expression, i.e.: order_by=_username, Oh, that makes sense. Thanks for the explanation. -Ken --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Need SqlAlchemy Model Advice for a table with many foreign keys.
Excellent, thank you! Getting closer. I now use the same declarative_base on all instances, and I can now successfully refer to the relation() classes as actual class names instead of strings. During this query: memberProfile = session.query(MemberProfile).filter_by (memberID=81017).first() I get this error: Traceback (most recent call last): File MemberProfiles.py, line 108, in ? memberProfile = session.query(MemberProfile).filter_by (memberID=81017).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
[sqlalchemy] Re: Need SqlAlchemy Model Advice for a table with many foreign keys.
Just to make it easier to read, I'm missing a FROM clause: sqlalchemy.exc.ProgrammingError: (ProgrammingError) missing FROM- clause entry for table member_profiles at character 5151 I read from the archive that this was a bug in 0.4. Thanks again, Gloria --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Getting a column's type from the column
Hi. After working out my metadata issues (and scrapping four revisions and a Tomboy notepad of plans for my framework), I've realized that I am going to need something that is like FormAlchemy, but will convert models to a WTForms form instead. I've got the basis of the form field extraction code from the wtforms.ext.django.orm module, but what I need is a way to check the type of a column from the column instance. I'm having trouble tracing where the type is registered since everything inherits from three or four other classes, so is there a method or attribute of Column that contains the type provided? Regards again, Leaf --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: getting columns name from query result
maxi wrote:
Hi,
Are there any approach to get columns names from query result ?
I'm executing a dynamic sql statemet using text function and execute.
s = 'select * from foo where foo.name like = :name'
txt = text(s, bind=database.metadata.bind)
p = {'name':u'some name'}
result = txt.execute(p).fetchall()
Now, I need something like...
result.get_columns_names()
Thanks in advance.
The ResultProxy returned by execute has a keys attribute.
e = sa.create_engine('postgres:///test1')
rp = e.execute('select * from customers')
rp.keys
[u'id', u'name']
--Randall
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Getting a column's type from the column
PacSci wrote: Hi. After working out my metadata issues (and scrapping four revisions and a Tomboy notepad of plans for my framework), I've realized that I am going to need something that is like FormAlchemy, but will convert models to a WTForms form instead. I've got the basis of the form field extraction code from the wtforms.ext.django.orm module, but what I need is a way to check the type of a column from the column instance. I'm having trouble tracing where the type is registered since everything inherits from three or four other classes, so is there a method or attribute of Column that contains the type provided? Regards again, Leaf The type attribute (col.type) is all I'm aware of. I would study the types module (sqlalchemy.types), since col.type will subclass one of those base types. Also, col.type.get_col_spec() may be of interest. An example of a Postgresql text typed column, where t is the table def. t.c.name.type PGText(length=None, convert_unicode=False, assert_unicode=None) t.c.name.type.get_col_spec() 'TEXT' --Randall --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
