If we were using SQL we would write something like the following to do a simple update to our Employees table in the D:\Test\emp4.db SQLite database. We have a table called Employees. It has three columns: id, name and position.

UPDATE Employees SET name='Sally' WHERE id=1

In SQLAlchemy Core we use an expression like the following one.

table.update().where(conditions).values(SET expressions)

To create an example from our database…

Employees.update().where(id==1).values(name='Rowan')

Now let’s run some code in our Python Jupyter Notebook project. It’s called SQLAlchemy Core Update. I’ve noticed that sometime I get an error that says the database is locked. I’ve have to shut down other scripts in Jupyter by going to the Running tab and clicking the shutdown button for other projects using the same database. Also, perhaps DB Browser has it locked.

import sqlalchemy as sa
engine = sa.create_engine(r'sqlite:///D:\Test\emp4.db') 
connection = engine.connect()

meta_data = sa.MetaData()
employees = sa.Table('Employees', meta_data, autoload_with=engine)

update_query = employees.update().where(employees.c.id==1).values(position='teacher')
connection.execute(update_query)

When I checked the table in DB Browser I found that it worked. I can also print the table in Python.

new_query = employees.select()
result = connection.execute(new_query).fetchall()
print(result)

[(1, 'Tracy', 'teacher'), (2, 'Tracy', 'python programmer'), (3, 'bart', 'vp operations'), (4, 'hugh', 'janitorial engineering'), (5, 'suzie', 'manager')]

It worked. The first person is now a teacher.

update_query1 = sa.update(employees).where(employees.c.id == 2).values(position='marketing manager')
connection.execute(update_query1)
new_query = employees.select()
result = connection.execute(new_query).fetchall()
print(result)

[(1, 'Tracy', 'teacher'), (2, 'Tracy', 'marketing manager'), (3, 'bart', 'vp operations'), (4, 'hugh', 'janitorial engineering'), (5, 'suzie', 'manager')]

Now the second person is a marketing manager.