Essential SQLAlchemy, 2nd Edition

Chapter 2. Working with Data via SQLAlchemy Core

Now that we have tables in our database, let’s start working with data inside of those tables. We’ll look at how to insert, retrieve, and delete data, and follow that with learning how to sort, group, and use relationships in our data. We’ll be using the SQL Expression Language (SEL) provided by SQLAlchemy Core. We’re going to continue using the tables we created in Chapter 1 for our examples in this chapter. Let’s start by learning how to insert data.

Inserting Data

First, we’ll build an insert statement to put my favorite kind of cookie (chocolate chip) into the cookies table. To do this, we can call the insert() method on the cookies table, and then use the values() statement with keyword arguments for each column that we are filling with data. Example 2-1 does just that.

Example 2-1. Single insert as a method

ins = cookies.insert().values(
    cookie_name="chocolate chip",
    cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
    cookie_sku="CC01",
    quantity="12",
    unit_cost="0.50"
)
print(str(ins))

In Example 2-1, print(str(ins)) shows us the actual SQL statement that will be executed:

INSERT INTO cookies
    (cookie_name, cookie_recipe_url, cookie_sku, quantity, unit_cost)
VALUES
    (:cookie_name, :cookie_recipe_url, :cookie_sku, :quantity, :unit_cost)

Our supplied values have been replaced with :column_name in this SQL statement, which is how SQLAlchemy represents parameters displayed via the str() function. Parameters are used to ...