CRUD using SQLAlchemy Core

SQL Expression Language

SQL Expression Language is a backend neutral way to writing SQL statement using Python.

Inserting Records

There are several ways to insert records into the database. The most basic way is to use the insert() method of the Table instance and pass values of the columns as keyword arguments to the values() method.

To view the SQL this code would generate type the following:

Expected Output:

Notice that the VALUES clause contains the bind parameters (i.e a parameter of the form :name) instead of the values passed to the values() method.

When the query is run against the database the dialect will replace the bind parameters with the actual values. The dialect will also escape the values to mitigate the risk of SQL injection.

We can view the values that will replace the bind parameters by compiling the insert statement.

Expected Output:

We have created the insert statement but we haven’t sent it to the database. To do that, call the execute() method of the Connection object.

Expected Output:

The above code inserts the following record in the customers table.

The execute() method returns an object of type ResultProxy. The ResultProxy provides several attributes, one of them is called inserted_primary_key which returns the primary key of the records just inserted.

Expected Output:

Another way to create insert statement is to use the standalone insert() function from the sqlalchemy package.

Expected Output:

Multiple Inserts

Instead of passing values to the values() method as keyword arguments, we can also pass them to the execute() method.

Expected Output:

The execute() method is quite flexible because it allows us to insert multiple rows by passing a list of dictionaries each representing a row to be inserted.

Expected Output:

Before moving on to the next section, let’s add some records to the items, orders and order_lines table.

Expected Output:

Selecting Records

To select records we use select() method of the Table object.

Expected Output:

As you can see, this query is not qualified in any way, as a result, it will return all the rows from the customers table.

Another way to create SELECT query is to use the standalone select() function. It accepts a list of tables or columns from where to retrieve data.

Expected Output:

As usual, to send the query to the database we use the execute() method:

Expected Output:

The fetchall() method of the ResultProxy object returns all the records matched by the query. Once the result set is exhausted, subsequent calls to fetchall() will return an empty list.

Expected Output:

The fetchall() method loads all the results into the memory at once. Thus, it is not very efficient on a large reset set. Alternatively, you can use for loop to iterate over the result set one at a time.

Expected Output:

Here is a list of some common methods and attributes of the ResultProxy object.

Method/Attribute Description
fetchone() fetch the next row from the result set. If the result set has been exhausted, subsequent calls to fetchone() returns None.
fetchmany(size=None) fetch the specified number of rows from the result set. If the result set has been exhausted, subsequent calls to fetchmany() returns None.
fetchall() fetch all the rows from the result set. If the result set has been exhausted, subsequent calls to fetchall() returns None.
first() fetch the first row from the result set and close the connection. This means that after calling the first() method we can't access any other rows in the result set, until we send the query to the database again (using the execute() method).
rowcount returns the number of rows in the result set.
keys() returns a list of columns from where data is retrieved.
scalar() fetch the first column from the first row and close the connection. If the result set is empty it returns None.

The following shell sessions demonstrates methods and attributes we just discussed in action.

fetchone()

Expected Output:

fetchmany()

Expected Output:

first()

Expected Output:

rowcount

Expected Ouput:

keys()

Expected Output:

scalar()

Expected Output:

It is important to note that rows returns by methods fetchxxx() and first() are not tuples or dictionaries, instead, it is an object of type RowProxy, which allows us to access data in the row using column name, index position or Column instance. For example:

Expected Output:

To access data from multiple tables simply pass comma separated list of Table instances to the select() function.

This code would return the Cartesian product of rows present in both the tables. We will learn how to create an SQL JOIN later in this chapter.

Filtering Records

To filter records we use where() method. It accept a condition and adds a WHERE clause to the SELECT statement.

This query will return all the items whose cost price is greater than 20.

Expected Output:

We can specify additional conditions by simple chaining the where() method.

Expected Output:

As you can see, when we chain the where() method the conditions are ANDed together.

So how do we specify OR or NOT conditions to our SELECT statement?

It turns out that instead of chaining where() method there are two other ways to combine conditions:

  1. Bitwise Operators.
  2. Conjunctions.

Let’s start with the first one.

Bitwise Operators

Bitwise Operators &, | and ~ allow us to connect conditions with SQL AND, OR and NOT operators respectively.

The preceding query can be coded using bitwise operators as follows:

