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)