Error while inserting duplicate values into DP , using ON Duplicate Key using python mysql DB

Question

These are the following entries in Table MyTable

mysql> select * from myTable;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|  110 | c      | 23   |
|  114 | chadns | 897  |
| 1112 | chadns | 897  |
+------+--------+------+

This is the schema of MyTable

mysql> desc myTable;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

While Executing the following code i am getting the following error

    import MySQLdb

con_local =  MySQLdb.connect( host ="127.0.0.1",user = "root",passwd ="ctl",db ="ads")
cursor_local = con_local.cursor()

cursor_local.execute("select * from myTable")
x = cursor_local.fetchall()



for i in x:

    s = "insert into myTable values(%d,%s,%s) on duplicate key update id = id+1000"

    cursor_local.execute('''insert into myTable values(%d,%s,%s) on duplicate key update id = id+1000'''%(i[0],i[1],i[2]));
    cursor_local.execute("commit");

ERRORS:

Traceback (most recent call last):
  File "chandu.py", line 15, in <module>
    cursor_local.execute('''insert into Chandana values(%d,%s,%s) on duplicate key update id = id+1000'''%(i[0],i[1],i[2]));
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'c' in 'field list'")

I am trying to insert duplicate values but changing the primary key id , but it is not working can anyone tell me the solution

You either need to quote the strings in the query, or use a prepared query like in Alex's answer. — Barmar
– Barmar, Commented Oct 23, 2015 at 12:15

Alex · Accepted Answer · 2015-10-23 12:35:11Z

1

cursor_local.execute("insert into myTable values(%d,%s,%s) on duplicate key update id = id+1000",(int(i[0]),i[1],i[2]));

edited Oct 23, 2015 at 12:35

answered Oct 23, 2015 at 12:13

Alex

17.3k1 gold badge31 silver badges52 bronze badges

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

3 Comments

user3646858 Over a year ago

File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 159, in execute query = query % db.literal(args) TypeError: %d format: a number is required, not str

user3646858 Over a year ago

i am using %d which cant concatenate with a string so

Alex Over a year ago

i[0] is a string, but integer should be there

Collectives™ on Stack Overflow

Error while inserting duplicate values into DP , using ON Duplicate Key using python mysql DB

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related