CODEWITHSUNDEEP
pythonmysqlmysql-pythonmysql-connector
phibaa
phibaa

Reputation: 23

Python mysql connector insert with %s

I'm trying to append a set containing a number into my MySQL database using the Python MySQLConnector. I am able to add data manually, but the following expression with %s won't work. I tried several variations on this, but nothing from the documentation seems to work in my case. The table was already buildt as you can see:

#Table erstellen:
#cursor.execute('''CREATE TABLE anzahlids( tweetid INT  )''')

Here is my code and the error:

print len(idset)
    id_data = [
        len(idset)
    ]
    print id_data
    insert = ("""INSERT INTO anzahlids (idnummer) VALUES (%s)""")
    cursor.executemany(insert, id_data)
    db_connection.commit()

"Failed processing format-parameters; %s" % e)
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; argument 2 to map() must support iteration

Upvotes: 2

Views: 9899

Answers (2)

geertjanvdk
geertjanvdk

Reputation: 3520

Late answer, but I would like to post some nicer code. Also, the original question was using MySQL Connector/Python.

The use of executemany() is wrong. The executemany() method expects a sequence of tuples, for example, [ (1,), (2,) ].

For the problem at hand, executemany() is actually not useful and execute() should be used:

cur.execute("DROP TABLE IF EXISTS anzahlids")
cur.execute("CREATE TABLE anzahlids (tweetid INT)")

some_ids = [ 1, 2, 3, 4, 5]
cur.execute("INSERT INTO anzahlids (tweetid) VALUES (%s)",
            (len(some_ids),))
cnx.commit()

And with MySQL Connector/Python (unlike with MySQLdb), you have to make sure you are committing.

(Note for non-German speakers: 'anzahlids' means 'number_of_ids')

Upvotes: 3

Keith John Hutchison
Keith John Hutchison

Reputation: 5287

The following is an example that worked on my machine.

import MySQLdb
db = MySQLdb.connect(host="localhost", user="stackoverflow", passwd="", db="stackoverflow")
cursor = db.cursor()
try:
    sql = 'create table if not exists anzahlids( tweetid int ) ; '
except:
    #ignore
    pass

sql = ("""INSERT INTO anzahlids (tweetid) VALUES (%s)""")
data = [1,2,3,4,5,6,7,8,9]
length = [len(data)]
cursor.executemany(sql,length)
db.commit()

if idset is a single value you can use 

sql = ("""INSERT INTO anzahlids (tweetid) VALUES (%s)""") % len(idset)
cursor.execute(sql)
db.commit()

Upvotes: 0

Related Questions