Sqlite Parameter Substitution Problem

SQLite parameter substitution problem

The Cursor.execute() method expects a sequence as second parameter. You are supplying a string which happens to be 8 characters long.

Use the following form instead:

self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", [item])

Python library reference: sqlite3 Cursor Objects.

SQLite parameter substitution and quotes

about """If I delete the quotes sourronding the ?, it works. But I want the quotes to remain there since I remember that there are cases where I need them."""

What you remember from when you were building the whole SQL statement yourself is irrelevant.

The new story is: mark with a ? each place in the SQL statement where you want a value substituted then pass in a tuple containing one value per ? -- it's that simple; the wrapper will quote any strings to make sure that they are acceptable SQL constants.

SQLite: Parameter and field name substitution with Python

In agreement with the answer from @SergeBallesta, you can do something like:

sql = 'SELECT {col_name} FROM structures WHERE id = ?'.format(col_name=colname)
cursor.execute(sql, [n])

Which uses a hybrid of the two conventions.

Using SQLite parameter substitution for values as well as NULL

In SQLite, the IS operator also handles non-NULL values:

c.execute("SELECT * FROM tab WHERE value IS ?", (anything,))

Parameter substitution for a SQLite with multiple “IN” clause

The number of ?s in the SQL query needs to match the number of items in the parameters tuple. In your first example there are two items in the tuple: names and ids. But there are four question marks. Try tuple(names) + tuple(ids), or define the variables as tuples using round brackets and then just use names + ids.

In the second example it's one tuple names containing two names, hence it works.

sqlite3 variable substitution not working python3

You must concatenate the placeholder ?:

SELECT DISTINCT date 
FROM schedule 
WHERE date BETWEEN DATETIME('NOW') AND DATETIME('NOW', '-' || ? || ' DAY') ORDER BY date

Parameter substitution for a SQLite IN clause

You do need the right number of ?s, but that doesn't pose a sql injection risk:

>>> result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' %
                           ','.join('?'*len(desired_ids)), desired_ids)
>>> print result_set.fetchall()
[(1, u'Ubuntu'), (2, u'Fedora'), (5, u'SuSE')]

SQLite3 pass column argument as variable

You cannot parametrize column names, but you can string substitute them.

For the parameter substitution be sure to use the right placeholder for SQLite3 which is a ?.

Then the second (2nd) argument to .execute() needs to be a sequence; here it is a one-tuple.

qry = """SELECT ID FROM vocabulary WHERE {col_} <= ?""".format(**values) 
crs.execute(qry, (datetime.datetime.now().isoformat(),))

Python SQLite parameter substitution with wildcards in LIKE

The quotes protect either ? or :name from being taken as a place-holder -- they're taken literally. You need to place the percent signs around the string you're passing, and use the plain placeholder without quotes. I.e.:

self.cursor.execute(
  "select string from stringtable where string like ? and type = ?",
  ('%'+searchstr+'%', type))

Note that neither ? is in quotes -- and that's exactly as it should be for them to be taken as placeholders.

---

Related Topics