在Python中从SQLite表中读取原始数据

Question

我目前正在尝试制作一个登录系统，只是为了学习一些更高级的Python脚本。

最初，我使用.txt文件保存所有信息，但这样做很混乱且不安全。

在完成第一组脚本后，我决定尝试将信息保存到数据库文件中。我已经想出了如何从表中读取数据，但它会带着[(''),]符号。

这是个问题，因为我需要用原始数据来比较输入，而这不能与符号一起完成。

因此，我想知道是否可能将数据作为原始字符串读取，而不带符号。我已经在主项目的一个单独文件中进行了操作。以下是该文件的内容：

import sqlite3 as sql

con = sql.connect(r"E:\Users.db")
c = con.cursor()
con.row_factory = sql.Row
username = input("What is your username: ")

readdata = input("Select the message you wish to read: ")
c.execute("SELECT * FROM Mail WHERE (Subject LIKE '"+readdata+"') AND   (Recipient LIKE '"+username+"')")

component = c.fetchall()

for row in component:
    c.execute("SELECT Recipient FROM Mail WHERE (Subject LIKE '"+readdata+"') AND (Recipient LIKE '"+username+"')")
    rc = list(c.fetchall())
    rec = str(rc)
    c.execute("SELECT Sender FROM Mail WHERE (Subject LIKE '"+readdata+"') AND (Recipient LIKE '"+username+"')")
    snt = list(c.fetchall())
    sent = str(snt)
    c.execute("SELECT Subject FROM Mail WHERE (Subject LIKE '"+readdata+"') AND (Recipient LIKE '"+username+"')")
    sbject = list(c.fetchall())
    subject = str(sbject)
    c.execute("SELECT Message FROM Mail WHERE (Subject LIKE '"+readdata+"') AND (Recipient LIKE '"+username+"')")
    mg = list(c.fetchall())
    msg = str(mg)

    print()
    print("Sender: " + sent)
    print("Recipient: " + rec)
    print("Subject: " + subject)
    print()
    print("Message: " + msg)

以下是输出结果：

What is your username: James
Select the message you wish to read: LOL

Sender: [('Kieran',)]
Recipient: [('James',)]
Subject: [('LOL',)]

Message: [('Hey',)]

这是全部正确的数据，但我需要删除[(''),]才能进行比较。我正在使用Python 3.4.2。

- Kamg300

Answer 1

你目前的做法存在问题：你重复执行了4次相同的请求以获取4个属性，并且把输入数据拼接到查询字符串中，这可能导致SQL注入。

你应该这样重写它：

import sqlite3 as sql

con = sql.connect(r"E:\Users.db")
c = con.cursor()
con.row_factory = sql.Row
username = input("What is your username: ")

readdata = input("Select the message you wish to read: ")
c.execute("SELECT Sender, Recipient, Subject, Message FROM Mail WHERE (Subject LIKE ?) AND   (Recipient LIKE ?)", (readdata, username))

component = c.fetchall()

for row in component:
    (sent, rec, subject, msg) = tuple(row)
    print()
    print("Sender: " + sent)
    print("Recipient: " + rec)
    print("Subject: " + subject)
    print()
    print("Message: " + msg)

- Serge Ballesta

Answer 2

您有包含所有行的列表，每行都是一系列列。如果您只期望 一行，请仅获取一行：

rc = c.fetchone()

现在你只有一个包含列的元组。

你可以使用索引或元组赋值获取其中一列:

first_column = rc[0]

或者

first_column, = rc

请注意这里有一个逗号。

您可以将其与`c.fetchone（）`调用结合使用：

first_column_of_first_row, = c.fetchone()

你需要使用SQL参数来避免SQL注入（一种安全问题，攻击者接管了你的数据库），并使得数据库驱动能够在从查询中删除特定数据时优化查询。你应该使用占位符而不是字符串拼接（'<query part>' + data + '<query part'），并将添加引号留给数据库驱动：

