This tutorial explains how to connect your PostgreSQL database to Python and query it.

Before you begin, make sure you have the following installed:

• Python: You can download and install Python from the official website (https://www.python.org).
• PostgreSQL: Install PostgreSQL on your computer by following the instructions from the official website (https://www.postgresql.org).

To access the PostgreSQL database, Python needs a PostgreSQL driver. You can install the psycopg2 package using pip, the Python package installer. Open your command prompt or terminal and run the following command:

pip install psycopg2

To use the psycopg2 package in your Python code, import the psycopg2 module:

import psycopg2

For the purpose of this example, we will need a sample database. Follow the steps below to create it:

1. Open a PostgreSQL client tool like pgadmin4 or psql.
2. Log in to the database using your credentials.
3. Run the following command to create a database, for example, company:

CREATE DATABASE company;

To connect to the previously created database (company), we use the connect() function. Establish a connection to the database by providing your PostgreSQL database's username, password, and database name in the Python code:

mydb = psycopg2.connect(
  host="localhost",
  user="yourUsername",
  password="yourPassword",
  database="company"
)

You need to create a cursor to execute SQL queries in your Python code:

mycursor = mydb.cursor()

Now you can use SQL commands to query the database.

You also need to include the commit function and set the automatic commit to be True:

mydb.set_session(autocommit=True)

This ensures that each action is committed or saved without having to call mydb.commit() after each command.

Now, let's create a table in the database using the cursor we created:

mycursor.execute('''CREATE TABLE employee(
    EmployeeID int,
    Name varchar(255),
    Email varchar(255));
''')

Now, let's insert values into the database:

mycursor.execute('''
INSERT INTO employee (EmployeeID, Name, Email)
    VALUES (101, 'Mark', '[email protected]'),
           (102, 'Robert', '[email protected]'),
           (103, 'Spencer', '[email protected]');
''')

Let's query the database:

mycursor.execute("SELECT * FROM employee")

After executing the query, you can use one of the psycopg2 functions to retrieve data rows:

• fetchone(): Retrieves exactly one row (the first row) after executing the SQL query.
• fetchall(): Retrieves all the rows.
• fetchmany(): Retrieves a specific number of rows.

For example:

print(mycursor.fetchone())

Output:

The most basic way to fetch data from your database is to use the fetchone() function. It returns exactly one row (the first row) after executing the SQL query.

print(mycursor.fetchall())

Output:

If you need more than one row from your database, you can use fetchall(), which returns all the rows.

print(mycursor.fetchmany(2))

Output:

With fetchmany(), you have another option to retrieve a specific number of rows from the database.

Choose the appropriate function based on your needs.

Finally, remember to close the cursor and the connection:

mycursor.close()
mydb.close()

You can also find the code snippet here.

This tutorial was written by Kishlay Jeet, with contributions from other people. If you encounter any issues with this tutorial, please let me know, and I will make improvements. If you found this tutorial helpful, please consider giving it a star.

If you have any feedback, please reach out to me at [email protected].