Error while Inserting Python List to Mysql

0
votes

Have a Sample Python List as Follows:

data = [
('Jane','dffd','sdas','sdas'),
('Jane','dffd','sdas','sdas'),
('Jane','dffd','sdas','sdas')
]

Tried inserting this list onto a My Sql database with tablename 'test' as Follows:

import MySQLdb as my

db = my.connect()

cursor = db.cursor()

data = [
('Jane','dffd','sdas','sdas'),
('Jane','dffd','sdas','sdas'),
('Jane','dffd','sdas','sdas')
]

ddt = str(data)

cursor.executemany('INSERT into test VALUES(%s, %s, %s,%s)' %  ddt)

db.commit()  

db.close()

Getting an error saying:

not enough arguments for format string

Table contains 4 columns : test1,test2,test3,test4 .I know Im making a silly mistake someplace, but can't seem to find it.

2
pythonmysql
str(data) doesn't do what you think it does. Use cursor.executemany("INSERT into...)", data) instead. See also dev.mysql.com/doc/connector-python/en/… - DYZ

2 Answers

0
votes

Full code can be this type::

import MySQLdb
import MySQLdb.cursors
encoding = "utf8mb4"
db = MySQLdb.connect(
    host='',
    user='',
    passwd='',
    db='',
    compress=1,
    port=,
    charset='utf8mb4',
    use_unicode=True,
)
cursor = db.cursor()
data = [
('Jane','dffd','sdas','sdas'),
('Jane','dffd','sdas','sdas'),
('Jane','dffd','sdas','sdas')
]

sql = "INSERT INTO test VALUES %s;" %(str(tuple(data)).replace('((','(').replace('))',')'))
print(sql) // you can print the sql query and verify to insert data on test mysqlworkbech for confirm data structure is 
cursor.execute(sql)
db.commit()  
db.close()
-1
votes

try this:

cursor.executemany('INSERT into test VALUES(%s, %s, %s,%s)',data)

Or

cursor.execute("INSERT INTO test VALUES %s;" %(str(tuple(data)).replace('((','(').replace('))',')')))