Do loop in Postgresql Using Psycopg2 Python

In this article, we use psycopg2 to loop through all data points using psycopg2 function in Python. We will first connect our PostgreSQL database using psycopg2.connect method, and pass the connection parameters such as the host, database, user, and password. Then we will create a cursor using the conn.cursor method. The cur.execute method, passes in the SQL statement as a string. Commit the changes to the database using the conn.commit method, and Close the cursor and the connection using the cur.close and conn.close methods, respectively. Before starting let’s understand some of the terminologies that we will use in this article:

• psycopg2: A popular Python library that provides a simple way to interact with PostgreSQL databases from Python scripts.
• SQL: Structured Query Language, a standard language used to interact with relational databases.
• SELECT: A SQL statement used to retrieve data from a database table.
• INSERT: A SQL statement used to insert data into a database table.
• UPDATE: A SQL statement used to update existing data in a database table.

Note: To run this code you need to first create a table in your postgres named ‘mytable’.

Use a Loop to Insert Multiple Rows into a Table

Import the psycopg2 library. Connect to the database, In this section of code, you connect to a PostgreSQL database by creating a conn object and passing it several parameters such as:

• database: The name of the database you want to connect to.
• user: The username used to connect to the database.
• password: The password for the specified user.
• host: The hostname of the server where the database is located.
• port: The port number used to connect to the database.

Then Create a cursor, A cursor is a control structure used to traverse and fetch the data stored in a database. In this code, you create a cursor by calling the cursor method on the conn object. The data you want to insert into the database is defined as a list of tuples in the data variable. Each tuple represents a row of data, with the first element being the id and the second element being the name. The query used for insertion is:

INSERT INTO mytable (id, name) VALUES (%s, %s)"

After that use for loop to insert data, this section uses a for loop to insert each row of data into the database. You define an SQL query in the SQL variable, with placeholders %s for the values that you want to insert. You then use the execute method on the cursor cur object, passing in the SQL query and the current row of data as parameters. Once the data has been inserted into the database, you need to commit the changes to the database. You do this by calling the commit method on the conn object. Finally, you close the cursor by calling the close method on the cur object, and you close the connection by calling the close method on the conn object.

Python3

import psycopg2

# Connect to the database
conn = psycopg2.connect(
    database="postgres",
    user='postgres',
    password='123456789',
    host='localhost',
    port='5432'
)

# Create a cursor
cur = conn.cursor()

# Define the data to be inserted
data = [(1, 'John Doe'), (2, 'Jane Doe'), (3, 'Jim Doe')]

# Use a for loop to insert each row of data into the table
for row in data:
    sql = "INSERT INTO mytable (id, name) VALUES (%s, %s)"
    cur.execute(sql, row)

# Commit the changes to the database
conn.commit()
print(f"{data}\nData is successfully inserted")

# Close the cursor and the connection
cur.close()
conn.close()

Output:

Use a Loop to Fetch all the Rows in a Table

This code also uses a similar approach only the difference is we are displaying data from the table in the database using fetchall() function and looping. To select complete data from mytable we use this query: SELECT * FROM mytable. And to apply it on each row we use it for loop and print IDs and Names. And Commit the changes and Close the cursor with the connection. 

Python3

import psycopg2

# Connect to the database
conn = psycopg2.connect(
    database="postgres",
    user='postgres',
    password='123456789',
    host='localhost',
    port='5432'
)

# Create a cursor
cur = conn.cursor()

# Execute a SELECT statement to retrieve
# all the rows from the table
cur.execute("SELECT * FROM mytable")

# Fetch all the rows using a for loop
rows = cur.fetchall()
for row in rows:
    id, name = row
    print(f"ID: {id}, Name: {name}")

# Close the cursor and the connection
cur.close()
conn.close()

Output: