How Can My sqllite3 interaction be fixed?

Question

I'm trying to get an admin account to edit a 'rank' (basically access level) for one of the profiles in my data-base. The error is:

Traceback (most recent call last):
  File "U:/A-level Computor Science/Y12-13/SQL/sqlite/Databases/ork task/Python for SQL V_2.py", line 154, in <module>
    main()
  File "U:/A-level Computor Science/Y12-13/SQL/sqlite/Databases/ork task/Python for SQL V_2.py", line 9, in main
    start_menu()
  File "U:/A-level Computor Science/Y12-13/SQL/sqlite/Databases/ork task/Python for SQL V_2.py", line 22, in start_menu
    login()
  File "U:/A-level Computor Science/Y12-13/SQL/sqlite/Databases/ork task/Python for SQL V_2.py", line 72, in login
    Mek_menu()
  File "U:/A-level Computor Science/Y12-13/SQL/sqlite/Databases/ork task/Python for SQL V_2.py", line 108, in Mek_menu
    where Uzaname = %s""" % (NewRank, Findaname))
sqlite3.OperationalError: unrecognized token: "0rk_D4T4B453"`

The code that seems to be the problem is:

cursor.execute(""" update 0rk_D4T4B453.Da_Boyz
                   set Rank = %s
                   where Uzaname = %s""" % (NewRank, Findaname))

Originally, it was all on one line and it didn't work, and now I've tried it on multiple lines and it still doesn't work. So I checked here to see if anyone could help.

EDIT1: Thanks for the suggestions. None of them have fixed the code, but I've narrowed the problem code to: where Uzaname = %s""" % (NewRank, Findaname))

Enclose `0rk_D4T4B453` inside backticks or square brackets: `update [0rk_D4T4B453].Da_Boyz set ....` — forpas, Oct 06 '20 at 13:37
@balderman The name of the database is 0rk_D4T4B453 and the name od the table is Da_Boyz in the example code it was database_name.table_name — james ghoulTB, Oct 06 '20 at 13:53
@forpas Unfortunatly neither of the tricks you suggested worked, I've narrowed down the code that's causing the problem to: `where Uzaname = %s""" % (NewRank, Findaname))` — james ghoulTB, Oct 06 '20 at 13:57
The error is: `unrecognized token: "0rk_D4T4B453"`, so it has nothing to do with the WHERE clause. — forpas, Oct 06 '20 at 14:44
Please show a fuller code block including `connect` line. We need to see how database is assigned. — Parfait, Oct 06 '20 at 18:14
@Parfait , The connect line is either```with sqlite3.connect("0rk_D4T4B453") as db: cursor = db.cursor()``` or ```conn = sqlite3.connect('jpg26(//evesham/users/Students/UserAreas/Year09)/A-level Computor Science/Y12-13/SQL/sqlite/Databases/ork task/0rk_D4T4B453.db') cursor = conn.cursor()``` — james ghoulTB, Oct 13 '20 at 11:20

score 0 · Accepted Answer · answered Oct 06 '20 at 18:34

Unless you use ATTACH, SQLite (a file-level database) does not recognize other databases. Usually server-level databases (Oracle, Postgres, SQL Server, etc.) use the database.schema.table reference. However, in SQLite the very database file you connect to is the main database in scope. But ATTACH allows you to connect to other SQLite databases and then recognizes database.table referencing.

Additionally, for best practices:

In sqlite3 and any other Python DB-APIs, use parameterization for literal values and do not format values to SQL statement.
In general Python, stop using the de-emphasized (not deprecated yet) string modulo operator, %. Use str.format or more recent F-string for string formatting. But neither is needed here.

Altogether, if you connect to the 0rk_D4T4B453 database, simply query without database reference:

conn = sqlite3.connect('/path/to/0rk_D4T4B453.db')
cursor = conn.cursor()

# PREPARED STATEMENT WITH QMARK PLACEHOLDERS
sql = """UPDATE Da_Boyz
         SET Rank = ?
         WHERE Uzaname = ?"""

# BIND WITH TUPLE OF PARAMS IN SECOND ARG
cursor.execute(sql, (NewRank, Findaname))
conn.commit()

If you do connect to a different database, call ATTACH. Here also, you can alias other database with better naming instead of number leading identifier.

cursor.execute("ATTACH '/path/to/0rk_D4T4B453.db' AS other_db")

sql = """UPDATE other_db.Da_Boyz
         SET Rank = ?
         WHERE Uzaname = ?"""

cursor.execute(sql, (NewRank, Findaname))
conn.commit()

cur.execute("DETACH other_db")

How Can My sqllite3 interaction be fixed?

1 Answers1