70

I am writing a quick and dirty script which requires interaction with a database (PG).

The script is a pragmatic, tactical solution to an existing problem. however, I envisage that the script will evolve over time into a more "refined" system. Given the fact that it is currently being put together very quickly (i.e. I don't have the time to pour over huge reams of documentation), I am tempted to go the quick and dirty route, using psycopg.

The advantages for psycopg2 (as I currently understand it) is that:

  1. written in C, so faster than sqlAlchemy (written in Python)?
  2. No abstraction layer over the DBAPI since works with one db and one db only (implication -> fast)
  3. (For now), I don't need an ORM, so I can directly execute my SQL statements without having to learn a new ORM syntax (i.e. lightweight)

Disadvantages:

  1. I KNOW that I will want an ORM further down the line
  2. psycopg2 is ("dated"?) - don't know how long it will remain around for

Are my perceptions of SqlAlchemy (slow/interpreted, bloated, steep learning curve) true - IS there anyway I can use sqlAlchemy in the "rough and ready" way I want to use psycopg - namely:

  1. execute SQL statements directly without having to mess about with the ORM layer, etc.

Any examples of doing this available?

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
Homunculus Reticulli
  • 65,167
  • 81
  • 216
  • 341

2 Answers2

132

SQLAlchemy is a ORM, psycopg2 is a database driver. These are completely different things: SQLAlchemy generates SQL statements and psycopg2 sends SQL statements to the database. SQLAlchemy depends on psycopg2 or other database drivers to communicate with the database!

As a rather complex software layer SQLAlchemy does add some overhead but it also is a huge boost to development speed, at least once you learned the library. SQLAlchemy is an excellent library and will teach you the whole ORM concept, but if you don't want to generate SQL statements to begin with then you don't want SQLAlchemy.

swiss_knight
  • 5,787
  • 8
  • 50
  • 92
Jochen Ritzel
  • 104,512
  • 31
  • 200
  • 194
  • 1
    Been reading up a bit on SQLAlchemy. Just found I can specify psycopg2 as a driver and more or less use hand crafted SQL like I wamnt to, so decided to go with SQLAlchemy after all. – Homunculus Reticulli Dec 21 '11 at 11:44
  • 21
    SQLAlchemy and psycopg2 are completely different things, that part is true. But SQLAlchemy is not an ORM. The ORM is only part of it, a part that you can choose to ignore completely (and has been intended to be completely optional by it's creator, as he states himself in this rather long introduction video: http://www.youtube.com/watch?v=P141KRbxVKc ). So in a case like this OP's (outdated) problem, SA could be the tool for the "q'n'd" raw SQL solution with being able to switch to orm later, without changing the tool. – kratenko Oct 01 '14 at 13:20
  • 1
    @kratenko I was in same situation as OP and I ended up making the same decision. Your comment helped in making up my mind. What started as a QnD script, ended up getting more complex and in the end I was glad I started with SqlAlchemy. – Sudarshan Kadam Jun 21 '23 at 14:08
14

To talk with database any one need driver for that. If you are using client like SQL Plus for oracle, MysqlCLI for Mysql then it will direct run the query and that client come with DBServer pack.

To communicate from outside with any language like java, c, python, C#... We need driver to for that database. psycopg2 is driver to run query for PostgreSQL from python.

SQLAlchemy is the ORM which is not same as database driver. It will give you flexibility so you can write your code without any database specific standard. ORM provide database independence for programmer. If you write object.save in ORM then it will check, which database is associated with that object and it will generate insert query according to the backend database.

Nilesh
  • 20,521
  • 16
  • 92
  • 148