CODEWITHSUNDEEP
pythonsqlitepython-2.6
Kimbluey
Kimbluey

Reputation: 1339

What is the syntax for querying in Python?

I sort of understand how this works, but I get lost after the WHERE. Please tell me what is wrong with the following and help me understand what it's doing in general:

myVariable = cursor.execute("SELECT TARGET_.*, TARGET_, SOFTWARE_TARGET_ WHERE"
                           "SOFTWARE_TARGET_.SOFTWARE1=%(sw_ID)s AND"
                           "SOFTWARE_TARGET_.TARGET2=TARGET_.ID", sw_ID=sw_ID))

I keep getting the following error:

OperationalError: near "%": syntax error

Upvotes: 1

Views: 67

Answers (2)

Martijn Pieters
Martijn Pieters

Reputation: 1121624

You have no whitespace between the WHERE and the next word; the string concatenation doesn't add spaces. The same happens for the AND at the end of the second string:

>>> ("SELECT TARGET_.*, TARGET_, SOFTWARE_TARGET_ WHERE"
...  "SOFTWARE_TARGET_.SOFTWARE1=%(sw_ID)s AND"
...  "SOFTWARE_TARGET_.TARGET2=TARGET_.ID")
'SELECT TARGET_.*, TARGET_, SOFTWARE_TARGET_ WHERESOFTWARE_TARGET_.SOFTWARE1=%(sw_ID)s ANDSOFTWARE_TARGET_.TARGET2=TARGET_.ID'

It is easier to use a """ triple quoted multi-line string instead here:

myVariable = cursor.execute("""
    SELECT TARGET_.*, TARGET_, SOFTWARE_TARGET_ WHERE
    SOFTWARE_TARGET_.SOFTWARE1=%(sw_ID)s AND
    SOFTWARE_TARGET_.TARGET2=TARGET_.ID""", {'sw_ID': sw_ID})

Note that if you are using sqlite3 you can only use positional SQL parameters using the ? placeholder:

myVariable = cursor.execute("""
    SELECT TARGET_.*, TARGET_, SOFTWARE_TARGET_ WHERE
    SOFTWARE_TARGET_.SOFTWARE1=? AND
    SOFTWARE_TARGET_.TARGET2=TARGET_.ID""", (sw_ID,))

Quoting the sqlite3 documentation:

Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.)

Upvotes: 4

pdowling
pdowling

Reputation: 500

Martijns answer appears correct - but your edited answer doesn't. Note that you're using sw_id==sw_id, which evaluates to a boolean and doesn't really make sense here. You probably want to replace that with {"sw_id":sw_id} (just like Martijn does in his answer).

Upvotes: 1

Related Questions