O'Reilly logo

Essential SQLAlchemy, 2nd Edition by Rick Copeland, Jason Myers

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required