Python: How do I query to pull data from sqlite3 table?

Question

I'm trying to get my code to work but I keep getting this error.

File "C:\Users\mikae\Desktop\Sem 9\CSE115\Assignment 2\Actual\A2 (Version 5).py", line 186, in main print_table_bookings(conn,booking_data[i])

IndexError: list index out of range

Currently, this is my code. I'm not done with it, but I'm trying to write out two queries:

Find all the bookings of a specific trainer
Find the gender of the trainer for a type of training.

I've tried a lot of variations but I just can't get the logic down. I would appreciate feedback and any help that I can get.

Code

import sqlite3
from sqlite3 import Error
import os

# Create a connection to the SQLite database via DB file.
def create_connection(db_file):
    """create a database connection to the SQLite database
    specified by db_file
    :param db_file:database file
    :return:Connection object or None
    """
    
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)
    return conn


# Create tables from SQL statement
def create_table(conn,create_table_sql):
    """create a table from the create_table_sql statement
    :param conn:Connection object
    :param create_table_sql:a CREATE TABLE statement
    :return:
    """

    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)
    
# Bookings Table
def print_table_bookings(conn, bookings):
    sql=""" INSERT INTO bookings (booking_id,trainer_id,training_id,session_date,session_slot)
    VALUES (?,?,?,?,?)"""
    c = conn.cursor()
    c.execute(sql,bookings)
    conn.commit()
    return c.lastrowid

# Trainers Table
def print_table_trainers(conn, trainers):
    sql=""" INSERT INTO trainers (trainer_id,name,gender,mobile,specialisation,rate)
    VALUES (?,?,?,?,?,?)"""
    c = conn.cursor()
    c.execute(sql,trainers)
    conn.commit()
    return c.lastrowid

# Trainings Table
def print_table_trainings(conn, trainings):
    sql=""" INSERT INTO trainings (training_id,description,duration)
    VALUES (?,?,?)"""
    c=conn.cursor()
    c.execute(sql,trainings)
    conn.commit()
    return c.lastrowid

# Print Tables
# Print Bookings
def display_bookings(conn):
    c=conn.cursor()
    c.execute("SELECT * FROM bookings")
    rows=c.fetchall()
    for row in rows:
        print(row)
        print("\n") 
        
# Print Bookings
def display_trainers(conn):
    c=conn.cursor()
    c.execute("SELECT * FROM trainers")
    rows=c.fetchall()
    for row in rows:
        print(row)
        print("\n")
        
# Print Bookings
def display_trainings(conn):
    c=conn.cursor()
    c.execute("SELECT * FROM trainings")
    rows=c.fetchall()
    for row in rows:
        print(row)
        print("\n")
        
# Query to display Trainer's bookings by Trainer's Name
# NOT DONE

