CALL a SQL 5.6 routine from a Python script

Question

I'm trying to write a Python script which will anonymise users stored in a production database in bulk. This is using an existing routine which has been used many times before and works fine.

My code looks like this:

with open('users_to_delete.csv', newline='') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',', quotechar='|')
for row in csv_reader:
    user_to_delete = row[0]
    delete_query = """CALL anonymise_accounts(""" + user_to_delete + ');'
    cursor.execute(delete_query, multi=True)
    db_connection.commit()

When running it, the following output is returned:

CALL anonymise_accounts(5308561);
CALL anonymise_accounts(2558082);

However, the accounts don't get anonymised.

I'd appreciate any ideas where I may be going wrong here.

try CALL anonymise_accounts(5308561); directly in database to see if it really works. maybe this function doesn't delete accounts. — furas
– furas, Commented Dec 23, 2020 at 1:14
Yes, as mentioned in my post it’s a script that use regularly and works with the print from delete_query. — George
– George, Commented Dec 23, 2020 at 1:24
I wouldn't trust it. I would check all again on my own. I would run check code again directly in database and later I would check if code connect to correct database. I don't see any other reason. — furas
– furas, Commented Dec 23, 2020 at 1:36

cherful · Accepted Answer · 2020-12-23 04:47:10Z

1

execute() is not execute multiple statements, you try to multi=False and delete the semicolon.

with open('users_to_delete.csv', newline='') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',', quotechar='|')
for row in csv_reader:
    user_to_delete = row[0]
    delete_query = """CALL anonymise_accounts(""" + user_to_delete + ')'
    cursor.execute(delete_query, multi=False)
    db_connection.commit()

answered Dec 23, 2020 at 4:47

cherful

1894 bronze badges

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

1 Comment

George Over a year ago

When I do that I get the following:

Traceback (most recent call last):   File "./bulk_del.py", line 64, in <module>     cursor.execute(delete_query, multi=False)   File "/usr/local/lib/python3.8/site-packages/mysql/connector/cursor.py", line 554, in execute     raise errors.InterfaceError( mysql.connector.errors.InterfaceError: Use multi=True when executing multiple statements

Arehandoro · Accepted Answer · 2020-12-23 16:43:59Z

0

with open('users_to_delete.csv', newline='') as csv_file:
csv_reader = csv.reader(csv_file, delimiter='\n', quotechar='|')
for row in csv_reader:
    user_to_delete = row[0]
    delete_query = """CALL rp_anonymise_ho(""" + user_to_delete + ')'
    cursor.execute(delete_query)
    print(cursor.fetchall())

Make sure your data in the csv is comma delimited. For example, there were only one row with no commas, the code would look like in the above.

answered Dec 23, 2020 at 16:43

Arehandoro

3771 gold badge3 silver badges13 bronze badges

Collectives™ on Stack Overflow

CALL a SQL 5.6 routine from a Python script

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related