Revisiting Python mysqldb exceptions

Back in September, I posted about Python mysqldb exceptions.

mysqldb is a MySQL database interface for Python programmers.

Back then, I thought this annoying problem had been fixed. After many weeks, actually months, of continuing to get these annoying integer value out of bounds errors, when data was being written into a MySQL table, I took another look at the mysqldb documentation and set the try except code a little differently.  So, a long term, fairly benign problem is solved.

The problem’s causes were two-fold. There were some meter ids (integer field) that were coming through as null strings, and I had fixed those quite some time ago, but exceptions continued. It turned out there were a few more meter ids that exceeded the value of a signed integer, and were fairly close to the upper bound of an unsigned integer, but my try except blocks were set up incorrectly and also did not print out enough information, like the meter id that caused the fuss.

I changed the column type to BIGINT as well as the original fix of making sure an integer 0 is substituted for any meter id that is a null string. The exceptions problem problem is now corrected. This was certainly neither a big problem nor one that was going to cause a problem, but fixing it was important and an important learning experience. Has I believed that null strings were taken care of and printed out the rest of the exceptions, all this would have been fixed a long time ago.

So, the motto of this post is believe in what you do, but it does not hurt to have proof that what you did also did what you expected it to.

Below is the source code from a little bot that runs periodically to clear up our MySQL database from daily water meter reads.

import sys
import os
import datetime
from datetime import datetime, timedelta, date

from util_lib import *
from util_class_lib import *
from mysql_lib import *

def main():
   print("Start daily reads archive.")

   Set up objects needed for this program.
   Find today's date.
   Calculate back to find 6 months ago from the first day of the month.
   Then move daily_reads to daily_reads_archive for all LoadDateLocal values < cutoff_date_time.

   gd = createGlobalData(getMySQLDateTime())

   1) Get today's date.

   2) Get the number of days aproximately 3 months into the future starting
      from the first of this month. Divide by 4 to get aproximately 90 days.

   3) Take leap year into account, when dividing.

   4) Calculate that time as a string for the query, cutoff_date_time.

   5) Create two queries and execute them in order.

   6) The first copies into daily_reads_archive.

   7) The second deletes from daily_reads.

   cur_day =

   if 0 == cur_day.year % 4:
      cutoff_date = cur_day - timedelta( + (366/4))
      cutoff_year = cur_day - timedelta( + 366)
      cutoff_date = cur_day - timedelta( + (365/4))
      cutoff_year = cur_day - timedelta( + 365)

   We're only taking the day and a space. We'll add midnight as the time.

   cutoff_date_time      = str(cutoff_date)[0:11] + " 00:00:00"
   cutoff_date_time_year = str(cutoff_year)[0:11] + " 00:00:00"

   print("Dates calculated.")

   db = connectToMysqlDB(None)

   wk_cur = db.cursor()

   print("Database connection established.")

   sql_cmd1 = "insert into daily_reads_archive " + 
   "select d.*, " + "'" + returnMySQLNowStr() + "' " + 
     "from daily_reads d " + 
     "where d.LoadDateLocal < " + "'" + cutoff_date_time + "' ; "

   sql_cmd2 = "delete from daily_reads " + 
      "where LoadDateLocal < " + "'" + cutoff_date_time + "' ; "

   sql_cmd3 = "delete from daily_reads_archive " + 
   "where LoadDateLocal < " + "'" + cutoff_date_time_year + "' ; "

   print("Queries created.")

   with warnings.catch_warnings():
      warnings.simplefilter('error', MySQLdb.Warning)

      print("Executing delete from daily_reads_archive query.")
      rc = wk_cur.execute(sql_cmd3)

      print("Executing copy to daily_reads_archive query.")
      rc = wk_cur.execute(sql_cmd1)

      print("Executing delete from daily_reads query.")
      rc = wk_cur.execute(sql_cmd2)

   except MySQLdb.Error, e:
      print("Here is the MySQL error: ", e)
      print("Here are the queries:n", sql_cmd1, "n", sql_cmd2, "n", sql_cmd3)

   except MySQLdb.Warning, e:
      print("Here is the MySQL warning ", e)

   print("Archiving complete.")

if __name__ == "__main__":


Leave a comment

Filed under MySQL, python

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s