Extracting course schedules from database

Courses
1

Hey folks, I’m having trouble with a SQL query in my Python code. I’m trying to get course info from a database, but I’m getting a syntax error. Here’s what I’m dealing with:

def fetch_class_schedule(db, class_code):
    query = '''SELECT Classes.Class FROM Classes 
               JOIN Schedule on Schedule.Class, Number, Day, Begin 
               FROM Schedule WHERE class = ?'''
    return execute_query(db, query, (class_code,))

When I run this, I get:

cursor.execute(query, params)
sqlite3.OperationalError: near "FROM": syntax error

I’m trying to get output like this:

print(fetch_class_schedule(db, "ECON101"))
# Should return something like:
# [('ECON101', '101', '2023-12-15', '09:00'), ('ECON101', '102', '2023-12-15', '09:00')]

Can someone help me fix this query? I think the JOIN part might be wrong, but I’m not sure how to correct it. Thanks!

2

yo ethan, i’ve been there too. SQL can be a pain sometimes! Here’s a quick fix for ya:

def fetch_class_schedule(db, class_code):
    query = '''SELECT c.Class, s.Number, s.Day, s.Begin 
               FROM Classes c
               JOIN Schedule s ON c.Class = s.Class
               WHERE c.Class = ?'''
    return execute_query(db, query, (class_code,))

This should do the trick. Let me know if u need anything else!

3

Hey Ethan85! :wave:

Looks like you’re wrestling with a tricky SQL query there. I feel your pain - I’ve been there before! :sweat_smile:

Your hunch about the JOIN part being off is spot on. The syntax for your JOIN clause is a bit wonky. Let me take a stab at fixing it for you:

def fetch_class_schedule(db, class_code):
    query = '''SELECT Classes.Class, Schedule.Number, Schedule.Day, Schedule.Begin 
               FROM Classes 
               JOIN Schedule ON Classes.Class = Schedule.Class
               WHERE Classes.Class = ?'''
    return execute_query(db, query, (class_code,))

This should give you the output you’re looking for. The key changes are:

  1. We’re now properly joining Classes and Schedule tables using the ON clause.
  2. We’re selecting the specific columns we want from both tables.
  3. The WHERE clause now uses Classes.Class to match your class_code parameter.

Give it a whirl and let me know if it works for you! If you’re still having issues, could you share a bit more about your table structures? Sometimes the devil’s in the details with these database queries.

Also, out of curiosity, what kind of app are you building with this? Sounds like it could be a cool class scheduling system! :thinking:

4

I’ve encountered similar issues when working with SQL queries in Python. Your problem seems to stem from incorrect JOIN syntax. Here’s a revised version that should work:

def fetch_class_schedule(db, class_code):
    query = '''SELECT Classes.Class, Schedule.Number, Schedule.Day, Schedule.Begin 
               FROM Classes 
               INNER JOIN Schedule ON Classes.Class = Schedule.Class
               WHERE Classes.Class = ?'''
    return execute_query(db, query, (class_code,))

This query properly joins the Classes and Schedule tables, selects the required columns, and filters by the class code. Make sure your table and column names match exactly. If you’re still encountering issues, double-check your database schema and table relationships. Additionally, verify that your execute_query function is correctly implemented to handle parameterized queries.

Let me know if this resolves your problem or if you need further assistance.