datafun-05-sql

Professional Python project: relational data and SQL analytics with a marimo app for parameterized queries (e.g. choose a region to update the chart)

Notebooks combine narration and code. This project works on related tabular data files using SQL and Python. It includes a reactive marimo app for interacting with the related data.

Note: With marimo, analysts can build interactive web apps! It's a whole new skill set, and not easy, but it does create engaging reports that showcase your analytic skills.

Motivation

We've mostly worked with data stored in files. Organizations often keep larger collections of related data in databases, where we can ask for the information we need instead of loading everything at once.

In this project, we'll use SQL to ask questions of data stored in a database. We'll select useful records, filter and organize results, summarize groups, and combine related information so it can be used in further analysis.

This Project

This project introduces relational data and SQL used when storing structured data in tables. Analysts are typically highly skilled at both SQL and Python.

Sample datasets are provided in the data/raw folder across several topic domains:

• retail - with regions / stores / employees (the worked example)
• library - with state / branch / books
• health - with clinic / patient / visit
• movies - using the MovieLens small dataset

Each domain has related tables. Run the retail example, and implement a similar project either by expanding the retail work, or choosing one of the other recommended domains.

Produced Artifacts

This project produces the same results in several useful forms.

• Reactive App (marimo)

  • run the analysis interactively in a browser

• Reactive Notebook (marimo)

  • view the Python source used to create the reactive app

Initial Results

Important Folders and Files

• data/* - raw CSV input files
• artifacts/ - generated database files, logs, or reports
• docs/ - project narrative and documentation
• src/datafun/ - project logic
• zensical.toml - update documentation site metadata

Common Workflow

Follow the step-by-step workflow guide carefully.

Challenges

Challenges are expected. Sometimes instructions may not quite match your operating system. When issues occur, share screenshots, error messages, and details about what you tried. Working through issues is part of implementing professional projects.

Success

After completing Phase 1. Start & Run, you'll have the example project, running on your machine. A new file project.log will appear in the root project folder and running the example script will print out:

===================================
END main() - Executed successfully!
===================================

Command Reference

The commands below are used in the workflow guide above. They are provided here for convenience.

Follow the guide for the full instructions.

Show command reference

In a machine terminal (open in your Repos folder)

Open a machine terminal in your Repos folder, change directory (cd) into the new folder, and run code . to open only this example project in VS Code:

git clone https://github.com/denisecase/datafun-05-sql

cd datafun-05-sql
code .

In a VS Code terminal

These are listed for convenience. For best results, follow the detailed instructions in pro-analytics-02 guide.

Use VS Code menu option Terminal / New Terminal to open a VS Code terminal in the root project folder. Copy each command, paste into your terminal, and hit ENTER, to run each command one at a time.

uv self update
uv python pin 3.14
uv python install
uv lock --upgrade
uv sync

uv run pre-commit install
uv run pre-commit autoupdate

git add -A
uv run pre-commit run --all-files
# repeat if changes were made by pre-commit tasks
git add -A
uv run pre-commit run --all-files

# run the Python module
uv run python -m datafun.app

# run marimo nb as a reactive app
# press Ctrl + C in the terminal to exit
uv run marimo run src/datafun/notebook.py

# Or: run marimo nb as a notebook
uv run marimo edit src/datafun/notebook.py

# do chores
uv run ruff format .
uv run ruff check . --fix
uv run ty check
uv run python -m pytest
uv run python -m zensical build

# save progress as you work
git add -A
git commit -m "your message here"
# repeat if changes were made (try the UP ARROW)
git add -A
git commit -m "your message here"

git push -u origin main

Helpful Tips

• Use the UP ARROW and DOWN ARROW in the terminal to scroll through past commands.
• Use CTRL+f to find (and replace) text within a file.

Much Can Be Ignored

• You do not need to add to or modify tests/. Tests are recommended and provided for example only.
• Many files are silent helpers. Explore as you like, but most files are never touched.
• You do NOT need to understand everything; let understanding build over time.

As Needed

If VS Code does not automatically use the new .venv environment:

1. Open the Command Palette (Ctrl+Shift+P).
2. Run Python: Select Interpreter.
3. Select the interpreter from this project's .venv folder.

If VS Code still does not recognize the environment or newly installed tools:

1. Open the Command Palette (Ctrl+Shift+P).
2. Run Developer: Reload Window.

Troubleshooting >>>

If you see something like this in your terminal: >>> or ... You accidentally started Python interactive mode. It happens. Press Ctrl c (both keys together) or Ctrl+Z then Enter on Windows.

Documentation

• Documentation

Data Card

• Palmer Penguins Data Card

Annotations

• .annotations/annotations.md

Citation

• CITATION.cff

License

This project is licensed under the MIT License.