Python PostgreSQL – Delete Data

In this article, we are going to see how to delete data in tables from PostgreSQL using pyscopg2 module in Python. In PostgreSQL, DELETE TABLE is used to delete the data in the existing table from the database. It removes table definition and all associated data, indexes, rules, triggers, and constraints for that table. If the particular table doesn’t exist then it shows an error. 

Table Used:

Here, we are using the accounts table for demonstration.

Now let’s drops this table, for we will use will psycopg2 module to connect the PostgreSQL and execute the SQL query in cursor.execute(query) object.

Syntax: cursor.execute(sql_query);

Example 1: Deleting all the data from the table

Here we are deleting all the table data using DELETE clause.

Syntax: DELETE FROM table_name

Code:

Python3

# importing psycopg2
import psycopg2

conn=psycopg2.connect(
    database="geeks",
    user="postgres",
    password="root",
    host="localhost",
    port="5432"
)


# Creating a cursor object using the cursor()
# method
cursor = conn.cursor()

# delete all details from account table
sql = ''' DELETE FROM account '''

# Executing the query
cursor.execute(sql)


# Commit your changes in the database
conn.commit()

# Closing the connection
conn.close()

Output:

Example 2: Using where clause

Where clause is used as a condition statement in SQL, it is used to filter records.

Syntax: DELETE FROM table_name FROM WHERE condition

Code:

Python3

# importing psycopg2
import psycopg2

conn=psycopg2.connect(
    database="geeks",
    user="postgres",
    password="password",
    host="localhost",
    port="5432"
)


# Creating a cursor object using the cursor()
# method
cursor = conn.cursor()

# delete details of row where id =1 from account
# table
sql = ''' DELETE FROM account WHERE id='151' '''

# Executing the query
cursor.execute(sql)

# Commit your changes in the database
conn.commit()

# Closing the connection
conn.close()

Output: