Where's my data??

[David Woods]

I recently upgraded to MySQL for Python 1.2, (I need to go to Unicode,) and now none of my data sticks in the database when I refresh my connection or restart my MySQL Server.

Here's my test script:

import MySQLdb

DBHost = "" # Add your Database Host here
UserName = "" # Add your User Name here
Password = "" # Add your Password here

print
print
print

Establish connection to the DB

d = MySQLdb.connect(host="%s" % DBHost, user="%s" % UserName, passwd="%s" % Password, use_unicode=True)

Create a DB Cursor

c = d.cursor()

s = u"SHOW DATABASES"
c.execute(s)
r = c.fetchall()

if (u'unicodetest',) in r:
print "UnicodeTest exists! Deleting it."

# Drop any old data that might exist
s = u'DROP DATABASE UnicodeTest'
c.execute(s)

else:
print "UnicodeTest does not exist. Creating it."

s = u'CREATE DATABASE UnicodeTest'
c.execute(s)

s = u'USE UnicodeTest'
c.execute(s)

print "Creating Table Test"
s = u"""CREATE TABLE IF NOT EXISTS Test
          (RecNum    INTEGER auto_increment,
           RecId     VARCHAR(100),
           PRIMARY KEY (RecNum))
           TYPE=InnoDB
           CHARACTER SET utf8"""

c.execute(s)

print "Inserting some records into the Test table"
s = u"INSERT INTO Test (RecId) VALUES ('Hello 1')"
c.execute(s)
s = u"INSERT INTO Test (RecId) VALUES ('Hello 2')"
c.execute(s)
s = u"INSERT INTO Test (RecId) VALUES ('Hello 3')"
c.execute(s)
s = u"INSERT INTO Test (RecId) VALUES ('Goodbye 1')"
c.execute(s)

print "Flushing Table Test"
s = u"FLUSH TABLE Test"
c.execute(s)

print "Reading all Records from Table Test"
s = u"SELECT * FROM Test"
c.execute(s)
r = c.fetchall()
print r

print "Closing the Cursor and the Database."
# Close the cursor
c.close()
# Close the database
d.close()

print
print "Re-opening the Database connection"
# Establish connection to the DB
d = MySQLdb.connect(host="%s" % DBHost, user="%s" % UserName, passwd="%s" % Password, use_unicode=True)

# Create a DB Cursor
c = d.cursor()

s = u'USE UnicodeTest'
c.execute(s)

print "Reading all Records from Table Test"
s = u"SELECT * FROM Test"
c.execute(s)
r = c.fetchall()
print r

if c.rowcount < 4:
    print "So.... where's the data??"

Close the cursor

c.close()

Close the database

d.close()

and here's my output:

UnicodeTest does not exist. Creating it.
Creating Table Test
Inserting some records into the Test table
Flushing Table Test
Reading all Records from Table Test
((1L, u'Hello 1'), (2L, u'Hello 2'), (3L, u'Hello 3'), (4L, u'Goodbye 1'))
Closing the Cursor and the Database.

Re-opening the Database connection
Reading all Records from Table Test
()
So.... where's the data??

I've tried it on Windows (Python 2.3.3) and on Mac OS X (Python 2.3), and I've tried it against MySQL 4.0.x and MySQL 4.1.13 databases on Windows, Mac OS X, and Linux. If I use an older version of MySQL for Python, I have to drop the "use_unicode" parameter, but then my data sticks. (Dropping this parameter doesn't help with MySQL for Python 1.2.)

So the problem is limited to MySQL for Python 1.2, but is consistent there. If I use MySQL for Python 1.2, I can't save any data permanently.

Any ideas what could be going on?

David

[David Woods]

Thanks for the help. Things are working beautifully now.

David

[Andy Dustman]

Try executing d.commit() sometime after inserting your rows but before d.close(). Auto-commit is off by default in 1.2.0 and newer to conform with PEP-249.

[David Woods]

Thanks for the quick and helpful reply, Andy.

I ended up adding:

s = u"SET AUTOCOMMIT = 1"
c.execute(s)

at the top of the file just after cursor c was created. I found that this avoided problems I was having with "locked tables or an active transaction" errors when running against OS X and Linux (but not Windows) MySQL Server instances. Also, then I don't have to go through all of my code and add d.commit() statements, as I'm nervous about missing one or two and introducing bugs into my program as well as accidentally inserting them where they would interfere with larger explicit transactions.

If you know of a good reason I shouldn't use this approach, I'd appreciate it if you would post it here.

David

[Andy Dustman]

Whether or not you want to use autocommit is up to you, but it is easier and cleaner to just do this:

d.autocommit(1)