Let’s say one of our primary business concerns is user acquisition. But our user records live in an SQL database. We want to connect to this database, query it, and display this data in our dashboard.

We want to display data from our external database when our app is opened. Anvil apps are just Python, so we can use the standard Python tools to query our database!

In this case, our database is PostgreSQL, so we’ll use the Psycopg2 library.

Go to your Server Module, and add this line at the top:

import psycopg2

If you’re using the free plan, you’ll need to use psycopg2 from an Uplink script instead (here’s why).

Follow the next chapter to see how to make queries from an Uplink script, then come back here and follow this chapter using your Uplink script instead of a Server Module.

Now write a function in your Server Module to query the database, and return the results. Here’s an example query you might perform to track user acquisition, if your database has a users table with a signup_date field:

def get_user_signups():
  # Connect and retrieve data from your existing database - example code below
  conn = psycopg2.connect("host=db.myapp.com dbname=my_app user=postgres password=secret")
  cur = conn.cursor()
     SELECT COUNT(${html`*`}), DATE_TRUNC('week', signup_date) AS d
          FROM users
          WHERE signup_date > NOW() - INTERVAL '3 months'
          GROUP BY DATE_TRUNC('week', signup_date)
          ORDER BY d;
  return list(cur)

For more information on using SQL databases with Anvil, click here.

If you don’t have a database available, you can use some dummy data instead. Remove the import psycopg2 statement, and add this function to your Server Module instead:

def get_user_signups():
# Here's some dummy data that you might return from your database, as an example
  return [{'signups': 120, 'date':datetime(2019, 6, 10, 0, 0)}, 
          {'signups': 180, 'date':datetime(2019, 6, 3, 0, 0)}, 
          {'signups': 150, 'date':datetime(2019, 5, 27, 0, 0)}]

We’re using the datetime library to create datetime objects, so you’ll also need to import the datetime class at the top of your Server Module:

from datetime import datetime