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)