python - 过滤 sqlite - 一个一个地执行 Action

Question

我正在开发一个与简单的 sqlite 数据库交互的 Python 程序。我正在尝试构建一个搜索工具，该工具能够根据用户输入交互地“过滤”数据库，然后返回与搜索匹配的行(项目)。例如...

我的 Python 程序(通过 if 语句、cgi.FieldStorage() 等)应该能够接受用户输入，然后搜索数据库。这是该程序的通用代码:

import cgitb; cgitb.enable()
import cgi
import sys
import sqlite3 as lite
import sys

con = lite.connect('bikes.db')
form = cgi.FieldStorage()
terrain_get = form.getlist("terrain")
terrains = ",".join(terrain_get)

handlebar_get = form.getlist("handlebar")
handlebars = ",".join(handlebar_get)

kickstand = form['kickstand'].value

如您所见，该部分是接收用户输入的部分；工作正常(我认为)。接下来，我需要帮助的地方:

if 'dirtrocky' not in terrains:
    FILTER the database to not return items that have "dirtrocky' in their terrain field

然后在程序的后期，我希望能够扩展我的过滤器:

if 'drop' not in handlebars:
    FILTER the database to, much like in previous one, not return items that have 'drop' in their 'handlebar' field

我的问题是，如何过滤数据库？理想情况下，我的最终结果应该是在我“过滤掉”上述内容后留下的行的 ID 元组。

谢谢!

Answer 1

首先，您应该定义您的数据库模式。最常见的方法是创建完全规范化的数据库，例如:

CREATE TABLE bikes (
    bike_id INTEGER AUTOINCREMENT PRIMARY KEY,
    manufacturer VARCHAR(20),
    price   FLOAT,
    ...
);

CREATE TABLE terrains (
    terrain_id INTEGER AUTOINCREMENT PRIMARY KEY,
    terrain VARCHAR(20),
    ...
);

CREATE TABLE handlebars (
    handlebar_id INTEGER AUTOINCREMENT PRIMARY KEY,
    handlebar VARCHAR(20),
    ...
);

CREATE TABLE bike_terrain (
    bike_id INTEGER,
    terrain_id INTEGER
);

CREATE TABLE bike_handlebar (
    bike_id INTEGER,
    handlebar_id INTEGER
);

请注意 bikes表不包含任何关于地形类型或 Handlebars 的信息:此信息将存储在连接表中，如 bike_terrain .

这个完全规范化的数据库使填充有点麻烦，但另一方面，它使查询更容易。

你如何查询它的多值字段？

您将需要动态构建 SQL 语句，如下所示:

SELECT
    b.manufacturer,
    b.price
FROM bikes b,
     terrains t,
     bike_terrain bt
WHERE b.bike_id    = bt.bike_id
  AND t.terrain_id = bt.terrain_id      
  AND t.terrain IN ('mountain', 'dirt', ...) -- this will be built dynamically
  ... -- add more for handlebars, etc...

通过动态构建 SQL 语句，几乎整个 WHERE 子句都必须动态构建和添加。

我强烈建议使用一些好的 SQLite GUI 来解决这个问题。在 Windows 上，SQLite Expert Personal 非常棒，在 Linux 上 sqliteman是很棒的。

一旦你填充了你的数据库并且它有超过几百行的东西，你应该添加适当的索引以便它快速运行。祝你好运!