Okay so here's what I'm trying to do. I'm trying to build a database with three tables. Bookings, Trainers, and Trainings. So far, I've tried looking online, watching a ton of YouTube videos, but I my code doesn't seem to run. Also, How do I query for
- all the bookings of a specific trainer
- find the gender of the trainer for a type of training.
Heres my current 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):
"""
: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)
# Function to print Bookings Table
def print_table_bookings(conn, bookings):
sql=""" INSERT INTO bookings VALUES (booking_id,trainer_id,training_id,session_date,session_slot)
VALUES (?,?,?,?,?)"""
c = conn.cursor()
c.execute(sql,bookings)
conn.commit()
return c.lastrowid
# Function to print Trainers Table
def print_table_trainers(conn, trainers):
sql=""" INSERT INTO trainers VALUES (trainer_id,name,gender,mobile,specialisation,rate)
VALUES (?,?,?,?,?)"""
c = conn.cursor()
c.execute(sql,trainers)
conn.commit()
return c.lastrowid
# Function to print Trainings Table
def print_table_trainings(conn, trainings):
sql=""" INSERT INTO trainings VALUES (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
many_bookings = [
('0010','001','0001','01082021','Morning'),
('0011','002','0001','01082021','Morning'),
('0012','002','0001','01082021','Morning'),
('0013','002','0001','01082021','Morning'),
('0014','002','0001','01082021','Morning'),
('0015','003','0001','01082021','Morning'),
('0016','004','0001','01082021','Morning'),
('0017','003','0001','01082021','Morning'),
('0018','004','0001','01082021','Morning'),
('0019','004','0001','01082021','Morning')
]
c.executemany ("INSERT INTO bookings VALUES (?,?,?,?,?", many_bookings)
many_trainers = [
('001','Gary','Male','91234567','Weight Loss','85'),
('002','Bary','Male','91234568','Weight Loss','185'),
('003','Mary','Female','91234569','Weight Loss','85'),
('004','Stephanie','Female','91234570','Weight Loss','85'),
('005','Austin','Male','91234571','Weight Loss','65'),
('006','Tynia','Female','91234572','Weight Loss','85'),
('007','Oswald','Male','91234573','Weight Loss','55'),
('008','Aria','Fenale','91234574','Weight Loss','45'),
('009','Micheal','Male','91234575','Weight Loss','95'),
('010','Lily','Female','91234576','Weight Loss','105'),
('011','Janet','Female','91234577','Weight Loss','99'),
('012','Imane','Female','91234578','Weight Loss','68'),
]
c.executemany("INSERT INTO bookings VALUES (?,?,?,?,?", many_trainers)
many_trainings = [
('0001','Weight Loss','90'),
('0001','Weight Loss','90'),
('0001','Weight Loss','90'),
('0001','Weight Loss','90'),
('0001','Weight Loss','90'),
('0001','Weight Loss','90'),
('0001','Weight Loss','90'),
('0001','Weight Loss','90'),
('0001','Weight Loss','90'),
('0001','Weight Loss','90'),
]
c.executemany("INSERT INTO bookings VALUES (?,?,?,?,?", many_trainings)
#Displaying Table
print_table_bookings(conn)
print()
print_table_trainers()
print()
print_table_trainings()
print()
if __name__=='__main__':
main()
If there's a more efficient way of adding data to the tables, please I would love to get some feedback. I'm really new to coding but I've spent my entire weekend trying to fix it.
The error I get is on line 55
File "C:\Users\mikae\Desktop\Sem 9\CSE115\Assignment 2\Actual\A2 (Version 4).py", line 155, in main c.executemany ("INSERT INTO bookings VALUES (?,?,?,?,?", many_bookings)
OperationalError: incomplete input
