---
title: A Quick Start to Connecting to PostgreSQL and Pulling Data into Pandas
description: As a data scientist, you want your data in a data frame; here's how you can quickly pull PostgreSQL tables into Pandas so you can start building models.
image: https://dataknowsall.com/hubfs/postgres_workbench.png
---

[Skip to content](https://dataknowsall.com/blog/postgres.html#main-content)

[![Lambda with Text - Wide](https://dataknowsall.com/hs-fs/hubfs/Lambda%20with%20Text%20-%20Wide.png?width=3068&height=301&name=Lambda%20with%20Text%20-%20Wide.png)](https://dataknowsall.com)

- [Home](https://dataknowsall.com)
- [Blog](https://dataknowsall.com/blog/)
- [Services](https://dataknowsall.com/services)
- [Technologies](https://dataknowsall.com/technologies)

Open main navigation

Close main navigation

- [Home](https://dataknowsall.com)
- [Blog](https://dataknowsall.com/blog/)
- [Services](https://dataknowsall.com/services)
- [Technologies](https://dataknowsall.com/technologies)
- [Contact us](https://dataknowsall.com/contact)

[Contact us](https://dataknowsall.com/contact)

[All posts](https://dataknowsall.com/blog/all)

 March 5, 2024

# A Quick Start to Connecting to PostgreSQL and Pulling Data into Pandas

![Picture of Brian Roepke](https://app.hubspot.com/settings/avatar/67adab425dfba5d6fa7e242d82fbe2c4) By  [Brian Roepke](https://dataknowsall.com/blog/author/brian-roepke)  ·   2 minute read

## What is PostgreSQL?

[PostgreSQL](https://www.postgresql.org/) is a powerful **relational database management system** (RDBMS) that many organizations use. Connecting to it is easy, and thanks to the great Python ecosystem, getting your data into a Data Frame in Pandas is just as easy. Let's look at a simple example that will help you get started.

## Running PostgreSQL Locally

One simple way to run PostgreSQL locally is to use [Docker](https://www.docker.com/). If you don't have it installed, you can [download Docker here](https://www.docker.com/products/docker-desktop/). Once you have Docker installed, you can run the following command to start a PostgreSQL container.

Head to the [Docker Hub](https://hub.docker.com/) and search for `postgres`. You'll see several different images. We want the `postgres` image, and you can pull it down with the following command.

```
docker pull postgres
```

After that, you can run the following command (per the documentation on Docker Hub) to start a container.

```
docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=postgrespw -d postgres
```

You can connect to the DB using any supported SQL tool such as [SQL Workbench](https://www.sql-workbench.eu/index.html).

![postgres_workbench](https://dataknowsall.com/hs-fs/hubfs/postgres_workbench.png?width=800&height=489&name=postgres_workbench.png)

```
CREATE DATABASE MAIN;CREATE TABLE CONTACTS ( ID integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, FIRST_NAME varchar(255), LAST_NAME varchar(255));INSERT INTO CONTACTS (FIRST_NAME, LAST_NAME)VALUES ('Brian', 'Roepke');SELECT * FROM CONTACTS;
```

Running the above commands will create a database called `MAIN` and a table called `CONTACTS`. It will also insert a single row into the table and then select all the rows from the table; that should get you started with a simple PG setup locally.

## Imports

The first thing we'll do is import the libraries we will need. We will need the `psycopg2`library to connect to PostgreSQL and the `pandas` library to work with our data. in addition; we're going to use `pandas`, `os`, `urllib`, and `sqlalchemy` to help us connect to our database.

```
import pandas as pdimport psycopg2import osfrom sqlalchemy import create_enginefrom urllib.parse import quote_plus
```

Next, we want to ensure we're not hard-coding our sensitive information but rather pulling it from environment variables. If you'd like to learn more about this process, check out this [article](https://dataknowsall.com/blog/envvar.html).

```
user = os.environ.get("USER")pw = os.environ.get("PASS")db = os.environ.get("DB")host = os.environ.get("HOST")api = os.environ.get("API")port = 5432
```

Next, we need to ensure any information in our connection string is **properly URL encoded**, like the username and password, which can be easily achieved with the `quote_plus` function from `urllib`. Let's take a quick look at a sample password and how it is encoded.

```
fake_pw = "p@ssw0rd'9'!"print(quote_plus(fake_pw))
```

```
p%40ssw0rd%279%27%21
```

Next, we can create our **connection string** that contains all the information needed to connect to our database. We will use string format (`f` in front of the string) to insert variables. We will also use the `quote_plus` function to encode our username and password.

In addition to the connection string, we will also create an **SQLAlchemy engine**.

```

```

That's it! Assuming you didn't receive an error, you are now connected to your PostgreSQL database. Let's look at how we can pull data into a Pandas Data Frame.

First, we'll define a query in the form of a string. A best practice here is to use triple quotes (`"""`), allowing us to write our query over multiple lines as well as avoid conflicts when our query itself contains quotes, such as `WHERE FIRST_NAME = 'Brian'`.

```
q = """SELECT * FROM CONTACTS"""
```

Next, we will create the connection from the `alchemyEngine` we created earlier.

```
dbConnection = alchemyEngine.connect();
```

We will then use the `read_sql` function from `pandas` to pull our data into a Data Frame.

```
df = pd.read_sql(q, dbConnection);
```

Now that we have a data frame and can display the first few rows and see our data.

```
df.head()
```

A best practice is to close your connection when you're done.

```
dbConnection.close();
```

## Conclusion

PostgreSQL is a powerful relational database management system (RDBMS) used by many companies. We covered connecting to it and getting your data into a Pandas dataframe. We also discussed some best practices like storing credentials in Environment Variables and how to easily URL encode your username and password, which often will have characters that are not supported.

Share: [facebook-f icon](http://www.facebook.com/share.php?u=https://dataknowsall.com/blog/postgres.html) [linkedin-in icon](http://www.linkedin.com/shareArticle?mini=true&url=https://dataknowsall.com/blog/postgres.html) [twitter icon](https://twitter.com/intent/tweet?url=https://dataknowsall.com/blog/postgres.html) [pinterest-p icon](http://pinterest.com/pin/create/link/?url=https://dataknowsall.com/blog/postgres.html) [envelope icon](mailto:?body=https://dataknowsall.com/blog/postgres.html)

## Related posts

[![Mongo Free Tier](https://dataknowsall.com/hubfs/mongo_01.png)](https://dataknowsall.com/blog/mongo.html)

[Data Engineering](https://dataknowsall.com/blog/tag/data-engineering)

### [A Quick Start for Taking MongoDB Collections into Pandas DataFrames](https://dataknowsall.com/blog/mongo.html)

[![Prefect Flow](https://dataknowsall.com/hubfs/prefect_flowrun.png)](https://dataknowsall.com/blog/prefectintro.html)

[Data Engineering](https://dataknowsall.com/blog/tag/data-engineering)

### [Getting Started with Prefect: Powerful Orchestration for Your Data](https://dataknowsall.com/blog/prefectintro.html)

[![dbt](https://dataknowsall.com/hubfs/dbt_lineage.png)](https://dataknowsall.com/blog/dbt.html)

[Data Engineering](https://dataknowsall.com/blog/tag/data-engineering)

### [dbt: Codify and Automate Transformation of Data in Your Data Warehouse](https://dataknowsall.com/blog/dbt.html)

[![Lambda Google Workspace](https://dataknowsall.com/hs-fs/hubfs/Lambda%20Google%20Workspace.png?width=320&height=132&name=Lambda%20Google%20Workspace.png "Lambda Google Workspace")](https://www.dataknowsall.com)

Specialists in Data Engineering, Machine Learning, and Analytics Consultation

[linkedin-in icon](https://www.linkedin.com/in/broepke/) [twitter icon](https://twitter.com/broepke) [Follow us on Facebook](https://github.com/broepke)

**Pages**

- [Home](https://dataknowsall.com)
- [Blog](https://dataknowsall.com/blog/)
- [Services](https://dataknowsall.com/services)
- [Technologies](https://dataknowsall.com/technologies)

### Get Updates In Your Inbox

Copyright © 2026, Data Knows All

```json
{
  "@context" : "https://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Brian Roepke",
    "url" : "https://dataknowsall.com/blog/author/brian-roepke"
  },
  "dateModified" : "2024-03-05T13:18:42.565Z",
  "datePublished" : "2023-02-21T11:13:00.000Z",
  "headline" : "A Quick Start to Connecting to PostgreSQL and Pulling Data into Pandas",
  "image" : [ "https://dataknowsall.com/hubfs/postgres_workbench.png" ],
  "mainEntityOfPage" : {
    "@id" : "https://dataknowsall.com/blog/postgres.html",
    "@type" : "WebPage"
  },
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://dataknowsall.com/hubfs/Lambda%20Google%20Workspace.png"
    },
    "name" : "Data Knows All, LLC"
  }
}
```

```json
{
  "@context" : "http://schema.org",
  "@type" : "Article",
  "author" : {
    "@type" : "Person",
    "name" : [ "Brian Roepke" ],
    "url" : "https://dataknowsall.com/blog/author/brian-roepke"
  },
  "datePublished" : "2023-02-21T11:13:00+0000",
  "description" : "As a data scientist, you want your data in a data frame; here's how you can quickly pull PostgreSQL tables into Pandas so you can start building models.",
  "headline" : "A Quick Start to Connecting to PostgreSQL and Pulling Data into Pandas",
  "image" : "https://44115987.fs1.hubspotusercontent-na1.net/hubfs/44115987/postgres_workbench.png",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "url" : "https://44115987.fs1.hubspotusercontent-na1.net/hubfs/44115987/Lambda%20Google%20Workspace.png"
    },
    "name" : "Data Knows All"
  },
  "url" : "https://dataknowsall.com/blog/postgres.html"
}
```