Read and Write Tables From PostgreSQL With Python

How to read and write tables from PostgreSQL with Python.

  1. Install the default Python-PostgreSQL driver of SQLAlchemy:

    pip3 install psycopg2
    You can use another driver.

  2. Install the following packages:

    import os
import pandas as pd
from sqlalchemy import create_engine

  3. Connect to PostgreSQL by running the following lines of code:

    # ====== Connection to PostgreSQL ====== #
# To connect to PostgreSQL by providing an SQLAlchemy engine.
engine = create_engine('postgresql://'+os.environ['POSTGRESQL_USER']+':'+os.environ['POSTGRESQL_PASSWORD']+'@'+os.environ['POSTGRESQL_HOST_IP']+':'+os.environ['POSTGRESQL_PORT']+'/'+os.environ['POSTGRESQL_DATABASE'],echo=False)
    The PostgreSQL connection URL format must be postgresql://user:password@host_ip:port/database.

  4. You can now read and write tables from PostgreSQL by running the following lines of code:

    • Read Tables

    • Write Tables

    # ====== To Read Tables ====== #
# To read a PostgreSQL table in a pandas DataFrame.
data = pd.read_sql('SELECT * FROM helloworld', engine)
    # ====== To Write Tables ====== #
# To create a simple pandas DataFrame with two columns.
liste_hello = ['hello1','hello2']
liste_world = ['world1','world2']
df = pd.DataFrame(data = {'hello' : liste_hello, 'world': liste_world})

# To write Dataframe to PostgreSQL and replace table if it already exists.
df.to_sql(name='helloworld', con=engine, if_exists = 'replace', index=False)
See also