Skip to content

SQLAlchemy, a Python ORM for databases


I haven’t talked about Python for a long time, not because I don’t have things to tell, but because I’ve been focused on what I’ve already explained here, and I haven’t expanded on other fields. In today’s post, I present SQLAlchemy, a powerful Python library for working with relational databases.

The basic concept is the antipode to what I have always done (I still have to rethink this new way of programming):

The use of normal Python objects to represent database tables and query execution

There are a number of benefits to this new approach:

  • The development of the application is done entirely in Python
  • Abstraction of the model without worrying about the database engine to use. It’s about you taking care of the mdelo, that SQLAlquemy is in charge of preparing the SQL to work with the database engine you choose, be it SQLite, Oracle, MySQL, …
  • Database errors are reduced, because there are 2 layers between the application and the database: the Python interpreter itself, and SQLAlchemy with all its APIs defined and ready for ready-made error checking
  • The database code is very efficient
  • Applying ORM, Object Relational Mapping, makes the code easier and more convenient to maintain, always with the “Don’t repeat yourself” mantra
  • SQLAlchemy helps prevent SQL injection vulnerabilities
  • Excellent community support
  • Wide range of additional libraries

With such a range of goodness, where’s the harm?

Obviously, if you are used to working on the database directly, this new concept will cost you a lot to adapt, which is what happens to me.

Applying ORM has its advantages, because if you make a modification to a table, if you do it directly on the database, you will also need to modify all the queries, while if you do it through an ORM, the ORM itself takes care of everything once you define the Python classes.


Perfect for orking with Flask

As you know, I have a predilection for Flask over other Python frameworks or microframeworks, so Flask’s own developers have adapted a flask-sqlalchemy extension so as not to reinvent the wheel.

The supported database engines are:

  • SQLite
  • MySQL
  • PostgreSQL
  • Firebird
  • Microsoft SQL Server
  • Oracle

Depending on the engine you are going to use, it would be convenient to install the necessary modules for the operation, as there are not many installed as standard (SQLite if it comes with Python standard).

Honestly, since I do quite well with SQL, I am very lazy (well, and partly unaware of it) to work with ORM like SQLAlchemy, because I like to be in control, but it is true that it has its advantages, as it is the engine that is responsible for preparing the different statements, accommodated according to the engine, since how should you know, SQL statements can vary from one engine to another.

Well, then, why don’t you try? Let’s go for it!

Happy coding!



Manejando Datos Newsletter

Noticias, entradas, eventos, cursos, … News, entrances, events, courses, …

Gracias por unirte a la newsletter. Thanks for joining the newsletter.