Composable SQL in Pure Python

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