Docs

Accessing an external database

Tutorial: Using an external database with Anvil

Because Anvil apps have a full Python instance in the back-end, you can use a database driver to access an external database from your Server Modules.

MySQL

You can use a MySQL driver such as Pymysql to control your MySQL database server, as you would in any Python environment.

Here’s an example of querying a MySQL database from an Anvil server function:

import pymysql

def connect():
  connection = pymysql.connect(host='db.example.com',
                               port=3306,
                               user='root',
                               password=anvil.secrets.get_secret('db_password'),
                               cursorclass=pymysql.cursors.DictCursor)
  return connection

@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 before 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 DictCursor to return SELECT results as lists of dictionaries. This data format is easy to use with Anvil’s Data Grids.)

PostgreSQL

You can use a Postgres driver such as Psycopg2 from a Server Module to control your Postgres database, as you would in any Python environment.

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'))
  return connection

@anvil.server.callable
def get_people():
  conn = connect()
  with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) 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 before 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 data format is easy to use with Anvil’s Data Grids.)

Do you still have questions?

Our Community Forum is full of helpful information and Anvil experts.

CSV and Excel import

On This Page: