Python SQLite Parameter Substitution with Wildcards in Like

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.

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.

CS50: LIKE operator, variable substitution with % expansion

Pass the entire search string as the parameter to the LIKE operator:

results = db.execute(text("SELECT * FROM books WHERE title LIKE :search"),
                     {"search": f"%{search}%"}).fetchall();

or alternatively concatenate in the database:

results = db.execute(
    text("SELECT * FROM books WHERE title LIKE ('%' || :search || '%')"),
    {"search": search}).fetchall();

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.

Group values using wildcards

Use TOTAL() aggregate function which returns 0 instead of null with conditional aggregation:

SELECT District, 
       TOTAL(Religion LIKE '%Cath%' OR Religion = 'RC') Catholic, 
       TOTAL(Religion LIKE '%Pres%') Presbyterian
FROM tablename
GROUP BY District;

If you identify other cases not covered by the current conditions, you can add them inside TOTAL() with the operator OR.

But, what you should do is update the table to the correct values:

UPDATE tablename
SET Religion = CASE
  WHEN Religion LIKE '%Cath%' OR Religion = 'RC' THEN 'Catholic'
  WHEN Religion LIKE '%Pres%' THEN 'Presbyterian'
END
WHERE Religion NOT IN ('Catholic', 'Presbyterian');

Then your requirement is simpler:

SELECT District, 
       TOTAL(Religion = 'Catholic') Catholic, 
       TOTAL(Religion = 'Presbyterian') Presbyterian
FROM tablename
GROUP BY District;

See the demo.

Inserting records into Sqlite using Python parameter substitution where some fields are blank

I haven't checked that this works, but I think it should:

from collections import defaultdict
d = { 'ID' : 0, 'COL1' : 'hi' }
cursor.execute(sql_insert, defaultdict(str, d))

defaultdict is a specialised dictionary where any missing keys generate a new value instead of throwing a KeyError.

Of course this only works if all the values need the same default such as an empty string or None. If you need different defaults then you'll need a dictionary containing the defaults and you can do:

DEFAULTS = { ... whatever ... }
d = { 'ID' : 0, 'COL1' : 'hi' }
cursor.execute(sql_insert, dict(DEFAULTS).update(d))

Note that you must copy DEFAULTS each time so you can update the copy with the actual values.

---

Related Topics