data being inserted into MySQL but not made permanent - Python

Question

I'm using MySQLdb to manipulate a MySQL database, and I have this following routine, to inject some data in a table called urls:

def insert_urls(dbconn, filenames):
    root = "<path>/"
    link = "http://<url>/"
    for f in filenames:
        filename = root + f + ".html"
        local_url = link + f + ".html"
        print(filename, local_url)
        sql = """
        INSERT INTO urls(url, filename) VALUES('%s', '%s');
        """ % (local_url, filename)
        print(sql)
        dbconn.execute_query(sql)

the declaration of the urls table is found here:

def create_urls_table():

    sql = """
        CREATE TABLE IF NOT EXISTS urls (
            id INT NOT NULL AUTO_INCREMENT,
            url BLOB NOT NULL,
            filename BLOB NOT NULL,
            PRIMARY KEY(id)
        ) ENGINE=INNODB;
    """
    return sql

dbconn is an object of the class Dbconn, defined as:

class Dbconn:
    def __init__(self,
                 host="",
                 user="",
                 pwd="",
                 database=""):

        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = database
        self.cursor = None
        self.conn = None

        try:
            self.conn = MySQLdb.connect(host=self.host,
                                        user=self.user,
                                        passwd =self.pwd,
                                        db=self.db)
            self.cursor = self.conn.cursor()
            print "Connection established"
        except MySQLdb.Error, e:
            print "An error has occurred ", e

    def execute_query(self, sql=""):
        try:
            self.cursor.execute(sql)
        except MySQLdb.Error, e:
            print "An error has occurred ", e

After running the procedure insert_urls I get the following output:

  INSERT INTO urls(url, filename) VALUES ('http://<url>/amazon.html','<path>/amazon.html');
  INSERT INTO urls(url, filename) VALUES('http://<url>/linkedin.html', '<path>/linkedin.html');
  INSERT INTO urls(url, filename) VALUES('http://<url>/nytimes.html', '<path>/nytimes.html');

which I was able to inject manually into MySQL through the command line. However doing a SELECT * FROM urls query I found nothing. After I inserted two lines manually I got:

mysql> select * from urls;
+----+------------------------------------------------+------------------------+
| id | url                                            | filename               |
+----+------------------------------------------------+------------------------+
| 19 | http://<url>/yelp.html                         | <path>/yelp.html       |       
| 29 | http://<url>/amazon.html                       | <path>/amazon.html     |
+----+------------------------------------------------+------------------------+

Please note that the id value is being incremented ... which it may means data is being inserted, but not made persistent? Could someone please help me with that?

Answer 1

You are probably using a transactional database, in which case you must call

self.conn.commit()

(Indeed, INNODB is a transactional database.)

---

You could incorporate commit into execute_query:

def execute_query(self, sql=""):
    try:
        self.cursor.execute(sql)
    except MySQLdb.Error as e:
        print "An error has occurred ", e
        self.conn.rollback()
    else:
        self.conn.commit()

However, there could be situations where you wish to execute a number of queries before you call commit or rollback. In that case you would want to remove commit from execute_query and either call commit explicitly when desired, or use a context manager to call commit when you exit the with-suite.

---

Note that MySQLdb connections are context managers. You can write

connection = MySQLdb.connect(
    host=config.HOST, user=config.USER,
    passwd=config.PASS, db=config.MYDB, )

with connection as cursor:
    cursor.execute(...)

and the connection will call connection.commit() upon exiting the with-suite, or connection.rollback() if there was an exception. Here is the code that controls this in MySQLdb.connections.py:

def __enter__(self): return self.cursor()

def __exit__(self, exc, value, tb):
    if exc:
        self.rollback()
    else:
        self.commit()

Answer 2

After your execute() statement, call commit():

self.cursor.execute(sql)
self.conn.commit()

For more info, see: python mysql insert data