Selecting data from a Table with SQLAlchemy
Excellent work so far! It's now time to build your first select statement using SQLAlchemy. SQLAlchemy provides a nice "Pythonic" way of interacting with databases. When you used raw SQL in the last exercise, you queried the database directly. When using SQLAlchemy, you will go through a Table
object instead, and SQLAlchemy will take case of translating your query to an appropriate SQL statement for you. So rather than dealing with the differences between specific dialects of traditional SQL such as MySQL or PostgreSQL, you can leverage the Pythonic framework of SQLAlchemy to streamline your workflow and more efficiently query your data. For this reason, it is worth learning even if you may already be familiar with traditional SQL.
In this exercise, you'll once again build a statement to query all records from the census
table. This time, however, you'll make use of the select()
function of the sqlalchemy
module. This function requires a list of tables or columns as the only required argument: for example, select([my_table])
.
You will also fetch only a few records of the ResultProxy by using .fetchmany()
with a size
argument specifying the number of records to fetch.
Table
and MetaData
have already been imported. The metadata is available as metadata
and the connection to the database as connection
.
This exercise is part of the course
Introduction to Databases in Python
Exercise instructions
- Import
select
from thesqlalchemy
module. - Reflect the
census
table. This code is already written for you. - Create a query using the
select()
function to retrieve all the records in thecensus
table. To do so, pass a list toselect()
containing a single element:census
. - Print
stmt
to see the actual SQL query being created. This code has been written for you. - Fetch 10 records from the
census
table and store then inresults
. To do this:- Use the
.execute()
method onconnection
withstmt
as the argument to retrieve the ResultProxy. - Use
.fetchmany()
with the appropriatesize
argument onconnection.execute(stmt)
to retrieve the ResultSet.
- Use the
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Import select
from ____ import ____
# Reflect census table via engine: census
census = Table('census', metadata, autoload=True, autoload_with=engine)
# Build select statement for census table: stmt
stmt = ____
# Print the emitted statement to see the SQL string
print(stmt)
# Execute the statement on connection and fetch 10 records: result
results = ____.____(____).____(size=___)
# Execute the statement and print the results
print(results)