6

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.

Improve this question
2
  • The first way is the correct way. What are the ID,Record,Latitude,Longitude and code values? Commented May 2, 2016 at 19:02
  • b8a0-4f8fe47a3e82, 4305-bd9d-5cf48c46c0c5, 38.922220, -77.205000, GS05 Commented May 2, 2016 at 19:08

1 Answer 1

Reset to default
5

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))
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

these are the values for them: b8a0-4f8fe47a3e82, 4305-bd9d-5cf48c46c0c5, 38.922220, -77.205000, GS05
@user2338089 okay, let's review are you sure b8a0-4f8fe47a3e82, 4305-bd9d-5cf48c46c0c5 and GS05 are strings? Could you also post the type(variable) for all of the 5 variables?
I get: <type 'unicode'> <type 'unicode'> <class 'bs4.element.NavigableString'> <class 'bs4.element.NavigableString'> <type 'unicode'> respectively
@user2338089 great, thanks, updated the answer. The mystery should be solved now.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.