By Chapter 1:
Query an external database with Python
In this chapter, we’ll connect to an external database and query it from the Anvil Editor.
Step 1: Create an external database
For this tutorial, we’ll use a temporary test Postgres database.
Hit Create test database below to set yours up:
This creates an instant database for you to use for this tutorial. Copy and save the database connection string as we’ll be using it later on to connect to our database.
claim url above.That’s the database part of the system already set up.
Optional: connect and take a look
If you have a postgres client installed, you can connect to your database and take a look at it.
For example, if you have psql you can connect using this command:
psql your_database_connection_stringYou’ll find that the database contains a table called inventory containing some items and quantities, such as what you might use to keep track of your inventory.
your_database_id=> select * from inventory;
id | name | quantity
----+----------------+----------
1 | Vase | 10
2 | Bookcase | 5
3 | Bathtowel | 20
4 | Frying Pan | 20
5 | Large Saucepan | 25
6 | Small Saucepan | 25
7 | Dinner Plate | 15
8 | Dining Chair | 10
(8 rows)If you don’t have a postgres client installed, don’t worry. You’ll connect to your database using Python in the next two steps.
Step 2: Create your Anvil App
Log in to Anvil and click ‘New Blank App’. Choose the Material Design theme.
We need our Anvil app to be able to communicate with the external database. To do that, we need to create a Server Module where all our server-side Python code will live.
Now that we have a Server Module, we need to install a library to connect to our Postgres database. The go-to Python library for this is psycopg2-binary, which allows you to run SQL queries using Python.
You can install Python packages in your Anvil apps. Select the gear icon from your Sidebar Menu and click on Python versions.
Search for the psycopg2-binary package and click Add.
Go back to your Server Module. At the top, import the psycopg2-binary package.
import psycopg2 as psycopg
import psycopg2.extrasStep 3: Query your database with Python
Now that we’ve installed the required packages, we need to connect to our external database and fetch items.
Grab your database connection string from earlier, then add the following code to your server module:
# Connect to PostgreSQL database
database_url = "<your_database_connection_string>"Then, we’ll create a context manager to manage our connections to the database:
from contextlib import contextmanager
@contextmanager
def db_connection():
conn = psycopg2.connect(database_url)
try:
yield conn
finally:
conn.close()The with db_connection() as conn: syntax is a context manager. It ensures that the database connection is always closed after we’re done with it, even if an error occurs.
You’ll see this pattern used throughout the tutorial wherever we connect to the database.
Let’s write a function to fetch all items from the inventory table. We select the id, name, and quantity columns, and order the results by id in descending order so that newly added items appear at the top of the table:
@anvil.server.callable
def get_items():
with db_connection() as conn:
cur = conn.cursor(cursor_factory=psycopg.extras.RealDictCursor)
cur.execute('SELECT id, name, quantity FROM inventory ORDER BY id DESC;')
return cur.fetchall()RealDictCursor is used to return SELECT results as a Python dictionary.We’re now successfully querying our external database using SQL from Python.
Great! We’ve got everything in place to build our app: we’ve got an external database and we’re querying it from within our Anvil app.
In the next chapter, we’ll display the database contents in a simple table.