Performing Insert operation in PostgreSQL database using Python
- The syntax for inserting records in a table is similar to that of the syntax used in conventionally SQL statements. Establish a connection with the PostgreSQL database as discussed above.
- Now, instantiate a new cursor() object. A cursor is a read-only pointer that allows a program, to access the result set of a query.
- Then, pass the query for inserting table records to the execute() function of psycopg2.
- Finally call the commit() method, indicating the connector object which was created to establish a connection with the database to commit all changes to the database. This will ensure that the records are inserted.
Python3
import psycopg2 try : connection = psycopg2.connect(user = "username" , password = "password" , host = "127.0.0.1" , port = "5432" , database = "databasename" ) cursor = connection.cursor() postgres_insert_query = """ INSERT INTO publisher(publisher_id, publisher_name, publisher_estd, publsiher_location, publsiher_type) VALUES (%s,%s,%s,%s,%s)""" record_to_insert = [( 1 , 'Packt' , 1950 , 'chennai' , 'books' ), ( 2 , 'Springer' , 1950 , 'chennai' , 'books' ), ( 3 , 'Springer' , 1950 , 'chennai' , 'articles' ), ( 4 , 'Oxford' , 1950 , 'chennai' , 'all' ), ( 5 , 'MIT' , 1950 , 'chennai' , 'books' )] for i in record_to_insert: cursor.execute(postgres_insert_query, i) connection.commit() count = cursor.rowcount print (count, "Record inserted successfully \ into publisher table") except (Exception, psycopg2.Error) as error: print ( "Failed to insert record into publisher table" , error) finally : # closing database connection. if connection: cursor.close() connection.close() print ( "PostgreSQL connection is closed" ) |
Output:
1 Record inserted successfully into publisher table
PostgreSQL connection is closed
Perform PostgreSQL CRUD operations from Python
The DDL is comprised of the Create, Read, Update, Delete (CRUD) operations which form the backbone of any SQL database system. Let us discuss how to perform CRUD operations on a PostgreSQL database using python. Pyscopg2 is the most preferred mode and is widely used to connect the PostgreSQL database using python. Pyscopg2 is a Database API which is a PostgreSQL compatible driver that manages connection pools.
In this article, we will learn how to connect to the PostgreSQL database using SQLAlchemy in python and also understood the procedure on how to perform CRUD operations against the PostgreSQL database.
Contact Us