c.execute(
    "SELECT Recipient FROM Mail WHERE (Subject LIKE ?) AND (Recipient LIKE ?)",
    (readdata, username))

现在，readdata 和 username 将会填入查询中的问号位置，包括适当的引用，还能处理数据本身中的引号转义。

对于 LIKE 查询，你可能需要包含 % 通配符：

c.execute(
    "SELECT Recipient FROM Mail WHERE (Subject LIKE ?) AND (Recipient LIKE ?)",
    ('%{}%'.format(readdata), '%{}%'.format(username)))

这里的str.format()调用在开头和结尾添加了%通配符。如果没有通配符，您可以使用COLUMNNAME = ?，因为在这种情况下，LIKE只会匹配整个列值。

接下来，由于您正在检索4个列，您可以在一个查询中获取所有这4个列，然后在循环中提取每行的结果。

c.execute(
    "SELECT Recipient, Sender, Subject, Message FROM Mail WHERE (Subject LIKE ?) AND (Recipient LIKE ?)",
    ('%{}%'.format(readdata), '%{}%'.format(username)))

for rec, sent, subject, msg in c:
    print("Sender: " + sent)
    print("Recipient: " + rec)
    print("Subject: " + subject)
    print()
    print("Message: " + msg)

我直接循环遍历 c 游标;除非您需要随机访问所有行的列表，否则不需要 fetchall() 调用。每行都由4列组成，因为 SELECT 按特定顺序请求了这些列，所以我们可以将它们直接分配给4个变量。

- Martijn Pieters

---

fetchone()是什么意思？:) 我之前总是做类似于 a = c.fetchall(); a.items()[0] 这样的操作。 - Remi Guan

@KevinGuan：它只获取一行数据，其余的结果稍后再获取。 - Martijn Pieters

@KevinGuan：请查看cursor.fetchone()文档。请注意，如果没有更多的结果可以获取，它将返回None。 - Martijn Pieters

Answer 3

fetchall 是遍历查询结果集的迭代器。使用 list 将此迭代器显式转换为元素列表。每个元素都是一个元组，其中包含您在查询中要求的列。

因此，获取所需元素应该像这样：rec = rc[0][0] 或者 sent = snt[0][0]，而不是 rec = str(rc) 或者 sent = str(snt)。

然而，您的查询和检索方式均可改进：

c.execute("SELECT Recipient, Sender, Subject, Message FROM Mail WHERE (Subject LIKE '"+readdata+"') AND (Recipient LIKE '"+username+"')")

将会在一次查询中获取到所有四个变量，这对您的数据库更加高效。

然而，请注意，使用字符串拼接将参数插入查询中容易受到SQL漏洞的影响。特别是如果变量的内容是由用户提交的。你应该像文档中所述，让sqlite驱动程序处理它:

c.execute("SELECT Recipient, Sender, Subject, Message FROM Mail WHERE (Subject LIKE ?) AND (Recipient LIKE ?)", (readdata, username))

使用c.fetchone（）将从此查询中仅检索一个结果，而无需将其转换为列表。

而且，将此结果同时影响到四个变量中，避免了手动使用索引来解包元组：

rec, sent, subject, msg = c.fetchone()

您可以进一步简化代码，只使用您的第一个查询，而无需每次执行新查询：

import sqlite3 as sql

con = sql.connect(r"E:\Users.db")
c = con.cursor()
con.row_factory = sql.Row
username = input("What is your username: ")

readdata = input("Select the message you wish to read: ")
c.execute("SELECT Recipient, Sender, Subject, Message FROM Mail WHERE (Subject LIKE ?) AND   (Recipient LIKE ?)", (readdata, username))

for rec, sent, subject, msg in c.fetchall():
    print()
    print("Sender: " + sent)
    print("Recipient: " + rec)
    print("Subject: " + subject)
    print()
    print("Message: " + msg)

- 301_Moved_Permanently