Skip to content

PostgreSQL

Python

In the example below, we show how to set up a connection to a PostgreSQL data source using Python. We demonstrate how you can read data from a PostgreSQL table to a Pandas DataFrame, and vice versa. To do this, we need Python 3.6 or newer (how to install Python). In a virtual environment, you should install:

We recommend using a requirements.txt file. In this file, you can add:

psycopg2-binary  # tested with version 2.9.1
SQLAlchemy  # tested with version 1.4.21
pandas  # tested with version 1.3.0
python-dotenv  # tested with version 0.18.0

Read from PostgreSQL table

In the following example code, we first set up a connection to PostgreSQL using SQLAlchemy. With pandas, we read a table from PostgreSQL to a Pandas DataFrame.

import os
from sqlalchemy import create_engine
import pandas as pd

# More about dotenv in the section dotenv
from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

engine = create_engine(
    "postgresql://{user}:{password}@{host}:{port}/{database}".format(
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        host=os.getenv("DB_HOST"),
        port=os.getenv("DB_PORT"),
        database=os.getenv("DB_DATABASE"),
    )
)

df = pd.read_sql_table(table_name, engine)

Info

For Python, there are many packages that you can use to set up a connection to PostgreSQL. The example we show here is to demonstrate a method you could use with Pandas and how you could apply it in AskAnna. The AskAnna platform is flexible. For example, if you don't use Pandas you could setup the connection directly using psycopg2. With Askanna you could use other packages to set up a connection as well.

Project variables

To run the above example as a job in AskAnna, you should add project variables. On the project page, go to the tab variables. Here you can create new variables. To run the above example, you should add variables with names:

  1. DB_HOST
  2. DB_PORT
  3. DB_USER
  4. DB_PASSWORD
  5. DB_DATABASE

Warning

Make sure that at least the variable DB_PASSWORD is set to masked. You don't want to expose this value.

dotenv

In the above example, we used:

from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

These two lines make it a lot easier to develop your Python code locally, while you can also run the same code in AskAnna. When you add project variables, these variables will become available as environment variables in the run environment. Locally, you can add a file .env and when you run the Python code locally, the environment variables are loaded from this file. Read more about this on the project page python-dotenv.

To run the above example, you need a .env file with:

DB_HOST={host}
DB_PORT={port}
DB_USER={user}
DB_PASSWORD={password}
DB_DATABASE={database}

Write to PostgreSQL table

We reuse almost to full setup as with reading the data from PostgreSQL. Instead of reading data, we first create a Pandas DataFrame and then write the data to PostgreSQL.

import os
from sqlalchemy import create_engine
import pandas as pd

# More about dotenv in the section dotenv
from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

engine = create_engine(
    "postgresql://{user}:{password}@{host}:{port}/{database}".format(
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        host=os.getenv("DB_HOST"),
        port=os.getenv("DB_PORT"),
        database=os.getenv("DB_DATABASE"),
    )
)

df = pd.DataFrame({'example' : ['value 1', 'value 2', 'value 3']})
df.to_sql(table_name, engine)