Python - MySQL - Unknown columns 'http://www.......................' in 'field list'

Question

I'm trying to select a record from my MySQL db, the problem is that it returns with this error which i don't understand, i searched on the web for a solution, and there are many similar cases, but none apply to this specific one.

The expression i'm trying to execute is the following:

result = cursor.execute("""select * from urls where domain = '%s';"""%(found_url,))

it is in a try clause and it always goes right to the except giving me this error:

OperationalError(1054, "Unknown column 'http://..................' in 'field list'")

(i omitted the url, the dots act as placeholders)

after some cycles, being in a loop, it stops giving me that error and changes it to this:

ProgrammingError(2014, "Commands out of sync; you can't run this command now")

any idea? i'm going crazy on this one.

Answer 1

Use query parameters instead of how you are doing it with string interpolation. The protects you against SQL Injection attacks.

cursor.execute("""select * from urls where domain = %s""", (found_url,))

Notice the differences here:

• The %s is NOT quoted, as the MySQLdb library will handle this automatically
• You are not using string interpolation (no % after the string to replace your placeholders). Instead, you are passing a tuple as the second argument to the execute function.
• Since you are only using one place holder and you still need to pass a tuple, there is a comma to indicate it is a tuple (found_url,)