Python SQL Query Error "psycopg2.errors.SyntaxError: syntax error at or near "where"

Question

I am trying to write a simple sql query to return columns if student_id is matched and if another column is true as following:

    student_info_query = """
    select student_age from students sd
    inner join registers rv on sd.id = rv.student_id
    where rv.is_paid is true and rv.is_accepted is true
    where sd.id = {}
    where sd.class_id = {}
    """ 
    query_df = db_connection.query(student_info_query.format(std_id, cls_id))

but I keep receiving error "psycopg2.errors.SyntaxError: syntax error at or near "where" LINE 5 where sd.id = 182" I also tried with rv.is_accepted = true and rv.is_accepted = TRUE, but still same error Not sure where the error is comming from. any ideas

Answer 1

Other than the syntax error pointed out by the other answers, don't use .format (or any other form of string concatenation or interpolation) when dealing with queries.

Instead, you should be using parametrized queries.

Not only this will let the database/ORM engine deal with types correctly, it will also protect your application from various SQL injection attacks.

Using a parametrized query this code would look something like (assuming you are using psycopg2)

student_info_query = """
    select student_age from students sd
    inner join registers rv on sd.id = rv.student_id
    where rv.is_paid is true and rv.is_accepted is true
    and sd.id = %s and sd.class_id = %s
""" 

curor = db_connection.cursor()
cursor.execute(student_info_query, (std_id, cls_id))
query_df = cursor.fetchone()

You should really read the docs: https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries