How to import and export data using CSV files in PostgreSQL
Last Updated :
23 Sep, 2021
In this article, we are going to see how to import and export data using CSV file in PostgreSQL, the data in CSV files can be easily imported and exported using PostgreSQL.
To create a CSV file, open any text editor (notepad, vim, atom). Write the column names in the first line. Add row values separated by commas to the corresponding columns in the next lines. Save the file as Demo_data.csv as shown below.
Now create a new table using psycopg2 where we will import the data from the CSV file:
Python3
import psycopg2
# connection establishment
conn = psycopg2.connect(
database="postgres",
user='postgres',
password='password',
host='localhost',
port='5432'
)
conn.autocommit = True
# Creating a cursor object
cursor = conn.cursor()
# query to create a table
sql = ''' CREATE TABLE demo
(
id INT,
name VARCHAR(50),
city VARCHAR(50),
age INT
); '''
# executing above query
cursor.execute(sql)
print("Table has been created successfully!!")
# Closing the connection
conn.close()
Output:
Table has been created successfully!!
Importing data using a CSV file
We use COPY command with FROM keyword to import the contents of the CSV file into a new table.
Syntax: COPY <table name> FROM 'location + file_name' DELIMITER ',' CSV HEADER;
Where:
- <table name> – name of the table where we want to import data.
- ‘location + file_name’ – full path of the CSV file from which we are importing data (make sure you have 'read' access to the file).
- DELIMITER ‘,’ – specifies the delimiter which is comma (,)
- CSV – specifies the format of the file from which we are importing data.
- HEADER – specifies that we have a header row in our .csv file and while importing we should ignore the first row.
Example:
Python3
import psycopg2
# connection establishment
conn = psycopg2.connect(
database="postgres",
user='postgres',
password='password',
host='localhost',
port='5432'
)
conn.autocommit = True
# Creating a cursor object
cursor = conn.cursor()
# query to import data from given csv
sql = '''COPY demo FROM
'C:\\Users\\DELL\\Downloads\\Demo_data.csv'
DELIMITER ',' CSV HEADER'''
# executing above query
cursor.execute(sql)
# Display the table
cursor.execute('SELECT * FROM demo')
print(cursor.fetchall())
# Closing the connection
conn.close()
Output:
[(1, 'Harry', 'Delhi', 29), (2, 'Mira', 'Mumbai', 24), (3, 'Emma', 'Bangalore', 30), (4, 'Kevin', 'Mumbai', 19)]
Exporting data using CSV file
To export data from a table into a CSV file, we use TO keyword with the COPY command.Â
Syntax: COPY <table name> TO 'location + file_name' DELIMITER ',' CSV HEADER;
Here, <table name> is the table from which we are exporting data.
'location + file_name' contains the path of the CSV file into which we want to export the data. Make sure you have 'write' access to the file.
Example:
Python3
import psycopg2
# connection establishment
conn = psycopg2.connect(
database="postgres",
user='postgres',
password='password',
host='localhost',
port='5432'
)
conn.autocommit = True
# Creating a cursor object
cursor = conn.cursor()
# query to export table into csv
sql = '''COPY demo TO
'C:\\Users\\DELL\\Downloads\\Exported_data.csv'
DELIMITER ',' CSV HEADER'''
# executing above query
cursor.execute(sql)
# Closing the connection
conn.close()
The exported CSV will look like this:
Similar Reads
How to import CSV file in SQLite database using Python ? In this article, we'll learn how to import data from a CSV file and store it in a table in the SQLite database using Python. You can download the CSV file from here which contains sample data on the name and age of a few students. Contents of the CSV file Approach: Importing necessary modulesRead da
2 min read
How to Import a CSV file into a SQLite database Table using Python? In this article, we are going to discuss how to import a CSV file content into an SQLite database table using Python. Approach:At first, we import csv module (to work with csv file) and sqlite3 module (to populate the database table).Then we connect to our geeks database using the sqlite3.connect()
3 min read
How to Export SQL Server Data to a CSV File? Here we will see, how to export SQL Server Data to CSV file by using the 'Import and Export wizard' of SQL Server Management Studio (SSMS). CSV (Comma-separated values): It is a file that consists of plain text data in which data is separated using comma(,). It is also known as Comma Delimited Files
2 min read
How to Import and Export SQL Server Data to an Excel File? SQL Server is very popular in Relational Database and it is used across many software industries. Portability of data is a much-required feature of any database. i.e. Database should support features like exporting database data to Excel/CSV/JSON and also should import data from them. In this articl
3 min read
How to Import Data From a CSV File in MySQL? Importing data from a CSV (Comma-Separated Values) file into a MySQL database is a common task for data migration and loading purposes. CSV files are widely used for storing and exchanging tabular data. However, we cannot run SQL queries on such CSV data so we must convert it to structured tables. I
10 min read
How To Export and Import a .SQL File From Command Line With Options? Structured Query Language is a computer language that we use to interact with a relational database.SQL is a tool for organizing, managing, and retrieving archived data from a computer database. In this article , we will learn to export and import .SQL files with command line options. Export:You can
2 min read
How to Import and Export SQL Server Database? Creating and managing a SQL Server database is an essential skill for database administrators and developers. In this article, We will go through the process of setting up a database in SQL Server, from creating the database and tables to inserting records, and finally, exporting and importing the d
3 min read
How to Export Data to the .CSV File Using SQL Server Stored Procedure? Exporting data from SQL Server to a CSV file is a common task when handling large datasets or sharing data with other applications. SQL Server Management Studio (SSMS) provides a straightforward way to export tables using its Import and Export Wizard. In this article, we will see, the process of exp
3 min read
How to Extract and Import file in Django In Django, extracting and importing files is a common requirement for various applications. Whether it's handling user-uploaded files, importing data from external sources, or processing files within your application, Django provides robust tools and libraries to facilitate these tasks. This article
4 min read
How to export Pandas DataFrame to a CSV file? Let us see how to export a Pandas DataFrame to a CSV file. We will be using the to_csv() function to save a DataFrame as a CSV file. DataFrame.to_csv() Syntax : to_csv(parameters) Parameters : path_or_buf : File path or object, if None is provided the result is returned as a string. sep : String of
3 min read