Multiple Postgres databases in psycopg2

PostgreSQL is the most powerful open-source object-relational database management system. Psycopg2 is the most popular PostgreSQL database adapter for Python language. It simply allows you to work with multiple databases in the same program at the same time. This indicates that you can easily switch between two different databases and can perform various operations on them, also efficiently managing your data too. With the use of psycopg2, you can write Python scripts to create, read, update, and delete data in Postgres databases, without using any external tools. It is a powerful and flexible tool that can be used to work with small as well as large-scale databases. It doesn’t matter if you are building a web application, managing a data warehouse, or creating a machine learning model, psycopg2 module can help you work with your Postgres databases as easily as possible.

Concepts related to the topic:

• Connection object: It is basically used to establish a connection with a particular database.
• Cursor object: It is used to execute SQL queries on a database.
• Switching databases: It is used to switch between databases, a new connection needs to be established using the appropriate credentials.

Required Modules:

pip install psycopg2

Implementation With Examples :

Steps Needed For Implementation :

• First, you can import the psycopg2 library
• Then establish a connection with the first database using appropriate or correct credentials
• After that create a cursor object for the execution of SQL queries on the first database
• Then switch to the second database by forming a new connection with the appropriate credentials
• Next is to create a cursor object for executing SQL queries on the second database like the first one
• Then perform the required operations on the second database
• After that close the cursor and connection for the second database
• With that perform any further operations on the first database
• Finally, close the cursor and connection for the first database and it’s done

Related Examples :

Example 1:

Creating a new table in database1 and inserting data

Python

import psycopg2

# Establishing connection with database1
conn1 = psycopg2.connect(
    database="database1",
    user="username",
    password="password",
    host="localhost",
    port="5432"
)
print("Connection successful!!")

# Creating a new table
cur1 = conn1.cursor()
cur1.execute('''CREATE TABLE employees
                (ID INT PRIMARY KEY NOT NULL,
                NAME TEXT NOT NULL,
                AGE INT NOT NULL,
                ADDRESS TEXT);''')
print("Table created successfully")

# Inserting data
cur1.execute(
    "INSERT INTO employees (ID, NAME, AGE, ADDRESS) VALUES (1, 'John', 30, 'California')")
conn1.commit()
print("Data inserted successfully")

# Closing connection
cur1.close()
conn1.close()
print("Connection Closed!!")

In this example, we establish a connection with the “database1” database using the psycopg2 library. Then, we create a new table called “employees” with the columns ID, NAME, AGE, and ADDRESS. We then insert data into this table. Finally, we close the connection with the “database1” database.

Function used :

• psycopg2.connect() – This function can form a connection to a PostgreSQL database. It accept some arguments that includes the database name, username, password, host, and port.

Python

conn1 = psycopg2.connect(
    database="database1",
    user="username",
    password="password",
    host="localhost",
    port="5432"
)

• conn.cursor() – This method form a new cursor object which is use to interact with the database.

Python

cur1 = conn1.cursor()

• cur. execute() – This method is use for  execution of a SQL query on the database.

Python

cur1.execute('''CREATE TABLE employees
                (ID INT PRIMARY KEY NOT NULL,
                NAME TEXT NOT NULL,
                AGE INT NOT NULL,
                ADDRESS TEXT);''')

This code can creates a new table with the given SQL queries named “employees” with four columns: ID (an integer primary key), NAME (text), AGE (integer), and ADDRESS (text).

• conn.commit() – This method is use to commits any of the changes made to the database since the last commit.

Python

conn1.commit()

• cur.close() and conn.close() – To close the cursor and the database connection which are in use currently.

Python

cur1.close()
conn1.close()

Output :

Connection successful!!
Table created successfully
Data inserted successfully
Connection Closed!!

Example 2: 

Querying data from database2

Python

import psycopg2

# Establishing connection with database2
conn2 = psycopg2.connect(
    database="database2",
    user="username",
    password="password",
    host="localhost",
    port="5432"
)
print("Successful Connection!!")

# Querying data
cur2 = conn2.cursor()
cur2.execute("SELECT * FROM employees")
rows = cur2.fetchall()
for row in rows:
    print(row)

# Closing connection
cur2.close()
conn2.close()
print("Connection closed!!!")

In this example, we establish a connection with the “database2” database using the psycopg2 library. We then query the “employees” table and print the retrieved data. Finally, we close the connection with the “database2” database.

Function used :

Here psycopg2.connect(), conn.cursor(), cur. close() and conn. close() are similar to example one 

• cur. execute() – Here this code executes a SELECT query to get all the data from the table named as  “employees” table.

Python

cur2.execute("SELECT * FROM employees")

• cur. fetch all () – This method uses to retrieve all of the rows of the result which is set from the last executed query.

Python

rows = cur2.fetchall()

Output :

Successful Connection!!
(1, 'John', 30, 'California')
Connection closed!!!