Regex Search, Replace, and Uppercase MySQL Table Names with Python

While very popular, MySQL is a database with a number of sometimes annoying quirks. On Windows, MySQL stores table names in lower case and does case insensitive comparisons by default. On Linux, it stores table names in their original cases and comparisons are case sensitive. This has obvious implications if one is trying to develop a database on Windows and then restore it to a Linux based production server, and is just the problem that I ran into. I know that I can reset those defaults to make this easier, but that really wasn’t an option on the production server, which hosts other databases that would have been effected by a change like this. So I decided that the best bet, being pressed for time, was to simply convert the lowercase table names in the mysqldump generated SQL dump, to the uppercase versions needed for the app running on the server. Having over 150 tables, in a 12MB dump file made changing these by hand extremely undesirable.

Regular expressions and Python to the rescue! Searching the web for a script that would find all text enclosed in back ticks and uppercase it, proved fruitless. And which my favorite editor for this, notepad++, can find and replace using regular expressions, it did not have any function I could invoke to perform the uppercasing automatically. So I present the following code I wrote to do the job. It’s very simple, only the very last part of how to grab the matched text that I wanted to uppercase, took any time. Please feel free to use this any way you like! I think it can serve as a good base for any similar search and replace operations. I used A.M. Kuchling’s Regular Expression HOWTO to learn how to use regex in python.

# uppercase_table_names.py
#
# utility script to find and uppercase all
# table names and references in mysql sql
# script. this actually uppercases anything
# in back ticks which is fine for my purposes
#
# feel free to use this script anyway you like
# -- blueZhift 2007-08-19
# http://bluezhift.proliphus.com

# see http://www.amk.ca/python/howto/regex/
# for more about using regular expressions in python
import re

def make_uppercase (match):
    input_string = str(match.group())
    return input_string.upper()

print "Beginning Program\n"

output_file=open("outputfile.sql", "w")
output_file.write("-- Uppercased Output\n")

# complile regular expression to look for
search_pattern = re.compile(r"`(.[^`]*)`", re.VERBOSE)

input_file = open("inputfile.sql")
for line in input_file.readlines():
    # find and replace
    edited_line = search_pattern.sub(make_uppercase,line)
    output_file.write(edited_line)
    print edited_line


input_file.close
output_file.close
print "Program Completed\n"

Click here to download source code plus input file example. (uppercase_table_names.zip)