Databases
StackOverflow2013
Postsdbo
POParameter substitution for a SQLite "IN" clause
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POParameter substitution for a SQLite "IN" clause
    primarykey
    data
    text
    <p>I am trying to use parameter substitution with <a href="http://docs.python.org/library/sqlite3.html" rel="noreferrer">SQLite within Python</a> for an IN clause. Here is a complete running example that demonstrates:</p> <pre><code>import sqlite3 c = sqlite3.connect(":memory:") c.execute('CREATE TABLE distro (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)') for name in 'Ubuntu Fedora Puppy DSL SuSE'.split(): c.execute('INSERT INTO distro (name) VALUES (?)', [ name ] ) desired_ids = ["1", "2", "5", "47"] result_set = c.execute('SELECT * FROM distro WHERE id IN (%s)' % (", ".join(desired_ids)), ()) for result in result_set: print result </code></pre> <p>It prints out:</p> <blockquote> <p>(1, u'Ubuntu') (2, u'Fedora') (5, u'SuSE')</p> </blockquote> <p>As the docs state that "[y]ou shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack," I am hoping to use parameter substitution.</p> <p>When I try:</p> <pre><code>result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ (", ".join(desired_ids)) ]) </code></pre> <p>I get an empty result set, and when I try:</p> <pre><code>result_set = c.execute('SELECT * FROM distro WHERE id IN (?)', [ desired_ids ] ) </code></pre> <p>I get:</p> <blockquote> <p>InterfaceError: Error binding parameter 0 - probably unsupported type.</p> </blockquote> <p>While I hope that any answer to this simplified problem will work, I would like to point out that the actual query I want to perform is in a doubly-nested subquery. To wit:</p> <pre><code>UPDATE dir_x_user SET user_revision = user_attempted_revision WHERE user_id IN (SELECT user_id FROM (SELECT user_id, MAX(revision) FROM users WHERE obfuscated_name IN ("Argl883", "Manf496", "Mook657") GROUP BY user_id ) ) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USClinton Blackmore
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POParameter substitution for a SQLite "IN" clause
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POParameter substitution for a SQLite "IN" clause
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POParameter substitution for a SQLite "IN" clause
      1. USSimon Knight
      1. VTFavorite
    1. COThanks for all the answers. It made a lot of sense when I finally saw that I just needed a question mark for every parameter I am substituting.
      singulars
      1. POParameter substitution for a SQLite "IN" clause
      1. USClinton Blackmore
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload