Keyword search using python and sqlite

Question

I have a plugin to a game server that writes down changes made to a map.The database contains entries formatted like this - id INTEGER PRIMARY KEY,matbefore INTEGER, matafter INTEGER, name VARCHAR(50), date DATE. I am trying to create a function that, when given a column name, an integer, string, or tuple of an integer or string, and a keyword, will find the selected entries. so far, this is the code that I have come to -

def readdb(self,keyword,column,returncolumn = "*"):
    self.memwrite
    if isinstance(keyword, int) or isinstance(keyword,str):
        entry = [keyword]
    qmarks = ("? OR " * len(entry))[:-4]
    statement = 'SELECT all {0} FROM main WHERE {1} is {2}'.format(returncolumn,column,qmarks)
    print(qmarks)
    self.memcursor.execute(statement, entry)
    return(self.memcursor.fetchall())

keyword is a keyword to search for, column is teh column to search in, and returncolumn is the column to return So I was wondering why this code always fetches no rows, EG - Returns None, no matter what I put for the function. It seems to work fine if I do these things in the console, but not if I wrap them in a function

John Machin · Accepted Answer · 2010-11-08 03:05:36Z

1

If entry is a list (like in yesterday's question) , it's not going to work.

>>> returncolumn = "*"
>>> column = "name"
>>> entry = ["Able", "Baker", "Charlie"]
>>> qmarks = ("? OR " * len(entry))[:-4]
>>> statement = 'SELECT all {0} FROM main WHERE {1} is {2}'.format(returncolumn,
column,qmarks)
>>> print statement
SELECT all * FROM main WHERE name is ? OR ? OR ?

and what SQLite will see is:

SELECT all * FROM main WHERE name is 'Able' OR 'Baker' OR 'Charlie'

which is not valid syntax because you need =, not is.

Even if you fix that then (using an integer query for example):

SELECT all * FROM main WHERE id = 1 or 2 or 3

you will get mysterious results because that means WHERE ((id = 1) or 2) or 3), not what you think it does ... you need WHERE id = 1 or id = 2 or id = 3 or (reverting to yesterday's question) WHERE id IN (1,2,3)

edited Nov 8, 2010 at 3:05

answered Nov 8, 2010 at 1:51

John Machin

83.3k12 gold badges147 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Varriount Over a year ago

Ok, I see where you're going.. again, sorry for that other post.. I was being stupid, not giving all the info

unutbu · Accepted Answer · 2010-11-08 01:21:05Z

0

def readdb(self,keyword,column,returncolumn = "*"):
    self.memwrite                                             # 1. 
    if isinstance(keyword, int) or isinstance(keyword,str):   # 2.
        entry = [keyword]                                     # 3.
    qmarks = ("? OR " * len(entry))[:-4]                      # 4.
    statement = 'SELECT all {0} FROM main WHERE {1} is {2}'.format(returncolumn,column,qmarks)
    print(qmarks)                                             # 5.
    self.memcursor.execute(statement, entry)
    return(self.memcursor.fetchall())

Not sure what this is supposed to do. Did you mean self.memwrite()?
Can be changed to if isinstance(keyword, (int,str))

Or better yet, don't test the type. As you've written it, keyword can not be a unicode string. Why restrict like this? In this case I think it would be better to use a try...except... block to catch the subsequent error than to restrict type.
So at best len(entry) = 1. Notice also, its possible to never reach this line if keyword is not of type int or str. In that case, you would get an error on line (4) since entry would not be defined..
This could also be written as
```
qmarks = ' OR '.join(['?']*len(entry))
```
It avoids the need for the somewhat magic number 4 in ("? OR " *1)[:-4].
What are you seeing here? If it was empty, that should have been a clue. Also it would be worth running print(statement) to get a full picture of what is being sent to .execute().

Perhaps try

statement = 'SELECT {0} FROM main WHERE {1} = ?'.format(
             returncolumn,column)

In particular, the is should be changed to =.

edited Nov 8, 2010 at 1:21

answered Nov 7, 2010 at 23:48

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

2 Comments

John Machin Over a year ago

Your points 1 and 5 were made to the OP yesterday when he asked much the same question (with two different versions of code, none of which were the code he actually ran) and then deleted his question. I predict a difficulty with getting any actual error messages or a print of the SQL statement being executed; believe it or not, these allegedly come out on a server's console and scroll off before they can be captured. Have fun ...

Varriount Over a year ago

Answers to your questions : (1)- Yes, that was a typo, thanks for finding it, though it doesn't do anything relevant other than update the database with info from other(working) functions. What I get when I run this statement is None. I actually did have a print line, but I deleted it when posting the question.

Collectives™ on Stack Overflow

Keyword search using python and sqlite

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related