Overview:
- The INSERT statement is one of the essential DML statements available as per SQL standards in any Relational Database Management System.
- Insert is one of the CRUD operations - Create/Read/Update and Delete.
- PostgreSQL is a popular open-source Relational Database Management System.
- Once a table is created on an existing PostgreSQL database, any PostgreSQL user with required privileges can add rows to the table using insert statement.
- After one or more rows are added using INSERT statement, the rows of the PostgreSQL table can be queried using the SELECT statement.
Inserting rows into a PostgreSQL table using Python:
- Psycopg2 is the Python client for the PostgreSQL Relational Database Management System.
- The Python example program given below connects to a PostgreSQL database and inserts two rows of data into a table.
- Through the connect() function of Psycopg2 module, a connection to a PostgreSQL database can be obtained.
- A cursor object is obtained from the connection and is used to execute the INSERT statement.
- After inserting one or more rows into the PostgreSQL table, a SELECT statement is executed using the database cursor, followed by calling one of the fetch() methods.
Example:
# ----- Example Python program to insert data into a PostgreSQL database table import psycopg2
# Open a DB session dbSession = psycopg2.connect("dbname='test' user='test' password='test'");
# Open a database cursor dbCursor = dbSession.cursor();
# SQL statement to create a table sqlCreateTable = "CREATE TABLE Cities(id bigint, cityname varchar(128), latitude numeric, longitude numeric);";
# Execute CREATE TABLE command dbCursor.execute(sqlCreateTable);
# Insert statements sqlInsertRow1 = "INSERT INTO Cities values(1, 'New York City', 40.73, -73.93)";
sqlInsertRow2 = "INSERT INTO Cities values(2, 'San Francisco', 37.733, -122.446)";
# Insert statement dbCursor.execute(sqlInsertRow1); dbCursor.execute(sqlInsertRow2);
# Select statement sqlSelect = "select * from test.Cities"; dbCursor.execute(sqlSelect); rows = dbCursor.fetchall();
# Print rows for row in rows: print(row); # It is essential to commit the transaction else it will be rollbacked dbSession.commit(); # Close the session and free up the resources |
Output:
(1, 'New York City', Decimal('40.73'), Decimal('-73.93')) (2, 'San Francisco', Decimal('37.733'), Decimal('-122.446')) |