[sqlalchemy] Query on a related object's field
What is the easiest way of getting the equivalent of this: session.query(Someclass).filter_by(related_obj.field=somevalue) Ie. I want to filter by a field of an object that is in relation to objects of Someclass. My original idea was to add related_obj.field as a new relation in the mapper for Someclass but I just couldn't accomplish that. Is there an easier way? --~--~-~--~~~---~--~~ 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: Query on a related object's field
Dnia 2009-11-13, Pt o godzinie 05:02 -0800, bojanb pisze: What is the easiest way of getting the equivalent of this: session.query(Someclass).filter_by(related_obj.field=somevalue) Ie. I want to filter by a field of an object that is in relation to objects of Someclass. My original idea was to add related_obj.field as a new relation in the mapper for Someclass but I just couldn't accomplish that. Is there an easier way? http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with-joins ? Tomasz Jezierski Tefnet www.tefnet.pl --~--~-~--~~~---~--~~ 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: Query on a related object's field
I haven't seen how to do this using the relation directly. I do: session.query(Someclass).join(OtherClass).filter_by(OtherClass.field==somevalue) I did do some experimenting with a more abstract approach, but did not find any need in my application. The only advantage is that it takes away the need to know up front what is the name of the other class. That might be useful if you are building a framework based on SA, but not in most applications. otherclas = SomeClass.relname.property.mapper session.query(Someclass).join(otherclas).filter_by(otherclas.c.field==somevalue) --~--~-~--~~~---~--~~ 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: Query on a related object's field
Mike Conley wrote: I haven't seen how to do this using the relation directly. I do: session.query(Someclass).join(OtherClass).filter_by(OtherClass.field==somevalue) I did do some experimenting with a more abstract approach, but did not find any need in my application. The only advantage is that it takes away the need to know up front what is the name of the other class. That might be useful if you are building a framework based on SA, but not in most applications. otherclas = SomeClass.relname.property.mapper session.query(Someclass).join(otherclas).filter_by(otherclas.c.field==somevalue) Another approach is using the has/any methods of the relation: If SomeClass.relation is many-to-one or one-to-one: session.query(SomeClass).filter(SomeClass.relation.has(field=somevalue)) or if the relation is one-to-many or many-to-many: session.query(SomeClass).filter(SomeClass.relation.any(field=somevalue)) This approach produces an EXISTS clause in the SQL, so it's usually slower than the join approach. On the other hand, it may be easier to use inside more complicated queries (e.g. inside AND, OR, or NOT expressions). -Conor --~--~-~--~~~---~--~~ 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] Writing a new database dialect
I'm writing a new database dialect for sqlalchemy 0.6 for Netezza. This is on Windows. I have an ODBC driver for Netezza. A couple of questions: - Why isn't there a generic talk to an ODBC source dialect? I thought that was the beauty of ODBC. Is this possible and just hasn't been done, or is there some technical reason that (for example) the SQL Server ODBC dialect won't just work? - Is there some documentation on writing a dialect, specifically for 0.6? I've looked around, but not found anything. Or is my best bet just to look at an existing dialect? Thanks! Eric. --~--~-~--~~~---~--~~ 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] NoSuchColumnError when process is forked.
I've been using sqlalchemy for the past couple months in development without incident until I started applying unique indexes. I've had issues with duplicate rows as expected, but now i'm seeing a different error that I hadn't seen before... NoSuchColumnError: Could not locate column in row for column 'rosetta_keys.id' It's hard to reproduce because it only shows up (some of the time) when the process forks and only when indexes are applied to tables in question. It's not always the same table, but I can say that it occurs when two processes are touching the same table. I'm using sqlsoup to make this all happen. I'm using sqlalchemy 0.56 Ubuntu 9.10 64-bit, python 2.6 mysql-python 1.2.3c1 Here's a stack trace... Traceback (most recent call last): File /home/ffernand/src/thescore-feed/feeds/fss/worker.py, line 46, in process_queue (errorlog, created, updated), processed = method(xmldoc), True File /home/ffernand/src/thescore-feed/feeds/__init__.py, line 129, in handle results = fn(*args, **kwargs) File /home/ffernand/src/thescore-feed/feeds/__init__.py, line 182, in handle results = fn(*args, **kwargs) File /home/ffernand/src/thescore-feed/feeds/__init__.py, line 152, in handle results = fn(*args, **kwargs) File /home/ffernand/src/thescore-feed/lib/feeds/sport/hockey/ entities.py, line 114, in handle results = fn(*args, **kwargs) File /home/ffernand/src/thescore-feed/feeds/fss/leagues/nhl/ playerstats.py, line 26, in NHL_PLAYERSTATS player_id = get_player_id(global_id, last_name, first_name) File /home/ffernand/src/thescore-feed/lib/feeds/sport/hockey/ entities.py, line 55, in get_player_id if global_id else None File /home/ffernand/src/thescore-feed/lib/feeds/rosetta.py, line 24, in get_rosetta_id obj, created = orm.db.rosetta_keys.filter_by(**filter).first(), False File ./lib/vendor/sqlalchemy/orm/query.py, line 1300, in first ret = list(self[0:1]) File ./lib/vendor/sqlalchemy/orm/query.py, line 1221, in __getitem__ return list(res) File ./lib/vendor/sqlalchemy/orm/query.py, line 1420, in instances process[0](context, row, rows) File ./lib/vendor/sqlalchemy/orm/query.py, line 2029, in main _instance(row, result) File ./lib/vendor/sqlalchemy/orm/mapper.py, line 1631, in _instance identitykey = identity_key(row) File ./lib/vendor/sqlalchemy/orm/mapper.py, line 1572, in identity_key return (identity_class, tuple(row[column] for column in pk_cols)) File ./lib/vendor/sqlalchemy/orm/mapper.py, line 1572, in genexpr return (identity_class, tuple(row[column] for column in pk_cols)) File ./lib/vendor/sqlalchemy/engine/base.py, line 1348, in __getitem__ return self.__parent._get_col(self.__row, key) File ./lib/vendor/sqlalchemy/engine/base.py, line 1609, in _get_col type_, processor, index = self._props[key] File ./lib/vendor/sqlalchemy/util.py, line 68, in __missing__ self[key] = val = self.creator(key) File ./lib/vendor/sqlalchemy/engine/base.py, line 1507, in fallback raise exc.NoSuchColumnError(Could not locate column in row for column '%s' % (str(key))) NoSuchColumnError: Could not locate column in row for column 'rosetta_keys.id' Any suggestions would be greatly appreciated... thanks, filipe --~--~-~--~~~---~--~~ 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: NoSuchColumnError when process is forked.
ffernand wrote: I've been using sqlalchemy for the past couple months in development without incident until I started applying unique indexes. I've had issues with duplicate rows as expected, but now i'm seeing a different error that I hadn't seen before... NoSuchColumnError: Could not locate column in row for column 'rosetta_keys.id' It's hard to reproduce because it only shows up (some of the time) when the process forks and only when indexes are applied to tables in question. It's not always the same table, but I can say that it occurs when two processes are touching the same table. I'm using sqlsoup to make this all happen. when you fork a child, any engines you have created must be replaced with new engines. TCP connections don't really travel across forks very well so you'll want to ensure create_engine() is called at the beginning of each child, and applied to all places where an engine is needed (i.e. sessions, metadata objects). I'm using sqlalchemy 0.56 Ubuntu 9.10 64-bit, python 2.6 mysql-python 1.2.3c1 Here's a stack trace... Traceback (most recent call last): File /home/ffernand/src/thescore-feed/feeds/fss/worker.py, line 46, in process_queue (errorlog, created, updated), processed = method(xmldoc), True File /home/ffernand/src/thescore-feed/feeds/__init__.py, line 129, in handle results = fn(*args, **kwargs) File /home/ffernand/src/thescore-feed/feeds/__init__.py, line 182, in handle results = fn(*args, **kwargs) File /home/ffernand/src/thescore-feed/feeds/__init__.py, line 152, in handle results = fn(*args, **kwargs) File /home/ffernand/src/thescore-feed/lib/feeds/sport/hockey/ entities.py, line 114, in handle results = fn(*args, **kwargs) File /home/ffernand/src/thescore-feed/feeds/fss/leagues/nhl/ playerstats.py, line 26, in NHL_PLAYERSTATS player_id = get_player_id(global_id, last_name, first_name) File /home/ffernand/src/thescore-feed/lib/feeds/sport/hockey/ entities.py, line 55, in get_player_id if global_id else None File /home/ffernand/src/thescore-feed/lib/feeds/rosetta.py, line 24, in get_rosetta_id obj, created = orm.db.rosetta_keys.filter_by(**filter).first(), False File ./lib/vendor/sqlalchemy/orm/query.py, line 1300, in first ret = list(self[0:1]) File ./lib/vendor/sqlalchemy/orm/query.py, line 1221, in __getitem__ return list(res) File ./lib/vendor/sqlalchemy/orm/query.py, line 1420, in instances process[0](context, row, rows) File ./lib/vendor/sqlalchemy/orm/query.py, line 2029, in main _instance(row, result) File ./lib/vendor/sqlalchemy/orm/mapper.py, line 1631, in _instance identitykey = identity_key(row) File ./lib/vendor/sqlalchemy/orm/mapper.py, line 1572, in identity_key return (identity_class, tuple(row[column] for column in pk_cols)) File ./lib/vendor/sqlalchemy/orm/mapper.py, line 1572, in genexpr return (identity_class, tuple(row[column] for column in pk_cols)) File ./lib/vendor/sqlalchemy/engine/base.py, line 1348, in __getitem__ return self.__parent._get_col(self.__row, key) File ./lib/vendor/sqlalchemy/engine/base.py, line 1609, in _get_col type_, processor, index = self._props[key] File ./lib/vendor/sqlalchemy/util.py, line 68, in __missing__ self[key] = val = self.creator(key) File ./lib/vendor/sqlalchemy/engine/base.py, line 1507, in fallback raise exc.NoSuchColumnError(Could not locate column in row for column '%s' % (str(key))) NoSuchColumnError: Could not locate column in row for column 'rosetta_keys.id' Any suggestions would be greatly appreciated... thanks, filipe --~--~-~--~~~---~--~~ 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] TypeError when appending to Associationproxy
Hello,
Just getting started with association proxy and having trouble making
it work. The lookups seem to work fine but when I try to add a new
element to the association, I get TypeError: __init__() takes exactly
1 argument (2 given). Code test case below, I've left a bunch of
columns out for brevity:
from sqlalchemy import Column, Integer, String, DateTime, Boolean,
ForeignKey, Text, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.associationproxy import association_proxy
Base = declarative_base()
class Org(Base):
__tablename__ = 'tOrg'
id = Column(Integer, primary_key=True, name='OrgID')
name = Column(String(100), name='OrgName')
def __repr__(self):
return Org(%s: '%s') % (self.id, self.name,)
class News(Base):
__tablename__ = 'tNews'
id = Column(Integer, primary_key=True, name='NewsID')
title = Column(String(255), name='NewsTitle')
body = Column(Text, name='NewsBody')
author = Column(String(255), name='NewsAuthor')
is_active = Column(Boolean, name='NewsActive')
date = Column(Date, name='NewsDate')
priority = Column(Integer, name='NewsPriority')
orgs = association_proxy('newsorgs', 'org')
def __repr__(self):
return News(%s: '%s') % (self.id, self.title,)
class NewsOrg(Base):
__tablename__ = 'trefNewsOrg'
id = Column(Integer, primary_key=True, name='NewsOrgID')
news_id = Column(Integer, ForeignKey(News.id), name='NewsID')
news = relation('News', backref=backref('newsorgs'))
org_id = Column(Integer, ForeignKey(Org.id), name='OrgID')
org = relation(Org)
def __repr__(self):
if self.org:
orgname = self.org.name
else:
orgname = 'ALL'
return NewsOrg(%s: '%s', (%s)) % (self.id,
self.news.title, orgname,)
def testcase(session):
myorg = session.query(Org).filter(Org.id==6).one()
otherorg_news_associations = session.query(NewsOrg).filter
(NewsOrg.org_id==1).all()
mystory = otherorg_news_associations[0].news
mystory.orgs.append(myorg) # -- TypeError: __init__() takes
exactly 1 argument (2 given)
session.commit()
--~--~-~--~~~---~--~~
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: TypeError when appending to Associationproxy
Matthew R wrote:
Hello,
Just getting started with association proxy and having trouble making
it work. The lookups seem to work fine but when I try to add a new
element to the association, I get TypeError: __init__() takes exactly
1 argument (2 given). Code test case below, I've left a bunch of
columns out for brevity:
from sqlalchemy import Column, Integer, String, DateTime, Boolean,
ForeignKey, Text, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.associationproxy import association_proxy
Base = declarative_base()
class Org(Base):
__tablename__ = 'tOrg'
id = Column(Integer, primary_key=True, name='OrgID')
name = Column(String(100), name='OrgName')
def __repr__(self):
return Org(%s: '%s') % (self.id, self.name,)
class News(Base):
__tablename__ = 'tNews'
id = Column(Integer, primary_key=True, name='NewsID')
title = Column(String(255), name='NewsTitle')
body = Column(Text, name='NewsBody')
author = Column(String(255), name='NewsAuthor')
is_active = Column(Boolean, name='NewsActive')
date = Column(Date, name='NewsDate')
priority = Column(Integer, name='NewsPriority')
orgs = association_proxy('newsorgs', 'org')
def __repr__(self):
return News(%s: '%s') % (self.id, self.title,)
class NewsOrg(Base):
__tablename__ = 'trefNewsOrg'
id = Column(Integer, primary_key=True, name='NewsOrgID')
news_id = Column(Integer, ForeignKey(News.id), name='NewsID')
news = relation('News', backref=backref('newsorgs'))
org_id = Column(Integer, ForeignKey(Org.id), name='OrgID')
org = relation(Org)
def __repr__(self):
if self.org:
orgname = self.org.name
else:
orgname = 'ALL'
return NewsOrg(%s: '%s', (%s)) % (self.id,
self.news.title, orgname,)
def testcase(session):
myorg = session.query(Org).filter(Org.id==6).one()
otherorg_news_associations = session.query(NewsOrg).filter
(NewsOrg.org_id==1).all()
mystory = otherorg_news_associations[0].news
mystory.orgs.append(myorg) # -- TypeError: __init__() takes
exactly 1 argument (2 given)
session.commit()
The association_proxy is trying to create the NewsOrg object by calling
NewsOrg(myorg). Since your NewsOrg class does not override __init__, the
default declarative __init__ is used, which takes only keyword
arguments. This causes the TypeError: __init__() takes exactly 1
argument (2 given) error.
You can fix this by either adding an __init__ method to NewsOrg like this:
def __init__(self, org=None, **kwargs):
super(NewsOrg, self).__init__(**kwargs)
self.org = org
or, preferably, add a creator argument to association_proxy:
orgs = association_proxy('newsorgs', 'org', creator=lambda org:
NewsOrg(org=org))
You can find out more about the 'creator' argument at:
http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html#api
-Conor
--~--~-~--~~~---~--~~
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: NoSuchColumnError when process is forked.
On Fri, Nov 13, 2009 at 2:16 PM, Michael Bayer mike...@zzzcomputing.com wrote: ffernand wrote: I've been using sqlalchemy for the past couple months in development without incident until I started applying unique indexes. I've had issues with duplicate rows as expected, but now i'm seeing a different error that I hadn't seen before... NoSuchColumnError: Could not locate column in row for column 'rosetta_keys.id' It's hard to reproduce because it only shows up (some of the time) when the process forks and only when indexes are applied to tables in question. It's not always the same table, but I can say that it occurs when two processes are touching the same table. I'm using sqlsoup to make this all happen. when you fork a child, any engines you have created must be replaced with new engines. TCP connections don't really travel across forks very well so you'll want to ensure create_engine() is called at the beginning of each child, and applied to all places where an engine is needed (i.e. sessions, metadata objects). thanks for the quick reply... I can say, that that is not the case... I do re-create the engine (using create_engine) upon fork. After a fork the following is executed. orm.session.close_all() conn = orm.session.connection() conn.detach(); conn.close() (the above i think is overkill) then I recreate... engine = create_engine(conn_str, convert_unicode=True, echo= False, echo_pool= False) Session.configure(bind= engine, autoflush= True, autocommit= True, expire_on_commit= True) orm.db, orm.session = SqlSoup(MetaData(engine)), Session() Also, the exception NoSuchColumnError never shows up until unique indexes are applied thoughout the tables. --~--~-~--~~~---~--~~ 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: TypeError when appending to Associationproxy
Excellent, thank you. A few minutes after I posted I was able to
figure out the NewsOrg.__init__ method fix, but the creator lambda
argument to association_proxy is much cleaner. Thanks again!
On Nov 13, 2:45 pm, Conor conor.edward.da...@gmail.com wrote:
Matthew R wrote:
Hello,
Just getting started with association proxy and having trouble making
it work. The lookups seem to work fine but when I try to add a new
element to the association, I get TypeError: __init__() takes exactly
1 argument (2 given). Code test case below, I've left a bunch of
columns out for brevity:
from sqlalchemy import Column, Integer, String, DateTime, Boolean,
ForeignKey, Text, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.associationproxy import association_proxy
Base = declarative_base()
class Org(Base):
__tablename__ = 'tOrg'
id = Column(Integer, primary_key=True, name='OrgID')
name = Column(String(100), name='OrgName')
def __repr__(self):
return Org(%s: '%s') % (self.id, self.name,)
class News(Base):
__tablename__ = 'tNews'
id = Column(Integer, primary_key=True, name='NewsID')
title = Column(String(255), name='NewsTitle')
body = Column(Text, name='NewsBody')
author = Column(String(255), name='NewsAuthor')
is_active = Column(Boolean, name='NewsActive')
date = Column(Date, name='NewsDate')
priority = Column(Integer, name='NewsPriority')
orgs = association_proxy('newsorgs', 'org')
def __repr__(self):
return News(%s: '%s') % (self.id, self.title,)
class NewsOrg(Base):
__tablename__ = 'trefNewsOrg'
id = Column(Integer, primary_key=True, name='NewsOrgID')
news_id = Column(Integer, ForeignKey(News.id), name='NewsID')
news = relation('News', backref=backref('newsorgs'))
org_id = Column(Integer, ForeignKey(Org.id), name='OrgID')
org = relation(Org)
def __repr__(self):
if self.org:
orgname = self.org.name
else:
orgname = 'ALL'
return NewsOrg(%s: '%s', (%s)) % (self.id,
self.news.title, orgname,)
def testcase(session):
myorg = session.query(Org).filter(Org.id==6).one()
otherorg_news_associations = session.query(NewsOrg).filter
(NewsOrg.org_id==1).all()
mystory = otherorg_news_associations[0].news
mystory.orgs.append(myorg) # -- TypeError: __init__() takes
exactly 1 argument (2 given)
session.commit()
The association_proxy is trying to create the NewsOrg object by calling
NewsOrg(myorg). Since your NewsOrg class does not override __init__, the
default declarative __init__ is used, which takes only keyword
arguments. This causes the TypeError: __init__() takes exactly 1
argument (2 given) error.
You can fix this by either adding an __init__ method to NewsOrg like this:
def __init__(self, org=None, **kwargs):
super(NewsOrg, self).__init__(**kwargs)
self.org = org
or, preferably, add a creator argument to association_proxy:
orgs = association_proxy('newsorgs', 'org', creator=lambda org:
NewsOrg(org=org))
You can find out more about the 'creator' argument
at:http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html...
-Conor
--~--~-~--~~~---~--~~
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: NoSuchColumnError when process is forked.
Filipe Fernandes wrote: Also, the exception NoSuchColumnError never shows up until unique indexes are applied thoughout the tables. then you have an unknown error. you should enable SQL + result set logging and see if in fact a SELECT is issued for the table without the primary key column present in the columns clause. Or if it is present, but then is not present in the result set as expected. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