Notice that the conditions are wrapped using parentheses, this is because the precedence of bitwise operators is greater than that of + and > operators.

Here are some more examples:

Example: 1

Expected Output:

Example: 2

Expected Output:

Example: 3

Expected Output:

Conjunctions

Another way to connect conditions is to use conjunction functions i.e and_(), or_() and not_(). This is the preferred way of defining conditions in SQLAlchemy.

Here are some examples:

Example 1:

Expected Output:

Example 2:

Expected Output:

Example 3:

Expected Output:

Other Common Comparison Operators

The following listing demonstrates how to use some other comparison operators while defining conditions in SQLAlchemy.

IS NULL

Expected Output:

IS NOT NULL

Expected Output:

IN

Expected Output:

NOT IN

Expected Output:

BETWEEN

Expected Output:

NOT BETWEEN

Expected Output:

LIKE

Expected Output:

The like() method performs a case-sensitive match. For case-insensitive match use ilike().

Expected Output:

NOT LIKE

Expected Output:

Ordering Result

The order_by() method adds ORDER BY clause to the SELECT statement. It accepts one or more columns to sort by. For each column listed in the order_by() clause, you can specify whether the rows are sorted in ascending order (using asc()) or descending order (using desc()). If neither specified rows are sorted in ascending order. For example:

Expected Output:

This query returns the rows sorted by cost_price in ascending order. It is equivalent to:

To sort the result in descending order use desc() function. For example:

Expected Output:

Here is another example which sorts the rows according to two columns, first by quantity (in ascending order) and then by cost_price (in descending order).

Expected Output:

Limiting Results

The limit() method adds the LIMIT clause to the SELECT statement. It accepts an integer which indicates the number of rows to return. For example:

Expected Output:

To specify an offset (i.e. the starting position) to the LIMIT clause use the offset() method.

Expected Output:

Limiting Columns

The SELECT statements we have created so far has returned data from all the columns of the table. We can limit the number of fields returned by the query by passing the name of the fields as a list to the select() function. For example:

Expected Output:

This query returns the data only from the name and quantity columns from the items table.

Just as in SQL, we can perform simple calculations on the rows retrieved before sending them to the output. For example:

Expected Output:

Notice that the items.c.selling_price * 5 is not an actual column, therefore, an anonymous name called anon_1 is generated to display the query result (line 5).

We can assign a label to a column or expression using the label() method, which works by adding an AS subclause to the SELECT statement.

Expected Output:

Accessing Built-in Functions

To access the built-in functions provided by the database we use func object. The following listing shows how to use date/time, mathematical and string functions found in PostgreSQL database.

Expected Output:

You also have access to aggregate functions via the func object.

Expected Output:

Grouping Results

Grouping results is done via GROUP BY clause. It is commonly used in conjunction with the aggregate functions. We add GROUP BY clause to the select statement using group_by() method. It accepts one or more columns and groups the rows according to the values in the column. For example:

Expected Output:

This query returns the number of customers lives in each town.

To filter out the results based on the values returned by aggregate functions we use having() method which adds the HAVING clause to the SELECT statement. Just like the where() clause, it accepts a condition.

Expected Output:

Joins

The Table instance provides the following two methods to create joins:

  1. join() – creates inner join
  2. outerjoin() – creates outer join (LEFT OUTER JOIN to be specific)

The inner join returns only the rows which matches the join condition, whereas the outer join returns the rows which matches the join condition as well as some additional rows.

Both methods accept a Table instance, figures out the join condition based on the foreign key relationship and returns a JOIN construct.

If the methods can’t figure out the join condition correctly or you want to specify an alternate condition, you can do so by passing the join condition manually as a second argument.

When we specify tables or list of columns in the select() function, SQLAlchemy automatically places those tables in the FROM clause. However, when we use join, we know exactly the tables we want in the FROM clause, so we use the select_from() method. However, if we want we can use select_from() in queries not involving joins too. For example:

Expected Output:

Let’s use this knowledge to find all the orders placed by customer name John Green.

Expected Output:

The preceding query only returns the order id and date_placed. Wouldn’t it be better to know the items and quantity of items in each order?

To get the items and quantity of items in each order we have to create a total of 3 joins, spanning all the way to the items table.

Expected Output:

Finally, here is an example of how to define an outer join.

