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))
Advertisement
Answer
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,
%
. Usestr.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")