4 ms·
Meh. SQLAlchemy will already do almost exactly the same thing, if you use its lower-level functions and avoid the fancy ORM layer. It's also cross-database, so
by ForHackernews 11y ago
Meh. SQLAlchemy will already do almost exactly the same thing, if you use its lower-level functions and avoid the fancy ORM layer. It's also cross-database, so you can use the same code with SQLite for development, and deploy to MySQL or Postgres.
- rspeer 11y agoWhy 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.
- drdaeman 11y agoHey, 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.
- ForHackernews 11y ago> 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.
- ta0967 11y agothe 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.
- ForHackernews 11y agoIt'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".
- zzzeek 11y agotargeting 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.
- drdaeman 11y agoIt'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.
- josefdlange 11y agoPeewee [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 https://github.com/coleifer/peewee
- coleifer 11y agoThanks for the kind words!
- deno 11y agoThat’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 http://docs.sqlalchemy.org/en/latest/core/tutorial.html
- drdaeman 11y agoUnfortunately, 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.
- kenneth_reitz 11y agoAgreed! 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.
- cedk 11y agoTo generate raw SQL queries that is database engine independent, you can use: https://pypi.python.org/pypi/python-sql https://pypi.python.org/pypi/python-sql