Python - Read & Write tables from PostgreSQL with Security

Owned by Sébastien Collet (Unlicensed)
Oct 12, 2017
1 min read

Gist Page : example-python-read-and-write-from-postgresql

Common part

Requirements

Install sqlalchemy default Python-PostgreSQL driver : psycopg2

It's possible to use another driver if needed

pip3 install psycopg2

Libraries dependency

import pandas as pd
from sqlalchemy import create_engine

PostgreSQL Connection

SQLAlchemy PostgreSQL URI with default driver looks like this : 'postgresql://user:password@host_ip:port/database'

Connection

# ====== Connection ======
# Connecting to PostgreSQL by providing a sqlachemy 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)

How to create a table in PostgreSQL with Python ?

Code example

# ====== Writing table ====== #
# Creating 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})

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

How to query a table from PostgreSQL with Python ?

Code example

# ====== Reading table ======
# Reading PostgreSQL table into a pandas DataFrame
data = pd.read_sql('SELECT * FROM helloworld', engine)