Re: [PossibleSpam][5.0] Re: [sqlalchemy] Concise, Pythonic query syntax
All, I've just updated the package to use the MIT license. Bryan On Mon, Nov 6, 2017 at 2:22 PM, Jones, Bryan <bjo...@ece.msstate.edu> wrote: > Chris, > > I'm open to BSD or MIT as well. Looking, I see that SQLAlchemy is > MIT-license, so I can re-license it to that. > > Bryan > > On Mon, Nov 6, 2017 at 12:25 PM, Chris Withers <ch...@withers.org> wrote: > >> Great looking library, shame about the license. >> >> You particularly attached to GPL3 or would you be amenable to BSD or MIT? >> >> Chris >> >> On 03/11/2017 21:52, Bryan Jones wrote: >> >> All, >> >> I've just released the pythonic_sqlalchemy_query package on PyPI, which >> provides concise, Pythonic query syntax for SQLAlchemy. For example, these >> two queries produce identical results: >> >> pythonic_query = >> session.User['jack'].addresses['j...@google.com']traditional_query = ( >> # Ask for the Address... >> session.query(Address). >> # by querying a User named 'jack'... >> select_from(User).filter(User.name == 'jack'). >> # then joining this to the Address 'j...@google.com`. >> join(Address).filter(Address.email_address == 'j...@google.com') >> >> For more information, see: >> http://pythonic-sqlalchemy-query.readthedocs.io/en/latest/README.html >> >> Thanks to Mike for his feedback and encouragement to post this on PyPI. >> I've addressed the weaknesses he mentioned and added more features and >> tests. Comments and feedback are welcome. Enjoy! >> >> Bryan >> >> On Friday, June 16, 2017 at 4:54:36 PM UTC-5, Mike Bayer wrote: >>> >>> >>> >>> On 06/15/2017 04:11 PM, Bryan Jones wrote: >>> > All, >>> > >>> > While working on my SQLAlchemy-based application, I noticed an >>> > opportunity to provide a more concise, Pythonic query syntax. For >>> > example, User['jack'].addresses produces a Query for the Address of a >>> > User named jack. I had two questions >>> > >>> > 1. Has someone already done this? If so, would you provide a link? >>> > 2. If not, would this be reasonable for inclusion in SQLAlchemy, >>> either >>> > as an ORM example, or as a part of the core code base? If so, I >>> can >>> > submit a pull request. >>> > >>> >>> >>> Hi Bryan - >>> >>> thanks for working on this. >>> >>> I can see a lot of variety of ways that systems like this might work. >>> For example, I notice we are building up an expression, but instead of >>> sending it to a function like session.run_query(my_query), you've >>> flipped it around to say my_query.to_query(session). We do have a >>> similar approach with the "baked" query API, where you build up >>> BakedQuery without a session then call baked_query.for_session(session). >>> >>> >>> It seems like there's maybe a theme to this recipe which is that it >>> makes a certain subset of query structures more succinct, but at the >>> expense of serving only a limited set of types of queries. It seems >>> like an expression can either select from just the lead entity, or from >>> a single column, then if I wanted more entities I need to drop into >>> query.add_entity(). It's not clear how I'd select only a SQL >>> expression, e.g. "SELECT lower(fullname) FROM jack", etc. I do like >>> how the functionality of __getitem__ is essentially pluggable. That's >>> a nice concept to add to a "query convenience" system. >>> >>> There are other patterns like this, the most common are entity-bound >>> query generators like "User.query" which these days is mostly popular >>> with Flask. There's a lot of query helpers and facades around within >>> individual projects. However in SQLAlchemy itself, we've moved away >>> from providing or endorsing helpers like these built in due to the fact >>> that they create one API for running the subset of queries that happen >>> to fall under the convenience syntax, and then you have to use a >>> different API for queries that fall outside of the convenience syntax. >>> When a single product presents multiple, overlapping APIs, it generally >>> causes confusion in learning the product.It's easier for people to >>> understand a particular convenience API as an entirely separate add-on. >>> >>> SQLAlchemy certainly suffers from this in an
Re: [PossibleSpam][5.0] Re: [sqlalchemy] Concise, Pythonic query syntax
Chris, I'm open to BSD or MIT as well. Looking, I see that SQLAlchemy is MIT-license, so I can re-license it to that. Bryan On Mon, Nov 6, 2017 at 12:25 PM, Chris Withers <ch...@withers.org> wrote: > Great looking library, shame about the license. > > You particularly attached to GPL3 or would you be amenable to BSD or MIT? > > Chris > > On 03/11/2017 21:52, Bryan Jones wrote: > > All, > > I've just released the pythonic_sqlalchemy_query package on PyPI, which > provides concise, Pythonic query syntax for SQLAlchemy. For example, these > two queries produce identical results: > > pythonic_query = > session.User['jack'].addresses['j...@google.com']traditional_query = ( > # Ask for the Address... > session.query(Address). > # by querying a User named 'jack'... > select_from(User).filter(User.name == 'jack'). > # then joining this to the Address 'j...@google.com`. > join(Address).filter(Address.email_address == 'j...@google.com') > > For more information, see: > http://pythonic-sqlalchemy-query.readthedocs.io/en/latest/README.html > > Thanks to Mike for his feedback and encouragement to post this on PyPI. > I've addressed the weaknesses he mentioned and added more features and > tests. Comments and feedback are welcome. Enjoy! > > Bryan > > On Friday, June 16, 2017 at 4:54:36 PM UTC-5, Mike Bayer wrote: >> >> >> >> On 06/15/2017 04:11 PM, Bryan Jones wrote: >> > All, >> > >> > While working on my SQLAlchemy-based application, I noticed an >> > opportunity to provide a more concise, Pythonic query syntax. For >> > example, User['jack'].addresses produces a Query for the Address of a >> > User named jack. I had two questions >> > >> > 1. Has someone already done this? If so, would you provide a link? >> > 2. If not, would this be reasonable for inclusion in SQLAlchemy, >> either >> > as an ORM example, or as a part of the core code base? If so, I can >> > submit a pull request. >> > >> >> >> Hi Bryan - >> >> thanks for working on this. >> >> I can see a lot of variety of ways that systems like this might work. >> For example, I notice we are building up an expression, but instead of >> sending it to a function like session.run_query(my_query), you've >> flipped it around to say my_query.to_query(session). We do have a >> similar approach with the "baked" query API, where you build up >> BakedQuery without a session then call baked_query.for_session(session). >> >> It seems like there's maybe a theme to this recipe which is that it >> makes a certain subset of query structures more succinct, but at the >> expense of serving only a limited set of types of queries. It seems >> like an expression can either select from just the lead entity, or from >> a single column, then if I wanted more entities I need to drop into >> query.add_entity(). It's not clear how I'd select only a SQL >> expression, e.g. "SELECT lower(fullname) FROM jack", etc. I do like >> how the functionality of __getitem__ is essentially pluggable. That's >> a nice concept to add to a "query convenience" system. >> >> There are other patterns like this, the most common are entity-bound >> query generators like "User.query" which these days is mostly popular >> with Flask. There's a lot of query helpers and facades around within >> individual projects. However in SQLAlchemy itself, we've moved away >> from providing or endorsing helpers like these built in due to the fact >> that they create one API for running the subset of queries that happen >> to fall under the convenience syntax, and then you have to use a >> different API for queries that fall outside of the convenience syntax. >> When a single product presents multiple, overlapping APIs, it generally >> causes confusion in learning the product.It's easier for people to >> understand a particular convenience API as an entirely separate add-on. >> >> SQLAlchemy certainly suffers from this in any case, such as that we have >> both "classical mapping" and "declarative", "Core" and "ORM" querying >> styles, things like that; though historically, we've put lots of effort >> into making it so that if you are using ORM Query, you really don't need >> to use Core at all for just about any structure of query, and similarly >> Declarative has totally replaced mapper() in virtually all cases. Long >> ago we bundled a convenience library called SQLSoup, which I eventually >> bro
Re: [sqlalchemy] Concise, Pythonic query syntax
All, I've just released the pythonic_sqlalchemy_query package on PyPI, which provides concise, Pythonic query syntax for SQLAlchemy. For example, these two queries produce identical results: pythonic_query = session.User['jack'].addresses['j...@google.com']traditional_query = ( # Ask for the Address... session.query(Address). # by querying a User named 'jack'... select_from(User).filter(User.name == 'jack'). # then joining this to the Address 'j...@google.com`. join(Address).filter(Address.email_address == 'j...@google.com') For more information, see: http://pythonic-sqlalchemy-query.readthedocs.io/en/latest/README.html Thanks to Mike for his feedback and encouragement to post this on PyPI. I've addressed the weaknesses he mentioned and added more features and tests. Comments and feedback are welcome. Enjoy! Bryan On Friday, June 16, 2017 at 4:54:36 PM UTC-5, Mike Bayer wrote: > > > > On 06/15/2017 04:11 PM, Bryan Jones wrote: > > All, > > > > While working on my SQLAlchemy-based application, I noticed an > > opportunity to provide a more concise, Pythonic query syntax. For > > example, User['jack'].addresses produces a Query for the Address of a > > User named jack. I had two questions > > > > 1. Has someone already done this? If so, would you provide a link? > > 2. If not, would this be reasonable for inclusion in SQLAlchemy, either > > as an ORM example, or as a part of the core code base? If so, I can > > submit a pull request. > > > > > Hi Bryan - > > thanks for working on this. > > I can see a lot of variety of ways that systems like this might work. > For example, I notice we are building up an expression, but instead of > sending it to a function like session.run_query(my_query), you've > flipped it around to say my_query.to_query(session). We do have a > similar approach with the "baked" query API, where you build up > BakedQuery without a session then call baked_query.for_session(session). > > It seems like there's maybe a theme to this recipe which is that it > makes a certain subset of query structures more succinct, but at the > expense of serving only a limited set of types of queries. It seems > like an expression can either select from just the lead entity, or from > a single column, then if I wanted more entities I need to drop into > query.add_entity(). It's not clear how I'd select only a SQL > expression, e.g. "SELECT lower(fullname) FROM jack", etc. I do like > how the functionality of __getitem__ is essentially pluggable. That's > a nice concept to add to a "query convenience" system. > > There are other patterns like this, the most common are entity-bound > query generators like "User.query" which these days is mostly popular > with Flask. There's a lot of query helpers and facades around within > individual projects. However in SQLAlchemy itself, we've moved away > from providing or endorsing helpers like these built in due to the fact > that they create one API for running the subset of queries that happen > to fall under the convenience syntax, and then you have to use a > different API for queries that fall outside of the convenience syntax. > When a single product presents multiple, overlapping APIs, it generally > causes confusion in learning the product.It's easier for people to > understand a particular convenience API as an entirely separate add-on. > > SQLAlchemy certainly suffers from this in any case, such as that we have > both "classical mapping" and "declarative", "Core" and "ORM" querying > styles, things like that; though historically, we've put lots of effort > into making it so that if you are using ORM Query, you really don't need > to use Core at all for just about any structure of query, and similarly > Declarative has totally replaced mapper() in virtually all cases. Long > ago we bundled a convenience library called SQLSoup, which I eventually > broke out into a separate project, and then I later added the "automap" > extension as a more fundamentals-based system to get the same effect > without using an alternate query API. > > I've always encouraged people to write other kinds of query languages on > top of SQLAlchemy's language. There's another style that I've yet to > see someone implement for SQLAlchemy, even though it's quite doable, > which is to parse Python AST into SQLAlchemy queries, with an emphasis > on generator expressions acting like SELECT constructs. There are two > SQL libraries, one very old and unknown called GeniuSQL, and one current > ORM called Pony, that use this approach. I'm not a fa
[sqlalchemy] Re: Concise, Pythonic query syntax
Mike, Thanks for your careful analysis and thoughtful comments. I appreciate the time you spent to think about this. I agree that this does represent simpler syntax for a narrow class of common operations. Per your advice, I'll work this up into a package and post it on pypi. Thanks for the feedback! Bryan On Thursday, June 15, 2017 at 3:11:47 PM UTC-5, Bryan Jones wrote: > > All, > > While working on my SQLAlchemy-based application, I noticed an opportunity > to provide a more concise, Pythonic query syntax. For example, > User['jack'].addresses produces a Query for the Address of a User named > jack. I had two questions > >1. Has someone already done this? If so, would you provide a link? >2. If not, would this be reasonable for inclusion in SQLAlchemy, >either as an ORM example, or as a part of the core code base? If so, I can >submit a pull request. > > A quick comparison of this statement to the traditional approach: > > User['jack'] .addresses > Query([]).select_from(User).filter(User.name == 'jack').join(Address). > add_entity(Address) > > A few more (complete) examples of this approach: > # Ask for the full User object for jack. > User['jack'].to_query(session) > # Ask only for Jack's full name. > User['jack'].fullname.to_query(session) > # Get all of Jack's addresses. > User['jack'].addresses.to_query(session) > # Get just the email-address of all of Jack's addresses. > User['jack'].addresses.email_address.to_query(session) > # Get just the email-address j...@yahoo.com of Jack's addresses. > User['jack'].addresses['j...@yahoo.com'].to_query(session) > # Ask for the full Address object for j...@yahoo.com. > Address['j...@yahoo.com'].to_query(session) > # Ask for the User associated with this address. > Address['j...@yahoo.com'].user.to_query(session) > # Use a filter criterion to select a User with a full name of Jack Bean. > User[User.fullname == 'Jack Bean'].to_query(session) > # Use two filter criteria to find the user named jack with a full name of > Jack Bean. > User['jack'][User.fullname == 'Jack Bean'].to_query(session) > # Look for the user with id 1. > User[1].to_query(session) > > Tested on Python 3.6.1, Windows 10, SQLAlchemy 1.1.10. I've attached the > code, and a HTML document of the code with helpful hyperlinks. > > Bryan > -- > Bryan A. Jones, Ph.D. > Associate Professor > Department of Electrical and Computer Engineering > 231 Simrall / PO Box 9571 > Mississippi State University > Mississippi State, MS 39762 > http://www.ece.msstate.edu/~bjones > bjones AT ece DOT msstate DOT edu > voice 662-325-3149 > fax 662-325-2298 > > Our Master, Jesus Christ, is on his way. He'll show up right on > time, his arrival guaranteed by the Blessed and Undisputed Ruler, > High King, High God. > - 1 Tim. 6:14b-15 (The Message) > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Concise, Pythonic query syntax
All, While working on my SQLAlchemy-based application, I noticed an opportunity to provide a more concise, Pythonic query syntax. For example, User['jack'].addresses produces a Query for the Address of a User named jack. I had two questions 1. Has someone already done this? If so, would you provide a link? 2. If not, would this be reasonable for inclusion in SQLAlchemy, either as an ORM example, or as a part of the core code base? If so, I can submit a pull request. A quick comparison of this statement to the traditional approach: User['jack'] .addresses Query([]).select_from(User).filter(User.name == 'jack').join(Address). add_entity(Address) A few more (complete) examples of this approach: # Ask for the full User object for jack. User['jack'].to_query(session) # Ask only for Jack's full name. User['jack'].fullname.to_query(session) # Get all of Jack's addresses. User['jack'].addresses.to_query(session) # Get just the email-address of all of Jack's addresses. User['jack'].addresses.email_address.to_query(session) # Get just the email-address j...@yahoo.com of Jack's addresses. User['jack'].addresses['j...@yahoo.com'].to_query(session) # Ask for the full Address object for j...@yahoo.com. Address['j...@yahoo.com'].to_query(session) # Ask for the User associated with this address. Address['j...@yahoo.com'].user.to_query(session) # Use a filter criterion to select a User with a full name of Jack Bean. User[User.fullname == 'Jack Bean'].to_query(session) # Use two filter criteria to find the user named jack with a full name of Jack Bean. User['jack'][User.fullname == 'Jack Bean'].to_query(session) # Look for the user with id 1. User[1].to_query(session) Tested on Python 3.6.1, Windows 10, SQLAlchemy 1.1.10. I've attached the code, and a HTML document of the code with helpful hyperlinks. Bryan -- Bryan A. Jones, Ph.D. Associate Professor Department of Electrical and Computer Engineering 231 Simrall / PO Box 9571 Mississippi State University Mississippi State, MS 39762 http://www.ece.msstate.edu/~bjones bjones AT ece DOT msstate DOT edu voice 662-325-3149 fax 662-325-2298 Our Master, Jesus Christ, is on his way. He'll show up right on time, his arrival guaranteed by the Blessed and Undisputed Ruler, High King, High God. - 1 Tim. 6:14b-15 (The Message) -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. # ** # QueryMaker - Proivde concise, Pythonic query syntax for SQLAlchemy # ** from sqlalchemy import create_engine, ForeignKey, Column, Integer, String from sqlalchemy.ext.declarative import declarative_base, DeclarativeMeta from sqlalchemy.orm import sessionmaker, Query, relationship from sqlalchemy.orm.attributes import InstrumentedAttribute from sqlalchemy.orm.properties import ColumnProperty, RelationshipProperty from sqlalchemy.orm.base import _generative from sqlalchemy.sql.elements import ClauseElement # # QueryMaker # == # This class provides a concise, Pythonic syntax for simple queries; for example, ``User['jack'].addresses`` produces a Query_ for the Address of a User named jack. Comparing this to a traditional query: # # .. code:: # # User['jack'] .addresses # Query([]).select_from(User).filter(User.name == 'jack').join(Address).add_entity(Address) # # See the demonstration_ for more examples. class QueryMaker: def __init__(self, # A `Declarative class <http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#declare-a-mapping>`_ to query. declarative_class, # Optionally, begin with an existing query_. query=None): # Keep track of which `Declarative class`_ we're querying. self.declarative_class = declarative_class # Keep track of the last selectable construct, to generate the select in ``go``. self.select = declarative_class # If it's not provied, create an empty `query <http://docs.sqlalchemy.org/en/latest/orm/query.html>`_; ``go`` will fill in the missing information. TODO: If a query was provided, could I infer the declarative_class based on what the left side of a join would be? There's a reset_joinpoint, but I want to find the current joinpoint
[sqlalchemy] Explicit main table
I'm having trouble telling an orm query which table is the main table when I only use a single column from the main table and it is wrapped up in an SQL function. It's almost like SqlAlchemy can't see that I am using a column from that table because it is inside of a function:: # -- Schema --- # # Labor # = # id # hours # createdBy (user ref) # editedBy (user ref) # # # User # # id # username # - # -- Code --- CREATED_BY = aliased(User, name='createdBy') EDITED_BY = aliased(User, name='editedBy') q = query(CREATED_BY.username, func.sum(Labor.hours)) q = q.join((CREATED_BY, Labor.createdBy==CREATED_BY.id)) q.all() This is producing a query like this:: SELECT user_1.username, sum(labor.st) FROM user AS user_1 INNER JOIN user AS user_1 ON labor.createdBy = user_1.id Which gives me a OperationalError 1066, Not unique table/alias: 'user_1'. I would expect this:: SELECT createdBy.username, sum(labor.st) FROM labor INNER JOIN user AS createdBy ON labor.createdBy = createdBy.id As soon as I add a column from the Labor table to the query, and it is not in a function, the query works. For example, this works:: q = query(CREATED_BY.username, Labor.id, func.sum(Labor.hours)) Mysql 5 SqlAlchemy 0.5.2 -- 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: Explicit main table
Thanks, that worked. On Jul 7, 11:57 am, Michael Bayer mike...@zzzcomputing.com wrote: On Jul 7, 2011, at 2:16 PM, Bryan wrote: I'm having trouble telling an orm query which table is the main table when I only use a single column from the main table and it is wrapped up in an SQL function. It's almost like SqlAlchemy can't see that I am using a column from that table because it is inside of a function:: # -- Schema --- # # Labor # = # id # hours # createdBy (user ref) # editedBy (user ref) # # # User # # id # username # - # -- Code --- CREATED_BY = aliased(User, name='createdBy') EDITED_BY = aliased(User, name='editedBy') q = query(CREATED_BY.username, func.sum(Labor.hours)) q = q.join((CREATED_BY, Labor.createdBy==CREATED_BY.id)) q.all() This is producing a query like this:: SELECT user_1.username, sum(labor.st) FROM user AS user_1 INNER JOIN user AS user_1 ON labor.createdBy = user_1.id Which gives me a OperationalError 1066, Not unique table/alias: 'user_1'. I would expect this:: SELECT createdBy.username, sum(labor.st) FROM labor INNER JOIN user AS createdBy ON labor.createdBy = createdBy.id As soon as I add a column from the Labor table to the query, and it is not in a function, the query works. For example, this works:: q = query(CREATED_BY.username, Labor.id, func.sum(Labor.hours)) Mysql 5 SqlAlchemy 0.5.2 if you could upgrade to 0.6 or 0.7, you would say query(created_by).select_from(Labor).join(created_by, onclause) else if stuck with 0.5 you need to use from sqlalchemy.orm import join query(created_by).select_from(join(Labor, created_by, onclause).join(whatever else needs to be joined)) i.e. the whole JOIN needs to be in select_from -- 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.
Re: [sqlalchemy] MySQL DATE_ADD function
On Tue, Oct 5, 2010 at 1:58 AM, Chris Withers ch...@simplistix.co.uk wrote:
Are you looking for something database agnostic or something that just works
for MySQL?
If the latter, look at text:
http://www.sqlalchemy.org/docs/core/tutorial.html#using-text
If the former, then you'll want a database agnostic implementation. So,
what's the above sql actually trying to achieve?
Chris
I'm fine with a MySQL-only solution. The text construct is always the fallback,
but I'm wondering if there is a way that I can use the attributes of my class
for the column name, instead of just a string. My column names are going to
change soon, but my object model will stay the same, so I am trying not to
explicitly use the column names in my code.
Can I do something like this?
'DATE_ADD(' + Class.dateAttr + ', INTERVAL(1 - ' \
+ 'DAYOFWEEK(' + Class.dateAttr + ')) DAY)'
If I can't use my class's attributes, is there a way I can at least use the
table object's columns like this:
'DATE_ADD(' + table.c.date_col.name + ', INTERVAL(1 - ' \
+ 'DAYOFWEEK(' + table.c.date_col.name + ')) DAY)'
I prefer one of these to a string because I will get an error during testing
when the statement is encountered. With a string, I will only get an error if
the statement actually runs in the DB.
--
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: MySQL DATE_ADD function
On Oct 5, 4:45 pm, Bryan Vicknair bryanv...@gmail.com wrote:
I'm fine with a MySQL-only solution. The text construct is always the
fallback,
but I'm wondering if there is a way that I can use the attributes of my
class
for the column name, instead of just a string. My column names are going to
change soon, but my object model will stay the same, so I am trying not to
explicitly use the column names in my code.
This was my final solution. I cheated by using a different MYSQL
function, one
that actually accepts arguments seperated by commas.
func.timestampadd(text('day'),
func.if_(func.dayofweek(EmpTime.day) == 1,
0,
8 - func.dayofweek(EmpTime.day)),
EmpTime.day)
--
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] MySQL DATE_ADD function
I'm having trouble converting this SQL into an ORM statement. DATE_ADD(datecol, INTERVAL(1 - DAYOFWEEK(datecol)) DAY) This is as far as I can get, which is basically nowhere. The second argument to date_add requires literal strings INTERVAL and DAY, but I also need to insert a function in the middle there. Any help is appreciated. func.date_add(Class.dateAttr, INTERVAL(1 - DAYOFWEEK(Class.dateAttr)) DAY) -- 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] Checking internals of query object
I am writing a function that adds particular columns and groupings to a query based on some options. I am trying to write some unit tests for the function, and would like to check that the correct columns are being added/grouped. Give a query like: q = session.query(Employee.firstName, Employee.lastName) How can I check later that the query object has included Employee.firstName in the output columns? Similarly, how can I check, for instance, that the query object is grouping on Employee.lastName? -- 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: Checking internals of query object
I'm considering just checking for terms in the sql statement produced from str(query). That way I don't have to muddle with the internals of Query. However, I do introduce table and column names from the DB into my test code as strings, which of course defeats one of the reasons for using SA. However, this is *just* test code, and when I change column names in the DB and the tests fail, I''l change the tests. The alternative would to be to change the tests when the internals of Query change, which I don't have as much control over. Thanks for the insight. On Aug 27, 10:58 am, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 27, 1:24 pm, Bryan bryanv...@gmail.com wrote: I am writing a function that adds particular columns and groupings to a query based on some options. I am trying to write some unit tests for the function, and would like to check that the correct columns are being added/grouped. Give a query like: q = session.query(Employee.firstName, Employee.lastName) How can I check later that the query object has included Employee.firstName in the output columns? for output columns, we have recently added a method for this purpose as of version 0.6.3: http://www.sqlalchemy.org/docs/reference/orm/query.html#sqlalchemy.or... I'm using it successfully in a library that converts Query objects into Excel spreadsheets with xlwt. Similarly, how can I check, for instance, that the query object is grouping on Employee.lastName? stuff like that is semi-private but relatively stable, most are available by names like query._group_by, query._order_by, query._criterion. If you look at the top of the Query class (the source), they are all defaulted at the class level. Semi-private because I really don't want to push things like that to be first class accessors until we are super certain nothing is changing there. -- 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: func type_ not being used
Same behavior with 0.6.3. On Aug 3, 4:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 3, 2010, at 5:00 PM, Bryan wrote: Python 2.5.4 MySQL python 1.2.3c1 sqlalchemy 0.5.2 just curious can you try with SQLA 0.6.3 ? Here is the actual code. It references my object model etc so it won't run for you, but just in case I made a mistake converting it to a simplified version of the problem here it is: dollars = func.if_(EmpTime.actTotal != None, EmpTime.actTotal, EmpTime.estTotal, type_=types.Numeric) q = orm.query( Account.code, func.lower(TimeType.shortName), func.sum(EmpTime.hours), func.sum(dollars, type_=types.Numeric) ) q = q.join(EmpTime.acc).join(EmpTime.timeType) q = q.group_by(Account.code).group_by(TimeType.shortName) q = q.filter(EmpTime.day = start) q = q.filter(EmpTime.day = end) q = q.filter(EmpTime.jobId == jobId) labor = q.all() On Aug 3, 1:26 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 3, 2010, at 2:56 PM, Bryan wrote: This returns a Decimal type for c2, which is what I want: c1 = literal(5, type_=Numeric) c2 = func.sum(c1, type_=Numeric) This returns a Float type for c2, but I'm telling c1 that it is a Numeric. How can I get a decimal returned when using an if function? c1 = func.if_(Table.foo != None, Table.foo, Table.bar, type_=Numeric) c2 = func.sum(c1, type_=Numeric) I see nothing wrong with that code. Can I get some SQLA version / database backend / DBAPI details ? -- 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 athttp://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 athttp://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 at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: func type_ not being used
OK, I'll put together a case later today. On Aug 4, 8:24 am, Michael Bayer mike...@zzzcomputing.com wrote: nothing wrong with the code I see, and I am noticing that to recreate your test is taking me longer than one minute, so please provide a fully reproducing test case. On Aug 4, 2010, at 11:14 AM, Bryan wrote: Same behavior with 0.6.3. On Aug 3, 4:17 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 3, 2010, at 5:00 PM, Bryan wrote: Python 2.5.4 MySQL python 1.2.3c1 sqlalchemy 0.5.2 just curious can you try with SQLA 0.6.3 ? Here is the actual code. It references my object model etc so it won't run for you, but just in case I made a mistake converting it to a simplified version of the problem here it is: dollars = func.if_(EmpTime.actTotal != None, EmpTime.actTotal, EmpTime.estTotal, type_=types.Numeric) q = orm.query( Account.code, func.lower(TimeType.shortName), func.sum(EmpTime.hours), func.sum(dollars, type_=types.Numeric) ) q = q.join(EmpTime.acc).join(EmpTime.timeType) q = q.group_by(Account.code).group_by(TimeType.shortName) q = q.filter(EmpTime.day = start) q = q.filter(EmpTime.day = end) q = q.filter(EmpTime.jobId == jobId) labor = q.all() On Aug 3, 1:26 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 3, 2010, at 2:56 PM, Bryan wrote: This returns a Decimal type for c2, which is what I want: c1 = literal(5, type_=Numeric) c2 = func.sum(c1, type_=Numeric) This returns a Float type for c2, but I'm telling c1 that it is a Numeric. How can I get a decimal returned when using an if function? c1 = func.if_(Table.foo != None, Table.foo, Table.bar, type_=Numeric) c2 = func.sum(c1, type_=Numeric) I see nothing wrong with that code. Can I get some SQLA version / database backend / DBAPI details ? -- 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 athttp://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 athttp://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 athttp://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 at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] func type_ not being used
This returns a Decimal type for c2, which is what I want: c1 = literal(5, type_=Numeric) c2 = func.sum(c1, type_=Numeric) This returns a Float type for c2, but I'm telling c1 that it is a Numeric. How can I get a decimal returned when using an if function? c1 = func.if_(Table.foo != None, Table.foo, Table.bar, type_=Numeric) c2 = func.sum(c1, type_=Numeric) -- 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: func type_ not being used
Python 2.5.4 MySQL python 1.2.3c1 sqlalchemy 0.5.2 Here is the actual code. It references my object model etc so it won't run for you, but just in case I made a mistake converting it to a simplified version of the problem here it is: dollars = func.if_(EmpTime.actTotal != None, EmpTime.actTotal, EmpTime.estTotal, type_=types.Numeric) q = orm.query( Account.code, func.lower(TimeType.shortName), func.sum(EmpTime.hours), func.sum(dollars, type_=types.Numeric) ) q = q.join(EmpTime.acc).join(EmpTime.timeType) q = q.group_by(Account.code).group_by(TimeType.shortName) q = q.filter(EmpTime.day = start) q = q.filter(EmpTime.day = end) q = q.filter(EmpTime.jobId == jobId) labor = q.all() On Aug 3, 1:26 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 3, 2010, at 2:56 PM, Bryan wrote: This returns a Decimal type for c2, which is what I want: c1 = literal(5, type_=Numeric) c2 = func.sum(c1, type_=Numeric) This returns a Float type for c2, but I'm telling c1 that it is a Numeric. How can I get a decimal returned when using an if function? c1 = func.if_(Table.foo != None, Table.foo, Table.bar, type_=Numeric) c2 = func.sum(c1, type_=Numeric) I see nothing wrong with that code. Can I get some SQLA version / database backend / DBAPI details ? -- 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 athttp://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 at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Updating table w/ bindparam
I know this has got to be simple. I am updating table1 in MySQL.
u = table1.update()
u.values(col1=bindparam('_col1'), col2=bindparam('_col2') ...
updateVals = [
{'_col1': 5, '_col2': table1.col1 * 5}
]
engine.execute(u, updateVals)
I was expecting table1.col1 * 5 to show up as: `table1`.`col2` =
`table1`.`col1` * 5
But it shows up as: '`table1`.`col1` * %s'
in the query log.
Note the quotes around the actual result, and the missing 5 value.
The 5 is never being placed into the string being sent to the server.
--
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: Updating table w/ bindparam
How about some sort of literal: table1.col1 * 5 (without the quotes)
as the value of a bindparam? So to the bindparam it would look like a
constant, but when it got to the server, it would be interpreted as an
expression? I would be loosing the automatic table/column name
insertion that SA provides.
On May 20, 8:01 am, Conor conor.edward.da...@gmail.com wrote:
On 05/20/2010 09:56 AM, Bryan wrote:I know this has got to be simple. I am
updating table1 in MySQL. u = table1.update()
u.values(col1=bindparam('_col1'), col2=bindparam('_col2') ... updateVals = [
{'_col1': 5, '_col2': table1.col1 * 5} ] engine.execute(u, updateVals) I was
expecting table1.col1 * 5 to show up as: `table1`.`col2` = `table1`.`col1` *
5 But it shows up as: '`table1`.`col1` * %s' in the query log. Note the
quotes around the actual result, and the missing 5 value. The 5 is never
being placed into the string being sent to the server.
You are not allowed to use expressions as bind params, only constants. Bind
params are not allowed to change the structure of the SQL statement,
because part of the reason for bind params in the first place is the server
only has to parse/plan the SQL statement once, regardless of how many items
you put in updateVals.
-Conor
--
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 at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Updating table w/ bindparam
Including the expressing in the values() clause w/ a bindparam like in
your second example did the trick. I haven't checked the MySQL logs
if I loose the executemany() benefits (not sure off the top of my head
how the MySQL logs would show those benefits), but at least my tests
are passing now.
I'm curious, isn't this still better than looping through all the
updates I need to do and building an update object for each row?
MySQL may see the same amount of unique UPDATE queries either way, but
at least SA only needs to create one update object when using
bindparams and a list of values.
My actual code doesn't modify the update object in place, sorry about
the typo.
On May 20, 8:41 am, Conor conor.edward.da...@gmail.com wrote:
On 05/20/2010 10:28 AM, Bryan wrote:
How about some sort of literal: table1.col1 * 5 (without the quotes)
as the value of a bindparam? So to the bindparam it would look like a
constant, but when it got to the server, it would be interpreted as an
expression? I would be loosing the automatic table/column name
insertion that SA provides.
Bind params don't work like that: you just cannot inject arbitrary SQL
via bind params. You have to include the expression as part of the
update statement itself. The only downside to this approach is you (may)
lose the executemany() behavior you may have been looking for
(executemany is when you pass an array of bind params to execute):
u = table1.update()
u = u.values(col1=5, col2=table1.c.col1 * 5)
engine.execute(u)
If you really need bind params, the best you can do is this, which is
probably not what you want:
u = table1.update()
u = u.values(col1=bindparam('_col1'), col2=table1.c.col1 * bindparam('_col2'))
engine.execute(u, [{'_col1': 5, '_col2': 5}])
I also just noticed that your original example:
u = table1.update()
u.values(col1=bindparam('_col1'), col2=bindparam('_col2') ...
tried to modify the update object in place, but the values() method
returns a new update object instead of modifying the original.
-Conor
--
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
athttp://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 at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Updating table w/ bindparam
I see. Greatly appreciated. SA is amazing.
On May 20, 9:09 am, Conor conor.edward.da...@gmail.com wrote:
On 05/20/2010 10:53 AM, Bryan wrote:
Including the expressing in the values() clause w/ a bindparam like in
your second example did the trick. I haven't checked the MySQL logs
if I loose the executemany() benefits (not sure off the top of my head
how the MySQL logs would show those benefits), but at least my tests
are passing now.
I'm curious, isn't this still better than looping through all the
updates I need to do and building an update object for each row?
MySQL may see the same amount of unique UPDATE queries either way, but
at least SA only needs to create one update object when using
bindparams and a list of values.
The performance advantage of executemany is that the DBAPI client only
needs to send one UPDATE statement to the server along with a bunch of
values instead of a bunch of UPDATE statements. So you only pay the
parsing/planning/network latency penalty once.
The cost of SQLAlchemy generating update objects and compiling them to
SQL is generally negligible compared to the cost of actually executing
them. If all your updates are of the form col1=constant, col2=col1 *
constant, then by all means use executemany. I had just figured that
you wanted some rows to use col2=constant while other rows used
col2=col1 * constant, which is not supported by a single executemany
statement.
-Conor
My actual code doesn't modify the update object in place, sorry about
the typo.
On May 20, 8:41 am, Conor conor.edward.da...@gmail.com wrote:
On 05/20/2010 10:28 AM, Bryan wrote:
How about some sort of literal: table1.col1 * 5 (without the quotes)
as the value of a bindparam? So to the bindparam it would look like a
constant, but when it got to the server, it would be interpreted as an
expression? I would be loosing the automatic table/column name
insertion that SA provides.
Bind params don't work like that: you just cannot inject arbitrary SQL
via bind params. You have to include the expression as part of the
update statement itself. The only downside to this approach is you (may)
lose the executemany() behavior you may have been looking for
(executemany is when you pass an array of bind params to execute):
u = table1.update()
u = u.values(col1=5, col2=table1.c.col1 * 5)
engine.execute(u)
If you really need bind params, the best you can do is this, which is
probably not what you want:
u = table1.update()
u = u.values(col1=bindparam('_col1'), col2=table1.c.col1 *
bindparam('_col2'))
engine.execute(u, [{'_col1': 5, '_col2': 5}])
I also just noticed that your original example:
u = table1.update()
u.values(col1=bindparam('_col1'), col2=bindparam('_col2') ...
tried to modify the update object in place, but the values() method
returns a new update object instead of modifying the original.
-Conor
--
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
athttp://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 at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Override onupdate timestamp
I have a table 'table', with a column, 'stamp', that has an onupdate clause onupdate=datetime.now. I am trying to update table.otherColumn, and I don't want table.stamp to be updated with the latest time. I saw one discussion about overriding onupdate here: http://groups.google.com/group/sqlalchemy/browse_thread/thread/815734c0a383c6ac/be8fc1c1535a70ff?lnk=gstq=onupdate+override#be8fc1c1535a70ff ...but in that case, we knew what we wanted the override value to be for the column, which was setting it to True. At the time of building my update, I'm not sure what table.stamp is equal to, so I don't know what I should override it with. Is it possible to avoid the onupdate clause from executing? -- 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] Column type in select w/ if condition
The underlying column returns a Decimal object when queried regularly, and when summed as follows: select([ mytable.c.hours ]) Decimal(1.0) select([ func.sum(mytable.c.hours) ]) Decimal(1.0) ...but when I sum it w/ an if statement, it returns a float: select([ func.sum(func.if_(True, mytable.c.hours, 0)) ]) 1.0 How can I control the return type of that summed if column? -- 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: Column type in select w/ if condition
That worked, thanks. On Mar 30, 7:40 am, Mariano Mara mariano.m...@gmail.com wrote: Excerpts from Bryan's message of Tue Mar 30 11:27:57 -0300 2010: The underlying column returns a Decimal object when queried regularly, and when summed as follows: select([ mytable.c.hours ]) Decimal(1.0) select([ func.sum(mytable.c.hours) ]) Decimal(1.0) ...but when I sum it w/ an if statement, it returns a float: select([ func.sum(func.if_(True, mytable.c.hours, 0)) ]) 1.0 How can I control the return type of that summed if column? You could use cast [1] (example: casting to Float, untested): from sqlalchemy.sql.expression import cast from sqlalchemy.sa import Float ... select([ cast(func.sum(func.if_(True, mytable.c.hours, 0)), Float)]) [1]http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#... -- 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] Can't append WHERE col IN to select object
Trying to append this to a select object: WHERE jobId IN (SELECT id FROM job WHERE number=1) So I do this: query = select(Bunch of stuff including a `jobId` column) subq = select([job.c.id], job.c.number==1).as_scalar() query = query.where(query.c.jobId.in_(subq)) But that is not working: Every derived table must have its own alias How can I append the where clause to a select object? --~--~-~--~~~---~--~~ 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: Can't append WHERE col IN to select object
On Nov 10, 12:46 pm, Michael Bayer mike...@zzzcomputing.com wrote: Bryan wrote: Trying to append this to a select object: WHERE jobId IN (SELECT id FROM job WHERE number=1) So I do this: query = select(Bunch of stuff including a `jobId` column) subq = select([job.c.id], job.c.number==1).as_scalar() query = query.where(query.c.jobId.in_(subq)) But that is not working: Every derived table must have its own alias It's probably because you're pulling the jobId column of your outer SELECT statement and sticking it inside the WHERE clause of that statement (query.where(query.c.jobId...). I changed query.where(query.c.jobId... to query.where(column (jobId)... and that seems to be working. Why can't I refer to a column with query.c at this point? The query already has columns in its c collection, and I don't want to have repeat string names of columns, it would be cleaner to say query.c.jobId than jobId. I thought that query.c.jobId would simply generate jobId when the sql was generated. --~--~-~--~~~---~--~~ 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: Mapping select to Read-only reporting class
To make it work I selected a column for the primary key as you suggested, and that worked thanks. However, my selection of a primary_key column could be arbitrary as there is no real primary key in my SELECT statement. When defining a table, I can define a foreign key column. Then when creating the mapper, I define a relation(). This allows my object to have an attribute that actually references an object, and all is good. I am able to query my objects based on criteria for their children objects like so: class Shipment - date - job (Job object) query(Shipment).filter(Job.number==12345).all() ... gets me all shipments where the job number is 12345 When using a select statement in my mapper instead of a table, how do I tell SA that a certain field in the SELECT is a foreign key? Is it possible to set up a relation in a mapper that is based on a SELECT statement. When I did it, it seemed to join my SELECT statement with the job table as a Cartesian product, because it didn't understand how to join my SELECT statement with the job table because there were no foreign keys defined. On Sep 14, 3:00 pm, Michael Bayer mike...@zzzcomputing.com wrote: Bryan wrote: I want to abstract some ugly reporting SQL strings into a read-only object model. I have created an empty class, and then I map it to a select object that pulls some statistical information from the DB. The mapper is complaining that it can't assemble a primary key. I am only using this object as a simplified way of querying the database, and will never want to persist the object. The object is read-only. Is there a way to tell sqlalchemy not to worry about persisting this class? the primary key is for more than just persistence. Pick whatever columns on your select object you think are suitable, then configure them on the mapper using the primary_key option: mapper(MyClass, myselect, primary_key=[myselect.c.foo, myselect.c.bar]) --~--~-~--~~~---~--~~ 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] relation in object mapped to select statement?
The following code models a simple system that tracks the transfer of
construction tools between jobs. Equip (equipment) is transferred
between Jobs via Shipments.
Towards the end I attempt to map a class to a select statement in
order to make reporting simple. Instead of dealing with sql to do the
reporting, I wanted to map an object to a summarizing sql statement,
and create a sort of object model that covers most of the summarizing
I will need to do.
I can't figure out how to map an object to a select statement and
include a relation in the object. The code below should run in python
2.6:
from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker, relation
from sqlalchemy.sql import *
from datetime import date
# SA objects
db = create_engine('sqlite://', echo=True)
meta = MetaData()
session = sessionmaker(bind=db)()
# Table schema
job = Table('job', meta,
Column('id', Integer, primary_key=True),
Column('number', Integer))
equip = Table('equip', meta,
Column('id', Integer, primary_key=True),
Column('name', Unicode(255)))
equip_shipment = Table('equip_shipment', meta,
Column('id', Integer, primary_key=True),
Column('shipDate', Date),
Column('fromJobId', Integer, ForeignKey('job.id')),
Column('toJobId', Integer, ForeignKey('job.id')),
Column('isBroken', Boolean))
equip_shipment_item = Table('equip_shipment_item', meta,
Column('id', Integer, primary_key=True),
Column('shipmentId', Integer, ForeignKey
('equip_shipment.id')),
Column('equipId', Integer, ForeignKey('equip.id')),
Column('qty', Integer))
meta.create_all(db)
# Objects
class KeywordInitMixin(object):
'''Fills object's attributes with whatever keyword args were given
to init.
As an example, allows me to simply inherit from this class like
this:
class Test(KeywordInitMixin):
pass
...And then create objects like this:
t = Test(foo=1, bar='spam')
assert t.foo == 1
assert t.bar == 'spam'
'''
def __init__(self, **kwargs):
for attr in self.ATTRS:
if attr in kwargs:
setattr(self, attr, kwargs[attr])
else:
setattr(self, attr, None)
# Set any properties
for attr, val in kwargs.items():
# See if class has a property by this name
if (hasattr(self.__class__, attr) and
getattr(self.__class__, attr).__class__ is property):
setattr(self, attr, val)
def __repr__(self):
args = ['%s=%s' % (arg, val) for arg, val in
self.__dict__.items() if
arg in self.ATTRS and val]
args = ', '.join(args)
name = self.__class__.__name__
result = '%s(%s)' % (name, args)
return result
class Job(KeywordInitMixin):
ATTRS = ['number']
class Equip(KeywordInitMixin):
ATTRS = ['name']
class Shipment(KeywordInitMixin):
ATTRS = ['shipDate', 'fromJob', 'toJob', 'isBroken']
class ShipmentItem(KeywordInitMixin):
ATTRS = ['shipment', 'equip', 'qty']
# Map schema to objects
mapper(Job, job)
mapper(Equip, equip)
mapper(Shipment, equip_shipment,
properties={
'fromJob': relation(Job,
primaryjoin=equip_shipment.c.fromJobId==job.c.id),
'toJob': relation(Job,
primaryjoin=equip_shipment.c.toJobId==job.c.id),
}
)
mapper(ShipmentItem, equip_shipment_item,
properties={
'shipment': relation(Shipment, backref='items'),
'equip': relation(Equip)
}
)
# -
# Create some test data
# -
# Jobs
warehouse1 = Job(number=10001)
job1 = Job(number=1)
job2 = Job(number=2)
# Equipment
bClamps = Equip(name=u'Bridge Clamps')
cLocks = Equip(name=u'420 Channel Lock')
smallLock = Equip(name=u'Small 3210 Lock')
toolChest = Equip(name=u'Tool Chest')
# Add to orm
session.add_all([warehouse1, job1, job2, bClamps, cLocks])
# Ship tools to job 1
ship1 = Shipment(fromJob=warehouse1, toJob=job1, shipDate=date.today
())
ship1.items.append(ShipmentItem(qty=5, equip=bClamps))
# Transfer tools from job 1 to job 2
ship2 = Shipment(fromJob=job1, toJob=job2, shipDate=date.today())
ship2.items.append(ShipmentItem(qty=2, equip=bClamps))
# Job 1 returns some tools to the warehouse
ship3 = Shipment(fromJob=job1, toJob=warehouse1, shipDate=date.today
())
ship3.loadedBy = ship3.deliveredBy = 'jane doe'
ship3.items.append(ShipmentItem(qty=2, equip=smallLock))
# Add to orm
session.add_all([ship1, ship2, ship3])
# Job 1 breaks some tools
broken = Shipment(fromJob=job1, isBroken=True, shipDate=date.today())
broken.items.append(ShipmentItem(qty=1, equip=smallLock))
# Break more of same equip, but in different line item to test
aggregation
broken.items.append(ShipmentItem(qty=4, equip=smallLock))
# Job 2 breaks stuff too
broken2 = Shipment(fromJob=job2,
[sqlalchemy] Mapping select to Read-only reporting class
I want to abstract some ugly reporting SQL strings into a read-only object model. I have created an empty class, and then I map it to a select object that pulls some statistical information from the DB. The mapper is complaining that it can't assemble a primary key. I am only using this object as a simplified way of querying the database, and will never want to persist the object. The object is read-only. Is there a way to tell sqlalchemy not to worry about persisting this class? --~--~-~--~~~---~--~~ 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] Building an or_ filter in loop
I can't figure out a clean way of adding a bunch of filter terms to a
query in a loop joined by an OR clause. Successive calls to filter
join the expressions by AND. I would like to do something like the
following, but have the expressions joined by OR
terms = ['apple', 'orange', 'peach']
q = Session.query(Fruit)
for term in terms:
q = q.filter(Fruit.name.like('%' + term + '%')
Desired pseudo-sql:
SELECT * FROM fruit WHERE name like '%apple%' OR name like '%orange%'
OR name like '%peach%'
--~--~-~--~~~---~--~~
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: Building an or_ filter in loop
That worked, thanks
On May 8, 12:59 pm, Kyle Schaffrick k...@raidi.us wrote:
On Fri, 8 May 2009 12:52:09 -0700 (PDT)
Bryan bryanv...@gmail.com wrote:
I can't figure out a clean way of adding a bunch of filter terms to a
query in a loop joined by an OR clause. Successive calls to filter
join the expressions by AND. I would like to do something like the
following, but have the expressions joined by OR
terms = ['apple', 'orange', 'peach']
q = Session.query(Fruit)
for term in terms:
q = q.filter(Fruit.name.like('%' + term + '%')
Desired pseudo-sql:
SELECT * FROM fruit WHERE name like '%apple%' OR name like '%orange%'
OR name like '%peach%'
I think this might do what you want:
cond = or_(*[ Fruit.name.like('%' + term + '%') for term in terms ])
q = Session.query(Fruit).filter(cond)
-Kyle
--~--~-~--~~~---~--~~
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] GUID creation causing foreign key errors
The primary keys in my db are GUIDs, char(36). When I generate the
GUID in python using the uuid module, everything works fine. But when
I allow the db to generate the GUIDs I get foreign key errors when
trying to save a new parent and child.
A look at the SQL generated shows that the parent is being saved
first, but when the child is saved, it does not have the parent's new
primary key in the related field. Instead of the parent's new GUID in
the related field, it has 0L.
When using the first method below, what is stopping sqlalchemy from
getting the newly created guid so it can be referenced by the child's
SQL??
# This way does not work
#
---
def colId(): return Column('id', types.CHAR(36), primary_key=True,
default=func.convert(literal_column('UUID() USING utf8')))
# This way works
#
---
from uuid import uuid4
def colId(): return Column('id', types.CHAR(36), primary_key=True,
default=lambda: str(uuid4()))
--~--~-~--~~~---~--~~
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] How to clarify ambiguous join
I have a table 'Time' that has many-to-1 relationships to tables 'Job' and 'Account'. There is also a 1-to-many relationship between job and Account. Tables -- Time -- Account -- Job Job -- Account I am trying to pull back a few rows from Time, Job and Account via the orm library. I want to try and avoid using the actual sql objects. I can't join them simply like below because the reference between Job and Account is making the join ambiguous. I want to join Time to Job and Time to Account. q = orm.query( func.sum(Time.hours), Time.day, Job.number, Account.code ) q = q.join(Job) q = q.join(Account) How can i tell sqlalchemy to join Job and Account to Time, and not to eachother? Bryan --~--~-~--~~~---~--~~ 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: How to clarify ambiguous join
The join works great now. Thanks. This query is actually being used for a subquery. Table 'Time' also has a column 'employeeId', which translates to an orm attribute of 'emp'. When I add Time.emp to the columns of this subquery, all columns of the 'Time' table are output instead of just the 'employeeId' column. When I say: q = orm.query(Time.emp) print q A large SQL statement with all the columns in 'Time' appears. I would like it to just show the Time.employee_time column. Is this possible using only the orm library, or must I use the tables underlying columnn objects? On Mar 9, 1:12 pm, Michael Bayer mike...@zzzcomputing.com wrote: specify the join as an on condition: q.join(Time.account) q.join(Time.job) Bryan wrote: I have a table 'Time' that has many-to-1 relationships to tables 'Job' and 'Account'. There is also a 1-to-many relationship between job and Account. Tables -- Time -- Account -- Job Job -- Account I am trying to pull back a few rows from Time, Job and Account via the orm library. I want to try and avoid using the actual sql objects. I can't join them simply like below because the reference between Job and Account is making the join ambiguous. I want to join Time to Job and Time to Account. q = orm.query( func.sum(Time.hours), Time.day, Job.number, Account.code ) q = q.join(Job) q = q.join(Account) How can i tell sqlalchemy to join Job and Account to Time, and not to eachother? Bryan --~--~-~--~~~---~--~~ 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: How to clarify ambiguous join
Yes, Time.emp is a relation to the Employee object. I was trying to avoid having this query know anything about the actual table column names, so I wanted to avoid Time.employeeId. When I say Time.emp, howcome all of the table's columns are added to the query output? On Mar 9, 2:02 pm, Michael Bayer mike...@zzzcomputing.com wrote: Bryan wrote: The join works great now. Thanks. This query is actually being used for a subquery. Table 'Time' also has a column 'employeeId', which translates to an orm attribute of 'emp'. When I add Time.emp to the columns of this subquery, all columns of the 'Time' table are output instead of just the 'employeeId' column. When I say: q = orm.query(Time.emp) print q A large SQL statement with all the columns in 'Time' appears. I would like it to just show the Time.employee_time column. Is this possible using only the orm library, or must I use the tables underlying columnn objects? do you mean that Time.emp is a relation() to the Employee object ? if you want just employeeId, ask for that: query(Time.employeeId) On Mar 9, 1:12 pm, Michael Bayer mike...@zzzcomputing.com wrote: specify the join as an on condition: q.join(Time.account) q.join(Time.job) Bryan wrote: I have a table 'Time' that has many-to-1 relationships to tables 'Job' and 'Account'. There is also a 1-to-many relationship between job and Account. Tables -- Time -- Account -- Job Job -- Account I am trying to pull back a few rows from Time, Job and Account via the orm library. I want to try and avoid using the actual sql objects. I can't join them simply like below because the reference between Job and Account is making the join ambiguous. I want to join Time to Job and Time to Account. q = orm.query( func.sum(Time.hours), Time.day, Job.number, Account.code ) q = q.join(Job) q = q.join(Account) How can i tell sqlalchemy to join Job and Account to Time, and not to eachother? Bryan --~--~-~--~~~---~--~~ 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] Using a SQL function w/ strange syntax as default
I want to use MySQL's uuid() function to create default values for a column. I need to convert the output of uuid() to utf-8 however, so the full function in SQL looks like this: CONVERT(UUID() USING utf8) I can't set this as a column default using the func.function() syntax, because python complains about invalid syntax when it encounters the USING part. How can I set this chain of functions as a default? Is there some sort of SQL literal function that I could use? I tried using the literal object, but it of course escapes whatever the value is which does not work. Bryan --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
