Python SQLAlchemy

Python SQLAlchemy and Object-Relational Mapping

Whenever it comes to programming a web service, you will require a solid database backend. In the past, programmers used to write raw SQL statements, pass them to the database engine and parse the returned results as a normal array of records. Nowadays, programmers can write Object-Relational Mapping (ORM) programs to remove the necessity of writing tedious and error-prone raw SQL statements.



Most programming language platforms are object oriented. ORM is a programming technique for converting data between incompatible type systems in object-oriented programming languages. Data in RDBMS (Relational Database Management System) servers on the other hand is stored as tables. Object relation mapping is a technique of mapping object parameters to the underlying RDBMS table structure. An ORM API provides methods to perform create, read, update, delete (CRUD) operations without having to write raw SQL statements. So basically, an ORM takes care of these issues while you can focus on programming the logics of the system.



In order to run the python script with SQLAlchemy codes in it, you need to install flask-sqlalchemy extension first for that run the command :


Let’s Get To Know SQLAlchemy


For using functions from SQLAlchemy in your program, you need to import it first using the statement :

Now you can create your Flask application and make surem you set the URI for the database which is to be used in your program.

Now, you need to create an object of SQLAlchemy class with the application object provided as parameter. The object provides helper functions for ORM operations and also provides a Model class using which user-defined models for database is declared. In the following code snippet a persons model is created:

To create or use the database that we specified in the URI run the create_all() method.

To add an object data to the database we can use the following code:

For deleting just replace the add function with delete function and if you need to retrieve the records of the table use:

You can also apply filters while retrieving records. For eg:

will return the set of table rows whose city attribute is equal to ‘Mumbai’.




Now, let’s see an entire program based on what we’ve learned so far:

First let’s setup an html page to display our database contents.


We’ve set an HTML page with a heading SQLAlchemy Test and it has a table with two columns to display a persons name and phone number. Now we need to code a python script to do our job.

In the above python script, we create a model class for person which can hold values for a person’s name and phone number. We set the route of out app to the homepage of localhost. So after running the app, when you open the browser and visit your localhost homepage by typing in your browser’s address bar, you can see the html page we’ve setup. It will show all function finally returns the rendered template of show_all.html populated with the contents in the database.

Now, we can checkout how we add contents to the database. For that we need to create objects of our model class and provide values for its attributes i.e. name and phone of a person. Now we can add it to our database using SQLAlchemy_object.session.add(model object) function. After adding all the values make sure you commit the changes to the database in order to prevent data corruption. Now, we have our database ready with contents, we can display it on our html page using render template(‘html_file_name’, model = model.query.all()) function.

Save the python file and place the html file in a folder named templates.  If no such folder exists create one. Now you can run the python script and visit in your browser to see your app running.


Benefits of SQLAlchemy:

According to here are the top 10 reasons to love SQLAlchemy

  • Let’s you define the database schema in your code
  • Automatically synchronise the model and schema
  • Easy to read
  • Simple queries.
  • Seamless integration with web frameworks
  • Fast loading, Better performance
  • Transparent Polymorphism
  • Works with legacy frameworks
  • Easy to customise the library
  • Great Documentation


Challenges of SQLAlchemy.

  • The concept of unit of work is not very known among the developer community
  • A heavy-weight API



Leave a Reply