[Tutor] inexplicable MySQLdb behavior, INSERT data doesn't appear

[Danny Yoo]

On Tue, 19 Apr 2005 tpc at csua.berkeley.edu wrote:

> hi all, while recently trying to insert some data into the following
> table:
>
> # stores unique course definitions
> CREATE TABLE adminCourses (
> ID TINYINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
> Code CHAR(6),
> Title VARCHAR(55),
> Units TINYINT UNSIGNED
> ) TYPE = InnoDB;
>
> I got a 1L value when I ran the following:
>
> cursor.execute("INSERT INTO adminCourses(ID, Code, Title, Units) VALUES
> (NULL, 'EAT100', 'Josue', 30);")

Hi Tpc,


Do you know if MySQLdb still continues to use AUTOCOMMIT mode, or has this
been changed in new releases of the module?  According to PEP 249:


    http://www.python.org/peps/pep-0249.html

Database modules that conform to the standard are required to keep from
doing autocommits:

"""
            Commit any pending transaction to the database. Note that
            if the database supports an auto-commit feature, this must
            be initially off. An interface method may be provided to
            turn it back on.
"""

But MySQL didn't have transactions for the longest time, so this never
really worked well until MySQL4 with InnoDB table support.


Now that MySQL is using transactions, it's possible that MySQLdb may have
autocommit off by default now.  In fact, it looks like it, as there's a
snippet of code in the driver now with the following:

######
        self._transactional = (self.server_capabilities &
                               CLIENT.TRANSACTIONS)
        if self._transactional:
            # PEP-249 requires autocommit to be initially off
            self.autocommit(0)
######


Ah, ok, so it does look like MySQL now doesn't autocommit by default.
That's new: I'll have to remember that.

Try doing a conn.commit() at the end of your database programs.



Best of wishes to you!