Inserts, Updates, Deletes in SQLAlchemy 1.4/2.0 Core

Introduction

SQLAlchemy is a library of Python or a Database Toolkit that consists of comprehensive tools to work with databases. It has two portions i.e., The Core and The ORM. You can use Python code directly to perform operations such as Create, Read, Update and Delete. This helps in speeding up the development time and it can often make managing data less error-prone.

SQLAlchemy Core consists of SQL Expression Language. The SQL Expression Language allows us to create SQL expressions and further these expressions will be executed against a targeted database and finally returns a dataset.

 Before performing any operation it is necessary to first connect to the database. So, to establish the connection, we will be creating an object called Engine. This object establishes a connection with the database. We use the method create_engine() to create the Engine object.

Creating an Engine Object

Python Code:

Creating a Table Using MetaData

We then create an object of MetaData() to create a Table. In this example, I am creating a user_account table that stores the ‘username’ and ’emailid’ and contains a primary key ‘id’.

Result

We can also use constraints on a Table. In the below example, I am creating a Foreign Key constraint which we will be using further in this guide. To create a Foreign Key constraint, we import ForeignKey from sqlalchemy as shown below.

Result

Inserting Values to the Table

syntax:

insert(table[, values, inline, bind, ...], **dialect_kw)

Example:

from sqlalchemy import insert

stmt = (insert(user_table).values(name='username', fullname='Full Username'))

Parameters

• table: The database table where the values are to be inserted.
• values: Set of values to be inserted
• inline: If set to True, the SQL expressions will be rendered inline without the need for pre-execution.

Example

#creating an insert statement
insert_stmt=user_table.insert().values(
    username="spongebob", emailid="sponge@g,ail.com")
#executing the insert_stmt
with engine.begin() as connection:

connection.execute(insert_stmt)

In the above example, we are using the insert statement with the target table and VALUES clause at once and then executing it using the engine object connection.

Another way of inserting values is as below in which the VALUES clause is generated automatically.

with engine.begin() as connection:
    connection.execute(
        user_table.insert(),{"username":"sandy","emailid":"[email protected]"}

)

or

So far, we have inserted 4 different rows into the Table.

Updating Values

The update() function is used to generate any Update statement which updates an existing table in the database.

Syntax:

update(table[, whereclause, values, inline, ...], **dialect_kw)

Example:

from sqlalchemy import update

stmt = (update(user_table).where(user_table.c.id == 3).values(name='User 3'))

Parameters

• table: This refers to the database table that is to be updated
• where clause: describes the where condition of the update statement
• Values: Optional dictionary which specifies the SET condition of the Update.
• inline: If True, the Columns with the default keyword will not be pre-executed and will be compiled inline.
• preserve_parameter_order: If this is set to True, the parameters will be received only via the Update.values() method. The parameters must be passed as a Python list of 2-tuples.

A basic update statement looks like this in which we are updating the email id of a user where the username matches “sandy” and then executing the statement.

We can also make use of column expressions in the update statement as below:

with engine.begin() as connection:
    update_stmt=(user_table.update().
                 values(emailid=user_table.c.username+"e:"+user_table.c.emailid))

result=connection.execute(update_stmt)

Update using bindparam() where many parameter sets can be invoked using the same statement.

from sqlalchemy import update

from sqlalchemy import bindparam
with engine.begin() as connection:
    update_stmt = update(user_table).where(user_table.c.username == bindparam('oldname')).values(username=bindparam('newname'))
    connection.execute(update_stmt,
                   [
                       {'oldname': 'Mary', 'newname': 'marie'},
                       {'oldname': 'patrick', 'newname': 'patryk'},
                   ]
                   )

Result

Correlated Updates

A correlated subquery can be used in the update statement by using rows of another table.

from sqlalchemy import select
scalar_subq=(
    select(address_table.c.address).
    where(address_table.c.user_id == user_table.c.id).
    order_by(address_table.c.id).
    limit(1).
    scalar_subquery())
update_stmt = update(user_table).values(username=scalar_subq)
print(update_stmt)

Update…FROM

When we use additional tables in the where clause, it automatically generates an UPDATE FROM clause as you can see in the result

update_stmt = (
    update(user_table).
    where(user_table.c.id == address_table.c.user_id).
    where(address_table.c.address == 'India').
    values(username='Patrik')
  )
print(update_stmt)

Deleting Values

The delete() function is used to create any delete statement that deletes rows from the table.

Syntax:

Example:

from sqlalchemy import deletestmt = (delete(user_table).where(user_table.c.id == 3))

Parameters

• table: The database table from
  where the rows are to be deleted
• where clause: An optional SQL expression
  that describes the where condition of the DELETE statement.

A simple delete statement is like the below:

from sqlalchemy import delete
delete_stmt=delete(user_table).where(user_table.c.username=="marie")
print(delete_stmt)

Result

Deleting from multiple tables

Correlated subqueries including the WHERE clause can be used with the delete() function to delete rows that are related to multiple tables.

delete_stmt = delete(user_table).where(user_table.c.id == address_table.c.user_id).where(address_table.c.address == "India")
print(delete_stmt)

Result

Getting Row Count

We can get the row count which indicates the number of rows that matches the where clause of an update or a delete statement.

with engine.begin() as conn:
    result=conn.execute(
        update(user_table).
        values(username="Marie Sam").
        where(user_table.c.username=="marie"))
print(result.rowcount)

Result

with engine.begin() as conn:
    result=conn.execute(
        update(user_table).
        values(username="spongebobby").
        where(user_table.c.username=="spongebob"))
print(result.rowcount)

Result

Using Returning

The update() and delete() method also supports returning the selected columns from all rows that match the where criteria of a statement into the result object by using Update.Returning() and Delete.Returning() methods. The result object can further be iterated to get the rows matched.

update_stmt=(
    update(user_table).
       where(user_table.c.username == "sandy").
       values(username="Sandy Sun").
       returning(user_table.c.id,user_table.c.username)
)
print(update_stmt)

Result

delete_stmt=(
    delete(user_table).
         where(user_table.c.username == "sandy").
         returning(user_table.c.id,user_table.c.username)
)
print(delete_stmt)

Result

Conclusion

We have learned the basics of SQLAlchemy Core in this article. We’ve learned about basic SQLAlchemy concepts like

• Engines that help us in establishing a connection to a database, and then we learned how to work with Database MetaData.
• How to create a Table using MetaData object.
• We’ve learned about how we can work with Data i.e., perform operations such as Insert, Delete, and Update on Tables using an example for each.
• In summary, we had the chance to learn and practice the most important pieces of SQLAlchemy (Core) 1.4/2.0.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.