Database Interaction with SQLAlchemy - Raw DML & DQL
Tags: beginners, python, database, sql
Published: 2022-10-19 05:02:25

_Image credit to [Игорь Левченко](https://pixabay.com/users/stergo-4163614/)_

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 database

- `main.py`: application entrypoint.

> _To follow along, clone codebase from [github](https://github.com/spaceofmiah/practical-route-to-alchemy/tree/dde32f88b59dd9de3d1c03c50e73b539722daf83)_

> _To understand codebase structure, visit [SQLAlchemy with Docker - The Structure](https://dev.to/spaceofmiah/sqlalchemy-with-docker-the-structure-8hl)._

---

> _**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](https://dev.to/spaceofmiah/database-interaction-with-sqlalchemy-raw-ddl-tour-ie8#alter-x-rename-statement-in-action). 

Below code fragment contains a function that runs the SELECT clause to retrieve all records from `Item` table.

`db/scripts/queries.py`

```python
# ....         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`
```python
# ....         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**

```python
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 ?** 

```python
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`
```python
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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/an4vpuonf5f5r1glkcyq.png)


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`
```python
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`
```python
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.

![Multiple insertion into database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rin0x1bosuk6qux7bjv7.png)


## 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`
```python
# ....         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`
```python
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`
```python
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`
```python
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.

:coffee:
- A lot is accomplished with a cup of [coffee](https://www.buymeacoffee.com/spaceofmiah)

Thank you for your time and for staying with me on this one.