Pymysql Insert Into not working

Question

I'm running this from PyDev in Eclipse...

import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='userid', passwd='password', db='fan')
cur = conn.cursor()
print "writing to db"
cur.execute("INSERT INTO cbs_transactions(leagueID) VALUES ('test val')")
print "wrote to db"

The result is, at the top of the Console it says C:...test.py, and in the Console:

writing to db wrote to db

So it's not terminating until after the execute command. But when I look in the table in MySQL it's empty. A record did not get inserted.

First off, why isn't it writing the record. Second, how can I see a log or error to see what happened. Usually there should be some kind of error in red if the code fails.

Possible duplicate of Database does not update automatically with MySQL and Python — Simeon
– Simeon, Commented Feb 7, 2018 at 9:11

qmorgan · Accepted Answer · 2013-12-10 03:17:55Z

140

Did you commit it? conn.commit()

answered Dec 10, 2013 at 3:17

qmorgan

4,9242 gold badges21 silver badges14 bronze badges

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

1 Comment

qarly_blue Over a year ago

you're my hero!

Steely Wing · Accepted Answer · 2019-06-10 01:23:10Z

126

PyMySQL disable autocommit by default, you can add autocommit=True to connect():

conn = pymysql.connect(
    host='localhost',
    user='user',
    passwd='passwd',
    db='db',
    autocommit=True
)

or call conn.commit() after insert

edited Jun 10, 2019 at 1:23

answered Mar 27, 2014 at 7:28

Steely Wing

17.9k10 gold badges62 silver badges55 bronze badges

5 Comments

davesave Over a year ago

Thanks. I was looking for autocommit example. Excellent!

pythonian29033 Over a year ago

guys in 2019 this is not working when using pymysql in python3.x where's this project so that we can create an issue?

Ernesto Over a year ago

Something I could not find easily was that you can change the autocommit property later with the method connection.autocommit(True)

George Ogden Over a year ago

This is really helpful, but I cannot believe that they would not do this by default!

Fellipe Over a year ago

in the documentation there is no 'autocommit' parameter when calling the 'pymysql.connect' function pymssql.org/ref/pymssql.html#pymssql.connect

Simeon · Accepted Answer · 2018-02-07 09:09:27Z

4

You can either do

conn.commit() before calling close

or

enable autocommit via conn.autocommit(True) right after creating the connection object.

Both ways have been suggested from various people at a duplication of the question that can be found here: Database does not update automatically with MySQL and Python

answered Feb 7, 2018 at 9:09

Simeon

8181 gold badge11 silver badges28 bronze badges

Collectives™ on Stack Overflow

Pymysql Insert Into not working

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related