Image credit to Игорь Левченко
In this discovery, we'll look at how to perform SQL DML ( CREATE, UPDATE, DELETE ) and DQL ( SELECT ) operations with SQLAlchemy taking a raw ( writing all SQL query ) approach.
This is a series where I document discoveries in my Journey to SQLAlchemy. If you haven't seen my previous posts, check them out - links are somewhere at the top and bottom of this page.
This journey would solely utilize
db/scripts/queries.py: contains queries to interact with the databasemain.py: application entrypoint.
To follow along, clone codebase from github
To understand codebase structure, visit SQLAlchemy with Docker - The Structure.
NOTE
This journey is quite technical and requires basic understanding of SQL. This guide focuses on SQLAlchemy processes to interact with databases.
Data Manipulation Language (DML) : with this language, we're able to interact with data in the database through clauses which allows us to add (CREATE), modify (UPDATE) and trash (DELETE) data records.
Data Query Language (DQL) is used to retrieve data from the database. This uses the SELECT clause.
Using SQL SELECT clause in SQLAlchemy
With SELECT clause, we're able to retrieve data from the database. We previously used this clause to retrieve tables available in our database.
Below code fragment contains a function that runs the SELECT clause to retrieve all records from Item table.
db/scripts/queries.py
# .... other code are above this function
def retrieve_all_item():
"""Retrieves all records from Item table"""
with create_connection() as conn:
results = conn.execute(
text("SELECT * FROM Item")
)
for result in results:
print(result)
When database is queried/interacted with, there're responses containing the effect of the interaction which are returned as an instance of Result object. In this case, we queried the database to return all records in Item table. The returned records are stored on Result instance variable results, automatically created on execution of SELECT statement. From above code, we iterate over results and output each record with print statement.
Using SQL INSERT clause in SQLAlchemy
We currently do not have any record in the database tables, with the help of SQLAlchemy, we'll execute CREATE clause to add some.
db/scripts/queries.py
# .... other code are above this function
def insert_item(name:str, category:str):
"""Inserts new record into Item table
param: name [str] Represents an item name to be added
param: category [str] Represents an item category
"""
with create_connection() as conn:
conn.execute(
text("INSERT INTO Item (name, category) VALUES (:name, :category)"),
{'name':name, 'category':category}
)
conn.commit()
Above code contains a function that adds a single record to Item table. To add a new record, the data would be collected from a source e.g a human user, automation e.t.c. which will be fed through named parameter
Explaining named parameter
conn.execute(
text("INSERT INTO Item (name, category) VALUES (:name, :category)"),
# ... collapsed code
)
SQLAlchemy recommends executing an external source (e.g human user, automation process) data using named parameter syntax denoted with :any_given_name when using textual construct. This is important for many reasons, most importantly, it mitigates against SQL injection. The named parameter from above code snippet are :name and :category
How is named parameter value passed ?
conn.execute(
text(...), # statement within text function are collapsed
{'name':name, 'category':category} )
To populate named parameters, a dictionary is passed as the second argument for conn.execute() function, where the key maps to the name assigned to the named parameters
main.py
from db.scripts.queries import (
# ........ other imported functions above
insert_item,
retrieve_all_item,
)
if __name__ == "__main__":
insert_item(name="Tesla Model S", category="Auto")
retrieve_all_item()
We imported insert_item and retrieve_all_item function and utilize them within if block. Running python main.py from terminal to execute the above code would insert some data to Item table and retrieve the inserted data.
SQLAlchemy can also handle multiple insertion with the INSERT clause. To do this, instead of passing a dictionary when feeding data to named parameter, a list of dictionary would be expected, where each dictionary will represent distinct rows and have keys that map appropriately to the expected named parameters. Add the following code to allow for multiple data insertion.
db/scripts/queries.py
from typing import List, Any, Dict
# .... other code are above this function
def insert_multiple_items(data:List[Dict[str, Any]]):
"""Allows insertion of multiple records into Item table
param: data [List] sequence of dictionary where each
dictionary represents a record to be added to
the db
"""
with create_connection() as conn:
conn.execute(
text("INSERT INTO Item (name, category) VALUES (:name, :category)"),
data
)
conn.commit()
To see multiple insertion in practice, we'll utilize insert_multiple_items function within main.py as shown below.
main.py
from db.scripts.queries import (
# ........ other imported functions above
insert_multiple_items,
)
if __name__ == "__main__":
insert_multiple_items([
{
'name': 'Iphone 14 Pro Max',
'category': 'mobile'
},
{
'name': 'Pizza',
'category': 'meal'
}
])
retrieve_all_item()
Running python main.py, would insert two new records into Item table (making total records to be 3 in Item table), thereafter all records would be outputted. Your result should be similar to that of the image below.
Using SQL UPDATE clause in SQLAlchemy
With an UPDATE clause, existing record can be modified. To update an existing record, the unique identifier for said record is required for explicit identification of the record.
We currently have three (3) records in Item table, we'll update the name value of the first record (with a unique identifier 1) from Tesla Model S to Tesla Starlink.
Below code shows function that processes an update on Item record existing in the database.
db/scripts/queries.py
# .... other code are above this function
def update_existing_item_name(item_id:int, new_value:Any):
"""Handles the update of an existing record name
in `Item` table
param: item_id [int] A unique identifier of the record to be
updated.
param: new_value [Any] Replacement value for previous `name` column value.
"""
with create_connection() as conn:
conn.execute(
text("UPDATE Item SET name=:update_value WHERE id=:id"),
{'id': item_id, 'update_value': new_value}
)
conn.commit()
Utilize this function within main.py by first importing it and then call it in the codebase as show below.
main.py
from db.scripts.queries import (
# ... there're other import statement above
update_existing_item_name,
)
if __name__ == "__main__":
update_existing_item_name(item_id=1, new_value="Tesla Starlink")
retrieve_all_item()
Running python main.py would update the previous value from "Tesla Model S" -> to Tesla Starlink for record with of
a unique identifier of 1. Note that an error would be raised for an unexistent identifier.
Using SQL DELETE clause in SQLAlchemy
DELETE clause handles removal of existing record from the database. Just like UPDATE clause, to delete a unique record, DELETE clause would require the unique identifier of record to be deleted. Below code shows an example function utilizing DELETE clause.
db/scripts/queries.py
def delete_item(id:int):
"""Removes an existing item record from `Item` table.
param: id [int] A unique identifier for item to be deleted
"""
with create_connection() as conn:
conn.execute(text("DELETE FROM Item WHERE id=:id"), {'id':id})
conn.commit()
When delete_item function is called within main.py, it deletes record whose id is given as a unique identifier on the function call.
main.py
from db.scripts.queries import (
# ... there're other import statement above
delete_item,
)
if __name__ == "__main__":
delete_item(1)
retrieve_all_item()
The above should delete an item whose unique identifier is 1 ( 1 is passed to delete_item() function call). If an unexistent identifier is given, an error would be raised.
Conclusion
SQLAlchemy at it's raw state (using text function and writing all queries) provides a very easy to follow process/structure to interacting with the database. There're little to no changes with construct when using DML, DQL, DDL clauses while interacting with the database.
Thank you for being AWESOME
✍️
- Encountered any bugs, typos or concept misinterpretation from this guide ? Let me know in the comment section below.
❤️
- Hit the LIKE button and FOLLOW me so you're notified on future discoveries.
☕
- A lot is accomplished with a cup of coffee
Thank you for your time and for staying with me on this one.
Top comments (0)