Basic Operations and Integration in Python & SQLite
Tags: python, sql
Published: 2024-06-06 04:14:52

In this blog post, I aim to cover some fundamental operations and integration techniques for using Python with SQL. Given my tendency to be a bit forgetful, this blog will serve as a handy reminder for setting up a Python SQLite environment from scratch, and I hope it will benefit you too!

I will be using a recent restaurant food order app which I did for my phase project as an example in this post. You can fork this project using this [link](https://github.com/sherylmichaela/phase-3-restaurant-food-order-app.git) to follow along.

---
## How to connect Python to SQLite Database?
1. Create the root directory. i.e. `restaurant-food-order-app`
2. Create a sub-directory and name it `lib`. All your `.py` files will live here.
3. Inside the `lib` folder, create a `models.py` file.
4. Inside Terminal, navigate to the `/restaurant-food-order-app/` directory. Install dependencies such as SQLAlchemy and Alembic using `pipenv install sqlalchemy alembic`. The `pipenv` tool is used for managing Python dependencies, virtual environments, and `Pipfile` dependencies.
5. Run `pipenv shell` to run the virtual environment.
6. Import required modules, initialise the database and define the models. You can refer to my `models.py` file.
7. Initialise Alembic by running `alembic init migrations`.
8. Locate and open the `alembic.ini` file. Look for `sqlalchemy.url` setting. Modify the URL which points to your database. i.e. `sqlalchemy.url = sqlite:///lib/data.db`
9. Locate and open the `env.py` file. Look for `target_metadata = None` and replace it with:
```
from models import Base
target_metadata = Base.metadata
```

- `from models import Base` imports the base class for your SQLAlchemy models.
- `target_metadata = Base.metadata` sets Alembic’s target metadata to the collection of table definitions from your SQLAlchemy models, enabling Alembic to manage and synchronise your database schema based on these definitions.

Once all of the above is done, run `alembic revision --autogenerate -m "Create models"`. This is used to create a new Alembic revision file that includes automatically generated migration scripts. Then, run `alembic upgrade head` to upgrade to the latest database version. Run `alembic current` to check the current database version you're in.

---
## How to set up a SQLAlchemy engine and session for interacting with a SQLite database?
Create a `config.py` file inside the `lib` folder. Include the following code:
```
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("sqlite:///lib/data.db")
Session = sessionmaker(bind=engine)
session = Session()
```
- `create_engine` is a function from SQLAlchemy that creates a new database engine instance.
- `sessionmaker` is a factory for creating new Session objects, which are used to interact with the database.
- `create_engine("sqlite:///lib/data.db")` creates a new SQLAlchemy engine that connects to a SQLite database located at lib/data.db.
- `sessionmaker(bind=engine)` creates a configurable session factory, Session, which will create Session objects that are bound to the engine. Binding the session to the engine means that any session created by this factory will use the specified engine to connect to the database.
- `Session()` creates an instance of a session object from the session factory. This session object is used to interact with the database. You can use it to execute queries, add and delete objects, and commit transactions.

Once done, you can import the above in your other `.py` files to interact with your database by including the following code:
```
from config import session
```

---
## SQL Queries and CRUD Operations in Python
With everything set in place, you can now proceed to perform various database operations. In this example, I will be working with the `Customer` object which has the following attributes: `first_name`, `last_name` and `mobile`.

- **Adding an Object:** 
This code adds a new Customer object to the database and commits the transaction.
```
new_customer = Customer(first_name="John", last_name="Doe", mobile="0412345678")
session.add(new_customer)
session.commit()
```

- **Querying the Database:** 
This code retrieves all Customer objects from the database and prints their first and last names.
```
customers = session.query(Customer).all()
for customer in customers:
   print(f"{customer.first_name} {customer.last_name}")
```

- **Updating an Object:** 
This code updates the first name of the first user found with the mobile "0412345678" to "Andy" and commits the transaction.
```
customer = session.query(Customer).filter(mobile="0412345678").first()
customer.first_name = "Andy"
session.commit()
```

- **Deleting an Object:** 
This code deletes the user named "Andy Doe" from the database and commits the transaction.
```
customer = session.query(Customer).filter(first_name="Andy", last_name="Doe").first()
session.delete(customer)
session.commit()
```