Python and MySQLdb - data not inserted, data conversion error too

Question

I am trying to do a simple

INSERT into a localhost MySQL 5.7 db
with a Python 2.7 script

Nothing fancy but that is exactly the result I get - nothing.

I tested the darn thing bit by bit in a separate test script, no errors raised but no data inserted into table, as well. Problem occurs at the first SQL variable (probably) and there is one error in the last try/catch.

I guess I am not doing the data type conversion correctly?

Here is the code:

#!/usr/bin/python

import MySQLdb
import pandas as pd 
import numpy as np 
import xlwings as xw

# ---------- Connect to Excel sheet, get data (list of lists)
wb = xw.Workbook('/users/edchigliak/sites/xlwings/baza.xlsx')
data = xw.Range('dimensions', 'A2:F71').value

# ---------- Open database connection, insert data into table
db = MySQLdb.connect("localhost","root","/pass/","budgeteer")
cursor = db.cursor()

Following sql and for could be written badly:

sql = """INSERT INTO dimensions(dimension, dimType, abbreviation, translationHr, code) VALUES (%s, %d, %s, %s, %d)"""  

for row in data:
    try:
        cursor.execute(sql, row)
        db.commit()
    except:
        db.rollback()


# ---------- Check if INSERT successful
sql = """SELECT * FROM dimensions"""

try:
    cursor.execute(sql)

    # ---------- Fetch all the rows in a list of lists
    results = cursor.fetchall()

for row in cursor:

    id = row[0]
    dimension = row[1]
    dimType = row[2]
    abbreviation = row[3]
    translationHr = row[4]
    code = row[5]

Here is the error, goes to "except":

Error: unable to fetch data.

    # Now print fetched result
    print "Dim. ID: %d, Dim.: %s, Dim. type: %d, Abbrev.: %d, Translat.: %s, Code: %d" % (id, dimension, dimType, abbreviation, translationHr, code)

except:
    print "Error: unable to fetch data"

db.close()

And if I remove try block:

python git:master ❯ python proba.py
Traceback (most recent call last):
File "proba.py", line 47, in
print "Dim. ID: %d, Dim.: %s, Dim. type: %d, Abbrev.: %d, Translat.: %s, Code: %d" % (id, dimension, dimType, abbreviation, translationHr, code) TypeError: %d format: a number is required, not str
python git:master ❯

For what it's worth, this is the sample of a list that gets read from Excel in the beginning of the script (maybe 'None' is making problems?):

[1.0, u'austria', 1.0, u'at', None, 4.0]
[2.0, u'belgium', 1.0, u'be', None, 7.0]
[3.0, u'czech', 1.0, u'cz', None, 9.0]

Please don't use a bare except. You are carefully hiding what the problem is. Don't do that. Remove that try/except completely so you can see what error is actually being raised; then you should be able to fix your problem yourself. — Daniel Roseman
– Daniel Roseman, Commented Feb 26, 2016 at 13:35
Thanks Daniel Roseman :). I did exactly that in the last block of my post and there is the error that the interpreter returns: TypeError: %d format: a number is required, not str but I don't know how to solve this? Is it the db that's returning a different format or ...? Also, when I check in my other Shell instance I see that no rows were added to this particular table. — Alexander Starbuck
– Alexander Starbuck, Commented Feb 26, 2016 at 13:43
No, I meant in the cursor.execute loop. But you shouldn't do it anywhere. — Daniel Roseman
– Daniel Roseman, Commented Feb 26, 2016 at 13:44

Eugene Yarmash · Accepted Answer · 2016-02-26 15:04:23Z

1

You should always use %s as a parameter marker (not %d, etc.), no matter the type of the value:

sql = ("INSERT INTO dimensions(dimension, dimType, abbreviation, "
       "translationHr, code) VALUES (%s, %s, %s, %s, %s)")
cursor.execute(sql, row)

The database driver will handle any quoting for you.

edited Feb 26, 2016 at 15:04

answered Feb 26, 2016 at 13:42

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

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

4 Comments

Alexander Starbuck Over a year ago

If I do that and remove the first "try...except" I get this error: TypeError: not all arguments converted during string formatting :?

Eugene Yarmash Over a year ago

@AlexStarbuck: this means that row has more items than the number of placeholders (5).

Alexander Starbuck Over a year ago

Aaah, of course! From Excel I get 'id' column as well, which I don't need in my sql. So I should rewrite for row...statement that it excludes first item from each row in data[]. I did this and now there is another error:

_mysql_exceptions.OperationalError: (1366, "Incorrect string value: '\\xC4\\x87enja' for column 'translationHr' at row 1")

I guess it doesn't know how to convert 'None' into NULL

Eugene Yarmash Over a year ago

@AlexStarbuck: you need to encode the unicode strings, e.g. translationHr.encode().

Collectives™ on Stack Overflow

Python and MySQLdb - data not inserted, data conversion error too

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related