Re: [sqlalchemy] group by and Oracle
Yes, Ian, it works, :-) thank you j Ian Kelly wrote: On Fri, Dec 3, 2010 at 4:08 AM, jo jose.soa...@sferacarta.com wrote: Hi all, I'm trying to write a GROUP BY query grouped by a function (to_char) using a variable format, which could be 'yy' or '' as in: sql=session.query( func.to_char(Prestazione.c.data,format), func.sum(Prestazione.c.quantita).label('quantita'), func.sum(Prestazione.c.importo).label('importo') ) sql=sql.filter(Verifica.c.codice == Tariffa.c.codice) sql=sql.filter(Prestazione.c.id_tariffa == Tariffa.c.id) sql=sql.group_by(Verifica.c.codice, func.to_char(Prestazione.c.data,format)) Have you tried using the same func result in both places, i.e.: to_char = func.to_char(Prestazione.c.data,format) sql=session.query( to_char, func.sum(Prestazione.c.quantita).label('quantita'), func.sum(Prestazione.c.importo).label('importo') ) sql=sql.filter(Verifica.c.codice == Tariffa.c.codice) sql=sql.filter(Prestazione.c.id_tariffa == Tariffa.c.id) sql=sql.group_by(Verifica.c.codice, to_char) -- Jose Soares Sferacarta Net Via Bazzanese 69 40033 Casalecchio di Reno Bologna - Italy Ph +39051591054 fax +390516131537 web:www.sferacarta.com Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie. This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] finding if a table is already join in a query
What if your query already has a join yet you need to add another WHERE clause to the join? This fails with This query already has a join for Table xxx. Any way to modify your join to a query after you join it? -- Thadeus On Wed, Dec 1, 2010 at 8:08 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Dec 1, 2010, at 1:28 AM, James Neethling wrote: if you would like multiple references to Address to all work from the same join, your routine needs to track which entities have already been joined as a destination in a separate collection: def search(columns): already_joined = set() ... if class_ not in already_joined: q = q.join(destination) already_joined.add(class_) Hi Michael, Thank you for the quick response. Unfortunately we don't always know where this query comes from (my example was a little contrived :( ) Is there any way to get the tables that are currently in the join for a query? You can iterate through q._from_obj(), and for each object that is a join(), recursively descend through j.left and j.right looking for Table objects. Table objects can be embedded in subqueries and alias objects too but I'm assuming your query buildup here is simple enough that gray areas like that aren't expected. If it were me, I'd not be passing a raw Query around, I'd have it wrapped inside a facade that is doing the abovementioned tracking of important state explicitly (and also ensuring that those more grayish areas aren't occurring with this particular Query). That way any other interesting facts about the query as built so far can be tracked as well. Also easier to unit test. Here is a cut down sample implementation that will hopefully remove any confusion... Note the TODO: in Employee.search() ---8---8---8 from sqlalchemy import create_engine, Column, ForeignKey, or_ from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import scoped_session, sessionmaker, relationship, joinedload from sqlalchemy.types import Integer, String, Text from sqlalchemy.sql.expression import cast engine = create_engine('sqlite:///:memory:', echo=True) Base = declarative_base(bind=engine) Session = scoped_session(sessionmaker(bind=engine)) class Employee(Base): __tablename__ = 'employee' id = Column(Integer, primary_key=True) name = Column(String) def search(self, value, columns): query = Session.query(Employee) for i, column in enumerate(columns): model = column.parententity.class_ if Employee is not model: #TODO: Are we already joined from Employee onto model? query = query.outerjoin(model) args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns] return query.filter(or_(*args)) class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) employee_id = Column(Integer, ForeignKey(Employee.id)) street1 = Column(String(50)) street2 = Column(String(50)) employee = relationship(Employee) Base.metadata.create_all() #e = Employee(name='Bob') #a = Address(employee=e, street1='street1', street2='street2') #Session.add(a) #Session.commit() q = Employee().search('stree', [Employee.name, Address.street1, Address.street2]) print q SELECT employee.id AS employee_id, employee.name AS employee_name FROM employee LEFT OUTER JOIN address ON employee.id = address.employee_id LEFT OUTER JOIN address ON employee.id = address.employee_id WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR lower(CAST(address.street2 AS TEXT)) LIKE lower(?) ---8---8---8 TIA Jim -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@googlegroups.com . For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- James Neethling Development Manager XO Africa Safari (t) +27 21 486 2700 (ext. 127) (e) jam...@xoafrica.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this
Re: [sqlalchemy] finding if a table is already join in a query
To the ON clause ? you'd need to find the join() and surgically replace its on clause, which again likely has issues in more complex cases, such as if any kind of aliasing is going on, joined table inheritance in use, etc. I'll reiterate that this is not the way I'd be approaching this problem. On Dec 5, 2010, at 3:32 PM, Thadeus Burgess wrote: What if your query already has a join yet you need to add another WHERE clause to the join? This fails with This query already has a join for Table xxx. Any way to modify your join to a query after you join it? -- Thadeus On Wed, Dec 1, 2010 at 8:08 AM, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 1, 2010, at 1:28 AM, James Neethling wrote: if you would like multiple references to Address to all work from the same join, your routine needs to track which entities have already been joined as a destination in a separate collection: def search(columns): already_joined = set() ... if class_ not in already_joined: q = q.join(destination) already_joined.add(class_) Hi Michael, Thank you for the quick response. Unfortunately we don't always know where this query comes from (my example was a little contrived :( ) Is there any way to get the tables that are currently in the join for a query? You can iterate through q._from_obj(), and for each object that is a join(), recursively descend through j.left and j.right looking for Table objects. Table objects can be embedded in subqueries and alias objects too but I'm assuming your query buildup here is simple enough that gray areas like that aren't expected. If it were me, I'd not be passing a raw Query around, I'd have it wrapped inside a facade that is doing the abovementioned tracking of important state explicitly (and also ensuring that those more grayish areas aren't occurring with this particular Query). That way any other interesting facts about the query as built so far can be tracked as well. Also easier to unit test. Here is a cut down sample implementation that will hopefully remove any confusion... Note the TODO: in Employee.search() ---8---8---8 from sqlalchemy import create_engine, Column, ForeignKey, or_ from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import scoped_session, sessionmaker, relationship, joinedload from sqlalchemy.types import Integer, String, Text from sqlalchemy.sql.expression import cast engine = create_engine('sqlite:///:memory:', echo=True) Base = declarative_base(bind=engine) Session = scoped_session(sessionmaker(bind=engine)) class Employee(Base): __tablename__ = 'employee' id = Column(Integer, primary_key=True) name = Column(String) def search(self, value, columns): query = Session.query(Employee) for i, column in enumerate(columns): model = column.parententity.class_ if Employee is not model: #TODO: Are we already joined from Employee onto model? query = query.outerjoin(model) args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns] return query.filter(or_(*args)) class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) employee_id = Column(Integer, ForeignKey(Employee.id)) street1 = Column(String(50)) street2 = Column(String(50)) employee = relationship(Employee) Base.metadata.create_all() #e = Employee(name='Bob') #a = Address(employee=e, street1='street1', street2='street2') #Session.add(a) #Session.commit() q = Employee().search('stree', [Employee.name, Address.street1, Address.street2]) print q SELECT employee.id AS employee_id, employee.name AS employee_name FROM employee LEFT OUTER JOIN address ON employee.id = address.employee_id LEFT OUTER JOIN address ON employee.id = address.employee_id WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR lower(CAST(address.street2 AS TEXT)) LIKE lower(?) ---8---8---8 TIA Jim -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group
[sqlalchemy] Re: Adding expressions to a Query
one option would be to add the expression to your model as a column_property This is unfortunately not an option as this extra column compiles it's value from the values of more than one model. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Adding expressions to a Query
I found that it's really as easy as using query.add_columns(). My problem was caused by my not reading the documentation properly and trying to use a list in the add_columns() method instead of just positional arguments. one option would be to add the expression to your model as a column_property This is unfortunately not an option as this extra column compiles it's value from the values of more than one model. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Excluding a column form Query result
Is there a way to add a column to a Query object to be used elsewhere in the query, but prevent it from being listed in the result of the Query? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.