In the preceding chapter, we've understood what an Update expression does. The subsequent expression that we are going to examine is Delete.
The delete operation may be accomplished by means of jogging delete() approach on target table item as given within the following assertion −
stmt = students.delete()
In case of students desk, the above line of code constructs a SQL expression as following −
'DELETE FROM students'
However, this may delete all rows in students table. Usually DELETE query is associated with a logical expression designated by using WHERE clause. The following announcement shows wherein parameter −
stmt = students.delete().where(students.c.id > 2)
The resultant SQL expression will have a sure parameter in order to be substituted at runtime while the assertion is executed.
'DELETE FROM students WHERE students.id > :id_1'
Following code example will delete the ones rows from students table having lastname as ‘Khanna’ −
from sqlalchemy.sql.expression import update
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)
conn = engine.connect()
stmt = students.delete().where(students.c.lastname == 'Khanna')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()
To affirm the result, refresh the information view of college students table in SQLiteStudio.