-1

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

moh19814
  • 133
  • 1
  • 1
  • 14

2 Answers2

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

DeepSpace
  • 78,697
  • 11
  • 109
  • 154
0

You can't have multiple where statements. I believe you want something more like:

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 = {}
and sd.class_id = {}
""" 
query_df = db_connection.query(student_info_query.format(std_id, cls_id))
stdunbar
  • 16,263
  • 11
  • 31
  • 53
  • Thanks a lot that indeed solve my problem but not I am getting another error "psycopg2.errors.UndefinedFunction: operator does not exist: character varying = integer" but my std_id is string and not integer. Also cls_id is string. – moh19814 Jun 09 '23 at 16:40
  • The problem is that your query string will look like `sd.id = 182` whereas it needs to have `sd.id = '182'` if the column in the DB is of string type. Rather than doing string substitution, it would be better to use a parametrised query, which takes care of aspects like quoting for you. It is also more secure (avoids SQL injection). See https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python – slothrop Jun 09 '23 at 16:53