Database Primary Key is incrementing, but no new rows are being added

Question

I am writing my first python script, and I am trying to connect it to a mysql db to insert rows.

import MySQLdb

db = MySQLdb.connect("localhost","root","xxx","pytest" )

cursor = db.cursor()

cursor.execute("INSERT INTO `first_table` (`name`) VALUES ('boop') ")

When I check the mysql db via phpmyadmin, it contains no rows, however if the auto incrementing ID was 5 and then I run the script 2 times, when I insert a new row it inserts it as id= 8 so the script has been incrementing the primary key but not inserting the rows?

The script reports no mysql errors, so I'm a bit lost here.

you forgot to call commit, call `db.commit()` after insert query — Grijesh Chauhan, Jun 25 '13 at 11:33
ah ok...I am used to working with PHP, why is a commit required? what does it do? — Melbourne2991, Jun 25 '13 at 11:34
[`MySQLConnection.commit()`](http://dev.mysql.com/doc/connector-python/en/myconnpy_MySQLConnection_commit.html) — Grijesh Chauhan, Jun 25 '13 at 11:38
@Melbourne2991, a commit "saves" the state of the database. That is to say that it writes all pending transactions. — John, Jun 25 '13 at 11:38
Hm, in what situations may that be needed out of curiosity? Why would one want their transactions pending @johnthexiii — Melbourne2991, Jun 25 '13 at 11:39
@Melbourne2991 for DB changes, insert, update, delete .. you need commit , Whereas select, join don't need commit — Grijesh Chauhan, Jun 25 '13 at 11:40
You'd want your transactions pending, in case a screw up is made, then you can rollback. — John, Jun 25 '13 at 11:47
There are times when an auto increment primary key is incremented for a new record but then the insert fails. Result is the key is still incremented but no record is inserted. — Kickstart, Jun 25 '13 at 11:49
You also might want to change your password now that it's public... — Gerard Yin, Jun 25 '13 at 11:52

score 2 · Accepted Answer · edited May 23 '17 at 12:29

2

In yuor case please use

import MySQLdb

    db = MySQLdb.connect("localhost","root","jimmypq79","pytest" )
    cursor = db.cursor()
    cursor.execute("INSERT INTO `first_table` (`name`) VALUES ('boop') ")
    db.commit()

Please put this in top of the code like this--

db = MySQLdb.connect("localhost","root","jimmypq79","pytest" )
db.autocommit(True)

check here

edited May 23 '17 at 12:29

Community

1
1

answered Jun 25 '13 at 11:45

Nisarg

3,024
5
32
54

score 1 · Answer 2 · answered Jun 25 '13 at 11:41

1

You can use

 cursor.autocommit(True)

in the beginning of the code for automatically committing the changes .

answered Jun 25 '13 at 11:41

tusharmakkar08

706
1
12
32

The cursor has no attribute 'autocommit', but the connection has. – renz0fr Jan 08 '15 at 15:37

score 1 · Answer 3 · answered Jun 25 '13 at 12:22

1

you use,

  db.commit()

after insert query

answered Jun 25 '13 at 12:22

Marsh

173
1
14

Database Primary Key is incrementing, but no new rows are being added

3 Answers3