CODEWITHSUNDEEP
python-2.7
Esbee
Esbee

Reputation: 33

Python/MySQL executemany() - Insertion of data: Type Error: not many arguments for format string

I am learning python 2.7.4/MySQL 5.6. I have a db by name TESTDB. It has a Table called EMPLOYEE. I've been trying to insert data using executemany(). I am getting

Type Error: not enough arguments for format string

I tried to learn from the various answers for similar problem posted in stack overflow and make changes in my code without success. My code is:

import MySQLdb

# Open database connection
import MySQLdb

db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
cursor.executemany("""INSERT INTO EMPLOYEE(ID,FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
                   VALUES(%s,%s,%s,%s,%s,%s)""" %
                    {5,'Geetha','Ramam',22,'F',5600},
                    {6,'Radha','Krishna',34,'F',7500},
                    {7,'Ramesh','Tiwari',28,'M',6500},
                    {8,'Govind','Nihalani',45,'M',8900},
                    {9,'Yousuf','Patel',21,'M',4500})

db.commit()



cursor.close()  

# disconnect from server
db.close()

Shall be grateful for a suggestion.

Upvotes: 3

Views: 10737

Answers (2)

mapleflow
mapleflow

Reputation: 74

executemany Type Error: not enough arguments for format string

in may case executemany columns < values unmatch, try check like this.

# count or regexp
sql_str = """INSERT xxx VALUES(%s,%s,%s,%s,%s,%s)"""
sql_args = [(5,'Geetha','Ramam',22,'F',5600, '1'), (xxx), (xxx)]
sql_cols_len = sql_str.count('%s,') + 1
for sql_val in sql_args:
    sql_val_len = len(sql_val)
    if sql_cols_len != sql_val_len:
        log.error("columns {sql_cols_len} != values {sql_val_len}, {sql_val}]".format(
            sql_cols_len=sql_cols_len,
            sql_val_len=sql_val_len,
            sql_val=sql_val
        ))

Upvotes: 0

mata
mata

Reputation: 69042

The first problem is that you're using the % operator (string formatting), which you should never do to create sql queries. However you're using it with a set literal on the right side, which does not work because a set is not expanded during string formatting but used as a single argument ("%s" % {1} produces "{1}"), unpacking the set in this use case wouldn't make any sense because sets are unordered by definition. That is why you see this error message, the query contains 6 formating specifiers, but there is only one argument to format.

The second problem is that executemany takes a query and a list of arguments as parameters, which are then used to construct the queries. However you're passing multiple arguments (set literals). You should pass a list (or tuple) of lists (or tuples) instead:

cursor.executemany("""INSERT INTO EMPLOYEE(ID,FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
                   VALUES(%s,%s,%s,%s,%s,%s)""", [
                    (5,'Geetha','Ramam',22,'F',5600),
                    (6,'Radha','Krishna',34,'F',7500),
                    (7,'Ramesh','Tiwari',28,'M',6500),
                    (8,'Govind','Nihalani',45,'M',89009),
                    (9,'Yousuf','Patel',21,'M',4500)])

Upvotes: 4

Related Questions