python + SQLAlchemy: deleting with the Session object
PythonSqlalchemyPython Problem Overview
I can't quite figure this out: I'd like to delete all the records from a table on a matching query. Kind of like this.
engine = sqlalchemy.create_engine(string)
meta = MetaData(bind=engine)
meta.reflect(bind=engine, schema='myschema')
Base = automap_base(metadata=meta)
Base.prepare(engine, reflect=True)
Classes = Base.classes
Session = sessionmaker(bind=engine)
session = Session()
session.delete(plays.record_id == '123')
But that isn't working. What's the idea here? Error I get is:
error in parsing record ID 020087: Class 'sqlalchemy.sql.elements.BinaryExpression' is not mapped
Python Solutions
Solution 1 - Python
In SQL Alchemy you are deleting Objects that you get with a query from the database. This you can do in 2 Ways:
Deleting with query (will issue just one DELETE
statement):
session.query(User).filter(User.id==7).delete()
session.commit()
Deleting object instance returned by a query (will issue 2 statements: first SELECT
, then DELETE
):
obj=session.query(User).filter(User.id==7).first()
session.delete(obj)
session.commit()
Solution 2 - Python
Delete All Records
#for all records
session.query(Model).delete()
session.commit()
Deleted Single Row
If you want to delete specific records then try filter
clause in the query.
ex.
#for specific value
session.query(Model).filter(Model.id==123).delete()
session.commit()
Delete Single Record by Object
record_obj = session.query(Model).filter(Model.id==123).first()
session.delete(record_obj)
session.commit()
https://flask-sqlalchemy.palletsprojects.com/en/2.x/queries/#deleting-records