SQLAlchemy Delete Row

Introduction to SQLAlchemy Delete Row

SQLAlchemy delete row is the method available in SQLAlchemy which can help you to delete all the existing rows of a particular table or only selective rows inside the student’s table along with the help of the where() method. In this article, we will learn about sqlalchemy delete row, how to delete sqlalchemy delete row, sqlalchemy deleting records, examples related to it, and a conclusion about the same.

Overviews sqlalchemy delete row

Sometimes it is required that we need to delete some of the rows that are present inside one of the tables as they are no more required in the database. It is a good practice, to set free the memory for the database by deleting those elements which are not required for memory utilization and increase in performance of the application along with DBMS. Also, sometimes, the situation may arise as if we don’t need to maintain anything in the particular table or want it to become vacant for some purpose, at that time the delete() method in SQLAlchemy comes for the rescue.

In this article, we will have a detailed review of how we can change the existing rows of the particular table either all of them or on a selective basis using the delete() method.

How to delete sqlalchemy delete row?

We are going to perform all the operations by using SQLAlchemy’s statement inside the application file like for example, the python file when you are using SQLAlchemy in python. Here, we will need to select the objects related to a particular target table as SQLAlchemy has completely object-oriented working.

We can go for the execution of the delete statement as shown below to delete all the rows of the existing target table –

Sample_statement = target_table.delete()

This syntax will result in deleting all the existing rows from the target_table once the sample_statement is executed. The above statement will have a similar execution in SQL as shown in the below query statement. This is because internally, it is interpreted like this while executing for the SQL database –

DELETE FROM target_table

In case, if you want to go for selective deletion of the existing rows by applying some filter for selecting rows to be deleted, you can go with the assistance of the where() method along with the delete() method while executing the statement.

For selective deletion the delete() method is oftenly used in association with the where() method which will have the following syntax –

Sample_statement = target_table.delete().where(some constraint to be applied)

The above example will in turn into the following statement having a bound parameter associated with it which will be further replaced by the where statement on execution.

DELETE FROM target_table WHERE some constraint to be applied

sqlalchemy Deleting records

Let us consider some sample demonstrative examples that will help you to understand the working of the delete method.

Step 1 – Create table – Let us create a table named Technology_blogs by following the below steps –

CREATE TABLE Technology_blogs (
[SKU] INT UNIQUE
, [Technology_type] NVARCHAR(100)
, [Domain] NVARCHAR(100)
, [Technology_name] NVARCHAR(100)
, [Number_of_pages] INT
, [rate_per_page] FLOAT
, [time_of_creation] DATETIME
, [time_of_updation] DATETIME
)

Output is as shown below –

Step 2 – Inserting data –

INSERT INTO Technology_blogs ([SKU], [Technology_type], [Domain], [Technology_name], [Number_of_pages], [rate_per_page]) VALUES
(123, 'Data Handling', 'Database', 'SQL', 1337, 1299.95)
, (24, 'Data Handling', 'Cloud', 'AWS', 885, 1389.99)
, (5577, 'Data Handling', 'DBMS', 'SQl_Server', 8, 2295.00)
, (1233, 'Front End', 'Structure', 'HTML', 334, 1659.99)
, (33, 'Front End', 'Beautification', 'CSS', 25, 2517.00)
, (34, 'Back End', 'Handling and manipulating requests', 'Python', 12, 319.00)

The execution of the above statement gives the following output –

Step 3 – Selecting data –

Let us have a look at all the contents of the Technology_blogs table –

Step 4 – Deletion of rows – For deleting the records on a conditional basis that is by applying some filter on the rows of the table then we will be using the where method along with the delete method as shown below –

Sample_statement = Technology_blogs.where(Technology Type = “Data Handling”)

Which will be further interpreted as shown below on runtime in SQL environment –

DELETE FROM Technology_blogs WHERE Technology Type = “Data Handling”;

Which will give the following output in SQLlite Studio when executed.

Let us confirm if the records are deleted or not by again executing the select statement –

We have a sample table named Technology_blogs which we want to consider for deleting all the rows. We will have the following statement for deleting all the rows –

Sample_statement = Technology_blogs.delete()

Which will be interpreted further as following statement in runtime for SQL database –

DELETE FROM Technology_blogs;

Which will give the following output in SQLlite Studio when executed.

Let us confirm by checking all the records from the table by executing the below example –

Example

Let us consider a detailed example of how we can perform the deletion process by using SQLAlchemy –

from sqlalchemy.sql.expression import update
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///educba.db', echo = True)
sample_metadata = MetaData()
Technology_blogs = Table(
'Technology_blogs', sample_metadata,
Column('Technology_type', Integer, primary_key = True),
Column('Domain', String),
Column('Technology_name', String),
Column('Number_of_pages', Integer),
Column('rate_per_page', Decimal),
Column('time_of_creation', DATETIME),
Column('time_of_creation', DATETIME),
)
sample_connection_obj = engine.connect()
sample_statement = Technology_blogs.delete().where(Technology_blogs.c.technology_type == 'Data Handling')
sample_connection_obj.execute(sample_statement)
s = Technology_blogs.select()
sample_connection_obj.execute(s).fetchall()

Output of the above code is as shown below –

We can see that the total rows loaded are 3 which means no data exists having the technology name Data Handling which is the same filter that we used while deleting the records.

In the above code snippet, what we are doing is –

• importing all the required libraries and classes at the top and then creating the object for the table having all the attributes considering the columns that exist in the table.
• Further, we go for creating the connection object using which we can communicate from our python application to SQLAlchemy database.
• We create the object containing a statement that has the delete method used along with the constraints and filter mentioned in the associated where clause.
• Finally, we go for executing the statement and then create an object containing the statement for selecting the records which are further executed and all the records are retrieved.

Conclusion

We can go for deletion of all the rows existing inside a particular table by using the delete() method of SQLAlchemy. Also, for selective deletion, make use of the where() method in an association.

Recommended Articles

This is a guide to SQLAlchemy Delete Row. Here we discuss the Introduction, overviews, How to delete sqlalchemy delete row, and examples with code. You may also have a look at the following articles to learn more –

1. SQL WAITFOR
2. PL/SQL Exception
3. PLSQL procedure
4. PLSQL rowtype