SQLite3 with selecting data (in python)

Question

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

CL. · Accepted Answer · 2013-02-26 12:18:18Z

2

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

answered Feb 26, 2013 at 12:18

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hans de Jong Over a year ago

Bows thanks a lot, (I was doing the last part in other parts, but to simplify I tested with the 123). This helps me out a lot (works now). Thanks :)

Niclas Nilsson Over a year ago

In that case, accept the answer :-) Press the symbol beneath the lower voting arrow.

Collectives™ on Stack Overflow

SQLite3 with selecting data (in python)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related