SQLite Northwind 1# Create Database

Step 1: Create a New Folder

• Create a folder named sqlite_db in drive E (or any preferred location on your computer). This folder will store your database files.

---

Step 2: Create a New Python Script

• Open a new Python script and save it as create_database_tables.py. This will be the file where you write all the code to create and manage tables in the database.

---

Step 3: Import sqlite3 Module and Error Class

• To interact with a SQLite database in Python, import the sqlite3 module, along with the Error class for handling exceptions.

    # create_database_tables.py
    import sqlite3
    from sqlite3 import Error
  

---

Step 4: Define connect_database() Method

• This method will establish a connection to the SQLite database file.

• The function connect_database() takes db_file as an argument, which is the path to the database file.

• It attempts to connect to the database. If successful, it returns the connection object. If it fails, it catches the error and prints it.

    def connect_database(db_file):
        conn = None
        try:
            conn = sqlite3.connect(db_file)
            return conn
        except Error as e:
            print(e)
  

---

Step 5: Define execute_sql() Method

• The execute_sql() function is designed to execute SQL commands.

• It takes two arguments: conn (the database connection object) and sql_table (the SQL command to be executed).

• This function creates a cursor object, executes the SQL command, and handles any errors if they occur.

    def execute_sql(conn, sql_table):
        try:
            c = conn.cursor()
            c.execute(sql_table)
        except Error as e:
            print(e)
  

---

Step 6: Define create_region_table() Method and Run It to Create the region Table

1. Define the Function create_region_table():

   • The function contains the SQL commands to:

     • Create the region table if it doesn’t exist.

     • Add an index for the regiondescription column.

     • Define triggers to automatically update the last_update column during INSERT and UPDATE.

Code for create_region_table():

def create_region_table():
    # Table structure for the region table
    sql = """
    CREATE TABLE IF NOT EXISTS region(
        regionid INTEGER PRIMARY KEY AUTOINCREMENT,
        regiondescription VARCHAR(50) NOT NULL,
        last_update TIMESTAMP NOT NULL
    );
    """

    # SQL command to create an index on regiondescription
    reg_index = """
    CREATE INDEX idx_reg_desc
    ON region(regiondescription);
    """

    # Trigger to update last_update column after INSERT
    reg_trigger_insert = """
    CREATE TRIGGER reg_trigger_ai
    AFTER INSERT ON region
    BEGIN
        UPDATE region SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    # Trigger to update last_update column after UPDATE
    reg_trigger_update = """
    CREATE TRIGGER reg_trigger_au
    AFTER UPDATE ON region
    BEGIN
        UPDATE region SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    # Return all the SQL commands
    return sql, reg_index, reg_trigger_insert, reg_trigger_update

---

2. Define the main() Function:

   • The main() function initializes the database path, connects to it, calls create_region_table() to fetch SQL commands, and executes them.

Code for main():

def main():
    db_file = r"E:\northwind\northwind.db"

    # Connect to the database
    conn = connect_database(db_file)

    if conn is not None:
        # Get SQL commands for the region table
        sql, reg_index, reg_trigger_insert, reg_trigger_update = create_region_table()

        # Execute the SQL commands to create the table, index, and triggers
        execute_sql(conn, sql)
        execute_sql(conn, reg_index)
        execute_sql(conn, reg_trigger_insert)
        execute_sql(conn, reg_trigger_update)

        print("The region table is successfully created.")
    else:
        print("Error! Cannot create the database connection.")

if __name__ == "__main__":
    main()

---

Explanation:

• SQL Commands:

  • CREATE TABLE: Defines the structure of the region table with three columns:

    • regionid: A primary key with auto-increment.

    • regiondescription: A text description of the region.

    • last_update: A timestamp to track updates.

  • CREATE INDEX: Adds an index on the regiondescription column for faster searches.

  • Triggers:

    • reg_trigger_ai: Automatically updates last_update when a new row is inserted.

    • reg_trigger_au: Automatically updates last_update when a row is updated.

• Python Functions:

  • create_region_table():

    • Returns all SQL commands needed for the region table.

  • main():

    • Connects to the database.

    • Calls create_region_table() to fetch SQL commands.

    • Executes the commands sequentially using execute_sql().

This step ensures the region table is created with additional functionality provided by the index and triggers.

