Open In App

Chain multiple statements within Psycopg2

Improve
Improve
Like Article
Like
Save
Share
Report

In this tutorial, we will see how to chain multiple statements with psycopg2 which is a PostgreSQL database adapter for Python. Chaining is supported by most SQL databases and can be used in various types of SQL clients, such as command-line interfaces, web-based management interfaces, and programming language libraries like psycopg2. let’s understand chaining in SQL chaining.

What is chaining in SQL?

In SQL chaining refers to the process of linking multiple SQL statements together into a single string, separated by semicolons. This allows you to execute multiple SQL statements at once, without having to execute them individually. 

For example, you might chain together a SELECT statement to retrieve data from a table, followed by an UPDATE statement to modify the data, and then a DELETE statement to remove it. When using chaining, it is important to note that each statement will be executed in the order they appear in the chain and that the results of one statement can be used in the next one. Additionally, when chaining SQL statements, if any statement in the chain fails, the entire chain will fail and none of the statements will be executed. It is important to use it carefully because if any statement in the chain fails, the entire chain will fail and none of the statements will be executed.

What is psycopg2?

The psycopg2 is a PostgreSQL adapter for the Python programming language. It is used to connect to, query, and manage PostgreSQL databases from a Python script. It provides a set of Python classes and methods that allow for the creation, management, and manipulation of database objects, as well as the execution of SQL statements and queries.

It provides a convenient way to interact with PostgreSQL through Python code by abstracting the underlying database communication. It supports Python’s DB-API 2.0 specification, making it compatible with a wide range of Python libraries and frameworks. With psycopg2, you can connect to a PostgreSQL database, create tables, insert, update and retrieve data, and execute SQL statements. It also provides support for advanced features like Asynchronous execution, connection pooling, and COPY command. It is widely used in Python web development, data analysis, and other applications that require a connection to a PostgreSQL database.

Note: In all the codes below, replace your password with the password you have set. In this article, we have used a database named sampleDB which also has to be set according to your needs.

Method 1: Using the execute() function

In psycopg2, you can chain multiple statements together using the execute() method. The execute() function in psycopg2 is used to execute a single SQL statement on a PostgreSQL database. It is a method of the cursor object in psycopg2, which is used to interact with the database. The execute() function takes a single argument, which is the SQL statement that you want to execute. The statement can be a string containing a single SQL command, or a list of strings containing multiple SQL commands separated by semicolons.

It is important to note that each statement must be separated by a semicolon (;). Additionally, you should use placeholders (%s) for any values that you want to pass to the SQL statements, and pass the actual values as a tuple in the second argument of the execute() method. This helps to prevent SQL injection attacks.

Example 1

In this example, the execute() method will execute all three statements in the order they are provided. The first statement creates a table named gfg_table, the second statement inserts a row into the table, and the third statement selects all rows from the table. The statements are then executed using the execute() function and the values are passed as parameters. The results of the SELECT statement are then fetched and printed. The cursor and connection are then closed. 

Python3




import psycopg2
try:
  
    conn = psycopg2.connect(host="localhost", port="5432",
                            database="sampleDB", user="postgres",
                            password="your_password")
    # Create a cursor
    cur = conn.cursor()
    # Define your SQL statements as separate strings
  
    # Create table gfg_table
    sql_statement_1 = 'CREATE TABLE IF NOT EXISTS gfg_table\
            (id SERIAL PRIMARY KEY, name TEXT, age INTEGER)'
  
    # Insert values into table. Values are passed in execute()
    sql_statement_2 = 'INSERT INTO gfg_table \
                        (name, age) VALUES (%s,%s)'
  
    # Get all data from gfg_table
    sql_statement_3 = 'SELECT * FROM gfg_table'
  
    # Execute all of the statements together
  
    cur.execute(sql_statement_1 + ';'+sql_statement_2 +
                ';' + sql_statement_3, 
                                ('Steve Harvey', 65))
  
    # Fetch the results of the SELECT statement
    results = cur.fetchall()
  
    # Print the results
    print(results)
  
    # Close the cursor and connection
    cur.close()
    conn.close()
  
except Exception as e:
    print("Some Error Occurred...", e)


Output

[(1, 'Steve Harvey', 65)]

Example 2

In this example, the execute() method will execute all three statements in the order they are provided. The first statement creates a table named student_table with columns id, username, city, and age. The second statement will insert a row into the table, and the third statement selects all rows from the table. The statements are then executed using the execute() function and the values are passed as parameters. The results of the SELECT statement are then fetched and printed. The cursor and connection are then closed. 

