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__":

Decision To Implement Progamming Language

As many already know, our town started on a large water project nearly two years ago. Specifically, the project’s goal was to change how water meters were read. Nearly twenty years ago, a similar project made the meter’s reading accessible on the outside of most structures, except where meters were installed in manholes and so-called “pits”. Either an ARB in early years or a touchpad within the last five years, was installed to facilitate reading the meter from outside the structure, that is within 50-100′ of a meter.

The current project is implementing a “fixed network”, by replacing the ARB or touchpad with a radio endpoint. The fixed part of the fixed network means that collectors listen to the endpoints; gather reads that are bubbled up throughout the day; and send them back to an application that matches the endpoint serial number with its associated customer data. Electric and gas meters often participate in a mobile network, where a vehicle traverses a route that comes near to all endpoints in the area, so they can be read.

The piece that sent customer configuration to the fixed network application and processed the water meter reads generated daily fell to the town’s IT department to implement. We call the application and server AMR (automated meter reading).

Two of this server’s primary functions are automatic. It checks for and processes customer configuration data, and sends it to the endpoint configuration system, where the meter reads are processed from the fixed network. The other function is to process daily meter reads. Other functions, like collecting reads for a billing cycle and reports comprise the other functions. It is a goal to have a web application facilitate configuration, report generation, and snap-shotting reads for a billing cycle.

One of our goals was to use as much open source as possible, including Python. Python makes up nearly 100% of the software implementation. Our python software makes use of the mysqldb module, which facilitates a SQL access to the MySQL database.

Our current feelings are that Python was the right choice. It is a language widely taught in high school, and there is a large pool of Python programming talent. Any instances where we felt speed was an issue, was not due to Python, but both schema implementation and a need to re-write the SQL code.

And, like most software projects, there is a constant need to continue implementing functions and fix bugs. Python has worked well in this area, without encouraging a lot of patch-work (spaghetti) code.

A Good Example of Time Wasted

This is a story about letting something be important that is not; poking at — investigating — that unimportant thing over several months without devoting enough time to find out what was really going on; and finally solving the problem.

As readers of already know, I’m involved in working on an Automated Meter Reading (AMR) data transfer system. The main functions of the system are to store daily water meter reads from our Itron wireless meter reading system; store and push meter configuration to that wireless system; and snap-shot meter reads for water bills.

From the very moment the daily reads were automated in late May 2010, the python code would spit out this error:

/home/amr/bin/ Warning: Incorrect integer value: '' for column 'MeterID' at row 1
rc = ins_cursor.execute(sql_info[sqlCd.cmd], sql_info[])
/home/amr/bin/ Warning: Out of range value adjusted for column 'MeterID' at row 1
rc = ins_cursor.execute(sql_info[sqlCd.cmd], sql_info[])
Downloading files from Itron.

There seemed to be two obvious ways to solve this problem:


  test_meter_val = int(meter_val)
except ValueError:
  meter_val = 0


  rc = \
  ins_cursor.execute(sql_info[sqlCd.cmd], \
except MySQLdb.Error, e:
  raise e
except MySQLdb.Warning, e:
  print("Here is the MySQL warning ", e)

Well as it stands, the MySQLdb.Warning never triggered, because I needed to re-read this article —

Then I finally got the plain try except block to work.

However, getting it to work required more than a cursory glance and a one line code fix. It required a test system where the database was not the production database; hence no production data was at risk.

The moral of the story is this. Don’t chase down this kind of warning, unless it is going to cost you something big, and if you are going to chase down the problem, sink enough time in it to understand fully what is going on. I failed on both counts.