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="Lazyroar", 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="Lazyroar", 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:
