具有选择数据的SQLite3(在python中) [英] SQLite3 with selecting data (in python)

问题描述

我正在学习使用python进行一些编程，并使用了SQlite3 我一直遇到同样的问题，但我不知道出了什么问题.

I am learning myself some programming with python and make use of SQlite3 I keep running into the same problem, and I can't figure out what goes wrong.

我的餐桌设置

def user_table():
    data = lite.connect(database)
    dat = data.cursor()

    with data:
        dat.executescript("""CREATE TABLE IF NOT EXISTS Users(Id INTEGER PRIMARY KEY AUTOINCREMENT,
        'Username' UNIQUE,
        'Password',
        'Email',
        'UserCharacters_Id' INTEGER
        )""");

现在，我的代码选择一个用户名(用户名123存在并且表似乎正确(已通过SQLite Studio检查)

Now my code to select a Username (the username 123 exists and tables seem right (checked with SQLite studio)

database = 'test.db'
data = lite.connect(database)
dat = data.cursor()
with data:
    dat.execute("SELECT * FROM Users WHERE 'Username'='123'")
    user = dat.fetchone()   
    print user

我尝试了很多不同的方法，但是一直返回None. python部分似乎正常工作，只是SQL的select部分出错(已检查打印)

I tried a lot of different ways, but it keeps returning None. The python part seems to be working, just the select part of SQL goes wrong (checked with prints)

请帮帮我

推荐答案

在SQL中，单引号用于字符串，而表/列的名称则用双引号引起来. (SQLite支持后者的单引号，以在某些地方与MySQL兼容.)

In SQL, single quotes are used for strings, while table/columns names are quoted with double quotes. (SQLite supports single quotes for the latter for compatibility with MySQL in some places.)

您的查询将字符串Username与字符串123进行比较，并且此比较对于每条记录都会失败.

Your query compares the string Username against the string 123, and this comparison fails for every record.

使用此:

dat.execute('SELECT * FROM Users WHERE "Username" = \'123\'')

但是为了防止字符串格式化问题和SQL注入攻击，您应该使用参数:

But to prevent string formatting problems and SQL injection attacks, you should use parameters:

username = "123"
dat.execute('SELECT * FROM Users WHERE "Username" = ?', (username,))

这篇关于具有选择数据的SQLite3(在python中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！