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 and running 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=1.4.46

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']