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
<mailto: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
<mailto:j...@google.com>`.
join(Address).filter(Address.email_address == 'j...@google.com
<mailto:j...@google.com>')
For more information, see:
http://pythonic-sqlalchemy-query.readthedocs.io/en/latest/README.html
<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 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
<http://www.ece.msstate.edu/%7Ebjones>
> bjones AT ece DOT msstate DOT edu
> voice 662-325-3149 <tel:%28662%29%20325-3149>
> fax 662-325-2298 <tel:%28662%29%20325-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/ <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
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to
sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> 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
<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
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
