Pushing Data onto PostgreSQL database using pandas library function(to_sql)

Here problem statement is to push data on to PostgreSQL database

So there is direct method to dump .csv file onto PostgreSQL database.(Assuming your data is clean and ready to be posted on to database table that is according to your desired schema)

so here I am taking an example of person table that has following schema

CREATE TABLE persons ( id serial NOT NULL, first_name character varying(75), last_name character varying(75), dob date, email character varying(500), CONSTRAINT persons_pkey PRIMARY KEY (id) )

so here you just need to create CSV for the same.

first_name,last_name,dob,email
John,Smith,02-05-1983,jsmith@gmail.com
Carl,Brown,06-04-1959,cbrown06@gmail.com

given data is to be saved into .csv file and can be dumped into PostgreSQL with following method

COPY persons(first_name,last_name,dob,email) FROM '~/file_path/persons.csv' DELIMITER ',' CSV HEADER;
here is one method to dump .csv file onto PostgreSQL database.

Another method is where you can dump pandas DataFrame directly to PostgreSQL database using pandas library function called “to_sql”.

Objective Pushing data file to PostgreSQL using Pandas DataFrame

import pandas as pd df_to_insert = pd.read_csv("persons.csv")

prerequsites for data pushing with pandas to_sql function

sqlalchemy and psycopg2 should be installed using pip install sqlalchemy and sudo apt-get install python-psycopg2 (Note: use sudo apt-get update before firing command)
database should be created before inserting any data.

Create Engine for PostgreSQL

from sqlalchemy import create_engine engine = create_engine('postgresql://postgres:0000@localhost:5432/demo_db')
URL_FORMAT = dialect+driver://username:password@host:port/database
psycopg2 can be used as driver

df_to_insert.to_sql("persons",con=engine,if_exists='replace')

This method is hassle less because you don’t have to create table and define schema ‘to_sql’ function does that for you.