Inserting python variable into SQlite Database

Question

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()

When I run your code, I receive a different error message: TypeError: function takes at most 2 arguments (3 given). Which is expected since you would have to give the query arguments as a list. — Klaus D.
– Klaus D., Commented Sep 16, 2017 at 2:50

mhawke · Accepted Answer · 2017-09-16 03:00:25Z

1

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.

edited Sep 16, 2017 at 3:00

answered Sep 16, 2017 at 2:50

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Klaus D. Over a year ago

An other mistake, but not the one responsible for the error message.

mhawke Over a year ago

@KlausD. OP reports a different message than that which is actually generated by the code posted, as you commented, incorrect number of arguments. This actual error message is due to the reason explained in this answer.

Tim Over a year ago

Hi, thank you for your help. I have updated my code in response to your answer. My code is now running without error but data is not being input to the sqlite database

mhawke Over a year ago

@Tim: you've removed the db.commit(), is that the problem?

Tim Over a year ago

Yes it is... Thanks so much for your help

Collectives™ on Stack Overflow

Inserting python variable into SQlite Database

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related