Python and PostgreSQL: Data Manipulation

August 22, 2022

Blogs, Computer programming, StatisticsNo Comments

• Using lower case in PostgreSQL as much as possible. Otherwise, you will need to add "" to everything in your SQL queries.

When importing the csv to SQL, you can use the following Python code to make all the headers lower cases:

data.columns = map(str.lower, data.columns)
#or
data.columns = [x.lower() for x in data.columns]
#or
df.columns = df.columns.str.lower()

Using Psycopg to acess the data

Import the Psycopg and sqlio libraries:

import psycopg2
import pandas.io.sql as sqlio

Connect to the database:

conn = psycopg2.connect("dbname=<database name> user=<user name> password=<access password>")
cur = conn.cursor()

Read the query result as dataframe:

SQL = "SELECT * FROM <table>;"
dat = sqlio.read_sql_query(SQL, conn)

Execute the query (e.g. create new table):

SQL = "CREATE TABLE <table name>(<column name> <column data type> <optional: restriction>);"
cur.execute(SQL)
conn.commit()

Clear the connection:

conn = None

Close communication with the database

cur.close()
conn.close()