MySQL Python: JSON type throws error on assignment

  mysql, mysql-connector-python, python, sql

I am unable to update JSON type cells with ON DUPLICATE KEY UPDATE, as an error is thrown. I am using mysql-connector-python. Here is a minimal (not)working example:

First, I define my table as:

cursor = db.cursor()
cursor.execute('''
    CREATE TABLE test (
        id INT AUTO_INCREMENT PRIMARY KEY,
        foo VARCHAR(255) NOT NULL UNIQUE,
        bar JSON NOT NULL
    )
''')

Then I try to insert values into the table:

val = [
    {
        'foo': 'lorem ipsum',
        'bar': json.dumps({'a': 1}),
    },
    {
        'foo': 'dolor sit amet',
        'bar': json.dumps({'a': 2}),
    },
]
sql = '''
    INSERT INTO test
        (foo, bar)
    VALUES
        (%(foo)s, %(bar)s)
    ON DUPLICATE KEY UPDATE 
        bar = %(bar)s
'''
cursor = db.cursor()
cursor.executemany(sql, val)

Which leads to an error:

ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%(bar)s' at line 6

If I change the line that says bar = %(bar)s to bar = '%(bar)s', the code happily executes… at least for the first time, until I run this section again to invoke the ON DUPLICATE KEY UPDATE clause, which then results in another error:

DataError: 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'test.bar'.

Could you, please, advise me how to fix my syntax?

Source: Python Questions

LEAVE A COMMENT