Expected Output:

The Table instance we pass to the outerjoin() method is placed on the right side of the outer join. As a result, the above query will return all the rows from customers table (the left table), and only the rows that meets the join condition are returned from the orders table (the right table).

If you want all the rows from the order table but only the rows that meets the join condition from the orders table, call outerjoin() as follows:

Expected Output:

You can also create a FULL OUTER JOIN by passing full=True to outerjoin() method. For example:

Expected Output:

Updating Records

Updating records is achieved using the update() function. For example, the following query updates the selling_price and quantity of Water Bottle to 30 and 60, respectively.

Expected Output:

Deleting Records

To delete records we use the delete() function.

Expected Output:

This query will delete all the customers whose username starts with pablo.

Dealing with Duplicates

To deal with the duplicate rows in the result set we use the DISTINCT option. We can add DISTINCT option to the SELECT statement using the distinct() method. For example:

Expected Output:

Here is another example which uses distinct() option with the count() aggregate function and counts the distinct number of towns in the customers table.

Expected Output:

Casting

Casting (converting) data from one type to another is a common operation and is done via cast() function from the sqlalchemy package.

Expected Output:

Unions

The SQL’s UNION operator allows us to combine result set of multiple SELECT statements. To add UNION operator to our SELECT statement we use union() function.

Expected Output:

By default, union() removes all the duplicate rows from the result set. If you want to keep the duplicates use union_all().

Expected Output:

Creating Subqueries

We can also access data from multiple tables using subqueries.

The following query returns the id and name of the items ordered by John Green in his first order:

Expected Output:

This query can also be written using JOINs as follows:

Expected Output:

Raw Queries

SQLAlchemy also gives you the flexibility to execute raw SQL using the text() function. For example, the following SELECT statement returns all the orders, along with the items ordered by John Green.

Expected Output:

Notice that the SELECT statement contains two bind parameters: first_name and last_name. The values to these parameters is passed via the execute() method.

The text() function can also be embedded inside a select() construct. For example:

Expected Output:

Another way to execute raw SQL is to pass it directly to the execute() method. For example:

Expected Output:

Transactions

A transaction is a way to execute a set of SQL statements such that either all of the statements are executed successfully or nothing at all. If any of the statement involved in the transaction fails then the database is returned to the state it was in before the transaction was initiated.

We currently have two orders in the database. To fulfill an order we need to perform following two actions:

  1. Subtract the quantity of ordered items from the items table
  2. Update the date_shipped column to contain the datetime value.

Both of these action must be performed as a unit to ensure that the data in the tables are correct.

The Connection object provides a begin() method, which starts the transaction and returns an object of type Transaction. The Transaction object in turn provides rollback() and commit() method, to rollback and commit the transaction, respectively.

In the following listing we define dispatch_order() method which accepts order_id as argument, and performs the above mentioned actions using transaction.

Our first order is for 5 chairs, 2 pens and 1 headphone. Calling dispatch_order() function with order id of 1, will return the following output:

Expected Output:

At this point, items and order_lines tables should look like this:

Our next order is for 5 chairs and 4 pens, but we now only have 5 chairs and 1 pen in the stock!

Let’s try running dispatch_order() for second order and see what happens.

Expected Output:

As expected, our shipment failed because we don’t have enough pens in the stock and because we are using transaction our database is returned to the state it was in before the transaction was started.

3 thoughts on “CRUD using SQLAlchemy Core

    • Few Suggestions:

      Please add right in the beginning

      conn = engine.connect() metadata = MetaData(conn)

      customers = Table(‘customers’, metadata, autoload=True) items = Table(‘items’, metadata, autoload=True) orders = Table(‘orders’, metadata, autoload=True) order_lines = Table(‘order_lines’, metadata, autoload=True) Some newcomers may not be able to create the Table objects

      Explicit mention of MySQL will attract more viewers who may othewise mistake this is as postgres only

      Escaping new line with a end-of-line is not as “nice” as parentheses for multi-line statements

      A large import that has all of needed stuff will be helpful

      from sqlalchemy import ( createengine, MetaData, Table, Column, func, insert, select, update, and, or, not )

      in_ notin_ are methods on the column instance
      A Jupyter notebook in your site or github or https://notebooks.azure.com/ for two database URLs MySQL Postgres will be best

Leave a Comment