Skip to main content
Version: 0.14.13

How to connect to a PostgreSQL database

This guide will help you connect to data in a PostgreSQL database. This will allow you to ValidateThe act of applying an Expectation Suite to a Batch. and explore your data.

Prerequisites: This how-to guide assumes you have:
  • Completed the Getting Started Tutorial
  • Have a working installation of Great Expectations
  • Have access to data in a Postgres database

Steps​

1. Choose how to run the code in this guide​

Get an environment to run the code in this guide. Please choose an option below.

If you use the Great Expectations CLICommand Line Interface, run this command to automatically generate a pre-configured Jupyter Notebook. Then you can follow along in the YAML-based workflow below:

great_expectations datasource new

2. Install required dependencies​

First, install the necessary dependencies for Great Expectations to connect to your Postgres database by running the following in your terminal:

pip install sqlalchemy psycopg2

3. Add credentials​

Great Expectations provides multiple methods of using credentials for accessing databases. Options include using a file not checked into source control, environment variables, and using a cloud secret manager. Please read the article How to Configure Credentials for instructions on alternatives.

For this guide we will use a connection_string like this:

postgresql+psycopg2://<USERNAME>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>

4. Instantiate your project's DataContext​

Import these necessary packages and modules.


Load your DataContext into memory using the get_context() method.


5. Configure your Datasource​

Put your connection string in this template:


Run this code to test your configuration.


You will see your database tables listed as Available data_asset_names in the output of test_yaml_config().

Feel free to adjust your configuration and re-run test_yaml_config() as needed.

6. Save the Datasource configuration to your DataContext​

Save the configuration into your DataContext by using the add_datasource() function.


7. Test your new Datasource​

Verify your new DatasourceProvides a standard API for accessing and interacting with data from a wide variety of source systems. by loading data from it into a ValidatorUsed to run an Expectation Suite against data. using a BatchRequest.

Here is an example of loading data by specifying a SQL query.


🚀🚀 Congratulations! 🚀🚀 You successfully connected Great Expectations with your data.

Additional Notes​

To view the full scripts used in this page, see them on GitHub:

Next Steps​

Now that you've connected to your data, you'll want to work on these core skills: