Storing Data With Python

Data is invaluable, and this is especially true these days, where we store hundreds of terabytes of data on the Web. To handle this amount of data, we need more than the traditional set of SQL statements like select, insert, and update. Unfortunately, manually creating SQL statements can be a difficult job, not to mention that it's one that's very error prone. To address this, a new category of frameworks have been developed called ORMs or Object Relational Mappers which help developers to map their models used in the source code to data models stored in the databases.

One of the most commonly used ORM in the Python world is SQLAlchemy. This framework is widely adopted because it implements a lot of best practices while delivering on the features that enterprise applications require.

In this article, I will present how to map models from the code to database tables, as well as how to save, load and query data using SQLAlchemy and Python. All the code from this article is available on GitHub.

Installation

The installation of SQLAlchemy can be done using: pip install sqlalchemy command, but I recommend doing this in a virtual environment:

First I need to import the Column, String, Integer, and all the other methods that I need to use. These methods offer an abstraction over the different SQL syntax rules that are specific to the database engines. As a developer, I don't have to know how to declare an 800 character string field in MySQL, PostgreSQL, or MSSQL because SQLAlchemy knows instead of me. All I need to do is to set the correct parameters for the data fields, and SQLAlchemy will resolve the SQL statement generation.

The __tablename__ property holds the name of the table that the model will map to once the database is created.

The User class has a field called bids. This field is marked as a relationship that points to the Bid class. Please notice that the Bid class has a user_id field, which is Foreign Key for the user_name field. This field ensures that all the Bids can be associated to a user.

The Project class is almost identical with User class, except it has an id field which auto increments and a budget field which is a numeric value. The Project class has a relationship with the Bid class too, because one Project can have more bids.

Connecting to the database

SQLAlchemy can create the database in case there is no database available. The code that does this is in the init_database.py file:

First, there is the database engine specific key, which, in this case, is mysql+mysqldb. Then comes the user:password@host/database construct. After these, there is an option to define parameters for the connection. In this example, the path to the uinx_socket is specified with the path pointing to the mysql socket.

Once the db_engine is initialized, the BaseClass.metadata.create_all method will take all the classes and model configuration available in the context, creating tables.

The screenshot shows what SQL statements were generated and executed to create the tables and relationships.

Manipulating Data

To add or read data from the MySQL database, a new SQLAlchemy session has to be created. SQLAlchemy has the concept of session, the same way Hibernate (a well-known ORM for Java) has.

In the DAL class' constructor, a new database engine is created. A new session is then created for the engine using the sessionmaker method

In the add_user method, a new User object is created, which is then added to the session. In the last line, the commit method is invoked. This forces the session to flush all the changes in the session and write everything to the database.

Data can be loaded from the database using the query method, which can be extended with an order_by method. This generates the select SQL statement with and order by clause.

The session is queried for User objects, which is then filtered to return only users which have a similar username like the one passed as argument.

I just went through the installation steps of SQLAlchemy, and covered how the engine parameter should be configured, and how Python classes can be mapped to database models. This also demonstrates how these models can be queried, saved and deleted using SQLAlchemy's query API. You can find all the available query API calls supported by the framework on the SQLAlchemy help page.

I am a Software Engineer with over 7 years of experience in different domains(ERP, Financial Products and Alerting Systems). My main expertise is .NET, Java, Python and JavaScript.
I like technical writing and have good experience in creating tutorials and how to technical articles.
I am passionate about technology and I love what I do and I always intend to 100% fulfill the project which I am ...