[psycopg2] Executing Postgres query in Python script

1. Workflow of executing query in Python script

psycopg2 is a package for using Postgres in Python script. In the same way as sqlite3, a conn instance and a cursor instance are created to create a query.

 

In Postgres, we have transaction notation when we commit the queries to sever. A new transcation will automatically be created when we open a connection in psycopg2. When a commit is called, the Postgres engine will run all the queries at once.

 

import psycopg2
conn = psycopg2.connect("dbname=db user=ur")
cursor = conn.cursor()

query = "SELECT * FROM table;"
cursor.execute(query) 
users = cursor.fetchall()

conn.close()

 

2. Loading a file on Postgres database 

Step 1 : Create a connection and cursor object

import psycopg2
conn = psycopg2.connect("dbname=db user=ur")
cursor = conn.cursor()

 

Step 2 : Create a table on database

query = """
CREATE TABLE table (
    id integer PRIMARY KEY,
    col1 text,
    col2 text,
    col3 text
);
"""
cursor.execute(query)

 

Step 3 : Insert values on database

An INSERT INTO clause and indicator(%s) can be used to create external data in the Postgres database internal table for easy work. The cursor.execute() method receives a set of data for processing as an indicator in the second argument.

 

# If file has a header row. 
with open('file.csv', 'r') as file: 
    next(file) 
    reader = csv.reader(file) 
    for row in reader: 
        cursor.execute("INSERT INTO table VALUES (%s, %s, %s, ...)", row)

 

The data value, which is the second element inside the cur.execute, must be entered separately by tuple or list.

 

Step 4 : Commit and close the connection

conn.commit()
conn.close()