Failed processing format-parameters with mysql.connector in Python

Question

I can't figure out what I'm doing wrong with this insert statement. The error I'm getting is:

 "Failed processing format-parameters; %s" % err)
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; 
'MySQLConverter' object has no attribute '_navigablestring_to_mysql'`

The specific lines of code are:

update = '''INSERT INTO myDB.newtable (ID,Record,Latitude,Longitude,code) VALUES (%s,%s,%s,%s,%s)'''
cursor2.execute(update,(ID,Record,Latitude,Longitude,code))
cnx2.commit()

I have also tried this format:

update = ("INSERT INTO myDB.newtable (ID,Record,Latitude,Longitude,code) VALUES (%s, %s, %s, %s, %s)")%(ID,Record,Latitude,Longitude,code)
cursor2.execute(update)

and get this error: mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column '45676kb' in 'field list'.

45676kb is only a portion of the entire value. The complete string is 45676kb-98734-98734-123nn.

I think the syntax of the second attempt is more correct, because I'm at least getting an sql error but I can't figure out how to properly format my insert statement with mysql.connector.

Answer 1

The first option is the correct way to put query parameters into the query - it is called a parameterized query. In this case, you are letting the database driver to escape the query parameters, safely insert them into the query and handle the Python-to-MySQL type conversions.

The error you are getting means that it could not convert one of the ID, Record, Latitude, Longitude or code parameter values to a valid MySQL database type. To be specific, see the variable types you have posted:

ID        <type 'unicode'> 
Record    <type 'unicode'>
Latitude  <class 'bs4.element.NavigableString'>
Longitude <class 'bs4.element.NavigableString'>
code      <type 'unicode'>

The problem is with Latitude and Longitude - they are BeautifulSoup's NavigableString class instances - the MySQL converter having difficulties in understanding how to convert a NavigableString object into a valid MySQL type. Convert them to strings explicitly beforehand:

update = """
    INSERT INTO 
        myDB.newtable 
        (ID,Record,Latitude,Longitude,code) 
    VALUES 
        (%s,%s,%s,%s,%s)
"""
cursor2.execute(update, (ID, Record, str(Latitude), str(Longitude), code))