Python MySQLdb - Getting a ValueError

Question

Trying to import a list into MySQL through Python with MySQLdb...code below.

I get a ValueError when I run it -

query = query % tuple([db.literal(item) for item in args])
ValueError: unsupported format character ',' (0x2c) at index 51

Code_

import MySQLdb
import ystockquote
import re

mydb = MySQLdb.connect(host='localhost',
       user= '****',
       passwd='****',
       db='****')
cur = mydb.cursor()

name = raw_input('please input a symbol_')
data = ystockquote.get_all(name)
stock = data.values()
clean = (", ".join(stock))
#var_string = ', '.join('?' * len(clean))
query_string = 'INSERT IGNORE INTO YAHOO VALUES (\"%s\");' % clean
cur.execute(query_string, clean)
print clean

When I print query_string the statement looks good and the 'clean' values appear to be in good shape - any help would be kindly appreciated.

print clean
1.52, -59.49%, 0.93, 0.17, 800, "1.51 - 1.59", 1.54, 1.59, N/A, -0.01, +5.25%, 0.16, N/A, 0.08, -2.32, N/A, 6.90M, -1.24, N/A, N/A, 1.50, 100, 326754, N/A, N/A, N/A, N/A, 1.51, "NYQ", N/A, "0.93 - 3.90", N/A, "3:48pm - <b>1.58</b>", "Ciber, "+3.95%", N/A, 3700, N/A, N/A, -34.46%, N/A, 2.84, N/A, N/A, N/A, 0.65, N/A, 127.47M, N/A, +69.89%, 2.63, N/A, N/A, 693036, 1.58, -0.65, 73289000, N/A, N/A, -0.83, 0.53, 80680000, N/A, N/A, N/A,  Inc. Common Stock", 3.90, "6/14/2016", -0.16, N/A, N/A, 760.00M, +0.06, N/A, N/A, N/A, 2.41, "3:48pm", N/A, "+0.06 - +3.95%", 326754

print query_string
INSERT IGNORE INTO YAHOO VALUES ("1.52, -59.49%, 0.93, 0.17, 800, "1.51 - 1.59", 1.54, 1.59, N/A, -0.01, +5.25%, 0.16, N/A, 0.08, -2.32, N/A, 6.90M, -1.24, N/A, N/A, 1.50, 100, 335969, N/A, N/A, N/A, N/A, 1.51, "NYQ", N/A, "0.93 - 3.90", N/A, "3:51pm - <b>1.58</b>", "Ciber, "+3.95%", N/A, 3700, N/A, N/A, -34.46%, N/A, 2.84, N/A, N/A, N/A, 0.65, N/A, 127.47M, N/A, +69.89%, 2.63, N/A, N/A, 693036, 1.58, -0.65, 73289000, N/A, N/A, -0.83, 0.53, 80680000, N/A, N/A, N/A,  Inc. Common Stock", 3.90, "6/14/2016", -0.16, N/A, N/A, 760.00M, +0.06, N/A, N/A, N/A, 2.41, "3:51pm", N/A, "+0.06 - +3.95%", 335969");

no, 81, named to match each data point in the 'stock' return. — Derek_P
– Derek_P, Commented Jun 14, 2016 at 20:10

Fabricator · Accepted Answer · 2016-06-14 21:47:03Z

1

.execute expect 2 arguments when using with wildcards or string replacement in the following manner:

query_string with value placeholders %s
a tuple of values that will replace the above %s

you don't need to manually "clean" your values. I also included the column names in the query to make it clear:

data = ystockquote.get_all(name)
stock = data.values()
columns = data.keys()
columns[columns.index('change')] = '_change'
var_string = '%s' + ',%s' * (len(stock)-1))
query_string = '''
    INSERT IGNORE INTO YAHOO 
    ('''+','.join('`%s`' % (c,) for c in columns)+''') 
    VALUES (%s);''' % var_string
cur.execute(query_string, tuple(stock))

edited Jun 14, 2016 at 21:47

answered Jun 14, 2016 at 20:06

Fabricator

12.8k2 gold badges29 silver badges40 bronze badges

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

14 Comments

Fabricator Over a year ago

@citramaillo, weird. did you get this error from my latest answer?

Fabricator Over a year ago

@citramaillo, sorry. my var_string was constructed incorrectly. see updated answer

Fabricator Over a year ago

@citramaillo, you are probably missing an s after % in var_string = '%s' + ',%s' * (len(stock)-1))

Fabricator Over a year ago

@citramaillo, looks like change is a mysql reserved word, so we need to escape it with the tick symbol. see updated answer

Fabricator Over a year ago

@citramaillo, do you mean your column name is _change instead of change?

|

Collectives™ on Stack Overflow

Python MySQLdb - Getting a ValueError

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related