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 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()