[Tutor] SQLite LIKE question
Dinesh B Vadhia
dineshbvadhia at hotmail.com
Sat Apr 12 10:42:38 CEST 2008
Guys, I got it to work. The problem was to use pysqlite to search (in memory) a large number (>10,000) of string items containing the substring q (and to do it continuosly with different q's). The solution was to incase the substring q with % ie. '%q%'. The performance is excellent.
The code is in my recent post (Subject: pysqlite and functions) with a new problem ie. the code works as-is but not within a def function.
Date: Fri, 11 Apr 2008 13:20:12 +0100
From: Tim Golden <mail at timgolden.me.uk>
Subject: Re: [Tutor] SQLite LIKE question
Cc: tutor at python.org
Message-ID: <47FF577C.7090904 at timgolden.me.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Dinesh B Vadhia wrote:
> Okay, I've got this now:
>> con = sqlite3.connect(":memory:")
>> cur = con.cursor()
>> cur.execute("""CREATE TABLE db.table(col.a integer, col.b text)""")
>> con.executemany("""INSERT INTO db.table(col.a, col.b) VALUES (?, ?)""", m)
>> for row in con.execute("""SELECT col.a, col.b FROM db.table"""):
>> print row
>> # when run, all rows are printed correctly but as unicode strings
>> q = "dog"
>> for row in con.execute("""SELECT col.b FROM db.table WHERE col.b LIKE ? LIMIT 25""", q):
>> print row
> .. And, I get the following error:
> Traceback (most recent call last):
> for row in con.execute("SELECT col.b FROM db.table WHERE col.b LIKE ? LIMIT 25", q):
> ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.
Whenever you see this in a dbapi context, you can bet your socks
that you're passing a single item (such as a string, q) rather than
a list or tuple of items. Try passing [q] as the second parameter
to that .execute function and see what happens!
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Tutor