Step 7: Define insert_region() Method and Run It to Insert Some Rows into the region Table

1. Import Required Libraries:

   • sqlite3 for database operations.

   • Error class from sqlite3 to handle exceptions.

   • datetime from the datetime module to get the current timestamp.

2. Define insert_region() Function:

   • This function is responsible for inserting rows into the region table.

   • query defines an SQL INSERT statement with placeholders (?) for each value.

3. Establish Database Connection:

   • The function connects to the database (northwind.db).

   • It uses executemany() to insert multiple records into the region table.

4. Handle Errors:

   • If an error occurs, it prints the error message.

5. Close Connections:

   • Ensures the cursor and connection are closed after execution.

Code for insert_region():

# insert_region.py
import sqlite3
from sqlite3 import Error
from datetime import datetime

db_file = r"E:\northwind\northwind.db"

def insert_region(region):
    query = """INSERT INTO region(regionid, regiondescription, last_update)
               VALUES(?, ?, ?)"""
    try:
        """Connect to SQLite Database"""
        conn = sqlite3.connect(db_file)
        myCursor = conn.cursor()
        myCursor.executemany(query, region)
        conn.commit()
    except Error as error:
        print("Error: {}".format(error))
    finally:
        myCursor.close()
        conn.close()

6. Specify Data to Insert:

   • main() defines the data to be inserted into the region table. Each record contains:

     • regionid: A unique ID for each region.

     • regiondescription: Name of the region.

     • last_update: The current timestamp formatted as %Y-%m-%d %H:%M:%S.

Code for Inserting Data Using main():

