Mohammed Anees Mohammed Anees - 1 year ago 62
Python Question

Using psycopg2 to call functions instead of raw queries

I've got a pretty straightforward question. I intend to make a flask application that may end up making some complex SQL queries. For this reason, I have decided not to use an ORM and plus I prefer writing my own SQLs.

I wrote some simple SQLs for reading/writing data in a postgres function and then used psycopg2 to make function calls. I am thinking this approach would be better than writing raw SQLs as it would be easy to maintain.

Does anyone know of any pitfalls in taking this approach, or any limitations specific to psycopg2? Thank you.


An ORM can help you a lot, even if your application is Postgres only. I had to support really complicated deploys in different OS and I know ORMs can save you a lot of work and pain when requirements change. Definitely has some (small!) performance hit, but you can avoid most of the slow path when it is really important (bulk inserts/updates, plain SQL, etc.).

I'll take SQLAlchemy as an example, but most ORMs will have equivalent features.

  1. You have to write some data mapping to objects (or even a dict) in plain Python or with an ORM, the ORM have it for free if you want to use this feature.
  2. Define tables and models in your code. You don't even have to use the SQLAlchemy objects.
  3. Write SQL statements with Python code most of the time. It is compiled to the database of your choice. It does not work for a really complex query, but you always can fallback to plain SQL.
  4. Built-in migrations with alembic. All your database schema history with your code and most schema changes are auto generated.
  5. It is database and database driver agnostic. Internally, SQLAlchemy uses psycopg2 or other Postgres driver. No ORM I remember has its own driver. Once upon a time, I had unicode issues with psycopg2 (probably I was doing something wrong) and just changed SQLAlchemy to use another driver and worked fine. Other time, I wanted to run my application with PyPy and psycopg2 was not supported.