Untitled-2

Essential SQLAlchemy Tips and Techniques



SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

In this article, we are going to start with some basic SQL queries using SQLAlchemy and use them to interact with a database of your choice.

First Steps

You are going to need SQLAlchemy package (version 0.8.4) and respective database drivers if you are going to use Relational Database.

Creating Database

We are going to be creating a database to work with. So open up your text editor and create a file called sql.py which we will use to create the database.

First we need to connect to database from our script and to achieve this we will use the SQLAlchemy-provided Engine class for managing the connection to the database.

To get started using an Engine, you use the create_engine() function.

We have wide variety of options to choose what engine we need to create the database.

The configurational process starts by describing the database tables we’ll be dealing with, and then by defining our own classes which will be mapped to those tables and this process can be handled by the declarative class of SQLAlchemy.

Now that we have a Base , we can define any number of mapped classes in terms of it.

We will start with just a single table called books which will store records for the end-users using our application.

Notice how the Table constructor is given the SQL name of the table (‘books’) and a list of columns. The columns are similarly defined with their SQL names, data type. In this case, since we defined an ‘id’ column as a primary key, SQLAlchemy will automatically create the column with an auto-increment default value and the name and author are String types which can store the string values and price is the Integer type for storing integer values.

We define a constructor via __init__() and also a __repr__() method – both are optional. The class can have any number of other methods and attributes as required by the application, as it’s basically just a plain Python class.

We’re now ready to start talking to the database. SQLAlchemy uses sessionmaker() to map our model schema to database. Lets see how its done.

This custom-made Session class will create new Session objects which are bound to our database and we are binding our engine with the Session object.
Now, whenever you need to have a conversation with the database, you instantiate a Session.

The MetaData object offers is the ability to issue CREATE TABLE statements to the database for all tables that don’t yet exist. We illustrate this by calling the MetaData.create_all() method, passing in our Engine as a source of database connectivity. So lets add metadata binding to enable connectivity between our script and database.

Now run this script and you will notice that we have a SQLite database inside our project root.

Lets check if it contains our Book table through the Database browser.

sql1

Since we now have the database created successfully, lets add some sample data to it.

sql1

We have imported the Book class and session object and have instantiated the Book class with arguments and then by using session object we are pushing the book into session and then committing it to the database. Lets see if it worked.

sql1

Querying the Database

Since we have a database and table created, lets play with it by performing various operations on it.

The most important methods employed while querying are filter(), filter_by().

Lets look at each method one by one.

  • filter()

This method is used to filter our query based upon particular attributes of our model like User.name == "john", this will query the User table and filter all the results which has the name  attribute as "john".
We can pass .all() and .first() to this query to fetch the entries based upon our requirement.

I have created another entry to the database with the author name “trivedi”.

sql1

  • filter_by

Similarly we can use filter_by to query based upon the keyword which we pass to the method and can also perform .all() and .first() operations on it.

sql1

The major difference between the two is that filter_by uses keyword arguments, whereas filter allows pythonic filtering arguments like filter(User.name=="john").

Update and Delete

We can update the whole bunch of records very easily in SQLAlchemy.

sql1

We can easily delete records by calling the method .delete() on session object.

sql1

Relationships

When creating a database, common sense dictates that we use separate tables for different types of entities. Some examples are: authors, books, ISBN, etc… But we also need to have relationships between these tables. For instance, authors make books, and books contain ISBN numbers. These relationships need to be represented in the database. Also, when fetching data with SQL, we need to use certain types of JOIN queries to get what we need.
There are several types of database relationships. Today we are going to cover the following:

  • One to One Relationships
  • One to Many and Many to One Relationships
  • Many to Many Relationships

One to One Relationships

Let’s say you have a table for authors:

sql2

We can put the customer address information on a separate table:

sql2

Now we have a relationship between the Authors table and the Addresses table. If each address can belong to only one author , this relationship is “One to One”. Keep in mind that this kind of relationship is not very common. Our initial table that included the address along with the author could have worked fine in most cases.

Notice that now there is a field named “address_id” in the Authors table, that refers to the matching record in the Address table. This is called a “Foreign Key” and it is used for all kinds of database relationships.

We can visualize the relationship between the author and address records like this:

diagram

Note that the existence of a relationship can be optional, like having a customer record that has no related address record.

One to Many and Many to One Relationships

This is the most commonly used type of relationship. Consider an e-commerce website, with the following:

  • Customers can make many orders.
  • Orders can contain many items.
  • Items can have descriptions in many languages.

In these cases we would need to create “One to Many” relationships. Here is an example:

sql2

Each customer may have zero, one or multiple orders. But an order can belong to only one customer

Many to one places a foreign key in the parent table referencing the child. relationship() is declared on the parent.
 
Many to Many Relationships

sql2

The Items_Orders table has only one purpose, and that is to create a “Many to Many” relationship between the items and the orders.

Lets see one example for this type of relationship

Visualizing this representation:

diagram

Conclusion

Thank you for reading the article. I hope you enjoyed it! Please leave your comments and questions, and have a great day!