Python3




import psycopg2
try:
  
    conn = psycopg2.connect(host="localhost", port="5432",
                            database="sampleDB", user="postgres",
                            password="your_password")
    # Create a cursor
    cur = conn.cursor()
    # Define your SQL statements as separate strings
  
    # Create table gfg_table
    sql_statement_create = 'CREATE TABLE student_table \
        (id SERIAL PRIMARY KEY, username TEXT,city TEXT, age INTEGER)'
  
    # Insert values into table. Values are passed in execute()
    sql_statement_insert = 'INSERT INTO student_table \
                    (username,city, age) VALUES (%s,%s,%s)'
  
    # Get all data from gfg_table
    sql_statement_select = 'SELECT * FROM student_table'
  
    # Execute all of the statements together
  
    cur.execute(sql_statement_create + ';'+sql_statement_insert +
                ';' + sql_statement_select, 
                            ('gfg1', 'Mumbai', 22))
  
    # Fetch the results of the SELECT statement
    results = cur.fetchall()
  
    # Print the results
    print(results)
  
    # Close the cursor and connection
    cur.close()
    conn.close()
  
except Exception as e:
    print("Some Error Occurred...", e)


Output:

[(1, 'gfg1', 'Mumbai', 22)]

Method 2: Using a loop

In psycopg2, you can use a loop to chain multiple SQL statements together and execute them in a single call to the execute() method. This can be useful when you need to execute a large number of statements or when you want to automate repetitive tasks. 

Example 1

In this example, we have created a list of SQL statements to be executed. It consists of a create statement which creates the table gfg_table2 with 3 columns which are id, num, and data. There are 2 insert statements for inserting data into the table which are the name and email and then a select statement to fetch this data from the database. Each loop iterates over this list of SQL statements and executes each one of them separately. Once all statements are executed, the changes are committed to the database and all data from the table is printed. After that cursor and connection are closed.

Python3




import psycopg2
  
conn = psycopg2.connect(host="localhost", port="5432",
                        database="sampleDB", user="postgres"
                            password="your_password")
cursor = conn.cursor()
  
# Define a list of SQL statements
statements = [
    "CREATE TABLE IF NOT EXISTS gfg_table2 \
       (id serial PRIMARY KEY, num integer, data varchar)",
    "INSERT INTO gfg_table2 (num, data) VALUES (1, 'abc')",
    "INSERT INTO gfg_table2 (num, data) VALUES (2, 'def')",
    "SELECT * FROM gfg_table2"
]
  
# Execute each statement one by one
for statement in statements:
    cursor.execute(statement)
  
  
# Commit the changes to the database
conn.commit()
r = cursor.fetchall()
print(r)
# Close the connection
conn.close()
cursor.close()


Output

[(1, 1, 'abc'), (2, 2, 'def')]

Example 2

In this example, we have created a list of SQL statements to be executed. It consists of a create statement which creates the table users with 3 columns which are id, name, and email. There are 2 insert statements for inserting data into the table which are the name and email and then a select statement to fetch this data from the database. 

The Python for loop iterates over this list of SQL statements and executes each one of them separately. Once all statements are executed, the changes are committed to the database and all data from the table is printed. After that cursor and connection are closed.

Python3




import psycopg2
  
conn = psycopg2.connect(host="localhost", port="5432",
                        database="sampleDB", user="postgres", password="abcd6658")
  
cur = conn.cursor()
  
# Create a list of SQL statements
sql_statements = [
    "CREATE TABLE users (id SERIAL PRIMARY KEY, \
                    name VARCHAR(255), email VARCHAR(255))",
    "INSERT INTO users (name, email) VALUES ('John Doe', \
                                'john.doe@example.com')",
    "INSERT INTO users (name, email) VALUES ('Jane Smith', \
                        'jane.smith@example.com')",
    "SELECT * FROM users"
]
  
# Execute each statement one by one
for statement in sql_statements:
    cur.execute(statement)
  
# Commit the changes to the database
conn.commit()
r = cur.fetchall()
print(r)
# Close the connection and cursor
conn.close()
cur.close()


Output:

[(1, 'John Doe', 'john.doe@example.com'), (2, 'Jane Smith', 'jane.smith@example.com')]


Last Updated : 05 Feb, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads