如何在python/sqlite3中通过变量在一个表中的两列（或更多列）中执行搜索

Question

大家好。潜伏在这里是一个很大的帮助-提前谢谢。 我要做的是接受用户的输入，然后在“mytable”表的“type”和“count”列中搜索与用户输入匹配的任何内容。你知道吗

这是我的密码：

import sys
import sqlite3 as lite

for arg in sys.argv:
    print arg

var = raw_input("What are you looking for: ")
print "Standby; looking for : ", var
vart = '%'+var+'%'  # to add wildcards to the var string

con = lite.connect('test.db')

print 
print "Ok. Here's what I found."
print

with con:
    cur=con.cursor()
    cur.execute( "SELECT * FROM mytable" )
#   cur.execute( "SELECT * FROM mytable WHERE type LIKE ( ? )", [vart]) # this actually works - but only searches type column
#   cur.execute( "SELECT * FROM mytable WHERE type LIKE ( ? ) OR WHERE count like ( ? )", [vart], [vart] ) fails
#   cur.execute( "SELECT * FROM mytable WHERE type LIKE ( ? ) UNION ALL SELECT * FROM mytable WHERE count LIKE ( ?)", [vart], [vart])

    rows = cur.fetchall()
    # now row has each line to deal with
    #print len(rows)    #prints the number of lines in the db
    for row in rows:
        #print len(row) # prints the number of items in the list
        # if var in row[0]... then print
        mstr=row[0]
        print mstr.encode('ascii'), row[1]

以下是微不足道的数据库：

type : count
fox|23
dog|34
cat|99
bird|123
rat|201
mouse|23
hedgehog|44
gnu|666

我成功地只在一列中搜索输入字符串，但当我尝试同时搜索两列时，失败了。必须有一种方法使用sqlite3函数，而不是依赖python函数。你知道吗

Answer 1

一个有效的非嵌套SQL SELECT语句只有一个WHERE语句；而且，如果要将多个参数传递给sqlite游标，它们必须包含在一个单列表中。代码的正确语法是：

cur.execute('SELECT * FROM mytable WHERE type LIKE ( ? ) OR count like ( ? )', [vart, vart])

Answer 2

只需修改一下语法，我就把python打扮得更加友好（更接近python3支持，尽管原始输入不是python3的本机输入）。从那以后，你应该可以扩展。。。。你知道吗

import sys
import sqlite3 as lite
'''
made your code more pep8 python like
note comments in python are reserved for
why not what..e.g. code is self descriptive
of what, but why is what is important
'''

print('{}'.format(sys.argv))  # debug

var = raw_input("What are you looking for: ")
print("Standby; looking for :{}".format(var))
vart = '%{}%'.format(var)

con = lite.connect('test.db')

print("\nOk. Here's what I found.\n")

with con:
    cur = con.cursor()
    sql_query = 'SELECT * FROM mytable WHERE type LIKE ? or count LIKE ?'
    cur.execute(sql_query, ['%{0}%'.format(var), '%{0}%'.format(var)])

    try:
        rows = cur.fetchall()
    except Exception as err:
        print(err)
    for row in rows:
        mstr = row[0]
        print('Found: {} : {}'.format(mstr.encode('ascii'), row[1]))

输出示例

host-wifi:java user$ python /tmp/p.py
['/tmp/p.py']
What are you looking for: 99
Standby; looking for :99

Ok. Here's what I found.

Found: cat : 99
host-wifi:java user$ python /tmp/p.py
['/tmp/p.py']
What are you looking for: 3
Standby; looking for :3

Ok. Here's what I found.

Found: fox : 23
Found: dog : 34
Found: bird : 123