CRUD using SQLAlchemy Core (original) (raw)

1. Home
2. SQLAlchemy Tutorial
3. CRUD using SQLAlchemy Core

Last updated on July 27, 2020

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:

'SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \nFROM customers'

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:

'SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \nFROM customers'

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.

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 Ouput:

scalar() #

Expected Ouput:

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 Ouput:

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

select([tableOne, tableTwo])

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 Ouput:

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

Expected Ouput:

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 Ouput:

Example: 2

Expected Ouput:

Example: 3

Expected Ouput:

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 Ouput:

Example 2:

Expected Ouput:

Example 3:

Expected Ouput:

Other Common Comparison Operators #

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

IS NULL #

Expected Ouput:

IS NOT NULL #

Expected Output:

IN #

Expected Ouput:

NOT IN #

Expected Ouput:

BETWEEN #

Expected Ouput:

NOT BETWEEN #

Expected Ouput:

LIKE #

Expected Ouput:

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

Expected Ouput:

NOT LIKE #

Expected Ouput:

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 Ouput:

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 Ouput:

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 Ouput:

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 Ouput:

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

Expected Ouput:

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 Ouput:

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 Ouput:

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 Ouput:

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 Ouput:

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

Expected Ouput:

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 Ouput:

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 Ouput:

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.

| >>> >>> print(customers.join(items, ... customers.c.address.like(customers.c.first_name + '%') ... ) ... ) customers JOIN items ON customers.address LIKE customers.first_name || :first_name_1 >>> >>> | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

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 Ouput:

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

Expected Ouput:

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 Ouput:

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

Expected Ouput:

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 Ouput:

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

Expected Ouput:

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 Ouput:

Deleting Records #

To delete records we use the delete() function.

Expected Ouput:

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 Ouput:

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 Ouput:

Casting #

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

Expected Ouput:

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 Ouput:

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

Expected Ouput:

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 Ouput:

This query can also be written using JOINs as follows:

Expected Ouput:

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 Ouput:

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 Ouput:

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

Expected Ouput:

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 Ouput:

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.