Python with SQLite - How to Create SQL Tables (CREATE)

 

In this tutorial, we will build upon the database connection established in the previous post by creating tables within our SQLite database. For this example, we'll design a simple database for a bookstore. The database will consist of the following tables:

• Category

• Book

• Member

• Rented_Books

Below are the fields for each table:

Table Layouts

Category Table

• id: Unique identifier (integer)

• name: Name of the category (text)

Book Table

• id: Unique identifier (integer, primary key, auto-incremented)

• title: Title of the book (text)

• author: Author of the book (text)

• publisher: Publisher of the book (text)

• category: Foreign key linking to the Category table (integer)

• copies: Number of copies available (integer)

• added_on: Date the book was added (date)

Member Table

• id: Unique identifier (integer)

• name: Name of the member (text)

• phone: Phone number of the member (text)

• address: Address of the member (text)

• gender: Gender of the member (text)

Rented_Books Table

• id: Unique identifier (integer, primary key, auto-incremented)

• book_id: Foreign key linking to the Book table (integer)

• member_id: Foreign key linking to the Member table (integer)

• rented_on: Date the book was rented (date)

• return_date: Date the book is to be returned (date)

---

Creating Tables in Python

Creating the Category Table

with con:
    cur = con.cursor()
    cur.execute("""
        CREATE TABLE Category (
            id INTEGER PRIMARY KEY,
            name TEXT
        )
    """)

Creating the Book Table

with con:
    cur = con.cursor()
    cur.execute("""
        CREATE TABLE Book (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT,
            author TEXT,
            publisher TEXT,
            category INTEGER,
            copies INTEGER,
            added_on DATE,
            FOREIGN KEY (category) REFERENCES Category (id) ON DELETE CASCADE
        )
    """)

The FOREIGN KEY links the category field in the Book table to the id field in the Category table.

Creating the Member Table

with con:
    cur = con.cursor()
    cur.execute("""
        CREATE TABLE Member (
            id INTEGER PRIMARY KEY,
            name TEXT,
            phone TEXT,
            address TEXT,
            gender TEXT
        )
    """)

Creating the Rented_Books Table

with con:
    cur = con.cursor()
    cur.execute("""
        CREATE TABLE Rented_Books (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            book_id INTEGER,
            member_id INTEGER,
            rented_on DATE,
            return_date DATE,
            FOREIGN KEY (book_id) REFERENCES Book (id) ON DELETE CASCADE,
            FOREIGN KEY (member_id) REFERENCES Member (id) ON DELETE CASCADE
        )
    """)

 

Summary

We have now successfully created the tables for our bookstore database. Each table is interconnected using foreign keys, ensuring data integrity across the database. In the next tutorial, we will learn how to insert data into these tables and retrieve it using SQL queries.

Copyright Statement: Unless stated otherwise, all articles are original to this site, please credit the source when sharing.

Article link:http://pybeginners.com/sql-tutorial/python-with-sqlite-how-to-create-sql-tables-create/

License Agreement:Attribution-NonCommercial 4.0 International License