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
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 fan of the AST /
generator approach but lots of people think it's very "cool and
Pythonic" because it certainly looks clever, I've been asking
folks to
please build a proof of concept for this system to run on top of
SQLAlchemy as well.
Basically I want to encourage a whole ecosystem of APIs on top of
SQLAlchemy's (also see my old blog post at
http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/
<http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/> for some
notes on
this), but as far as SQLA itself we do very well by purposely
sticking
just to the "basic" API that can do everything in one consistent way,
without particular endorsement of one style or another.
I would encourage you to work your idea into a package you can
release
on Pypi and add some sphinx documentation. I get the sense it
might be
able to do more than I'm seeing at the moment.
- mike
> 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 <javascript:> of
Jack's addresses.
> User['jack'].addresses['j...@yahoo.com
<javascript:>'].to_query(session)
> # Ask for the full Address object for j...@yahoo.com <javascript:>.
> Address['j...@yahoo.com <javascript:>'].to_query(session)
> # Ask for the User associated with this address.
> Address['j...@yahoo.com <javascript:>'].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
<http://www.ece.msstate.edu/%7Ebjones>
> 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
<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+...@googlegroups.com <javascript:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
