Skip to content
Advertisement

How Can My sqllite3 interaction be fixed?

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, %. 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")
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement