Python PostgreSQL with psycopg2 tutorial

psycopg2 logo

I was looking to integrate PostgreSQL into my application using Python, as I mostly code in Python. During my search, I came across psycopg2, a popular library for connecting PostgreSQL with Python. As I’m currently learning psycopg2, I decided to write this article for myself and anyone else who wants simpler documentation. This article will focus on how to use psycopg2 rather than on SQL itself. I’ll provide code snippets along with output screenshots from pgAdmin4 to make things clearer and easier to understand.

1. CONNECT TO DATABASE

import psycopg2
try:
    conn = psycopg2.connect(
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD
    )
    print("Connected")
except:
    print("Unable to connect")

conn.close()

• psycopg2.connect(): This function is used to establish a connection to the database. We pass parameters such as the host, port, username, and password.
• host=HOST: The address of the PostgreSQL server (can be localhost or an IP address).
• port=PORT: The port where the PostgreSQL server is running (usually 5432 by default).
• user=USER: The username used to connect to the database.
• password=PASSWORD: The password for the given username.

If everything is correct, the psycopg2.connect() function opens a connection to the default database(usually “postgres” in PostgreSQL), allowing us to send queries and commands.

Get Saijyoti Tripathy’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

If we want to connect to an existing database we can add database name inside psycopg2.connect() like this -

conn = psycopg2.connect(
    database= "test",
    host=HOST,
    port=PORT,
    user=USER,
    password=PASSWORD
)

2. CREATE A NEW DATABASE

import psycopg2
conn = psycopg2.connect(
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD
    )
conn.autocommit=True
cur = conn.cursor()

sql = '''CREATE database mydb'''
cur.execute(sql)
print("Database created successfully........")

cur.close()
conn.close()

• conn.autocommit=True: By default, psycopg2 works in manual commit mode. This means any changes we make (like inserting or updating data) won’t be saved until we explicitly use commit() to save them. However, some commands, like CREATE DATABASE or DROP DATABASE, can’t run inside a transaction, so we need to turn on autocommit mode.
• conn.cursor(): This is like getting a pen to write SQL commands. We need it to interact with the database.
• sql = “CREATE database mydb”: The SQL command to create a database. All the sql commands go into cur.execute().
• cur.close(): Once we’re done writing, we close the cursor.
• conn.close(): Finally, we close the connection to the database, like locking up after finishing our work.

pgAdmin4

We can see a new database with the name “mydb” is created.

3. CREATE A TABLE IN DATABASE

import psycopg2
conn = psycopg2.connect(
        database= "mydb",
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD
    )
print("Opened database successfully")
cur = conn.cursor()

sql= '''CREATE TABLE COMPANY
        (ID INT PRIMARY KEY     NOT NULL,
        NAME           TEXT    NOT NULL,
        AGE            INT     NOT NULL,
        ADDRESS        CHAR(50),
        SALARY         REAL)
        '''
cur.execute(sql)
print("Table created successfully")

cur.close()
conn.commit()
conn.close()

Here we are opening the previously created database and creating a table inside that.

• conn.commit(): is called to makes sure the changes (creating the table) are saved in the database.

pgAdmin4

Here an empty table is created with the name company.

4. INSERTING DATA INTO TABLE

import psycopg2
conn = psycopg2.connect(
        database= "mydb",
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD
    )
cur = conn.cursor()

print("Inserting data -- method 1")
sql_s1= '''INSERT INTO COMPANY (ID, NAME, AGE, SALARY, ADDRESS) VALUES (1, 'A', 20, 20000, 'xyz')''';
sql_s2= '''INSERT INTO COMPANY (ID, NAME, AGE, SALARY, ADDRESS) VALUES (2, 'B', 21, 30000, 'xyz')''';
sql_s3= '''INSERT INTO COMPANY (ID, NAME, AGE, SALARY, ADDRESS) VALUES (3, 'C', 19, 25000, 'xyz')''';
cur.execute(sql_s1)
cur.execute(sql_s2)
cur.execute(sql_s3)
print("Data inserted successfully")

print("Inserting data -- method 2")
sql= '''INSERT INTO COMPANY (ID, NAME, AGE, SALARY, ADDRESS) VALUES (%s, %s, %s, %s, %s)'''
company_list= [(4, 'D', 27, 50000, 'abc'), (5, 'E', 24, 30000, 'xyz'), (6, 'F', 19, 25000, 'dag')]
for tuple in company_list:
    cur.execute(sql,tuple)
    print("Data inserted successfully")

print("Inserting data -- method 3")
sql= '''INSERT INTO COMPANY (ID, NAME, AGE, SALARY, ADDRESS) VALUES (%s, %s, %s, %s, %s)'''
company_list= [(7, 'G', 26, 55000, 'abc'), (8, 'H', 25, 37000, 'dgv'), (9, 'I', 34, 75000, 'faa')]
cur.executemany(sql,company_list)
print("Data inserted successfully")

