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.


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: , , , ,