[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.

Dinesh

..............................................................
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)
>> con.commit()
> 
>> 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!

TJG

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/tutor/attachments/20080412/fa306859/attachment.htm 


More information about the Tutor mailing list