pysqlite smart search

[Gabriel Genellina]

En Mon, 23 Feb 2009 06:17:41 -0200, klia <alwaseem307ster at yahoo.com>  
escribió:

> I need the program to have some smartness in search mechanism in which  
> the
> program will guess that the user is looking for these key words in the
> database.
>
> so far i came up with this but the search ain't smart, i have to write  
> the
> full key word in order to retrieve specific information.
>
> from pysqlite2 import dbapi2 as sqlite3
>
> connection = sqlite3.connect('Photos.db')
> memoryConnection = sqlite3.connect(':memory:')
> cursor = connection.cursor()
> prompt = 'Enter your search query?\n'
> keyword = raw_input(prompt)
> if cursor.execute('SELECT * FROM photos WHERE Tag LIKE ?',[keyword]):
> print cursor.fetchall()
> else:
> cursor.execute('SELECT * FROM photos WHERE Date LIKE ?', [keyword])
> print cursor.fetchall()

That's pretty good. SQL wildcard is '%' so try using '... where Tag like  
?', ('%'+keyword+'%',)
Also, the return value of cursor.execute is undefined in PEP249, and  
fetchall is a connection method, not cursor's. (Although this may work  
with SQLite, if you later decide to switch to another database it would be  
inconvenient).

cursor.execute('SELECT * FROM photos WHERE Tag LIKE ?', ('%'+keyword+'%',))
rows = cursor.fetchall()
if not rows:
   cursor.execute('SELECT * FROM photos WHERE Date LIKE ?', (keyword,))
   rows = cursor.fetchall()
print rows

-- 
Gabriel Genellina