World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Download our e-book of Introduction To Python
Related Blog
Top Discussion
Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? Couldn't able to import CSV file Define three individual functions to implement the filter, map and reduce functions. How to extracting text from PDF file using python How to leave/exit/deactivate a Python virtualenvironment Why is my f1_scores different when i calculate them manually vs output by sklearn.metrics How Python interpreter Works? Join Discussion
Top Courses
Master's In Artificial Intelligence Job Guarantee Program
4.5 (1,292 Ratings)
559 Learners
Webinars
Why You Should Learn Data Science in 2023?
Jun 8th (7:00 PM) 289 Registered
Python PostgreSQL - Insert Data
Neha Kumawat
2 years ago
Table of Contents
- Introduction
- Inserting Data Using Python
Introduction
You can insert record into an existing table in PostgreSQL using the INSERT INTO statement. While executing this, you need to specify the name of the table, and values for the columns in it.
Syntax
Following is the recommended syntax of the INSERT statement −
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
Where, column1, column2, column3,.. are the names of the columns of a table, and value1, value2, value3,... are the values you need to insert into the table.
Example
Assume we have created a table with name CRICKETERS using the CREATE TABLE statement as shown below −
postgres=# CREATE TABLE CRICKETERS (
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Age INT,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255)
);
CREATE TABLE
postgres=#
Following PostgreSQL statement inserts a row in the above created table −
postgres=# insert into CRICKETERS
(First_Name, Last_Name, Age, Place_Of_Birth, Country) values
('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=#
While inserting records using the INSERT INTO statement, if you skip any columns names Record will be inserted leaving empty spaces at columns which you have skipped.
postgres=# insert into CRICKETERS
(First_Name, Last_Name, Country) values('Jonathan', 'Trott', 'SouthAfrica');
INSERT 0 1
You can also insert records into a table without specifying the column names, if the order of values you pass is same as their respective column names in the table.
postgres=# insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1
postgres=#
After inserting the records into a table you can verify its contents using the SELECT statement as shown below −
postgres=# SELECT * from CRICKETERS;
first_name | last_name | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Shikhar | Dhawan | 33 | Delhi | India
Jonathan | Trott | | | SouthAfrica
Kumara | Sangakkara | 41 | Matale | Srilanka
Virat | Kohli | 30 | Delhi | India
Rohit | Sharma | 32 | Nagpur | India
(5 rows)
Inserting Data Using Python
The cursor class of psycopg2 provides a method with name execute() method. This method accepts the query as a parameter and executes it.
Therefore, to insert data into a table in PostgreSQL using python −
- Import psycopg2 package.
Import psycopg2 package.
- Create a connection object using the connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
Create a connection object using the connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
- Turn off the auto-commit mode by setting false as value to the attribute autocommit.
Turn off the auto-commit mode by setting false as value to the attribute autocommit.
- The cursor() method of the Connection class of the psycopg2 library returns a cursor object. Create a cursor object using this method.
The cursor() method of the Connection class of the psycopg2 library returns a cursor object. Create a cursor object using this method.
Enjoyed reading this blog? Then why not share it with others. Help us make this AI community stronger.
To learn more about such concepts related to Artificial Intelligence, visit our insideAIML blog page.
You can also ask direct queries related to Artificial Intelligence, Deep Learning, Data Science and Machine Learning on our live insideAIML discussion forum.
Keep Learning. Keep Growing.