It’s easy to access a PostgreSQL database from an Anvil app.
Because an Anvil app has a full Python instance in the back-end, you can use a Postgres driver such as
Psycopg2 to control your Postgres database, as you would in any Python environment.
Click here to open a full example app that uses a PostgreSQL database:
Here’s example code to query a Postgres database from an Anvil server function:
import psycopg2 import psycopg2.extras def connect(): connection = psycopg2.connect(dbname='mydatabase', host='db.example.com', user='postgres', password=anvil.secrets.get_secret('db_password'), cursorclass=psycopg2.extras.RealDictCursor) @anvil.server.callable def get_people(): conn = connect() with conn.cursor() as cur: cur.execute("SELECT name,date_of_birth,score FROM users") return cur.fetchall()
And here’s how you’d use it in your Forms to display data with a Data Grid:
class Form1(Form1Template): def __init__(self, **properties): # Set Form properties and Data Bindings. self.init_components(**properties) # Any code you write here will run when the form opens. self.repeating_panel_1.items = anvil.server.call('get_people')
(Note 1: This example uses the App Secrets service to store your database password encrypted, rather than leaving it “in the clear” in your source code.)
(Note 2: This example uses
RealDictCursor to return SELECT results as lists of dictionaries. This works super smoothly with Anvil’s Data Grids.)
Watch our video tutorial: Building a dashboard from a Postgres database.
No, you don’t!
Anvil also has a built-in database system called Data Tables (here’s a tutorial). Data Tables have visual designer for composing a schema, and Python methods for searching. Data Tables is backed by PostgreSQL, which provides robustness and scalability. On the Dedicated plan, Data Tables can be accessed in SQL, but most users prefer to use the Python API.