Sqlalchemy tutorial pdf

Sections that have a dark blue border on the right will discuss concepts that are primarily Core-only ; when using the ORM, these concepts are still in play but are less often explicit in user code. The ORM provides an additional configuration layer allowing user-defined Python classes to be mapped to database tables and other constructs, as well as an object persistence mechanism known as the Session. Sections that have a light blue border on the left will discuss concepts that are primarily ORM-only.

Core-only users can skip these. A section that has both light and dark borders on both sides will discuss a Core concept that is also used explicitly with the ORM. The tutorial will present both concepts in the natural order that they should be learned, first with a mostly-Core-centric approach and then spanning out into more ORM-centric concepts. This content is Core-centric however ORM users will want to be familiar with at least the Result object.

This section introduces how to do that from both a Core and an ORM perspective. Note that, creating an engine does not connect to the database instantly. To learn more about the options available to create SQLAlchemy engines, take a look at the official documentation. Connection pooling is one of the most traditional implementations of the object pool pattern.

Object pools are used as caches of pre-initialized objects ready to use. That is, instead of spending time to create objects that are frequently needed like connections to databases the program fetches an existing object from the pool, uses it as desired, and puts back when done.

The main reason why programs take advantage of this design pattern is to improve performance. In the case of database connections, opening and maintaining new ones is expensive, time-consuming, and wastes resources. Besides that, this pattern allows easier management of the number of connections that an application might use simultaneously.

There are various implementations of the connection pool pattern available on SQLAlchemy. This kind of pool comes configured with some reasonable defaults, like a maximum pool size of 5 connections. As usual production-ready programs need to override these defaults to fine-tune pools to their needs , most of the different implementations of connection pools provide a similar set of configuration options.

The following list shows the most common options with their descriptions:. To learn more about connection pools on SQLAlchemy, check out the official documentation. As SQLAlchemy is a facade that enables Python developers to create applications that communicate to different database engines through the same API, we need to make use of Dialects. Most of the popular relational databases available out there adhere to the SQL Structured Query Language standard, but they also introduce proprietary variations.

These variations are the solely responsible for the existence of dialects. For example, let's say that we want to fetch the first ten rows of a table called people. Therefore, to know precisely what query to issue, SQLAlchemy needs to be aware of the type of the database that it is dealing with.

This is exactly what Dialects do. They make SQLAlchemy aware of the dialect it needs to talk. Dialects for other database engines, like Amazon Redshift , are supported as external projects but can be easily installed. As explained by Martin Fowler in the article, Mappers are responsible for moving data between objects and a database while keeping them independent of each other. As object-oriented programming languages and relational databases structure data on different ways, we need specific code to translate from one schema to the other.

For example, in a programming language like Python, we can create a Product class and an Order class to relate as many instances as needed from one class to another i.

Product can contain a list of instances of Order and vice-versa. Though, on relational databases, we need three entities tables , one to persist products, another one to persist orders, and a third one to relate through foreign key products and orders. While using SQLAlchemy, we can rest assured that we will get support for the most common data types found in relational databases. For example, booleans, dates, times, strings, and numeric values are a just a subset of the types that SQLAlchemy provides abstractions for.

Besides these basic types, SQLAlchemy includes support for a few vendor-specific types like JSON and also allows developers to create custom types and redefine existing ones. To understand how we use SQLAlchemy data types to map properties of Python classes into columns on a relation database table, let's analyze the following example:. In the code snippet above, we are defining a class called Product that has six properties.

Let's take a look at what these properties do:. Seasoned developers will notice that usually relational databases do not have data types with these exact names. SQLAlchemy uses these types as generic representations to what databases support and use the dialect configured to understand what types they translate to. For example, on a PostgreSQL database, the title would be mapped to a varchar column.

Now that we know what ORM is and have look into data types, let's learn how to use SQLAlchemy to map relationships between classes to relationships between tables.

Note that this section will be an overview of all these types, but in the SQLAlchemy ORM in Practice action we will do a hands-on to practice mapping classes into tables and to learn how to insert, extract, and remove data from these tables. The first type, One To Many , is used to mark that an instance of a class can be associated with many instances of another class.

For example, on a blog engine, an instance of the Article class could be associated with many instances of the Comment class. In this case, we would map the mentioned classes and its relation as follows:. The second type, Many To One , refers to the same relationship described above but from the other perspective.

To give a different example, let's say that we want to map the relationship between instances of Tire to an instance of a Car. As many tires belong to one car and this car contains many tires, we would map this relation as follows:. The third type, One To One , refers to relationships where an instance of a particular class may only be associated with one instance of another class, and vice versa.

As an example, consider the relationship between a Person and a MobilePhone. Usually, one person possesses one mobile phone and this mobile phone belongs to this person only.

To map this relationship on SQLAlchemy, we would create the following code:. In this example, we pass two extra parameters to the relationship function. The official Relationships API documentation provides a complete explanation of these parameters and also covers other parameters not mentioned here. The last type supported by SQLAlchemy, Many To Many , is used when instances of a particular class can have zero or more associations to instances of another class.

For example, let's say that we are mapping the relationship of instances of Student and instances of Class in a system that manages a school.

To work around this issue, we can write our queries as objects using an ORM instead of writing them as strings. Next, we import the declarative base. It expresses Table, mapper , and class objects all at once within the class declaration. We can name our database anything, but here we named it books-collection. The last step in our configuration is to add Base.

In SQLAlchemy, classes are the object-oriented—or declarative—representation of tables in our database. For this tutorial, we only need to create one table: Book. Our Book table has four columns: id , title , author , and genre. Integer and String are used to define the type of the values stored in a column: the column title, author and genre are all strings, while column id is of an integer type. Then we let our program know which database engine we want to communicate with.

To make the connections between our class definitions and our tables in the database, we use the Base. In order to create, delete, read or update entries in our database, SQLAlchemy provides an interface called Session. To execute our queries, we need to add and commit our entry. It also provides us with a method called flush. Flush pushes our changes from memory to our database transaction buffer without committing the change. Depending on what we want to read, we can use different functions.

The Bell Jar. There are, however, a few gotchas that we need to be careful about. Deleting values from our database is almost the same as updating the values. Instead of updating, we delete the values.