How to insert data from pandas to PostgreSQL

When we have done our data analysis using pandas and now need to store this analysis, we can use to_csv option. However if data is too big, it make sense to store this in database.
Let us have a look at two simple methods to store data into PostgreSQL database.

Using sqlalchemy

import pandas as pd
from sqlalchemy import create_engine

df =pd.read_csv('input_file.csv',parse_dates=['timestamp'],index_col=0)
#df.info()

engine = create_engine('postgresql://admin:admin@localhost:5432/ibnse')
df.to_sql('stk_1min1', engine,if_exists='append')

Using psycopg2

pandas as pd
import psycopg2
conn = psycopg2.connect(database='dbname', user="user", password="password", host="127.0.0.1", port="5432")
cur = conn.cursor()

df =pd.read_csv('input_file.csv',parse_dates=['timestamp'],index_col=0)
for index, row in df.iterrows():
    insertdata =  "('"+str(index)+ "','"+row[0]+"','"+str(row[1])+"','"+str(row[2])+"','"+str(row[3])+"','"+str(row[4])+"','"+str(row[5])+"','"+str(row[6])+"','"+str(row[7])+"','"+str(row[8])+"')"
    print("insertdata :",insertdata)

    try:
        cur.execute("INSERT INTO stk_1min1 values "+insertdata)
        print( "row inserted:", insertdata)
    except psycopg2.IntegrityError:
        print( "Row already exist ")
        pass
    except Exception as e:
        print( "some insert error:", e, "ins: ", insertdata)
    conn.commit()

Few points while using sqlalchemy

• If table does not exists, it will get created.
• If table exists and you want to append, you need to use if_exists='append',  Its wise choice to use this option in most of the cases.
• When you use sqlalchemy, whole pandas dataframe will not be inserted even if you get unique index error for one single record.