SQLAlchemy Core – Multiple Table Updates

SQLAlchemy Core provides a powerful feature for performing updates on multiple tables in a database. This allows us to update related data in multiple tables in a single transaction, ensuring data consistency and integrity. In this approach, we define the tables using SQLAlchemy’s Table object and construct update statements using the update function.

Before proceeding, make sure you have Python and MySQL servers installed. You can install the SQLAlchemy library and MySQL Connector library in Python by executing the following command in your terminal:

pip install SQLAlchemy
pip install mysql-connector-python

Multiple Table Updates using SQLAlchemy

By using the SQLAlchemy Core’s multiple table update feature, we can easily update related data in different tables within a single transaction, ensuring consistency and reducing the need for multiple round trips to the database. It provides a convenient and efficient way to update data across multiple tables in a database system.

The process starts by creating an SQLAlchemy engine and connecting it to the database. Then define the tables using the Table object from SQLAlchemy’s SQL module. To perform an update on a table, specify the table object and use the values() method to set the new values for the columns we want to update.

Steps to Update Multiple Tables

In this code example, we are using SQLAlchemy to interact with a MySQL Server database. The code showcases how to create a database connection, define tables using SQLAlchemy’s Table object, perform insertions into the tables, and update records. The example demonstrates the basic usage of SQLAlchemy’s create_engine, MetaData, Table, insert, and update functions.

Step 1: Create a schema

First, create a schema in the MySQL Server.

Creating a Schema in MySQL Server Workbench

Created a Schema named ‘gfg‘.

gfg schema created

Step 2: Import necessary modules

The next step is to import various SQLAlchemy modules which are used to create a database and to perform basic operations like insert or update.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
from sqlalchemy.sql import insert,update

Step 3: Setting up the Database Connection

To begin, establish a connection to your MySQL database using SQLAlchemy. The create_engine() function is used to create an SQLAlchemy engine that represents a connection to the database. It takes the database URL as a parameter and returns an engine object.

Make sure to replace the placeholder values (username, password, host, and database_name) with your actual MySQL credentials. If your password contains the ‘@' symbol, it can interfere with the connection URL’s syntax, as the ‘@' the symbol is used to separate the username and password in the URL. To include a special character ‘@' in your password, you need to URL-encode it.

from urllib.parse import quote_plus

password = 'password'
encoded_password = quote_plus(password)
engine = create_engine('mysql+mysqlconnector://username:'+encoded_password+
   '@localhost:3306/database_name')
conn = engine.connect()

Step 4: Create Tables

Next, we will create two tables that we will later use to update their values. In this example, we created a ‘users’ table and an ‘orders’ table.

metadata = MetaData()

table1 = Table('table_name', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String(255)),
              Column('email', String(255))
              )

table2 = Table('table_name', metadata,
               Column('id', Integer, primary_key=True),
               Column('user_id', Integer),
               Column('product', String(255)),
               Column('quantity', Integer)
               )

metadata.create_all(engine)

Step 5: Insert values

Next, we will insert some values to the tables using the insert() which takes the table name as the parameter, and the values() function which takes a list of values as the parameters.

values1 = [
    {'id':1, 'name':'John', 'email':'john@example.com'},
    {'id':2, 'name':'Jane', 'email':'jane@example.com'}
    ]

values2 = [
    {'id':1, 'user_id':1, 'product':'Product A', 'quantity':5},
    {'id':2, 'user_id':2, 'product':'Product B', 'quantity':15}
]
ins = insert(table1).values(values1)
conn.execute(ins)
conn.commit()

ins = insert(table2).values(values2)
conn.execute(ins)
conn.commit()

Once the values are inserted, we can check the table created in the MySQL Server Workbench.

Step 6: Update tables

To perform multiple table updates using SQLAlchemy Core, we need to define the tables and their relationships using SQLAlchemy’s table constructs. Then, we can use SQLAlchemy’s update() function to update multiple tables in a single transaction.

stmt1 = update(users).values(name='John Do').where(users.c.id == 1)
conn.execute(stmt1)
conn.commit()

Step 7: Close the Session

Finally, close the session to release the database connection:

session.close()

Creating Tables using SQLAlchemy

Here’s an example that demonstrates how to update multiple tables using SQLAlchemy. First we will create two tables and insert some values into it.

Output:

Orders Table

Users Table

Updating Multiple Tables

In this example, we will update multiple tables – ‘orders’ and ‘users’ with different values based on some condition. We update the name column in the 'users' table for a specific user (id=1) and update the product column and quantity in the 'orders' table for the same user (user_id=1) with different values.

Output:

Orders table after Updating

Users table after Updating