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 if needed.
  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 create tables in PostgreSQL, as well as query tables from PostgreSQL by running the following lines of code:

    • Create Tables in PostgreSQL

    • Query Tables From PostgreSQL

    # ====== Writing 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)
    # ====== Reading Tables ====== #
    # To read a PostgreSQL table in a pandas DataFrame.
    data = pd.read_sql('SELECT * FROM helloworld', engine)