This article was published as a part of the Data Science Blogathon.
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.
Python Code:
from sqlalchemy import create_engine
engine=create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
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’.
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
syntax:
insert(table[, values, inline, bind, ...], **dialect_kw)
Example:
from sqlalchemy import insert
stmt = (insert(user_table).values(name='username', fullname='Full Username'))
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.
The update() function is used to generate any Update statement which updates an existing table in the database.
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'))
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'}, ] )
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)
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)
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))
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)
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
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)
with engine.begin() as conn: result=conn.execute( update(user_table). values(username="spongebobby"). where(user_table.c.username=="spongebob")) print(result.rowcount)
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)
delete_stmt=( delete(user_table). where(user_table.c.username == "sandy"). returning(user_table.c.id,user_table.c.username) ) print(delete_stmt)
We have learned the basics of SQLAlchemy Core in this article. We’ve learned about basic SQLAlchemy concepts like
The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.