Photo by Luca Bravo on Unsplash

PostgreSQL and pandas in Python

Photo by Luca Bravo on Unsplash

PostgreSQL and pandas in Python

How to write a pandas dataframe into PostgreSQL in Python

In this tutorial, you will learn how to write a pandas dataframe into your PostgreSQL database.

Prerequisites:

  • You need to have PostgreSQL installed on your machine

Installation of Python modules

First of all, we use Anaconda to install two Python SQL modules.

Psycopg

Psycopg is a PostgreSQL adapter for the Python programming language:

conda install -c anaconda psycopg2

SQLAlchemy

SQLAlchemy is a Python SQL toolkit:

conda install -c anaconda sqlalchemy

Database connection

To connect to your database, you need to start a PostgreSQL instance. Therefore, open Postgres on your machine and confirm that your database is running.

Now we import sqalchemy as sa and create an postgresql engine to make a connection to our database.

import sqlalchemy as sa

engine = sa.create_engine('postgresql://postgres:your_password@localhost:5438/postgres')

In this engine we need to provide the following information:

Replace your_username, your_password, your_port and your_database with your own values.

Here are some standard values for a common PostgreSQL installation:

  • your_username: postgres
  • your_port: 5432 or 5433 or 5438, … (you can find your port number in the settings of your PostgreSQL database)
  • your_database: postgres

To learn more about the engine configuration, visit the documentation.

Write pandas dataframe into PostgreSQL

Let’s create some data:

import pandas as pd

df = pd.read_csv("https://raw.githubusercontent.com/kirenz/datasets/master/gapminder.csv")

Now we use our engine to write the pandas data into our PostgreSQL database and call the table gapminder:

df.to_sql('gapminder', engine)

Show a list of all tables in the database:

engine.table_names()
['gapminder']
Avatar
Jan Kirenz
Professor

I’m a data scientist educator and consultant.

Related