When trying to update a column of type json in Postgres using psycopg2 the following error occurs: psycopg2.ProgrammingError: can't adapt type 'dict'
I am running this code:
self.cursor.execute("UPDATE tb_games SET infos_json = %s WHERE id = 10",
(json.dumps({'v1':'a','v2':'b'})))
conexao.commit()
The json I'm passing to json.dumps () is valid, I already tested it. Does anyone know what I might be doing wrong?
The second argument to execute should be a sequence, such as a tuple:
self.cursor.execute("UPDATE tb_games SET infos_json = %s WHERE id = 10",
(json.dumps({'v1':'a','v2':'b'}),) )
Notice that the comma in (json.dumps({'v1':'a','v2':'b'}),) makes the expression a tuple (containing one element). Without it, the entire expression in parentheses is evaluated as a string.
In [52]: type((json.dumps({'v1':'a','v2':'b'}),))
Out[52]: tuple
In [53]: type((json.dumps({'v1':'a','v2':'b'})))
Out[53]: str
To make sure that commit is called on the connection associated with the cursor self.cursor,
it might be better to call
self.cursor.connection.commit()
instead of
conexao.commit()
Or, use the connection as a context manager:
with conexao:
with conexao.cursor() as cursor:
cursor.execute("UPDATE tb_games SET infos_json = %s WHERE id = 10",
(json.dumps({'v1':'a','v2':'b'}),) )
When Python's flow of execution leaves the outer with-block, the transaction is committed, unless there was an error in which case the transaction is rolledback. This makes it easier to
consistently enforce the commit/rollback behavior without having to write a lot of boilerplate code.
print(self.cursor.mogrify("UPDATE tb_games SET infos_json = %s WHERE id = %s", (json.dumps({'v1':'a','v2':'b'}), id_game))) to inspect the SQL being sent to PostgreSQL.