Python SQLite and SQLAlchemy Database Exercise

Project Overview

This academic Jupyter Notebook demonstrates foundational database programming with Python. It uses the standard-library sqlite3 module and raw SQL to create and populate a file-backed SQLite database, then uses SQLAlchemy Core to reflect the existing table and query its records.

The notebook also contains two small Python module-import exercises before the database section. These exercises reference a local zoo module and are separate from the database workflow.

Learning Objectives

• Connect to a file-backed SQLite database
• Create a database table with raw SQL
• Insert records with SQL statements
• Commit database transactions
• Close a direct SQLite connection
• Connect to SQLite through SQLAlchemy
• Reflect an existing table with SQLAlchemy Core
• Select and sort records
• Fetch and iterate over query results

Notebook Contents

Module Import Exercises

The first two exercises demonstrate importing a local Python module:

• Importing zoo directly
• Importing zoo with the alias menagerie
• Calling the module’s hours() function

The required zoo.py file is not currently included in this repository. These cells are unrelated to the database exercise and cannot run from a clean clone without that module.

Database Exercise

The database section performs the following sequence:

1. Installs SQLAlchemy from inside the notebook.
2. Imports Python’s built-in sqlite3 module.
3. Opens or creates a file-backed SQLite database named books.db.
4. Creates a book table using raw SQL.
5. Inserts four book titles using raw INSERT statements.
6. Commits the database changes.
7. Closes the direct SQLite connection.
8. Creates a SQLAlchemy engine connected to the same database file.
9. Reflects the existing book table with SQLAlchemy Core.
10. Selects the title column in alphabetical order.
11. Fetches and prints the query results.

Database Schema

The notebook creates one table:

book

Field	Type	Constraint
id	INTEGER	PRIMARY KEY
title	TEXT	None explicitly defined

No additional constraints, relationships, or indexes are defined.

Verified Database Operations

Operation	Implemented?	Method
Connect to SQLite	Yes	sqlite3.connect('books.db')
Create a table	Yes	Raw SQL CREATE TABLE
Insert records	Yes	Raw SQL INSERT INTO
Select/read records	Yes	SQLAlchemy Core select()
Order query results	Yes	SQLAlchemy Core order_by()
Fetch query results	Yes	fetchall()
Commit changes	Yes	conn.commit()
Close direct SQLite connection	Yes	conn.close()
Update records	No	Not implemented
Delete records	No	Not implemented
Define ORM models	No	SQLAlchemy Core table reflection is used

The notebook demonstrates create, insert, and read operations. It is not a complete CRUD implementation.

Technology Stack

• Python
• Jupyter Notebook
• Python sqlite3
• SQLite
• SQL
• SQLAlchemy Core

Expected Query Output

The committed notebook displays the book titles in alphabetical order:

1984
Animal Farm
Brave New World
The Alchemist

Getting Started

Prerequisites

You will need:

• Python 3
• Jupyter Notebook
• SQLAlchemy

Install the required tools manually:

python -m pip install jupyter SQLAlchemy

Clone the Repository

git clone https://github.com/asmaayasser1/Module-4-and-Databases.git
cd Module-4-and-Databases

Open the Notebook

jupyter notebook

Open:

Module 4 and Databases.ipynb

The database section can be run independently after SQLAlchemy is installed. The earlier module-import exercises require zoo.py, which is not included in the repository.

Generated Database File

Running the database creation cell generates:

books.db

This is a file-backed SQLite database created in the notebook’s working directory. The database file is not committed to this repository.

Reproducibility Notes

The repository is not fully reproducible from a clean clone without additional context:

• zoo.py is required by the first two code cells but is not included.
• SQLAlchemy must be installed before the SQLAlchemy query can run.
• The database creation cell must run before the SQLAlchemy query cell.
• Table creation is not idempotent.
• Rerunning the creation cell after books.db and the book table already exist may produce a table-exists error.
• The saved execution counts are 2, 3, 5, 7, and 8, indicating that the notebook was not saved after a clean sequential top-to-bottom execution.

These conditions describe the repository’s current state; the notebook code and outputs have not been changed.

Current Limitations

• No update operation is implemented.
• No delete operation is implemented.
• SQLAlchemy ORM models are not used.
• No database migration workflow is included.
• No automated tests are provided.
• The SQLAlchemy connection is not explicitly closed.
• The notebook installs SQLAlchemy during execution rather than using a dependency manifest.
• The stored installation output is environment-specific.
• Unrelated module-import exercises are mixed with the database exercise.
• The required zoo.py module is absent.
• Repeated execution is not safely handled when the database table already exists.

Academic Context

This repository is an academic exercise focused on foundational Python module usage and database programming. It demonstrates direct SQLite access, basic SQL statements, and SQLAlchemy Core querying without presenting itself as a complete database application.

Project Status

Academic Python database project being refined for professional portfolio presentation.