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

Question

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.

mata · Accepted Answer · 2015-06-08 17:39:50Z

4

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)])

answered Jun 8, 2015 at 17:39

mata

69.3k10 gold badges168 silver badges162 bronze badges

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

2 Comments

Esbee Over a year ago

mata ! Thanks. But,this time I am getting error like this: line 16 (9,’Yousuf’,’Patel’,21,’M’,4500) Type Error: String indices must be integers not tuple.

Esbee Over a year ago

Thanks 'mata' ! Your version worked ! I initially missed the comma after the ending """, which separates the first argument (query/operation) from the second argument (sequence of paramaters). Thanks once again

mapleflow · Accepted Answer · 2018-06-06 02:11:23Z

0

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
        ))

edited Jun 6, 2018 at 2:11

answered Jun 6, 2018 at 2:03

mapleflow

741 silver badge8 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related