Category: Databases

Hello. I am making a database using SQLite. Then I need to make an API with functions that returns information about that database. Then I need to print out a report. However, when I try to return an answer to a query using inputs, nothing is returned. I either get an empty array or I get "<sqlite3.cursor object="" at="" 0x034d2d20="">". If I put default values in the query and not inputs, values are returned. Does anyone have any idea's on how I can fix this please (see below for more information)? Thank you in advance!

Here is the code that I have so far:

This is the code that creates the database....

import sqlite3

with sqlite3.connect('homework3.sq') as db:
    cursor = db.cursor()

cursor.execute(""" CREATE TABLE IF NOT EXISTS COURSES(
                    course_id       INTEGER              PRIMARY KEY,
                    department      varChar(100)         NOT NULL,
                    course_number   varChar(100)         NOT NULL,
                    course_name     varChar(100)         NOT NULL,
                    semester        varChar(100)         NOT NULL,
                    sem_year        varChar(20)          NOT NULL,
                    grade           varChar(1)           Not NULL)""")

cursor.execute("""CREATE TABLE IF NOT EXISTS PREREQUISITE(
                course_id       INTEGER              PRIMARY KEY,
                prereq1         Numeric(4)           NOT NULL,
                prereq2         Numeric(4)           NOT NULL,  
                ID REFERENCES COURSES("course_id")
                )""")

cursor.execute("""INSERT INTO COURSES (department, course_number, course_name, semester, sem_year, grade) VALUES 
                /*Sample Kpop Boy Bands- Present*/
                ('MATH', '1190', 'Calculus I', 'Summer', '2018', 'A'),
                ('CSE', '1322', 'Programming and Problem Solving', 'Fall', '2018', 'B'),
                ('CSE', '1322L', 'Programming and Problem Solving Lab', 'Fall', '2018', 'A'),
                ('CS', '3305', 'Data Structures', 'Spring', '2019', 'A'),
                ('CS', '3503', 'Computer Organization and Architecture', 'Spring', '2019', 'A'),  
                ('MATH','2202', 'Calculus II', 'Spring', '2019', 'B'),
                ('MATH', '2345', 'Discrete Mathematics', 'Fall', '2018', 'A'),
                ('CS', '3410', 'Introduction to Database Systems', 'Spring', '2020', 'A'),
                ('SWE', '3313', 'Introduction to Software Engineering', 'Spring', '2020', 'A'),
                ('CSE', '3801', 'Professional Practices and Ethics', 'Spring', '2020', 'A'),
                ('CS', '3502', 'Operating Systems', 'Fall', '2020', 'B'),
                ('CS', '4720', 'Internet Programming', 'Fall', '2020', 'A');""")

cursor.execute("""INSERT INTO PREREQUISITE(prereq1,prereq2) VALUES
                (0,0),
                (0,0),
                (0,0),
                (2,3),
                (2,3),
                (1,0),
                (0,0),
                (2,3),
                (2,3),
                (2,3),
                (4,5),
                (4,8)""")

db.commit()

This code is in the API file. This code works fine:

def infoA():
    return cursor.execute("SELECT * FROM COURSES WHERE sem_year = 2018 AND semester = 'Fall'")

But if I try to add parameters so that the function can accept inputs (as shown below), I don't get an answer:

def infoB(year, sem):
    return cursor.execute("SELECT * FROM COURSES WHERE sem_year = ? AND semester = ?", [year, sem])

Here is the report file also:

import API
# print line to dive results
print("=" * 111)

template = "|{:<5} | {:15} | {:15}| {:40} | {:10}|{:5}| {:>5}|"  # Writes the instructions on how to format column title
# writes the first column of the report by writing that column one is
row = template.format("ID", "Department", "Course Number", "Course Name", "Semester", "Year", "Grade")
print(row)

# print line to dive results
print("=" * 111)

# Gets function from API. When I run this function I get this: "[]"
info = API.infoB(2018, 'FALL') 

# If I run this, it will return information
# info = API.infoA()  

# If I run this using infoB, I get "<sqlite3.Cursor object at 0x034D2D20>"
# print(infoB.fetchall()) 

for data in info:
    id = data[0]
    depart = data[1]
    num = data[2]
    name = data[3]
    sem = data[4]
    class_year = data[5]
    class_grade = data[6]
    print(template.format(id, depart, num, name, sem, class_year, class_grade))

# print line to dive results
print("=" * 111)