Dynamically Update Multiple Rows with PostgreSQL and Python

In this tutorial, we will explore how to dynamically update multiple rows in a PostgreSQL database using Python. By the end of this tutorial, you’ll have a solid understanding of how to write efficient Python code that interacts with PostgreSQL to update multiple rows in a database. We’ll cover topics such as connecting to a PostgreSQL database, executing SQL queries, and updating multiple rows using parameterized queries. So, whether you’re a seasoned Python developer or just starting out, this tutorial will help you improve your skills and make you more productive when working with large datasets in PostgreSQL.

We can use the pyscopg2 library in Python for PostgreSQL.

Concept :

psycopg2 is a widely-used database adapter for PostgreSQL that allows Python programs to interact with PostgreSQL databases. This library offers a range of features, including.

• connection management 
• SQL query execution 
• parameter binding 
• transaction management 
• error handling
• object-relational mapping.

With psycopg2, Python developers can easily connect to a PostgreSQL database by specifying the necessary parameters like 

• hostname
• port number
• database name 
• username
• password. 

Once connected, they can execute SQL queries using the execute() method. The library also provides support for parameter binding, which helps prevent SQL injection attacks and makes it easier to write dynamic SQL queries.

psycopg2 offers robust transaction management functions, allowing developers to commit or roll back transactions using the commit() and rollback() methods of the connection object. Additionally, it includes an error-handling mechanism that allows developers to handle errors easily.

Finally, psycopg2 also supports object-relational mapping (ORM) frameworks like SQLAlchemy and Django ORM. Overall, psycopg2 is a powerful and flexible library for interacting with PostgreSQL databases from Python, and it has a large and active community of developers contributing to its development and maintenance.

General syntax :

The below python code is the basic outline of the code. We will have a look at the example further below.

Python3

import psycopg2

# Connection to the PostGreSql database
connection = psycopg2.connect(
    host="host_name",
    database="database_name",
    user="username",
    password="password"
)

# Creation of a cursor object
cursor = connection.cursor()

# Define the update statement with placeholders for dynamic data
update="UPDATE table_name SET column1 = %s, column2 = %s WHERE id = %s"

# Define the data to be updated
data = [
    (value1,value2,id1),
    (value1,value2,id2),
    (value1,value2,id3),
    # ...........
]

# Execute the update statement for each row of data
for row in data:
    cursor.execute(update,row)

# Commit the changes to the database
connection.commit()

# Close the cursor and database connection
cursor.close()
connection.close()

Example :

Now let’s consider a database named details, with column names [email, firstname, lastname, age]. Here email is the primary key.

Let’s see the same in pgAdmin application.

PGAdmin Image

As You can see, the table is not populated. Let’s add some values now.

PGAdmin Output

Code :

Now let’s change the values in the details’ table using the python script.

Python3

import psycopg2

# Connection to the PostGreSql database
connection = psycopg2.connect(
    host="localhost",
    database="example",
    user="postgres",
    password="samexp"
)

# Creation of a cursor object
cursor = connection.cursor()

# Define the update statement with placeholders for dynamic data
update="UPDATE details SET firstname = %s, lastname = %s, age = %s WHERE email = %s"

data=[]

n=int(input("Enter n: "))

for i in range(n):
    print("User - ",i," details: ")
    email=input("Email: ")
    fn=input("First name: ")
    ln=input("Last name: ")
    age=int(input("Age: "))

    tup=(fn,ln,age,email)
    data.append(tup)

# Execute the update statement for each row of data
for row in data:
    cursor.execute(update,row)

# Commit the changes to the database
connection.commit()
print("completed")

# Close the cursor and database connection
cursor.close()
connection.close()

Input :

Terminal Output

Now, after executing the above script, we can see that the values have been changed.

Final output :

PGAdmin output