Search code examples
pythonmysqlpymysql

PyMysql-Python:How to relate two inputs with a sql query?

Lets say for my question we have this table:

Actor1 Actor2 Movies
stef   bill   a
bill   stef   b
nick   nick   c
stef   stef   d
nick   bill   e
bill   nick   f

and we want to find the actors that played a movie and I give the name of the actors from the keyboard(input1,input2). Obviously if the input was one actor we would have something like this:

sql_query='select Actor1,Actor2 from movies where Actor1= {}'.format(input1)
cursor.execute(sql_query)

But when i have two inputs what should I do?

Solution

  • Use two placeholders in the query. You should also let cursor.execute() perform the placeholder substitution, as it uses a prepared statement, rather than string formatting, which is open to SQL-injection (you also forgot the quotes around {}, so you would have gotten an error).

    sql_query = 'select Actor1,Actor2 from movies where (Actor1= %s AND Actor2 = %s) OR (Actor1 = %s AND Actor2 = %s)'
cursor.execute(sql_query, (input1, input2, input2, input1))