Python MySQL connector, "wrong number of arg.."

Question

Can someone help and give me a tip here? I'm using Python 2.7 and MySQL-connector 1.0.12.

The following job_insert, with %f, raises the error "mysql.connector.errors.ProgrammingError: Wrong number of arguments during string formatting".

job = {'customer': u'Acme', 'period': 3.0, 'cost': 987654.543210123}
job_insert = "INSERT INTO f.job (customer, period, cost) VALUES (%(customer)s, %(period)f, %(cost)f);"

cursor.execute(job_insert, job)

When I use %s instead, mysql.connector inserts the values. However, the floats are trimmed by several decimal places, e.g. 3.0 to 3 and 987654.543210123 to 987654.5. Both database columns are float.

job_insert = "INSERT INTO f.job (customer, period, cost) VALUES (%(customer)s, %(period)s, %(cost)s);"

Thanks for your time.

B.Mr.W. · Accepted Answer · 2013-09-07 02:32:27Z

3

I would highly recommend using string format.

I am not quite sure of the column data type in your mysql table, usually if it is a varchcar type I put single quotes around them and numbers without single quote.

job_insert = "INSERT INTO f.job (customer, period, cost) VALUES ('{0}', {1}, {2});".format(job['customer'], job['period'], job['cost'])
cursor.execute(job_insert)
cursor.execute('commit')

This would assume your customer column is varchar and period and cost are numeric (int, float..?)

answered Sep 7, 2013 at 2:32

B.Mr.W.

19.7k35 gold badges122 silver badges185 bronze badges

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

1 Comment

Roman Over a year ago

DONT do that in case your input is in any way from a front end! There is no security againts SQL Injection this way!

user1971598 · Accepted Answer · 2013-09-07 01:34:46Z

So string formatting in Python is very similar to C and C like languages.

for example:

say you wanted to do

x = "Hello buddy" and you wanted the buddy to be whatever you wanted, then you would do:

x = "Hello %s" % ("Buddy") There are a few different types of % formats, like %i is for integers, %f is for floats, so on and so forth.

There is a newer, I think more pythonic, way to format strings, and thats with the .format method which you can take a look at here, http://docs.python.org/2/library/string.html#string-formatting

Hope this helps you, admittedly I didn't fully understand your question.

Thanks. I've edited. Hopefully that makes it a little more clear? Notice that %(period)f, %(cost)f raises the error. However, %(period)s, %(cost)s does not. But, the float is trimmed by several decimal places.

Collectives™ on Stack Overflow

Python MySQL connector, "wrong number of arg.."

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related