How to Query Data and Insert Database Rows With SQLAlchemy

Inserting rows

#

To be able to insert rows into the database, we need to create the tables on the database. SQLAlchemy provides us with a way to do that by looking at the models file and inferring what the database schema should be.

To start an interactive Flask sessions where we can interactively write Python, Flask provides us with a way to start an interactive shell

In your terminal (with the virtualenv activated), run the following command.

$ FLASK_ENV=development FLASK_APP="yumroad:create_app" flask shell

This launches a Python interactive shell with the application already initialized and imported as app.

Our first step will be to actually create the database and the associated tables. SQLAlchemy provides us with a method to do that with a method called create_all() which creates the database if necessary and any tables.

To run create_all(), we will need to import db from extensions since that is where our instance of Flask-SQLAlchemy has been defined. At this point, if we run db.create_all(), SQLAlchemy will set up a blank database, but it has no idea what models to create because the module containing all of the models (in models.py) has not been loaded/imported, so we will also need to import our models.

>>> from yumroad.extensions import db
>>> from yumroad.models import Product
>>> db.create_all()

At this point, a file named dev.db will be created in the yumroad directory (as specified in our configuration) and it will have the schema for the Product. At this point, we are able to insert and query for Product records.

Inserting

#

As mentioned earlier our ORM, SQLAlchemy, allows us to interact with models like Python objects. This interface also applies to creating new records. To create a new record, we first need to initialize and instance of the model class we're creating. In this case, the class name is Product and the fields that we've defined for it that require manual input are name and description. The id field is inferred to automatically be filled in by the database because it's configured as a primary key which means that the database will automatically assign a unique ID to each record.

To instantiate a record, we treat our model like any other Python class and pass in the named arguments for the fields we've defined:

Product(name="My Art", description="A random photo from my portfolio")

This instantiation doesn't automatically create a record in the database however. SQLAlchemy provides us with the concept of a session, which is essentially a shopping cart for changes we'd like to make to the database. When we're done with all of our changes, we can "checkout" by committing our changes. This distinction is an optimization that ensures we only have to talk to the database (which can take some time and slow down our program) only when necessary.

The session is the place where our Python objects are stored until we commit our changes, at which point SQLAlchemy figures out and executes the queries to our database to implement our associated changes. Flask-SQLAlchemy provides us with a convenient way to access the current database session through the session attribute on our instance of SQLAlchemy (which we've imported as db)

>>> art = Product(name="My Art", description="A random photo from my portfolio")
>>> print(art.name)
My Art
>>> art.id == None
True
>>> db.session.add(art)
>>> db.session.commit()
>>> art.id == None
False
>>> print(art.id)
1

Querying

#

SQLAlchemy provides methods for us to make queries and takes care of translating it into SQL. The database session that SQLAlchemy manages is also our interface to query the database. We can pass in the model(s) that we want to query against and what kind of results we would like.

To query for all records from the Product model, we would pass in the model to the query method and ask SQLAlchemy to fetch all records using the all() record. This returns a list of Product objects corresponding to the actual records in the database.

>>> db.session.query(Product).all()
[<Product 1>]

This query is essentially translated to SELECT * from product.

You can see exactly what queries SQLAlchemy is issuing by setting the SQLALCHEMY_ECHO configuration variable Within yumroad/config.py, if you add a configuration for this to true under the DevConfig SQLALCHEMY_ECHO = True, SQLAlchemy will print out the exact query it is issuing.

Since many queries only operate on a single model, Flask-SQLAlchemy provides a shortcut to the query method by accessing it through the model class Product.query.

>>> Product.query.count()
1
>>> Product.query.all()
[<Product 1>]
>>> product = Product.query.all()[0]
'My Art'
>>> art.id, art.name
(1, 'My Art')
>>> Product.query.get(1)
<Product 1>
>>> Product.query.get(2) == None
True

Chaining and filtering Results

#

SQLAlchemy won't actually issue our query until we indicate we're ready to actually fire off the query to the database. Until then we will be working with Query objects, which store SQLAlchemy's internal representation of what kind of query should be created. When we are satisifed with the query that we have built, we can have SQLAlchemy fire it off to the database by using specific methods that actually will go and fetch the record(s) like all() or first().

When get the base query class with Product.query, we start off with a basic query that simply would fetch all records if asked to (which is what we saw when we ran Product.query.all())

We can constrain the Query object by using some of the methods that SQLAlchemy provides, such as filter_by. If we wanted to find all products with the name of My Art, we could create that query by running Product.query.filter_by(name='My Art').all().