# Main Code
def main():
    database = os.path.abspath(os.getcwd()) + "\healthhub.db"

    # Create Bookings Table
    sql_create_bookings_table = """CREATE TABLE IF NOT EXISTS bookings (
                                booking_id INTEGER PRIMARY KEY,
                                trainer_id INTEGER NOT NULL,
                                training_id TEXT,
                                session_date INTEGER NOT NULL,
                                session_slot TEXT NOT NULL,
                                FOREIGN KEY(trainer_id) REFERENCES trainer(id),
                                FOREIGN KEY(training_id) REFERENCES training(id)
                                );"""

    # Create Trainer Table
    sql_create_trainers_table = """CREATE TABLE IF NOT EXISTS trainers (
                                trainer_id INTEGER PRIMARY KEY,
                                name TEXT NOT NULL,
                                gender TEXT NOT NULL,
                                mobile TEXT NOT NULL,
                                specialisation TEXT NOT NULL,
                                rate INTEGER NOT NULL
                                );"""

    # Create Trainings Table
    sql_create_trainings_table = """CREATE TABLE IF NOT EXISTS trainings (
                                training_id INTEGER PRIMARY KEY,
                                description TEXT NOT NULL,
                                duration INTEGER NOT NULL
                                );"""

    # Create a Database(DB) Connection
    conn = create_connection(database)

    # Create Tables using def above
    if conn is not None: 
        create_table(conn,sql_create_bookings_table)
        create_table(conn,sql_create_trainers_table)
        create_table(conn,sql_create_trainings_table)

        with conn:
            # Error prevention
            c=conn.cursor()
            c.execute("DELETE FROM bookings;");
            c.execute("DELETE FROM trainers;");
            c.execute("DELETE FROM trainings;");

            # Populating the tables
            # Bookings table
            booking_1 = (101,'001','0001','01082021','Morning')
            booking_2 = (102,'001','0001','01082021','Morning')
            booking_3 = (103,'001','0001','01082021','Morning')
            booking_4 = (104,'001','0001','01082021','Morning')
            booking_5 = (105,'001','0001','01082021','Morning')
            booking_6 = (106,'001','0001','01082021','Morning')
            booking_7 = (107,'001','0001','01082021','Morning')
            booking_8 = (108,'001','0001','01082021','Morning')
            booking_9 = (109,'001','0001','01082021','Morning')
            booking_10 = (110,'001','0001','01082021','Morning')

            # Trainers Table
            trainer_1 = (2021,'Gary','Male','91234567','Weight Loss','85')
            trainer_2 = (2022,'Bary','Male','91234568','Weight Loss','185')
            trainer_3 = (2023,'Mary','Female','91234569','Weight Loss','85')
            trainer_4 = (2024,'Stephanie','Female','91234570','Weight Loss','85')
            trainer_5 = (2025,'Austin','Male','91234571','Weight Loss','65')
            trainer_6 = (2026,'Tynia','Female','91234572','Weight Loss','85')
            trainer_7 = (2027,'Oswald','Male','91234573','Weight Loss','55')
            trainer_8 = (2028,'Aria','Female','91234574','Weight Loss','45')
            trainer_9 = (2029,'Micheal','Male','91234575','Weight Loss','95')
            trainer_10 = (2030,'Lily','Female','91234576','Weight Loss','105')


            #trainings table
            trainings_1 = (3031,'Weight Loss','90')
            trainings_2 = (3032,'Cardio','90')
            trainings_3 = (3033,'Boxing','90')
            trainings_4 = (3034,'Kickboxing','90')
            trainings_5 = (3035,'Muay Thai','90')
            trainings_6 = (3036,'Kettlebells','90')
            trainings_7 = (3037,'Strength training','90')
            trainings_8 = (3038,'Yoga','90')
            trainings_9 = (3039,'Sparring','90')
            trainings_10 = (3040,'Jiu-jitsu','90')

            #Loop to write data into table
            booking_data = [booking_1,booking_2,booking_3,booking_4,booking_5,booking_6,booking_7,booking_8,booking_9,booking_10]
            trainer_data = [trainer_1,trainer_2,trainer_3,trainer_4,trainer_5,trainer_6,trainer_7,trainer_8,trainer_9,trainer_10]
            training_data =  [trainings_1,trainings_2,trainings_3,trainings_4,trainings_5,trainings_6,trainings_7,trainings_8,trainings_9,trainings_10]
            i=0
            while i < 20:
                print_table_bookings(conn,booking_data[i])
                print_table_trainers(conn,trainer_data[i])
                print_table_trainings(conn,training_data[i])
                i=i+1 
            
            
            #Displaying Table
            print_table_bookings(conn)
            print()
            print_table_trainers(conn)
            print()
            print_table_trainings(conn)
            print()
                
            
if __name__=='__main__':
    main()

CaMMelo · Accepted Answer · 2021-03-22 16:14:06Z

1

here:

booking_1 = (101,'001','0001','01082021','Morning')

...

booking_data = [booking_1,booking_2,booking_3,booking_4,booking_5,booking_6,booking_7,booking_8,booking_9,booking_10]

...

create_table(conn,booking_data[i])

the booking_data[i] will never be a string containing a SQL instruction. Here you probably want to transform this tuple into a INSERT statement before you execute it.

answered Mar 22, 2021 at 16:14

CaMMelo

1476 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mikael Chan Over a year ago

How do I do that? Maybe could you show me? In my first version, I tried using INSERT but the code didn't work either.

CaMMelo Over a year ago

you can create a function that takes a booking tuple and return a string like f"INSERT INTO BOOKING VALUES ({tuple[0]}, {tuple[1]}, {tuple[2]}, {tuple[3]}, {tuple[4]});"

forpas · Accepted Answer · 2021-03-22 16:13:35Z

0

In main() after you create the tables, you are trying to insert new rows with this loop:

i=0
while i < 20:
    create_table(conn,booking_data[i])
    create_table(conn,trainer_data[i])
    create_table(conn,training_data[i])
    i=i+1

in which you use create_table() instead of print_table_bookings(), print_table_trainers() and print_table_trainings().

Change to this:

i=0
while i < 20:
    print_table_bookings(conn,booking_data[i])
    print_table_trainers(conn,trainer_data[i])
    print_table_trainings(conn,training_data[i])
    i=i+1

answered Mar 22, 2021 at 16:13

forpas

165k10 gold badges50 silver badges84 bronze badges

3 Comments

Mikael Chan Over a year ago

I updated the code, now I'm getting another error. I'll update the post.

forpas Over a year ago

@MikaelChan if you are getting another error this means that the problem you described in your question is solved. This code answers the question as you posted it. If you have a new requirement you can post a new question.

Mikael Chan Over a year ago

I see, okay sorry! I'm pretty new to this. I'll post a new question.

Collectives™ on Stack Overflow

Python: How do I query to pull data from sqlite3 table?

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related