Python "MySQL Error [-1]: Failed executing the operation; Could not process parameters"

Question

I am using following code to update many on for my table. However that's fine working on Windows with Python but not working on my Ubuntu machine. Update many keep saying MySQL Error [-1]: Failed executing the operation; Could not process parameters. Is there are any solution to trace what's exact causing this error?

def update_wifs(done_data):
    magicwallet = mysql.connector.connect(
        host="localhost",
        user="dumpass",
        password="dumuser",
        database="magicwallet"
    )
    mysql_table = "magic97mil"
    conn = magicwallet.cursor()
    
    query = ""
    values = []

    for data_dict in done_data:

        if not query:
            columns = ', '.join('`{0}`'.format(k) for k in data_dict)
            duplicates = ', '.join('{0}=VALUES({0})'.format(k) for k in data_dict)
            place_holders = ', '.join('%s'.format(k) for k in data_dict)
            query = "INSERT INTO {0} ({1}) VALUES ({2})".format(mysql_table, columns, place_holders)
            query = "{0} ON DUPLICATE KEY UPDATE {1}".format(query, duplicates)

        v = data_dict.values()
        values.append(v)

    try:
        conn.executemany(query, values)
    except Exception as e:
        try:
            print("MySQL Error [%d]: %s" % (e.args[0], e.args[1]))
        except IndexError:
            print("MySQL Error: %s" % str(e))

        magicwallet.rollback()
        return False

    magicwallet.commit()
    conn.close()
    magicwallet.close()

    return done_data

done_data coming like it. what's exact like name of the columns in my table. It's been working fine on Windows machine but keep has error on Unix

{'id': 73399, 'wif': 'uMx1VuwRT4cKQQyE', 'PublicAddress': 'p62GqtrDtRg', 'PublicAddressP2WPKH': 'dj3krprezquku7wkswv', 'phrase': '0075839', 'index_file': 73399, 'imported': 1}

{'id': 73400, 'wif': 'L1Ri4cv3vicfGbESct', 'PublicAddress': 'JfstH24WMHGZz63WEopk', 'PublicAddressP2WPKH': 'ffkt6xxgksktzzkq8ucydrn8ps', 'phrase': '007584', 'index_file': 73400, 'imported': 1}

UPD 0. query print

INSERT INTO magic97mil (id, wif, PublicAddress, PublicAddressP2WPKH, phrase, index_file, imported) VALUES (%s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE id=VALUES(id), wif=VALUES(wif), PublicAddress=VALUES(PublicAddress), PublicAddressP2WPKH=VALUES(PublicAddressP2WPKH), phrase=VALUES(phrase), index_file=VALUES(index_file), imported=VALUES(imported)

UPD 1. Full traceback

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/mysql/connector/cursor_cext.py", line 313, in _batch_insert
    prepared = self._cnx.prepare_for_mysql(params)
  File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 659, in prepare_for_mysql
    raise ValueError("Could not process parameters")
ValueError: Could not process parameters

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/var/www/html/WalletMagic97mil/to_wif.py", line 76, in update_wifs
    conn.executemany(query, values)
  File "/usr/local/lib/python3.8/dist-packages/mysql/connector/cursor_cext.py", line 355, in executemany
    stmt = self._batch_insert(operation, seq_params)
  File "/usr/local/lib/python3.8/dist-packages/mysql/connector/cursor_cext.py", line 333, in _batch_insert
    raise errors.InterfaceError(
mysql.connector.errors.InterfaceError: Failed executing the operation; Could not process parameters

UPD 2. Difference on windows and unix values

So the difference of print(values) on Unix and Windows is:

Windows it's like:

[{'id': 73401, 'wif': 'iVVUsHiLPLGq3uPaPyzdQ6xFeB3', 'PublicAddress': 'nLNtv3XUiMs7f1q8vU', 'PublicAddressP2WPKH': 'epw8ae08fnjpuva7x8783', 'phrase': '0075840', 'index_file': 73401, 'imported': 1}, 

{'id': 73402, 'wif': 'i41ZqWgvoKbUjYsbA41A', 'PublicAddress': 'Vd1D2krnjMucjmLU9', 'PublicAddressP2WPKH': '20g4my4rm4xgt04ph8xcmgyk', 'phrase': '0075841', 'index_file': 73402, 'imported': 1}]

Unix it's like:

[dict_values([73101, 'bmgHbonEKw4LoUqmwSg', '7K77mEtoiH5x8FnmJi2', 'dx3pdppq0zgacldefnge8ea3', '0075576', 73101, 1]),
 
dict_values([73102, 'nojKY4pzXxJ9TeFX14vpnk', 'qkuVECaPs3WcCj', 'j5sv9q28kzaqs0m6g', '0075577', 73102, 1])]

I think maybe this is exact error

Print query. I bet the problem will be obvious. You could also post the entire stacktrace. — Michael Ruth
– Michael Ruth, Commented Aug 13, 2021 at 22:12
@MichaelRuth I just updated the question in print query. I will provide now with full traceback — Arthur Yakovlev
– Arthur Yakovlev, Commented Aug 13, 2021 at 22:51
That is your problem: VALUES (%s, %s, %s, %s, %s, %s, %s), %s is not a valid value. — Michael Ruth
– Michael Ruth, Commented Aug 13, 2021 at 23:37
Are you running the same version of mysql-connector on both platforms? (print(mysql.connector.__version__)). — snakecharmerb
– snakecharmerb, Commented Aug 14, 2021 at 14:15

Michael Ruth · Accepted Answer · 2021-08-13 23:40:51Z

2

You should use one pattern for building queries:

        if not query:
            columns = ', '.join('`{0}`'.format(k) for k in data_dict)
            duplicates = ', '.join('{0}=VALUES({0})'.format(k) for k in data_dict)
            place_holders = ', '.join('`{0}`'.format(k) for k in data_dict)

Truly, you shouldn't string-build queries. It's unsafe, error-prone, and unnecessary. A better method is to use parameter binding with prepared statements.

answered Aug 13, 2021 at 23:40

Michael Ruth

3,5441 gold badge26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arthur Yakovlev Over a year ago

However still keep saying same error. But query look normal INSERT INTO magic97mil (id, wif, PublicAddress, PublicAddressP2WPKH, phrase, index_file, imported) VALUES (id, wif, PublicAddress, PublicAddressP2WPKH, phrase, index_file, imported) ON DUPLICATE KEY UPDATE id=VALUES(id), wif=VALUES(wif), PublicAddress=VALUES(PublicAddress), PublicAddressP2WPKH=VALUES(PublicAddressP2WPKH), phrase=VALUES(phrase), index_file=VALUES(index_file), imported=VALUES(imported)

Arthur Yakovlev · Accepted Answer · 2021-08-17 22:56:17Z

0

I do not know why. But I replace following code like it:

v = list(data_dict.values())
values.append(v)

And it's begun working fine for me

answered Aug 17, 2021 at 22:56

Arthur Yakovlev

9,3278 gold badges36 silver badges49 bronze badges

1 Comment

snakecharmerb Over a year ago

In Python2, dict.values() returns a list. In Python3, dict.values() returns a view. So it seems that on Windows you are using Python2, but on Unix Python3.

Collectives™ on Stack Overflow

Python "MySQL Error [-1]: Failed executing the operation; Could not process parameters"

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related