This time I will write the method of ** Delete ** with ** SQLAlchemy **.
First, simply search the table for the data whose "name" column is "Yuko Kuwata" and delete it.
sqlalchemy_delete.py
# -*- coding:utf-8 -*-
import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()
class Student(Base):
__tablename__ = 'students'
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
name = sqlalchemy.Column(sqlalchemy.String(20))
kana = sqlalchemy.Column(sqlalchemy.String(40))
def main():
url = 'mysql+pymysql://root:@localhost/test_db?charset=utf8'
engine = sqlalchemy.create_engine(url, echo=False)
#Create table
Base.metadata.create_all(engine)
#Create a session
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
#Delete all data
session.query(Student).delete()
#List of data to add to the database
student_list = [
Student(id=1, name='Yu Ishizaka', kana='Yu Ishizaka'),
Student(id=2, name='Seiichi Sugino', kana='Sugi no Seiichi'),
Student(id=3, name='Yuko Kuwata', kana='Yuko Kuwata'),
Student(id=4, name='Ai Kurihara', kana='Kurihara Ai'),
Student(id=5, name='Hitoshi Sakuma', kana='Sakuma Jin'),
]
#Add data list at once
session.add_all(student_list)
#Search for data to be deleted
found_student = session.query(Student).filter_by(name='Yuko Kuwata').first()
#Delete the specified data
session.delete(found_student)
#Output all data in the table
print_all_students(session)
#Reflected in database
session.commit()
#A function that outputs all the data in the table
def print_all_students(session):
students = session.query(Student).all()
for student in students:
print('%d, %s %s' % (student.id, student.name, student.kana))
if __name__ == '__main__':
main()
Next, as an application, delete the data by specifying the filter or IN clause.
sqlalchemy_delete.py
# -*- coding:utf-8 -*-
import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()
class Student(Base):
__tablename__ = 'students'
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
name = sqlalchemy.Column(sqlalchemy.String(20))
kana = sqlalchemy.Column(sqlalchemy.String(40))
def main():
url = 'mysql+pymysql://root:@localhost/test_db?charset=utf8'
engine = sqlalchemy.create_engine(url, echo=False)
#Create table
Base.metadata.create_all(engine)
#Create a session
Session = sqlalchemy.orm.sessionmaker(bind=engine)
session = Session()
#Delete all data
print("====== session.query(Student).delete() =====")
session.query(Student).delete()
print_all_students(session)
#List of data to add to the database
student_list = [
Student(id=1, name='Yu Ishizaka', kana='Yu Ishizaka'),
Student(id=2, name='Seiichi Sugino', kana='Sugi no Seiichi'),
Student(id=3, name='Yuko Kuwata', kana='Yuko Kuwata'),
Student(id=4, name='Ai Kurihara', kana='Kurihara Ai'),
Student(id=5, name='Hitoshi Sakuma', kana='Sakuma Jin'),
]
#Add data list at once
print("====== session.add_all(student_list) ======")
session.add_all(student_list)
print_all_students(session)
#Delete by specifying with a filter
print("====== session.query(Student).filter(Student.id==2).delete() =====")
session.query(Student).filter(Student.id==2).delete()
print_all_students(session)
#Delete by specifying in IN clause
print("====== session.query(Student).filter(Student.id.in_([3, 4])).delete(synchronize_session='fetch') =====")
session.query(Student).filter(Student.id.in_([3, 4])).delete(synchronize_session='fetch')
print_all_students(session)
#Reflected in database
session.commit()
#A function that outputs all the data in the table
def print_all_students(session):
students = session.query(Student).all()
for student in students:
print('%d, %s %s' % (student.id, student.name, student.kana))
if __name__ == '__main__':
main()
Since there is a method called add_all in the Session class, is it delete_all to delete all data? It seems that some people think that, but it is not so, so be careful.
Also, the default argument of the delete method argument synchronize_session is'evaluate', so if you specify it in the IN clause and delete it, you will get an exception if you do not specify'fetch'. See the Official Documentation (http://docs.sqlalchemy.org/en/rel_1_1/orm/query.html#sqlalchemy.orm.query.Query.delete) for more information.
[Error message]
sqlalchemy.exc.InvalidRequestError: Could not evaluate current criteria in Python. Specify 'fetch' or False for the synchronize_session parameter.
Official docs> Query API> delete (synchronize_session ='evaluate') http://docs.sqlalchemy.org/en/rel_1_1/orm/query.html#sqlalchemy.orm.query.Query.delete
Recommended Posts