By Chapter 1:
Query an external database from Python
We’ll connect an Anvil web app to an external database via a Python script.
We’re connecting an Anvil app to an external database via an Uplink script.
In this chapter, we’ll spin up a test database and query it from Python.
Step 1: Create an external database
For this tutorial, we’ll use a test Postgres database hosted by the Anvil team.
Hit ‘Create test database’ below to set yours up:
This creates a user and database in our server for you to use for this tutorial. The three-word ID shown above is used for both the username and database name.
It’s hosted at 18.133.244.120.
Make a note of your ID and password, as you’ll need it later.
Wherever this tutorial uses ‘your_database_id’, replace it with your own ID.
That’s the database part of the system already set up.
We’ve set up the database, ready to accept connections.
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 -h 18.133.244.120 -U your_database_idYou’ll find that the database contains a table called inventory containing some items and quantities, such as you
might use in a stock auditing app.
your_database_id=> select * from inventory;
id | item_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: Install psycopg2
The go-to Python library for connecting to Postgres is psycopg2. It allows you to connect your Python script to
your database and run SQL queries from Python.
Install it by running pip in your terminal:
pip install psycopg2You could also use an Object-Relational Mapper (ORM) such as SQLAlchemy to run your queries.
An ORM allows you to interact with your database using Python objects rather than raw SQL code.
Step 3: Query your database from Python
Let’s create a simple Python script that uses psycopg2 to query your database. Create a file called inventory.py on your own machine and open it in your editor.
First, import psycopg2:
# In a new file, inventory.py
import psycopg2Then create a connection to your database:
# Connect to PostgreSQL database
conn = psycopg2.connect(
host="18.133.244.120",
user="your_database_id",
password="your_database_password")
# Automatically commit transactions
conn.set_session(autocommit=True)The final line instructs Psycopg2 to automatically commit any transactions - in other words, queries you make take permanent effect in the database by default.
Then we need to get a cursor. This is a Python object with methods to run queries and iterate over the results:
# Create a cursor object
cur = conn.cursor()Now we can write a function to get some of the items from the inventory table:
def get_items():
# Run a query
cur.execute('SELECT id, item_name, quantity FROM inventory;')
# Get all the rows for that query
items = cur.fetchall()
# Convert the result into a list of dictionaries (useful later)
return [
{'id': item[0], 'name': item[1], 'quantity': item[2]}
for item in items
]Now add a print statement at the end of the script so you can inspect the result:
print(get_items())Run this from your terminal using python inventory.py - you’ll see your database table printed as a list of dictionaries!
[
{'id': 1, 'name': 'Vase', 'quantity': 10},
{'id': 2, 'name': 'Bookcase', 'quantity': 5},
{'id': 3, 'name': 'Bathtowel', 'quantity': 20},
{'id': 4, 'name': 'Frying Pan', 'quantity': 20},
{'id': 5, 'name': 'Large Saucepan', 'quantity': 25},
{'id': 6, 'name': 'Small Saucepan', 'quantity': 25},
{'id': 7, 'name': 'Dinner Plate', 'quantity': 15},
{'id': 8, 'name': 'Dining Chair', 'quantity': 10}
]The full script looks like this:
import psycopg2
conn = psycopg2.connect(
host="18.133.244.120",
user="your_database_id",
password="your_database_password")
conn.set_session(autocommit=True)
cur = conn.cursor()
def get_items():
cur.execute('SELECT id, item_name, quantity FROM inventory;')
items = cur.fetchall()
return [
{'id': item[0], 'name': item[1], 'quantity': item[2]}
for item in items
]
print(get_items())We’re now successfully querying our external database using SQL from Python.
We’ve set up the database and made SQL queries from Python.
Great! We’ve got everything in place to build our app: we’ve got an external database and we’ve written a Python script to connect to it.
In the next chapter, we’ll connect Anvil to our script and display the database contents in a simple table.