Concise, Pythonic query syntax

[Bryan Jones]

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)

[mike bayer]

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/ 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

> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@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.
> For more options, visit https://groups.google.com/d/optout.

[Bryan Jones]

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['j25@yahoo.com'].to_query(session)

[Bryan Jones]

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['ja...@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 'ja...@google.com`.
    join(Address).filter(Address.email_address == 'ja...@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

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[Chris Withers]

Great looking library, shame about the license.

You particularly attached to GPL3 or would you be amenable to BSD or MIT?

Chris

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

[Jones, Bryan]

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:

> <mailto:sqlalchemy+unsubscribe@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.
> 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+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Jones, Bryan]

All,

I've just updated the package to use the MIT license.

Bryan

[Chris Withers]

Fantastic, thanks!