How to Query Data and Insert Database Rows With SQLAlchemy
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
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.
create_all(), we will need to import
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.
How does SQLAlchemy figure out which tables to create?
When a class that inherits from the base SQLAlchemy model class is defined, SQLAlchemy registers it as a table that may need to be created. If the class is never evaluated (because no code imports the models), SQLAlchemy is unaware of the existence of those models.
Another solution to this issue would have been to import the models directly in the
yumroad/__init__.py file, since that file is loaded when we initialize the application
>>> 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.
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
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
>>> 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
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_ECHOconfiguration variable Within
yumroad/config.py, if you add a configuration for this to true under the
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.count() 1 >>> Product.query.all() [<Product 1>] >>> product = Product.query.all() 'My Art' >>> art.id, art.name (1, 'My Art') >>> Product.query.get(1) <Product 1> >>> Product.query.get(2) == None True
|Get a count of all records for this query|
|Get all records in this query|
|Get the record where the argument matches the primary key of the table|
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
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
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