How to update existing table rows in SQLAlchemy in Python?

In this article, we are going to see how to use the UPDATE statement in SQLAlchemy against a PostgreSQL database in Python.

Creating table for demonstration:

Import necessary functions from the SQLAlchemy package. Establish connection with the PostgreSQL database using create_engine() function as shown below, create a table called books with columns book_id and book_price. Insert record into the tables using insert() and values() function as shown.

Python3

# import necessary packages
from sqlalchemy.engine import result
import sqlalchemy
from sqlalchemy import create_engine, MetaData,\
    Table, Column, Numeric, Integer, VARCHAR, update

# establish connections
engine = create_engine(
    "database+dialect://username:password0@host:port/databasename")

# initialize the Metadata Object
meta = MetaData()
meta.reflect(bind=engine)

# create a table schema
books = Table(
    'books', meta,
    Column('book_id', Integer, primary_key=True),
    Column('book_price', Numeric),
    Column('genre', VARCHAR),
    Column('book_name', VARCHAR)
)

# Create tables in the database if they don't exist
meta.create_all(engine)

# Insert records into the table
insert_statements = [
    {'book_id': 1, 'book_price': 12.2, 'genre': 'fiction', 'book_name': 'Old age'},
    {'book_id': 2, 'book_price': 13.2, 'genre': 'non-fiction', 'book_name': 'Saturn rings'},
    {'book_id': 3, 'book_price': 121.6, 'genre': 'fiction', 'book_name': 'Supernova'},
    {'book_id': 4, 'book_price': 100, 'genre': 'non-fiction', 'book_name': 'History of the world'},
    {'book_id': 5, 'book_price': 1112.2, 'genre': 'fiction', 'book_name': 'Sun city'}
]

with engine.connect() as conn:
    for statement in insert_statements:
        conn.execute(books.insert().values(**statement))

Output:

Sample table

Update table elements in SQLAlchemy

updating table elements have a slightly different procedure than that of a conventional SQL query which is  shown below

from sqlalchemy import update
upd = update(tablename)
val = upd.values({"column_name":"value"})
cond = val.where(tablename.c.column_name == value)

Get the books to table from the Metadata object initialized while connecting to the database. Pass the update query to the execute() function and get all the results using fetchall() function. Use a for loop to iterate through the results.

The SQLAlchemy query shown in the below code updates the “fiction” genre as “sci-fi” genre this will effectively update multiple rows at one go. Then, we can write a conventional SQL query and use fetchall() to print the results to check whether the table is updated properly.

Python3

from sqlalchemy import text

# Get the `books` table from the Metadata object
BOOKS = meta.tables['books']

# update
u = update(BOOKS)
u = u.values({"genre": "sci-fi"})
u = u.where(BOOKS.c.genre == "fiction")

# Execute the update query using a connection
with engine.connect() as conn:
    conn.execute(u)

# Fetch all the records
sql = text("SELECT * from books") 
with engine.connect() as conn:
    result = conn.execute(sql).fetchall()

# View the records
for record in result:
    print("\n", record)

Output:

The result of update query