Python MySQL insert with list

Question

I am trying to insert data into MySQL with a list using Python.
The list contains both strings and integers.
I am not able to use %s for string replacement also % does not work.

def insert(self,data):
    db =  MySQLdb.connect(db=self.database,host=self.host,port=self.port,user=self.user,passwd=self.password)
    c=db.cursor()
    var_string = ', '.join('?' * len(data))
    query = 'INSERT INTO wmr200 (time, temp_in, temp_out, dewpoint, hum_in, hum_out, windspeed, wind_direction, windchill, rain_1h, rain_24h, rain_total, rel_pressure, abs_pressure, tendency, forecast) VALUES (now(), %s);' % var_string
    c.execute(query, data)

error is:

File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 159, in execute
query = query % db.literal(args)
TypeError: not all arguments converted during string formatting

NPE · Accepted Answer · 2012-04-28 12:58:48Z

2

The expression that produces the exception:

query % db.literal(args)

requires query to contain one format string -- such as %s -- per each element of db.literal(args).

Yet the value of query as produced by your function contains exactly zero format strings.

answered Apr 28, 2012 at 12:58

NPE

503k114 gold badges970 silver badges1k bronze badges

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

4 Comments

HyperDevil Over a year ago

If i understand you correctly, i need to add 15 %s to my query string?

HyperDevil Over a year ago

I added this:

query = 'INSERT INTO wmr200 (time, temp_in, temp_out, dewpoint, hum_in, hum_out, windspeed, wind_direction, windchill, rain_1h, rain_24h, rain_total, rel_pressure, abs_pressure, tendency, forecast) VALUES (now(), %s, %s ,%s ,%s, %s, %s, %s, %s, %s, %s, %s ,%s, %s, %s, %s);' % var_string

Now i get: TypeError: not enough arguments for format string

Wooble Over a year ago

@HyperDevil: in your example code, you can omit data altogether from the execute call since you're actually inserting the values into your query string with string formatting. However, this is probably a HORRIBLE idea since this is the best way to create SQL injection vulnerabilities. Lose the string formatting and var_string and do use the 15 %ss

HyperDevil Over a year ago

I used this (now(), %s, %s ,%s ,%s, %s, %s, %s, %s, %s, %s, %s ,%s, %s, %s, %s);' % data it is still complainting: TypeError: not enough arguments for format string

Collectives™ on Stack Overflow

Python MySQL insert with list

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