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()

We’re now successfully querying our external database using SQL from Python.