Tag Archives: python sqllite

How to solve the problem of potentially unsupported type in Python sqllite

Write a program to insert data into a table of sqllite, the general code is as follows:

class GameIdiom(object):
	def __init__(self, id, idiom_id=None, all_word=None, play_time=None, game_id=None):
		self.id = id
		self.idiom_id = idiom_id
		self.all_word = all_word
		self.play_time = play_time
		self.game_id = game_id
	def isNext(self, first_word):
		return self.all_word[len(self.all_word)-1] == first_word
	def copyFromIdiom(self, idiom):
		self.idiom_id = idiom.id
		self.all_word = idiom.all_word
....
class IdiomDao(object):
	def insertGameIdiom(self, gameIdiom):
		self.con.execute("insert into game_idiom(id, idiom_id, all_word, play_time, game_id) values(?,?,?,?,?)",(gameIdiom.id,gameIdiom.idiom_id,gameIdiom.all_word,gameIdiom.play_time,gameIdiom.game_id,))
...
class Game(object):
    gameIdiom = GameIdiom(id=uuid4(),play_time=time(),game_id=self.id)
    self.idiomDao.insertGame(self)

Error in execution:

sqlite3.InterfaceError: Error binding parameter 0 – probably unsupported type

Because it was the first time that Python was used to insert data into sqllite, I initially suspected that there was a problem in using this kind of placeholder method to insert data. I found no solution on the Internet for a long time. Later, I wondered if there was any problem with the value, so I printed all the values in the object to be inserted, as follows:

	def insertGameIdiom(self, gameIdiom):
		print('\n'.join(['%s:%s' % item for item in gameIdiom.__dict__.items()]))
		self.con.execute("insert into game_idiom(id, idiom_id, all_word, play_time, game_id) values(?,?,?,?,?)",(gameIdiom.id,gameIdiom.idiom_id,gameIdiom.all_word,gameIdiom.play_time,gameIdiom.game_id,))

The printed information is as follows:

id:50415712-8e3a-49bc-bb51-97fc58ead818
idiom_id:fc947044-4258-4f1f-86c1-03d1f7a08c43
all_word:111
play_time:1517109225.0413642
game_id:0c4ea9dc-470d-4ac4-af08-492d51bd68dc

Then manually set these values into the gameediom class, and call the insertgameediom method on the command line to test whether there is a problem with the values, as follows:

gameIdiom = GameIdiom('50415712-8e3a-49bc-bb51-97fc58ead818','fc947044-4258-4f1f-86c1-03d1f7a08c43','δΈ€ζœ›ζ— ι™…',1517109225.0413642,'0c4ea9dc-470d-4ac4-af08-492d51bd68dc')
idiomDao.insertGameIdiom(gameIdiom)

The result shows that there is no problem, which means that it is not the value in the object. This is the problem of looking at the exception information carefully, and the keyword is “potentially unsupported type”. So I thought whether it would be the parameter type problem, so I added the code of print type into the code, as follows:

	def insertGameIdiom(self, gameIdiom):
		print('\n'.join(['%s:%s' % item for item in gameIdiom.__dict__.items()]))
		print(type(gameIdiom.id))
		self.con.execute("insert into game_idiom(id, idiom_id, all_word, play_time, game_id) values(?,?,?,?,?)",(gameIdiom.id,gameIdiom.idiom_id,gameIdiom.all_word,gameIdiom.play_time,gameIdiom.game_id,))

Then execute and print the following information:

id:5e5326dd-6d46-4526-9d30-ec97177d748d
idiom_id:fc947044-4258-4f1f-86c1-03d1f7a08c43
all_word:111
play_time:1517133390.0591135
game_id:4daa77d1-fd70-4bcb-865e-8b4adcab7957
<class 'uuid.UUID'>

It is found that the type of the ID field is uuid.uuid, while the corresponding type in the database is varchar, so an error is reported. The solution is very simple, that is, when the parameter is passed in, convert UUID to STR, as follows:

gameIdiom = GameIdiom(id=str(uuid4()),play_time=time(),game_id=self.id)