conn.commit()
cur.close()
conn.close()

We have different methods of inserting data into the table COMPANY:

• Inserting data one by one

cur.execute(sql): We manually insert each row of data using individual INSERT commands.

• Using a loop

cur.execute(sql, tuple): Here, we insert multiple rows using a loop and placeholders (%s) for the values. Each tuple in the company_list is inserted into the table.

• Using executemany() for batch insert

cur.executemany(sql, company_list): This method inserts all the rows in company_list at once, improving efficiency. It’s the most efficient way to insert multiple records at once!

pgAdmin4

We can see the data inserted by running the SQL select query in pgAdmin4.

5. SELECTING DATA FROM TABLE

import psycopg2
conn = psycopg2.connect(
        database= "mydb",
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD
    )
cur = conn.cursor()

print("Printing all values ---")
sql= '''SELECT id,name,age,salary,address from COMPANY'''
cur.execute(sql)
rows= cur.fetchall()
for i in rows:
    print(f"ID : {i[0]} Name : {i[1]} Age : {i[2]} Salary : {i[3]} Address : {i[4]}")

print("Printing one value ---")
sql= '''SELECT id,name,age,salary,address from COMPANY'''
cur.execute(sql)
row= cur.fetchone()
print(f"ID : {row[0]} Name : {row[1]} Age : {row[2]} Salary : {row[3]} Address : {row[4]}")

print("Printing some value ---")
sql= '''SELECT id,name,age,salary,address from COMPANY'''
cur.execute(sql)
rows= cur.fetchmany(size=4)
for row in rows:
    print(f"ID: {row[0]} Name: {row[1]} Age: {row[2]} Salary: {row[3]} Address: {row[4]}")

conn.commit()
cur.close()
conn.close()

Like different ways to insert data into table, there are also different ways to fetch and print data from the table:

• Printing all values

cur.fetchall(): This fetches all rows from the query result at once.

• Printing one value

cur.fetchone(): This fetches just one row at a time from the result.

• Printing some values

cur.fetchmany(size=4): This fetches a specific number of rows (in this case, 4) from the query result.

6. UPDATING DATA IN TABLE

import psycopg2
conn = psycopg2.connect(
        database= "mydb",
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD
    )
cur = conn.cursor()

sql= '''UPDATE COMPANY set salary = 40000 WHERE age=19 '''
cur.execute(sql)
print("Data updated successfully")
print("Total rows affected - "+str(cur.rowcount))

sql= '''UPDATE COMPANY set salary = 39000 WHERE age IN (%s, %s, %s) '''
cur.execute(sql, (21,24,26))
print("Data updated successfully")
print("Total rows affected - "+str(cur.rowcount))

conn.commit()
cur.close()
conn.close()

Updating data in table can also be done one by one or mutiple. After each update, cur.rowcount is used to print how many rows were affected by the update.

7. DELETING DATA FROM TABLE

import psycopg2
conn = psycopg2.connect(
        database= "mydb",
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD
    )
cur = conn.cursor()

sql= '''DELETE FROM COMPANY WHERE age=20 '''
cur.execute(sql)
print("Data deleted successfully")
print("Total rows affected - "+str(cur.rowcount))

sql= '''DELETE FROM COMPANY WHERE age IN (%s, %s, %s)'''
cur.execute(sql,(21,24,26))
print("Data deleted successfully")
print("Total rows affected - "+str(cur.rowcount))

sql= '''SELECT id,name,age,salary,address from COMPANY'''
cur.execute(sql)
rows= cur.fetchall()
for i in rows:
    print(f"ID : {i[0]} Name : {i[1]} Age : {i[2]} Salary : {i[3]} Address : {i[4]}")

conn.commit()
cur.close()
conn.close()

pgAdmin4

8. DELETE TABLE

import psycopg2
conn = psycopg2.connect(
        database= "mydb",
        host=HOST,
        port=PORT,
        user=USER,
        password=PASSWORD
    )
conn.autocommit=True
cur = conn.cursor()

sql = '''DROP TABLE COMPANY'''
cur.execute(sql)
print("Table dropped successfully........")

cur.close()
conn.close()

pgAdmin4

No table is found after deletion.

Note that here conn.autocommit() is used instead of conn.commit().

In this article, we’ve covered the basics of using psycopg2 to connect to and interact with PostgreSQL from Python. I hope the examples and explanations have helped simplify the process for anyone starting with database integration. For those looking to dive deeper into psycopg2 or need more detailed information, feel free to refer to the official documentation. Happy coding!