Python – SQL Alchemy or plain SQL ?

Using SQL in Python reminded me of the “fun” I used to have in VBA MS Access, when I had to concatenate strings in VBA. Probably I was not the only one having “fun” and enjoying it, thus some good and wise people have provided a clever way to write SQL in Python, using the library sqlalchemy.

sqlalchemy-logo

So, in order to show the difference between the usage of SQL in python with sqlite3 from one side and sqlalchemy on the other, I have decided to resolve the following small task with the two methods. Here is the task:

  1. Make a program that generates a DB with one table.
    1. The rows in the table are: id, username, password, balance, message, mail
  2. Insert some data in the table
  3. Show the inserted data on the console.

That was it – pretty easy and quite useful to see the way to useORM (Object-relational mapping) such as SQL Alchemy.

With SQL Alchemy, I am using one class, which generates the table and the users are added to the session. This is how it looks like in Python:

With Sqlite, I am using a class, which holds the connection and the cursor.


With the good old sql, the things are trivial – we are using a class again and three functions, which enable us to give sql requests to the sqlite3 DB:

When we take a look in the number of lines, it is more or less the same (although both solutions are a little far away from optimal code), but the SQL Alchemy code is cleaner. So learn its syntax and enjoy yourself! 🙂

Tagged with: , , , ,