CODEWITHSUNDEEP
pythonpython-3.x
shantanuo
shantanuo

Reputation: 32336

cursor does not return correct results

I have tried to execute a mysql query using 2 different ways as shown below:

Option 1:

bankd1= ("SELECT * FROM  Bank_loan  WHERE id in  (%s)" % placeholders)
cur.execute(bankd1)

Option 2:

bankd= "SELECT * FROM  Bank_loan WHERE id in  (%s)"
cur.execute(bankd, placeholders)

The option 1 always works correctly. But in case of option 2, there are times when it fails to show the results even if records exist in the database (as confirmed by option 1)

Is there anything I am missing about string replacement in python or how cursors handle parameters?

Update:

In the following case, option 1 works because there is an entry for ID 81220

Option 1:

placeholders = '81220, 63266'

Option 2:

placeholders = '63266, 81220'

Option 2 does not work because there is no entry for 63266. Mysql checks only the first item in the string.

How do I supply tuple instead of string?

Using the first option that works (bankd1) is easy. But I will like to know the correct way of supplying tuple to execute method.

Upvotes: 1

Views: 43

Answers (2)

AlxFlrr
AlxFlrr

Reputation: 11

I also tried many ways an the only that worked was the instruction directly from Python documentation as follows, to use thecur.executemany(initial_script_with_question_marks,list_of_tuples)

Yours should be something like:

placeholders= [
    ('81220, 63266')
]
cur.executemany(bankd, placeholders)

con.commit()  # Remember to commit the transaction if executing INSERT.

if it doesn work try reading more: https://docs.python.org/3/library/sqlite3.html#sqlite3-placeholders

Upvotes: 0

chepner
chepner

Reputation: 531758

cur.execute takes a sequence as its second argument; a single string will be treated as a sequence of one-character strings instead. Use an explicit tuple:

cur.execute(bankd, (placeholders,))

Upvotes: 1

Related Questions