CODEWITHSUNDEEP
pythonmysql-connector
Arthur Yakovlev
Arthur Yakovlev

Reputation: 9309

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

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

Upvotes: 5

Views: 323

Answers (2)

Arthur Yakovlev
Arthur Yakovlev

Reputation: 9309

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

Upvotes: 0

Michael Ruth
Michael Ruth

Reputation: 3504

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.

Upvotes: 2

Related Questions