Live Chat

We'll need to share your messages (and your email address if you're logged in) with our live chat provider, Drift. Here's their privacy policy.

If you don't want to do this, you can email us instead at contact@anvil.works.

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)

@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 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'),
                                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 data format is easy to use with Anvil’s Data Grids.)

Indexing and SQL CSV and Excel import

Jump To: