Comprehensive Django SQLite and SQL Tutorial Introduction

Comprehensive Django SQLite and SQL Tutorial

Introduction

In this tutorial, we will take a deep dive into the world of databases, particularly focusing on how to work with databases in Django using SQLite. This tutorial is designed for beginners and intermediate learners who want to master the basics of databases, SQL, and how to integrate them with Django.

Objectives

• Understand what a database is and the different types of databases.
• Learn about database tables, columns, rows, and data types.
• Understand primary keys and foreign keys.
• Set up SQLite with Django.
• Explore SQLite using DB Browser.
• Learn basic SQL queries for retrieving, filtering, and manipulating data.
• Master advanced SQL concepts like aggregation, grouping, and joins.

Prerequisites

Before you begin, ensure you have the following:

• A basic understanding of Python programming.
• Django installed (version 4.0 or later recommended).
• Visual Studio Code (VS Code) or any other code editor.
• DB Browser for SQLite installed.

Understanding Databases and SQL

What is a Database?

A database is a structured collection of data that allows you to store, manage, and retrieve information efficiently. Databases can be of various types, but in this tutorial, we focus on relational databases.

Types of Databases

Django officially supports the following databases:

• PostgreSQL
• MariaDB
• MySQL
• Oracle
• SQLite (File-based, simplest to set up)

Example:

Imagine a simple Library System. The database might have tables like:

• Books: Stores information about each book (Title, Author, ISBN).
• Members: Stores information about library members (Name, Email, Membership Date).
• Loans: Tracks which members have borrowed which books (MemberID, BookID, Loan Date).

Working with SQLite in Django

Setting Up SQLite

SQLite is the default database for Django projects. When you create a new Django project, it automatically sets up SQLite as your database with a file named db.sqlite3.

Configuring SQLite in Django

1. Go to your Django project’s settings.py file.
2. Locate the DATABASES setting. By default, it looks like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}
        

1. This configuration tells Django to use SQLite for the database, with the database file named db.sqlite3 in your project directory.

Example:

• Create a new Django project:

django-admin startproject myproject
        

• Navigate to the project directory:

cd myproject
        

• Run migrations to create the database:

python manage.py migrate
        

Using SQLite Database Browser

Example:

• Open the db.sqlite3 file using DB Browser for SQLite.
• Browse the auth_user table to see default user records.

Introduction to SQL

Example SQL Queries

• Retrieve all records from a table:

SELECT * FROM books;
        

• Retrieve specific columns:

SELECT title, author FROM books;
        

• Filter records using WHERE clause:

SELECT * FROM books WHERE author = 'J.K. Rowling';
        

Advanced SQL Queries

Using Aggregation Functions

• SUM: Calculate the total value of a column.

SELECT SUM(price) FROM books;
        

• AVG: Calculate the average value of a column.

SELECT AVG(rating) FROM reviews;
        

Example:

• Calculate the total number of loans for each member:

SELECT member_id, COUNT(*) AS total_loans FROM loans GROUP BY member_id;
        

Performing Joins in SQL

Example:

• Join members table with loans table to list all members with their borrowed books.

SELECT m.name, b.title
FROM members m
INNER JOIN loans l ON m.id = l.member_id
INNER JOIN books b ON l.book_id = b.id;
        

Practical Exercises

1. Create and Explore Tables:
2. Basic Queries:
3. Advanced Queries:
4. Join Queries:

Conclusion

In this tutorial, you have learned:

• What databases are and the types supported by Django.
• How to use SQLite with Django and configure it.
• How to perform basic and advanced SQL queries.
• How to use JOINs to combine data from multiple tables.

With these skills, you are well-equipped to start working with databases in Django projects. Continue exploring more advanced database topics as you build your projects.