python - 从 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。

Answer 1

你的做法很糟糕:你执行了 4 次相同的请求以获得 4 个属性，并且你将输入数据连接到查询字符串中，这可能导致 sql injection .

你应该重写它:

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)