Why would you want to switch databases when you deploy? That's asking for bugs that only get revealed in production.

Also, the intersection of SQLite, MySQL, and Postgres is a pretty terrible database. You can be a lot more effective if you decide which one you're writing for.

targeting both MySQL and Postgresql is very reasonable and is commonplace. Openstack does it, for example. Lots of apps do. This assumes you're doing an app that's mostly CRUD. If you're doing a BI kind of app and need fancy analytical functions, then yes you'd target Postgresql.

Hey, wait. We don't even have a project defined but you're already talking about being effective. Isn't it a bit early? ;)

A lot of CRUD apps (which, I think, a lot of websites/webapps are) don't usually use anything but the basic SQL stuff.

> Why would you want to switch databases when you deploy? That's asking for bugs that only get revealed in production.

Only if you deploy directly to production!

Any competent organization should have at least a staging environment (and probably some other pre-staging testing environments) where you deploy and run your full application stack, and only promoted verified builds to production after they pass QA on earlier environments.

the top-level comment in this thread praises SQLAlchemy for being "cross-database, so you can use the same code with SQLite for development, and deploy to MySQL or Postgres.", and your parent just says that's a recipe for disaster.

It's pretty common to run SQLite on your local machine while developing. Apparently the parent is confused by reading "deploy" as "deploy directly to production".

It's sad that ORMs are always restrictive and raw SQL can't be safely and easily modified. I'd always wished for a library that one could just write SQL, and the library could do the transformations.

I.e. one writes plain SQL strings, but they're parsed under the hood, so AST transformations can be applied, like adding extra WHERE clauses conditionally or applying LIMIT.

Sadly, I haven't found any good Python SQL parsing library. That was long time ago, though - maybe someone had written one already.

Peewee [1] is my tool of choice because of its convenience and flexibility combined. I can create pretty complex queries using its Query API, but also execute arbitrary queries when I know I need something a little more nonstandard. Plus the maintainer (Charles Leifer) is an awesome dude who has always been really helpful to his users.

[1] Peewee: https://github.com/coleifer/peewee

Thanks for the kind words!

That’s sort of like SQL Alchemy Core works[1]. The ORM stuff is all built on top of that and completely optional.

[1] http://docs.sqlalchemy.org/en/latest/core/tutorial.html

Unfortunately, no, that's not what I meant, sorry.

I can't give SQLAlchemy (Core or ORM) a string like "SELECT * FROM foo AS f LEFT JOIN bar AS b ON b.id = f.bar_id WHERE f.baz > %(baz)s" and then transform it, by, say, appending the LIMIT clause or adding extra WHERE condition. AFAIK, there's no way to provide a raw SQL string and then say something like `query.where("NOT f.fnord")` OR `query.limit(10)` and get the updated SQL.

With ORMs or non-object-mapping wrappers if I want transformations, I have to use their own language instead of SQL. I do, but don't really want to.

Or things have changed and this is what SA can do this nowadays? I'll be more than happy to learn that I'm wrong.

you know what the problem is there, that a. parsing your SQL string into a tokenized structure b. altering it based on your given instructions c. re-compiling it back into a string, all on top of an interpreted scripting language, and then d. sending it to a database so that the database can parse it a second time, is just so inefficient, for a particular API style that is arguably not even any better than just learning how to write Core expressions. Core and ORM expressions can be cached to their resultant SQL to save on the tokenized-structure->string step too.

there are certainly SQL parsers that can easily produce such tokenized structures and from a technical standpoint, your API is pretty simple to produce, with or without shallow or deep SQLAlchemy integrations. It's just there's not really any interest in such a system and it's never been requested.

You actually can do that with SA. See the example here: http://docs.sqlalchemy.org/en/latest/core/tutorial.html#spec...

If you specify the columns involved with your text query, then you get back a 'TextAsFrom' object, and you can apply other transformations to it like .where() or .unique().

At least you can do that by adding other SA objects.

I used to do exactly this kind of filtering, by building up by SA query, back in the day (about 2008) when I last used SA in anger.

Agreed! I was heavily exploring building this upon SQLAlchemy, but decided that the first version needed only to support the only database I use regularly: Postgres.

There's a very big chance that SQLAlchemy will be integrated into the project, to allow for connections to multiple database types.

To generate raw SQL queries that is database engine independent, you can use: https://pypi.python.org/pypi/python-sql