CODEWITHSUNDEEP
pythonmysqlpymysql
Stylize
Stylize

Reputation: 1098

pymysql not inserting data; but "autoincrement" increases

this is a follow-up from https://stackoverflow.com/questions/33336963/use-a-python-dictionary-to-insert-into-mysql/33337128#33337128. 

import pymysql 
conn = pymysql.connect(server, user , password, "db")
    cur = conn.cursor()
    ORFs={'E7': '562', 'E6': '83', 'E1': '865', 'E2': '2756 '}
    table="genome"
    cols = ORFs.keys()
    vals = ORFs.values()
    sql = "INSERT INTO %s (%s) VALUES(%s)" % (
    table, ",".join(cols), ",".join(vals))

    print sql
    print ORFs.values()
    cur.execute(sql)


    cur.close()
    conn.close()

Thanks to Xiaohen, my program works (i.e. it does not throw any errors), but when I go and check the mysql database, the data is not inserted. I noticed that the autoincrement ID column does increase with every failed attempt. So this suggests that I am at least making contact with the database?

As always, any help is much appreciated

EDIT: I included the output from mysql> show create table genome;

| genome | CREATE TABLE `genome` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `state` char(255) DEFAULT NULL,
  `CG` text,
  `E1` char(25) DEFAULT NULL,
  `E2` char(25) DEFAULT NULL,
  `E6` char(25) DEFAULT NULL,
  `E7` char(25) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 2

Views: 1488

Answers (2)

Stylize
Stylize

Reputation: 1098

Think I figured it out. I will add the info here in case someone else comes across this question:

I need to add conn.commit() to the script

Upvotes: 4

huang
huang

Reputation: 519

You can use

 try:
     cur.execute(sql)
 except Exception, e:
     print e

If your code is wrong, the exception can tell you. And it has another question. the cols and vals are not match. The values should be 

vals = [dict[col] for col in  cols]

Upvotes: 1

Related Questions