Inserts, Updates, Deletes in SQLAlchemy 1.4/2.0 Core
source link: https://www.analyticsvidhya.com/blog/2022/06/inserts-updates-deletes-in-sqlalchemy-1-4-2-0-core/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
This article was published as a part of the Data Science Blogathon.
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
from sqlalchemy import create_engine engine=create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
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]"}
)
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.
Related
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK