Migration of table from CSVto postgres using Python

Last Updated : 09 Aug, 2024

In this article, we will learn to read data from a CSV file and insert it into a PostgreSQL database table using Python.

Prerequisites

Python installed on your machine.
PostgreSQL server and database setup.
psycopg2 library to connect to PostgreSQL from Python.
pandas library to handle CSV data.

Let's install the necessary Python libraries:

pip install psycopg2 pandas

Reading Data from CSV

Let's say we have a file called data.csv

name,age,gender
alice,21,female
abul,23,male
pranjali,22,female
priyanka,24,female
suraj,25,male

Now, we will read the data from the CSV file using the pandas library.

import pandas

file = 'data.csv'
data = pandas.read_csv(file)
print(data.head())

Output:

Setting Up PostgreSQL Connection

Now, set up the connection to PostgreSQL database using the psycopg2 library.

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="gfgdb",
    user="postgres",
    password="1234"
)
cursor = conn.cursor()

Make sure to use your own username and password.

Creating a Table in PostgreSQL

Now, the next step is to create a table in the PostgreSQL database to store the CSV data. The table must have all the columns in the CSV data. In this case, our table will have three columns name, age, and gender.

query = '''
CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    gender VARCHAR(10)
);
'''

cursor.execute(query)
conn.commit()

Inserting Data into PostgreSQL

Inserting the data from the CSV file into the PostgreSQL table.

query = '''
INSERT INTO employee(name, age, gender)
VALUES (%s, %s, %s);
'''

for _, row in data.iterrows():
    cursor.execute(query, tuple(row))

conn.commit()

Complete Example:

import pandas, psycopg2

# Step 1: Setting Up PostgreSQL Connection
conn = psycopg2.connect(
    host="localhost",
    database="gfgdb",
    user="postgres",
    password="1234"
)
cursor = conn.cursor()

# Step 2: Reading Data from CSV
file = 'data.csv'
data = pandas.read_csv(file)
print(data.head())

# Step 3: Creating a employee Table in PostgreSQL
query = '''
CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    gender VARCHAR(10)
);
'''
cursor.execute(query)
conn.commit()

# Step 4: Inserting Data into PostgreSQL
query = '''
INSERT INTO employee(name, age, gender)
VALUES (%s, %s, %s);
'''

for _, row in data.iterrows():
    cursor.execute(query, tuple(row))
conn.commit()

# Step 5: Verifying Data Migration
query = "SELECT * FROM employee;"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
    print(row)

# Closing the connection
cursor.close()
conn.close()

Verifying Data Migration

query = "SELECT * FROM employee;"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
    print(row)

Output:

By following the above steps, we can easily read data from our csv file and add to our postgresql database.

What is the Equivalent of DataFrame.drop_duplicates() from Pandas in Polars?

abulhax

Improve

Article Tags :

Practice Tags :

python