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