python中sqlite3好用,具有选择数据的SQLite3（在python中）

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.

My table setup

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

)""");

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

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)

Please help me out

解决方案

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.)

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

Use this:

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

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

username = "123"

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