Search code examples
pythonsqliteapsw

In APSW why does this sql binding not work

So why does this work:

sql="""UPDATE """+stream+"""_pointer SET id = """+str(message.id)+"""; insert into """+stream+""" VALUES ("""+str(message.id)+""",'"""+safemessage+"""','pending')"""
cursor.execute(sql)

but with exactly the same variables this doesn't:

sql="UPDATE ?_pointer SET id = ?; insert into ? VALUES (?,?,?)"
cursor.execute(sql,(stream,message.id,stream,message.id,safemessage,'pending'))

The helpful error message I am getting with the second is:

---> 31                 cursor.execute(sql,(streampointer,message.id,stream,message.id,safemessage,'pending'))

SQLError: SQLError: near "?": syntax error

using the augmented traceback gives no more clues:

File "/tmp/ipykernel_20009/643992601.py", line 31, in <module>
    cursor.execute(sql,(streampointer,message.id,stream,message.id,safemessage,'pending'))
apsw.SQLError: SQLError: near "?": syntax error

I am using jupyter notebook lab on chromebook

Solution

  • From sqlite doc

    SQLite allows a parameter wherever a string literal, numeric constant, or NULL is allowed. (Parameters may not be used for column or table names.)