def main():
    region = [
        (1, 'Eastern', datetime.now().strftime("%Y-%m-%d %H:%M:%S")),
        (2, 'Western', datetime.now().strftime("%Y-%m-%d %H:%M:%S")),
        (3, 'Northern', datetime.now().strftime("%Y-%m-%d %H:%M:%S")),
        (4, 'Southern', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    ]
    insert_region(region)

if __name__ == "__main__":
    main()

---

Step 8: Define create_territories_table() Method and Run It to Create the territories Table

Breakdown of the Code:

1. Define the Function create_territories_table():

   • This function contains the SQL commands needed to create the territories table, including:

     • The table structure.

     • An index on regionid for efficient lookups.

     • Triggers for automatically updating the last_update column on INSERT and UPDATE.

Code for create_territories_table():

def create_territories_table():
    # Table structure for territories table
    sql = """
    CREATE TABLE IF NOT EXISTS territories(
        territoryid INTEGER PRIMARY KEY AUTOINCREMENT,
        territorycode VARCHAR(20) NOT NULL,
        territorydescription VARCHAR(50) NOT NULL,
        regionid INTEGER NOT NULL,
        last_update TIMESTAMP NOT NULL,
        CONSTRAINT fk_territories_region FOREIGN KEY (regionid)
        REFERENCES region(regionid)
        ON DELETE RESTRICT ON UPDATE CASCADE
    );
    """

    # SQL command to create an index on regionid
    terr_index = """
    CREATE INDEX idx_terr_reg
    ON territories(regionid);
    """

    # Trigger to update last_update column after INSERT
    terr_trigger_insert = """
    CREATE TRIGGER terr_trigger_ai
    AFTER INSERT ON territories
    BEGIN
        UPDATE territories SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    # Trigger to update last_update column after UPDATE
    terr_trigger_update = """
    CREATE TRIGGER terr_trigger_au
    AFTER UPDATE ON territories
    BEGIN
        UPDATE territories SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    return sql, terr_index, terr_trigger_insert, terr_trigger_update

---

2. Define the main() Function:

   • This function initializes the database path and establishes a connection.

   • It calls create_territories_table() to retrieve SQL commands.

   • It executes these commands sequentially to create the territories table, add the index, and set up the triggers.

Code for main():

def main():
    db_file = r"E:\northwind\northwind.db"

    # Create a database connection
    conn = connect_database(db_file)

    if conn is not None:
        # Get SQL commands for the territories table
        sql, terr_index, terr_trigger_insert, terr_trigger_update = create_territories_table()

        # Execute SQL commands
        execute_sql(conn, sql)
        execute_sql(conn, terr_index)
        execute_sql(conn, terr_trigger_insert)
        execute_sql(conn, terr_trigger_update)

        print("The territories table is successfully created.")
    else:
        print("Error! Cannot create the database connection.")

if __name__ == "__main__":
    main()

---

Explanation:

• Table Structure:

  • territoryid: Primary key with auto-increment.

  • territorycode: Unique code for the territory.

  • territorydescription: Description of the territory.

  • regionid: Foreign key linking to the region table.

  • last_update: Timestamp for tracking updates.

• Foreign Key Constraint:

  • Ensures that regionid in territories references regionid in the region table.

  • Uses ON DELETE RESTRICT and ON UPDATE CASCADE.

• Index:

  • Adds an index on the regionid column to improve query performance.

• Triggers:

  • terr_trigger_ai: Updates the last_update timestamp after INSERT.

  • terr_trigger_au: Updates the last_update timestamp after UPDATE.

---

Execution Steps:

1. Connect to the database using connect_database().

2. Define and execute the SQL commands to create the table, index, and triggers.

3. Print success or error messages depending on the outcome.

This code completes the creation of the territories table with all necessary features. Let me know if you'd like assistance running or testing it!

Step 9: Define insert_territories() Method and Run It to Insert Some Rows into the territories Table

---

1. Import Required Libraries

• sqlite3: For database operations.

• datetime: To insert timestamps.

• Error: To handle exceptions during SQL execution.

Code:

# insert_territories.py
import sqlite3
from sqlite3 import Error
from datetime import datetime

db_file = r"E:\northwind\northwind.db"

---

2. Define insert_territories() Function

• This function inserts multiple rows into the territories table using executemany().

• The SQL INSERT query has placeholders (?) for the values: territoryid, territorycode, territorydescription, regionid, and last_update.

Code:

def insert_territories(territories):
    query = """INSERT INTO territories(territoryid, territorycode, territorydescription, regionid, last_update)
               VALUES(?, ?, ?, ?, ?)"""
    try:
        """Connect to SQLite Database"""
        conn = sqlite3.connect(db_file)
        myCursor = conn.cursor()
        myCursor.executemany(query, territories)
        conn.commit()
    except Error as error:
        print(error)
    finally:
        myCursor.close()
        conn.close()

---

3. Define main() to Insert Rows

• The main() function initializes a list of rows for the territories table.

• Each row has the following structure:

  • territoryid: Auto-increment or None.

  • territorycode: Unique territory code.

  • territorydescription: Description of the territory.

  • regionid: Foreign key referencing regionid in the region table.

  • last_update: Current timestamp using datetime.now().

Example Data:

• Sample rows include:

  • (None, '01581', 'Westboro', 1, <current_timestamp>)

  • (None, '02139', 'Cambridge', 1, <current_timestamp>)

Code:

def main():
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    territories = [
        (None, '01581', 'Westboro', 1, now),
        (None, '01730', 'Bedford', 1, now),
        (None, '01833', 'Georgetown', 1, now),
        (None, '02139', 'Cambridge', 1, now),
        (None, '94025', 'Menlo Park', 2, now),
        (None, '98052', 'Redmond', 2, now),
        (None, '94040', 'Mountain View', 2, now),
        (None, '30346', 'Atlanta', 4, now),
        (None, '75234', 'Dallas', 4, now),
        (None, '94089', 'Santa Clara', 2, now)
    ]
    insert_territories(territories)

if __name__ == "__main__":
    main()

---

Explanation:

1. insert_territories():

   • Executes a batch insert of multiple rows into the territories table.

   • Uses executemany() for efficient bulk inserts.

2. main():

   • Initializes a list of territories with territoryid set to None (auto-incremented by SQLite).

   • Generates the current timestamp for the last_update column.

---

Step 10: Define create_employees_table() Method and Run It to Create the employees Table

1. Define the Function create_employees_table()

• This function contains the SQL command to create the employees table, including:

  • Table structure.

  • Indexes for efficient querying.

  • Triggers to automatically update the last_update field on INSERT and UPDATE.

Code for create_employees_table():

def create_employees_table():
    # Table structure for employees table
    sql = """
    CREATE TABLE employees (
        employeeid INTEGER PRIMARY KEY AUTOINCREMENT,
        lastname VARCHAR(20) NOT NULL,
        firstname VARCHAR(10) NOT NULL,
        title VARCHAR(30),
        titleofcourtesy VARCHAR(25),
        birthdate DATETIME,
        hiredate DATETIME,
        address VARCHAR(60),
        city VARCHAR(15),
        region VARCHAR(15),
        postalcode VARCHAR(10),
        country VARCHAR(15),
        homephone VARCHAR(24),
        extension VARCHAR(4),
        photo LONGBLOB,
        notes MEDIUMTEXT NOT NULL,
        reportsto INTEGER,
        photopath VARCHAR(255),
        salary FLOAT,
        last_update TIMESTAMP NOT NULL
    );
    """

    # Create index for lastname
    emp_last_name_index = """
    CREATE INDEX idx_emp_lname
    ON employees(lastname);
    """

    # Create index for postalcode
    emp_postal_index = """
    CREATE INDEX idx_emp_postal
    ON employees(postalcode);
    """

    # Trigger to update last_update column after INSERT
    emp_trigger_insert = """
    CREATE TRIGGER emp_trigger_ai
    AFTER INSERT ON employees
    BEGIN
        UPDATE employees SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    # Trigger to update last_update column after UPDATE
    emp_trigger_update = """
    CREATE TRIGGER emp_trigger_au
    AFTER UPDATE ON employees
    BEGIN
        UPDATE employees SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    return sql, emp_last_name_index, emp_postal_index, emp_trigger_insert, emp_trigger_update

---

2. Define the main() Function

• The main() function initializes the database connection, retrieves SQL commands, and executes them to create the employees table, indexes, and triggers.

Code for main():

def main():
    db_file = r"E:\northwind\northwind.db"

    # Create a database connection
    conn = connect_database(db_file)

    if conn is not None:
        # Retrieve SQL commands for the employees table
        sql, emp_last_name_index, emp_postal_index, emp_trigger_insert, emp_trigger_update = create_employees_table()

        # Execute SQL commands
        execute_sql(conn, sql)
        execute_sql(conn, emp_last_name_index)
        execute_sql(conn, emp_postal_index)
        execute_sql(conn, emp_trigger_insert)
        execute_sql(conn, emp_trigger_update)

        print("The employees table is successfully created.")
    else:
        print("Error! Cannot create the database connection.")

if __name__ == "__main__":
    main()

---

Explanation:

1. Table Structure:

   • Columns:

     • employeeid: Primary key with auto-increment.

     • lastname, firstname: Employee’s name.

     • title, titleofcourtesy: Job title and courtesy title (e.g., Dr., Mr.).

     • birthdate, hiredate: Date of birth and date of hiring.

     • address, city, region, postalcode, country: Contact information.

     • homephone, extension: Phone and extension details.

     • photo: Binary data for the employee’s photo.

     • notes: Additional text information about the employee.

     • reportsto: A foreign key-like column to indicate their supervisor.

     • salary: Employee’s salary.

     • last_update: Timestamp for tracking updates.

2. Indexes:

   • idx_emp_lname: Speeds up queries based on lastname.

   • idx_emp_postal: Speeds up queries based on postalcode.

3. Triggers:

   • emp_trigger_ai: Automatically updates last_update after an INSERT.

   • emp_trigger_au: Automatically updates last_update after an UPDATE.

4. Workflow:

   • The main() function connects to the database, retrieves the SQL commands from create_employees_table(), and executes them using execute_sql().

---

Step 11: Define insert_employees() Method and Run It to Insert Some Rows into the employees Table

1. Import Required Libraries

• sqlite3: For interacting with the SQLite database.

• datetime: To handle timestamp formatting.

• Error: To manage and display errors if they occur during SQL operations.

Code:

# insert_employees.py
import sqlite3
from sqlite3 import Error
from datetime import datetime

db_file = r"E:\northwind\northwind.db"

---

2. Define insert_employees() Function

• This function inserts multiple rows into the employees table using executemany().

• It includes an SQL INSERT statement with placeholders for all fields, accommodating data like employee details, titles, addresses, contact information, and more.

Code:

def insert_employees(employees):
    query = """INSERT INTO employees(employeeid, lastname, firstname, title, titleofcourtesy, birthdate, hiredate, address, city, region, postalcode, country, homephone, extension, photo, notes, reportsto, photopath, salary, last_update)
               VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
    try:
        """Connect to SQLite Database"""
        conn = sqlite3.connect(db_file)
        myCursor = conn.cursor()
        myCursor.executemany(query, employees)
        conn.commit()
    except Error as error:
        print(error)
    finally:
        myCursor.close()
        conn.close()

---

3. Define main() to Insert Rows

• The main() function initializes a list of employee records, each with details like employeeid, lastname, firstname, title, titleofcourtesy, and last_update timestamp using datetime.now().

• Sample records include a range of job titles and personal details to populate the employees table.

Example Data:

• Example rows contain structured data for each employee, such as:

  • (None, 'Davolio', 'Nancy', 'Sales Representative', 'Ms.', <birthdate>, <hiredate>, ...)

  • (None, 'Fuller', 'Andrew', 'Vice President', 'Dr.', <birthdate>, <hiredate>, ...)

Code:

def main():
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    employees = [
        (None, 'Davolio', 'Nancy', 'Sales Representative', 'Ms.', '1948-12-08', '1992-05-01', '507 - 20th Ave. E.Apt. 2A', 'Seattle', 'WA', '98122', '(206) 555-9857', '5467', None, 'Education includes a BA in psychology from Colorado State University in 1970. She also completed "The Art of the Cold Call."', 2, None, 2954.55, now),
        (None, 'Fuller', 'Andrew', 'Vice President', 'Dr.', '1952-02-19', '1992-08-14', '908 W. Capital Way', 'Tacoma', 'WA', '98401', '(206) 555-9482', '3457', None, 'Andrew received his BTS commercial in 1974 and a Ph.D. in international marketing.', None, None, 2254.49, now),
        (None, 'Leverling', 'Janet', 'Sales Representative', 'Ms.', '1963-08-30', '1992-04-01', '722 Moss Bay Blvd.', 'Kirkland', 'WA', '98033', '(206) 555-3412', '3355', None, 'Janet has a BS degree in chemistry from Boston College.', 2, None, 3119.15, now),
        (None, 'Peacock', 'Margaret', 'Sales Representative', 'Mrs.', '1937-09-19', '1993-05-03', '4110 Old Redmond Rd.', 'Redmond', 'WA', '98052', '(206) 555-8122', '5176', None, 'Margaret holds a BA in English literature from Concordia College.', 5, None, 1861.08, now),
        (None, 'Dodsworth', 'Anne', 'Sales Representative', 'Ms.', '1966-01-27', '1994-11-15', '7 Houndstooth Rd.', 'London', 'WG2 7LT', 'UK', '(71) 555-4444', '452', None, 'Anne has a BA degree in English from St. Lawrence College. She is fluent in French and German.', 5, None, 2333.33, now)
    ]
    insert_employees(employees)

if __name__ == "__main__":
    main()

---

Explanation:

1. insert_employees():

   • Accepts a list of tuples representing employee data and inserts them into the employees table.

   • Uses executemany() for efficient bulk insertion of records.

2. main():

   • Defines a list of employee records with all necessary fields.

   • Calls insert_employees() to insert the records into the database.

3. Data Fields:

   • Each employee entry contains fields like lastname, firstname, title, address, phone, salary, etc.

   • A timestamp (last_update) is added using datetime.now().

---

Step 12: Define create_employee_territories_table() Method and Run It to Create the employeeterritories Table

1. Define the Function create_employee_territories_table()

• This function defines the SQL commands needed to create the employeeterritories table, which associates employees with territories.

• It includes:

  • The table structure.

  • Foreign key constraints linking employeeid and territoryid to the employees and territories tables, respectively.

  • Triggers to automatically update the last_update column on INSERT and UPDATE.

Code for create_employee_territories_table():

def create_employee_territories_table():
    # Table structure for employeeterritories table
    sql = """
    CREATE TABLE employeeterritories (
        employeeid INTEGER NOT NULL,
        territoryid INTEGER NOT NULL,
        last_update TIMESTAMP NOT NULL,
        CONSTRAINT fk_territories_employees
        FOREIGN KEY (employeeid)
        REFERENCES employees(employeeid)
        ON DELETE RESTRICT ON UPDATE CASCADE,
        CONSTRAINT fk_territories_territories
        FOREIGN KEY (territoryid)
        REFERENCES territories(territoryid)
        ON DELETE RESTRICT ON UPDATE CASCADE
    );
    """

    # Trigger to update last_update column after INSERT
    empterr_trigger_insert = """
    CREATE TRIGGER empterr_trigger_ai
    AFTER INSERT ON employeeterritories
    BEGIN
        UPDATE employeeterritories SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    # Trigger to update last_update column after UPDATE
    empterr_trigger_update = """
    CREATE TRIGGER empterr_trigger_au
    AFTER UPDATE ON employeeterritories
    BEGIN
        UPDATE employeeterritories SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    return sql, empterr_trigger_insert, empterr_trigger_update

---

2. Define the main() Function

• The main() function establishes a connection to the database, retrieves SQL commands, and executes them to create the employeeterritories table, along with the associated triggers.

Code for main():

def main():
    db_file = r"E:\northwind\northwind.db"

    # Create a database connection
    conn = connect_database(db_file)

    if conn is not None:
        # Retrieve SQL commands for employeeterritories table
        sql, empterr_trigger_insert, empterr_trigger_update = create_employee_territories_table()

        # Execute SQL commands
        execute_sql(conn, sql)
        execute_sql(conn, empterr_trigger_insert)
        execute_sql(conn, empterr_trigger_update)

        print("The employeeterritories table is successfully created.")
    else:
        print("Error! Cannot create the database connection.")

if __name__ == "__main__":
    main()

---

Explanation:

1. Table Structure:

   • Columns:

     • employeeid: A foreign key referencing employeeid in the employees table.

     • territoryid: A foreign key referencing territoryid in the territories table.

     • last_update: A timestamp field to track updates.

   • Foreign Key Constraints:

     • employeeid and territoryid are enforced with ON DELETE RESTRICT and ON UPDATE CASCADE.

2. Triggers:

   • empterr_trigger_ai: Automatically updates last_update after an INSERT.

   • empterr_trigger_au: Automatically updates last_update after an UPDATE.

3. Workflow:

   • The main() function connects to the database, calls create_employee_territories_table() to retrieve SQL commands, and executes them to set up the table and triggers.

---

Step 13: Define insert_employee_territories() Method and Run It to Insert Rows into the employeeterritories Table

1. Import Required Libraries

• sqlite3: For database operations.

• datetime: To manage timestamps.

• Error: For error handling during SQL operations.

Code:

# insert_employee_territories.py
import sqlite3
from sqlite3 import Error
from datetime import datetime

db_file = r"E:\northwind\northwind.db"

---

2. Define insert_employee_territories() Function

• This function inserts multiple rows into the employeeterritories table.

• The SQL INSERT statement uses placeholders for employeeid, territoryid, and last_update to add each row.

Code:

def insert_employee_territories(employee_territories):
    query = """INSERT INTO employeeterritories(employeeid, territoryid, last_update)
               VALUES(?, ?, ?)"""
    try:
        """Connect to SQLite Database"""
        conn = sqlite3.connect(db_file)
        myCursor = conn.cursor()
        myCursor.executemany(query, employee_territories)
        conn.commit()
    except Error as error:
        print(error)
    finally:
        myCursor.close()
        conn.close()

---

3. Define main() to Insert Rows

• The main() function initializes a list of records for the employeeterritories table, each containing:

  • employeeid: Reference to an employee.

  • territoryid: Reference to a territory.

  • last_update: Current timestamp generated using datetime.now().

Example Data:

• The employee_territories list contains example data for associations:

  • (1, 17, <timestamp>)

  • (2, 29, <timestamp>)

  • (3, 35, <timestamp>)

Code:

def main():
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    employee_territories = [
        (1, 17, now),
        (2, 19, now),
        (3, 23, now),
        (4, 34, now),
        (5, 27, now),
        (6, 28, now),
        (7, 22, now),
        (8, 29, now),
        (9, 30, now),
        (9, 33, now)
    ]
    insert_employee_territories(employee_territories)

if __name__ == "__main__":
    main()

---

Explanation:

1. insert_employee_territories():

   • Inserts a list of employee-territory associations into the employeeterritories table.

   • Uses executemany() for bulk insertion.

2. main():

   • Defines a list of employee-territory records, where each entry represents an association between an employee and a territory.

   • Calls insert_employee_territories() to execute the insertion.

3. Data Fields:

   • Each entry includes:

     • employeeid: References an employee.

     • territoryid: References a territory.

     • last_update: A timestamp for when the record was last updated.

---

Step 14: Define create_shippers_table() Method and Run It to Create the shippers Table

1. Define the Function create_shippers_table()

• This function creates the shippers table and sets up:

  • Table structure.

  • Index on companyname.

  • Triggers for automatically updating the last_update field on INSERT and UPDATE.

Code for create_shippers_table():

def create_shippers_table():
    # Table structure for shippers table
    sql = """
    CREATE TABLE shippers (
        shipperid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        companyname VARCHAR(40) NOT NULL,
        phone VARCHAR(24),
        last_update TIMESTAMP NOT NULL
    );
    """

    # Index for companyname
    ship_name_index = """
    CREATE INDEX idx_ship_name
    ON shippers(companyname);
    """

    # Trigger to update last_update column after INSERT
    ship_trigger_insert = """
    CREATE TRIGGER ship_trigger_ai
    AFTER INSERT ON shippers
    BEGIN
        UPDATE shippers SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    # Trigger to update last_update column after UPDATE
    ship_trigger_update = """
    CREATE TRIGGER ship_trigger_au
    AFTER UPDATE ON shippers
    BEGIN
        UPDATE shippers SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    return sql, ship_name_index, ship_trigger_insert, ship_trigger_update

---

2. Define the main() Function

• The main() function establishes a connection to the database, retrieves SQL commands, and executes them to create the shippers table, as well as the associated index and triggers.

Code for main():

def main():
    db_file = r"E:\northwind\northwind.db"

    # Create a database connection
    conn = connect_database(db_file)

    if conn is not None:
        # Retrieve SQL commands for shippers table
        sql, ship_name_index, ship_trigger_insert, ship_trigger_update = create_shippers_table()

        # Execute SQL commands
        execute_sql(conn, sql)
        execute_sql(conn, ship_name_index)
        execute_sql(conn, ship_trigger_insert)
        execute_sql(conn, ship_trigger_update)

        print("The shippers table is successfully created.")
    else:
        print("Error! Cannot create the database connection.")

if __name__ == "__main__":
    main()

---

Explanation:

1. Table Structure:

   • Columns:

     • shipperid: Primary key with auto-increment.

     • companyname: Name of the shipping company.

     • phone: Contact number for the shipper.

     • last_update: Timestamp to track when the record was last updated.

2. Index:

   • idx_ship_name: An index on companyname for efficient lookups.

3. Triggers:

   • ship_trigger_ai: Automatically updates last_update after an INSERT.

   • ship_trigger_au: Automatically updates last_update after an UPDATE.

4. Workflow:

   • The main() function connects to the database, retrieves SQL commands from create_shippers_table(), and executes them to set up the table and triggers.

---

Step 15: Define insert_shippers() Method and Run It to Insert Some Rows into the shippers Table

1. Import Required Libraries

• sqlite3: For database operations.

• datetime: To handle timestamps.

• Error: For error handling during SQL operations.

Code:

# insert_shippers.py
import sqlite3
from sqlite3 import Error
from datetime import datetime

db_file = r"E:\northwind\northwind.db"

---

2. Define insert_shippers() Function

• This function inserts multiple rows into the shippers table.

• The SQL INSERT statement uses placeholders for shipperid, companyname, phone, and last_update to add each row.

Code:

def insert_shippers(shippers):
    query = """INSERT INTO shippers(shipperid, companyname, phone, last_update)
               VALUES(?, ?, ?, ?)"""
    try:
        """Connect to SQLite Database"""
        conn = sqlite3.connect(db_file)
        myCursor = conn.cursor()
        myCursor.executemany(query, shippers)
        conn.commit()
    except Error as error:
        print(error)
    finally:
        myCursor.close()
        conn.close()

---

3. Define main() to Insert Rows

• The main() function initializes a list of records for the shippers table, each containing:

  • shipperid: Unique ID for each shipper.

  • companyname: Name of the shipping company.

  • phone: Contact number for the shipper.

  • last_update: Current timestamp generated using datetime.now().

Example Data:

• The shippers list contains example data for shipping companies:

  • (1, 'Speedy Express', '(503) 555-9831', <timestamp>)

  • (2, 'United Package', '(503) 555-3199', <timestamp>)

  • (3, 'Federal Shipping', '(503) 555-9931', <timestamp>)

Code:

def main():
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    shippers = [
        (1, 'Speedy Express', '(503) 555-9831', now),
        (2, 'United Package', '(503) 555-3199', now),
        (3, 'Federal Shipping', '(503) 555-9931', now)
    ]
    insert_shippers(shippers)

if __name__ == "__main__":
    main()

---

Explanation:

1. insert_shippers():

   • Inserts a list of shippers into the shippers table.

   • Uses executemany() to efficiently add multiple rows in one go.

2. main():

   • Defines a list of shipper records with required fields.

   • Calls insert_shippers() to execute the insertion.

3. Data Fields:

   • Each entry includes:

     • shipperid: Unique ID for the shipper.

     • companyname: Name of the shipping company.

     • phone: Contact phone number.

     • last_update: A timestamp for when the record was last updated.

---

Step 16: Define create_customers_table() Method and Run It to Create the customers Table

---

1. Define the Function create_customers_table()

• This function defines the SQL commands needed to create the customers table, including:

  • Table structure with customer information fields.

  • Index on companyname.

  • Triggers to automatically update the last_update field on INSERT and UPDATE.

Code for create_customers_table():

def create_customers_table():
    # Table structure for customers table
    sql = """
    CREATE TABLE customers (
        customerid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        customercode VARCHAR(5) NOT NULL,
        companyname VARCHAR(40) NOT NULL,
        contactname VARCHAR(30),
        contacttitle VARCHAR(30),
        address VARCHAR(60),
        city VARCHAR(15),
        region VARCHAR(15),
        postalcode VARCHAR(10),
        country VARCHAR(15),
        phone VARCHAR(24),
        fax VARCHAR(24),
        last_update TIMESTAMP NOT NULL
    );
    """

    # Index for companyname
    cust_name_index = """
    CREATE INDEX idx_cust_name
    ON customers(companyname);
    """

    # Trigger to update last_update column after INSERT
    cust_trigger_insert = """
    CREATE TRIGGER cust_trigger_ai
    AFTER INSERT ON customers
    BEGIN
        UPDATE customers SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    # Trigger to update last_update column after UPDATE
    cust_trigger_update = """
    CREATE TRIGGER cust_trigger_au
    AFTER UPDATE ON customers
    BEGIN
        UPDATE customers SET last_update = DATETIME('NOW')
        WHERE rowid = new.rowid;
    END;
    """

    return sql, cust_name_index, cust_trigger_insert, cust_trigger_update

---

2. Define the main() Function

• The main() function establishes a connection to the database, retrieves SQL commands, and executes them to create the customers table, along with the associated index and triggers.

Code for main():

def main():
    db_file = r"E:\northwind\northwind.db"

    # Create a database connection
    conn = connect_database(db_file)

    if conn is not None:
        # Retrieve SQL commands for customers table
        sql, cust_name_index, cust_trigger_insert, cust_trigger_update = create_customers_table()

        # Execute SQL commands
        execute_sql(conn, sql)
        execute_sql(conn, cust_name_index)
        execute_sql(conn, cust_trigger_insert)
        execute_sql(conn, cust_trigger_update)

        print("The customers table is successfully created.")
    else:
        print("Error! Cannot create the database connection.")

if __name__ == "__main__":
    main()

---

Explanation:

1. Table Structure:

   • Columns:

     • customerid: Primary key with auto-increment.

     • customercode: Unique code for each customer.

     • companyname, contactname, contacttitle: Customer's business and contact details.

     • address, city, region, postalcode, country: Contact information fields.

     • phone, fax: Contact phone and fax numbers.

     • last_update: Timestamp to track record updates.

2. Index:

   • idx_cust_name: An index on companyname for efficient retrieval.

3. Triggers:

   • cust_trigger_ai: Automatically updates last_update after an INSERT.

   • cust_trigger_au: Automatically updates last_update after an UPDATE.

4. Workflow:

   • The main() function connects to the database, calls create_customers_table() to retrieve SQL commands, and executes them to set up the table and triggers.

---