I'm writing some scoring server using python and sqlite, and error occured when using update.
Python 2.7.11 (v2.7.11:6d1b6a68f775, Dec 5 2015, 20:40:30) [MSC v.1500 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from server import *
>>> db = DB_control()
>>> db.update_user_score("ZSPEF1", "FXVCWI", 180)
UPDATE score SET FXVCWI = 180 WHERE USER_ID = ZSPEF1
Error raised while updating ID ZSPEF1's score to 180. Rolling back DB...
DB Successfully rolled back
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "server.py", line 102, in update_user_score
musica_db.execute(update_score_str)
sqlite3.OperationalError: no such column: ZSPEF1
>>>
As you see, there are FXVCWI column and ZSPEF1 row and want to change that value, but error says there are no ZSPEF1 column.
UPDATE command only occurs error on update_user_score function.
Why this happens to me? Also, error sometimes occurs when first character in string is number. Is there any way to prevent this error?
Here is my code
#!/usr/bin/env python
import sqlite3
musica_db_file = sqlite3.connect("musica.db")
musica_db = musica_db_file.cursor()
class DB_control(object):
def setupDB(self):
#This function should execute only on first run.
try:
userDB_setupDB_str = "NUM INTEGER PRIMARY KEY AUTOINCREMENT, "
userDB_setupDB_str += "CARD_ID TEXT NOT NULL UNIQUE, "
userDB_setupDB_str += "NAME TEXT NOT NULL UNIQUE, "
userDB_setupDB_str += "PASSWORD TEXT NOT NULL, "
userDB_setupDB_str += "ADMIN INT NOT NULL DEFAULT 0"
songDB_setupDB_str = "NUM INTEGER PRIMARY KEY AUTOINCREMENT, "
songDB_setupDB_str += "SONG_ID INT NOT NULL UNIQUE, "
songDB_setupDB_str += "NAME TEXT NOT NULL UNIQUE, "
songDB_setupDB_str += "FINGERPRINT TEXT NOT NULL UNIQUE"
scoreDB_setupDB_str = "USER_ID TEXT NOT NULL UNIQUE"
musica_db.execute('CREATE TABLE user({0}) '.format(userDB_setupDB_str))
musica_db.execute('CREATE TABLE song({0}) '.format(songDB_setupDB_str))
musica_db.execute('CREATE TABLE score({0})'.format(scoreDB_setupDB_str))
musica_db_file.commit()
self.add_user(randomID(), 'MU_Admin', 'yj809042', admin=True) #Create admin account.
self.add_song(randomID(), 'Tutorial', randomID()) #Create tutorial(dummy) song
print("DB setuped.")
except:
print("Error raised while setuping DB")
raise
def update_user_score(self, cardID, songID, score):
try:
update_score_str = "UPDATE score SET {0} = {1} WHERE USER_ID = {2}".format(songID, score, cardID)
print update_score_str
musica_db.execute(update_score_str)
musica_db_file.commit()
print("User ID {0}'s score is now {1}.".format(cardID, score))
except:
print("Error raised while updating ID {0}'s score to {1}. Rolling back DB...".format(cardID, score))
self.rollback_DB()
raise
def rollback_DB(self):
try:
musica_db_file.rollback()
musica_db_file.commit()
print("DB Successfully rolled back")
except:
print("Error raised while rolling back DB. Critical.")
raise

"UPDATE score SET {0} = {1} WHERE USER_ID = '{2}'"many to many relationship