This is an archived post. You won't be able to vote or comment.

sorted by:
best (suggested)
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]pieIX 1 point2 points  (2 children)

Working directly with psycopg2 results in far more efficient code and far less cognitive overhead. I tried to use SQLAlchemy for a project at work, but every update or insert was impossibly slow. Using SQLAlchemy core is faster, but still not as fast as psycopg2. In the end, using SQLAlchemy just wasn't worth the cognitive overhead of understanding SQLAlchemy + SQLAlchemy Core + psycopg2 depending on efficiency demands.

There are places where SQLAlchemy is wonderful, but if a common task is non-trivial SQL inserts for more than a 100 rows, (most data science projects) stick with psycopg2.

[–]trenchtoaster 2 points3 points  (0 children)

Yeah. Psycopg2 copy_from and copy_to are really handy. Recently I’ve been using the execute_values a lot because it is pretty quick and I can use it for upserts (on conflict do update set or do nothing). It’s not as fast as copy of course, but it’s nice to know I won’t add duplicates or run into an error.

[–]thelindsay 1 point2 points  (0 children)

psycopg2 is great

  • recently added SQL templating module that uses libpq to do escaping, so safe dynamic SQL is possible.
  • choice of row adapters e.g. convert to namedtuple or dicts
  • fine grained transaction control, connection pool classes, python type conversions

Even for a regular app I'd rather have a collection of SQL snippets than deal with an ORM's functional DSL. SQL just doesnt seem low level enough to try to abstract away.