Click here to Skip to main content
15,846,571 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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!

What I have tried:

Here is the code that I have so far:

This is the code that creates the database....
SQL
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:
Python
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:
Python
def infoB(year, sem):
    return cursor.execute("SELECT * FROM COURSES WHERE sem_year = ? AND semester = ?", [year, sem])


Here is the report file also:

Python
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)
Posted
Updated 19-Jun-20 16:24pm

1 solution

In infoB function,

def infoB(year, sem):
    return cursor.execute("SELECT * FROM COURSES WHERE sem_year = ? AND semester = ?", [year, sem])
why are there square brackets [] around the parameters year,sem ? ...

Does it work like this ?

def infoB(year, sem):
    return cursor.execute("SELECT * FROM COURSES WHERE sem_year = ? AND semester = ?", (year, sem))
?
 
Share this answer
 
Comments
tara lara 20-Jun-20 12:25pm    
Hi. Ok, I tried that but it's still not returning any values. Does anyone have any other idea?
Garth J Lancaster 20-Jun-20 19:08pm    
This site suggests you can used 'named parameters' http://zetcode.com/db/sqlitepythontutorial/ - look at parameterized_query2.py
tara lara 21-Jun-20 13:21pm    
Hello. I looked at the website. I re-wrote the function like this:

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

My function still is not returning anything. I tried doing this instead:

def infoB(year, sem):
return cursor.execute("SELECT * FROM COURSES WHERE sem_year = :year AND semester = :sem ", {"year": uyear, "sem": usem})

But I get an error with this. Can you refer me to another website?
Garth J Lancaster 21-Jun-20 22:10pm    
you do realise that
info = API.infoB(2018, 'FALL')
will give you a zero/nothing return right ?? look at your data 'Fall' vs what you are searching for 'FALL' .. you have to be a bit more precise - the computer can only do what you tell it, it doesnt know that 'Fall' != 'FALL'

I ran your code with
def infoB(year, sem):    return cursor.execute("SELECT * FROM COURSES WHERE sem_year = ? AND semester = ?", (year, sem))


and
info = API.infoB(2018, 'Fall')
and did get the correct result
tara lara 21-Jun-20 22:24pm    
Thank you so much!! I can't believe I didn't see that error the first time. Sorry about that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900