CODEWITHSUNDEEP
pythonmysqlsqlite
Tim
Tim

Reputation: 13

Inserting python variable into SQlite Database

I have seen some posts that suggesting using a ? as a place holder when inserting python variables into a SQL Query but all of these examples show the question mark at the end of the query followed by the python variable. What if you want to insert a python variable in the middle of a query and want to avoid SQL injection? I am using Python 3.6 and SQLite.

Update* - This code is working: id='13' text='YES'

db=sqlite3.connect('NEW_Inventory.sqlite')
cursor=db.cursor()
query=('''      
INSERT 
    OR REPLACE
INTO
    text (id, text)  
VALUES
    (?,
        (SELECT
           CASE 
              WHEN exists(SELECT 1  FROM text WHERE id=?)
              THEN 'good' 
              ELSE 'Hello' 
           END
         )
    )''')

cursor.execute(query, (id, id))
db.commit()

Upvotes: 1

Views: 253

Answers (1)

mhawke
mhawke

Reputation: 87064

You need to pass the parameters to execute() as a tuple. In your case you need to call it like this:

cursor.execute(query, (id, id))

where query is your parameterised SQL query string.

I assume that your code defines id somewhere, otherwise, execute() will try to use the builtin function id() to construct the query, resulting in another error.

It also worth mentioning that if you have only one parameter it must also be passed as a tuple like this (id,). Avoid the common mistake of this: (id) which is not a tuple.

Upvotes: 1

Related Questions