SQLAlchemy: update & delete value from database

Question

I'm newer in SQLAlchemy I use some examples to create table and insert information to it and it's working 100% .

But what I didn't find is some example for how can I update & delete some information from the database.

What I'm doing is :

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

Base = declarative_base()

## create
class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

engine = create_engine('sqlite:///database.db')
Base.metadata.create_all(engine)

## insert

Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)

session = DBSession()
new_person = Person(name='new person')
session.add(new_person)
session.commit()

## fetch
getperson = session.query(Person).first()
print getperson.name

# this will print : new person

# I need some example to how can I update and delete this : new person

So in this code it'll print "new person" my question is how can I update or delete it ?

Can you clarify what you mean by "update and delete"? It seems like that would be equivalent to "delete"; the data is gone either way. — chepner, Apr 15 '16 at 12:25
See here (they discuss using raw sql and execute - seems like that could work for you): http://stackoverflow.com/questions/17972020/how-to-execute-raw-sql-in-sqlalchemy-flask-app — flyingmeatball, Apr 15 '16 at 12:57

score 0 · Answer 1 · answered Apr 15 '16 at 13:19

Here's some example on each CRUD operation in sqlalchemy (ommiting Create, Read as you already know how to perform those): First, necessary imports and configs for any operation:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Category, Item, User are my tables
from database_setup import Base, Category, Item, User

# Generating session to connect to the db's ORM
engine = create_engine('sqlite:///catalogwithusers.db') # my db
Base.metadata.bind = engine
DBSession = sessionmaker(bind = engine)
session = DBSession()

Then peforming an update:

# Get the item filtering by it's id using a one() query on Item table
# If query is not empty, update the attributes, add query to session and commit

q = session.query(Item).filter_by(id=item_id).one()
if q != []:
    q.name = edited_name
    q.description = edited_description
    session.add(q)
    session.commit()

Finally, performing a deletion:

# Again get the item similarly to the example above
# Then if query returned results, use the delete method and commit
q = session.query(Item).filter_by(id=item_id).one()
if q != []:
    session.delete(q)
    session.commit()

These examples are taken from here. I suggest you have a look. ORM Creation is inside database_setup.py and CRUD ops are performed inside project.py and populatecatalog.py.

SQLAlchemy: update & delete value from database

1 Answers1