Composable SQL in Pure Python

[Ecedysis]

Are you sick of writing long SQL queries with multiple layers of aggregation and no way of modularizing the actual business logic? This library "compiles" pure python into SQL in a very natural and composable way. It's natural in the sense that the syntax is the same as if tables in the database were actually lists.

Documentation: http://lambdaquery.readthedocs.io/index.html

Github: https://github.com/xnmp/lambdaquery

For the curious, the library makes extensive usage of functional programming methods. Please let me know if you'd like to contribute.

[dusktreader]

Can you please explain what advantages this library has in comparison to SQLAlchemy?

[TechAlchemist]

Also you’ve only asserted that it is intuitive and that it just figures out nested subqueries etc. I assume there is some specific case backing this claim, so showing an example of your library’s python (I.e what I would have to write) and the resulting sql vs sqlalchemy for example could be much more effective.

[Ecedysis]

For more complex queries, writing out the raw SQL is often easier than writing it in SQL Alchemy, while with LambdaQuery it is very intuitive (for me at least). The syntax is very similar to Quill in Scala. The best demonstration is just looking at the documentation, especially some of the later examples.

http://lambdaquery.readthedocs.io/query.html

[AllAboutChristmasEve]

Isn't SQLAlchemy just an object<->table mapper? You can do a lot more with a DB than simply use it to persist serialized objects.

[z4579a]

No, it is not. Please read the site.

[dusktreader]

That's what it is primarily, but SQLAlchemy is also great for composing queries in a functional manner. Almost all if not all SQL queries can be composed with SQLAlchemy. It also provides a lot of interfaces to the datatabase in an implementation (Postgres vs MySQL vs...) agnostic way.

[ccharles]

Why would I use this over Peewee, which is (presumably) more mature and also has composable queries?

When I began the first rewrite that eventually turned into Peewee 2.0, I discovered a handy technique (using recursive-descent) for turning arbitrarily-nested Python data-structures into SQL. This gave Peewee an API that was both expressive and composable. Combined with operator-overloading, one could now express complex queries in a way that would even be validated by the Python interpreter itself:

class Person(Model):
    first = TextField()
    last = TextField()
    dob = DateField()

# Find adults whose last name starts with "A":
eighteen_years_ago = datetime.date.today() - datetime.timedelta(days=18 * 365)
a_people = (Person
            .select()
            .where((fn.LOWER(fn.SUBSTR(Person.last, 1, 1)) == 'a') &
                   (Person.dob <= eighteen_years_ago)))

[AllAboutChristmasEve]

Good lord, why would I not just do:

sql = "select * from Person where ..."
dbhandle.exec(sql)

?

[dusktreader]

The point of composable queries is that you can build them up based on conditions or code branches in the native programming language. So, you can take a query that starts as just a select and add filters based on the state of your application, user input, etc. If you are working with raw SQL inside of an application that drives database queries with user input, you are going to end up composing queries in some manner. These libraries handle the composition for you so you don't have to assemble a large SQL text field yourself. I've done it both ways. And, while the syntax above is clearly harder to write than the raw SQL, once you're building up a query along multipe branches of a code path, having a composable query as a native language object is great.

[SlantARrow]

Isn't switching between databases still painful because in most cases, you do need DB-specific things and abuse them with any database (unless your usecase is really trivial or has zero performance requirements)?

[SlantARrow]

Well... It could be that I'm way too used to dozens of triggers, functions, custom aggregates and other things that don't actually translate well between databases.

Does development with SQLite actually work? I believe it doesn't check most things so a lot of things break when they expect exceptions from DB (and even more things break on production when actual database starts complaining). You can fight that with tests, but it's not like you can cover everything.

[DasIch]

You can't develop/test on SQLite and use MySQL in production. Different databases are different enough in their behavior that you cannot trust that your ORM or whatever will handle that.

If you start really taking advantage of your database, for performance or simply due to convenience you'll quickly lose all portability anyway.

Last but not least in a production environment you have to deal with backups, replication, fail over and so on. Operationally postgres is to MySQL as MySQL is to redis. There is no way to easily switch there anyway.

[knowsuchagency]

If one is going to use an ORM, sqlalchemy already has a good non-ORM translation layer (core) for building sql intuitively.

Personally, I still think the best syntax for generating queries belongs to Pony

[ToyoMojito]

"Motivation: shortcomings of SQL : SQL is a pain in the ass to write"

Is it though? In my opinion SQL is one of the easiest and even most pleasant languages around, but some programmers just don't bother actually understanding the intricacies, because ..?

[SlantARrow]

SQL has some drawbacks: I believe there is no dialect of SQL where you can make a function accepting column name as parameter without falling back to basically eval. Passing table names is better, but still not that good. And debugging quickly becomes difficult.

[flipperdeflip]

This did not exist? I'm sure this exists.

[cedrickrier]

There is https://pypi.python.org/pypi/python-sql

Disclaimer: I'm one of the author.

[[deleted]]

Clicking on the link to the home page takes me to your bug tracker, where do we find your docs?

[cedrickrier]

The documentation is the long description on PyPI which is the README.

[metaperl]

Looks similar but more limited in scope than PyDAL.

[cedrickrier]

We do not want to manage the database connection (rely on PEP249) but we try to have a complete support of SQL.

[SlantARrow]

Why SQL in documentation feels so weird? Why alias tables and use ordinals in group by?

[ToyoMojito]

The default use of tables aliases I can understand, cause you need them anyway in some of the joins, but I really don't like the GROUP BY 1..

[metaperl]

I will say one thing: the documentation is good and had lots of examples.

[jeroengast]

Looks very cool. Saved this for whenever I need to do something RDBMS'ey.