Importing pickle file into mysql gives an error "ProgrammingError: not enough arguments for format string"

Question

I'm trying to insert data from .pickle file into MySQL. I'm getting an error "ProgrammingError: not enough arguments for format string". As I understand, this error happens due to the count of placeholders (%s) being greater than the count of values for formating/templating. But in my case they are equal. cursor.execute("""INSERT INTO 'directors'('id', 'first_name', 'last_name') VALUES (%s,%s,%s)""", (result, ))

import _pickle as cPickle
import pandas as pd
import MySQLdb

csv_file = 'NEW_DIRECTORS.csv'
filename = "myfile.pickle"

df = pd.read_csv(csv_file)
data_to_save = df 

with open(filename,'wb') as file_handle:
    cPickle.dump(data_to_save, file_handle)    
    
with open(filename,'rb') as file_handle:
    result = cPickle.load(file_handle)
    print(result)   

***

id first_name last_name
    0  31       Paul     Aaron
    1  32       Evan  Aaronson
    2  33     Reuben  Aaronson
    3  34     Heikki     Aarva



connection = MySQLdb.connect('localhost','root','','movies')

cursor = connection.cursor()

cursor.execute("""INSERT INTO `directors`(`id`, `first_name`, `last_name`) VALUES (%s,%s,%s)""", (result, ))

Tried also without quotes:

cursor.execute("""INSERT INTO directors(id, first_name, last_name) VALUES (%s,%s,%s)""", (result, ))

Same error.

Reference: http://blog.cameronleger.com/2011/05/31/python-example-pickling-things-into-mysql-databases/

repr(result)

gives me:

'   id first_name last_name\n0  31       Paul     Aaron\n1  32       Evan  Aaronson\n2  33     Reuben  Aaronson\n3  34     Heikki     Aarva'

The data needs to be parsed into individual column values. But it isn't clear which of the numeric values corresponds to `id` (for example in the last row is it 3 or 34?). — snakecharmerb, Nov 20 '22 at 12:01

score 0 · Answer 1 · answered Nov 20 '22 at 13:14

result is a DataFrame, so you could convert each row to a tuple and write the tuple to the database:

for t in result.itertuples():
    # We don't want the index, which is the first element.
    values = t[1:]
    cursor.execute("""INSERT INTO directors(id, first_name, last_name) VALUES (%s,%s,%s)""", values)
conn.commit()

This more compact code is equivalent:

row_data = [t[1:] for t in result.itertuples()]
cursor.executemany("""INSERT INTO directors(id, first_name, last_name) VALUES (%s,%s,%s)""", row_data)
conn.commit()

However note that you would usually just use the dataframe's to_sql method, as described in the answers here.

Importing pickle file into mysql gives an error "ProgrammingError: not enough arguments for format string"

1 Answers1