Arch Linux

mrunion

Member

From: Jonesborough, TN

Registered: 2007-01-26

Posts: 1,938

Website

Python Script to Generate MySQL Stored Routines

Here is a quick Python script that reads a MySQL scheme (database) and for each table, it generates Insert, Update, Get and Delete stored routines.  The script is just a "quick-n-dirty" script, but it does take into account that the Update, Get and Delete routines need to key off the primary key (and if there's not one, there could be trouble!).  Each stored routine is output into a separate file.

I'm attaching the script so you professional Python people can tell me where I could improve the script with respect to simplicity, readability, cleanliness, etc.

I have NOT yet got the command line parameters added to this script, but plan to soon.

#!/usr/bin/env python
#
#       spgen.py
#       
#       Copyright 2008 Matt Runion <mrunion@bigpony.com>

import sys
import MySQLdb

# The INSERT template
insertTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    INSERT INTO %s (
        %s
    ) VALUES (
        %s
    );
END$$

DELIMITER ;
"""

# The UPDATE template
updateTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    UPDATE %s SET
        %s
    WHERE
        %s;
END$$

DELIMITER ;
"""

# The GET template
getTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    SELECT
        %s
    FROM %s
    WHERE
        %s;
END$$

DELIMITER ;
"""

# The DELETE template
deleteTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    DELETE FROM %s
    WHERE
        %s;
END$$

DELIMITER ;
"""

def generateSPs(dbhost, dbname, dbuser, dbpasswd):
    """
    This method reads all the tables from the database and for each of them
    generates the following stored routines:
    
    <TableName>_Insert
    <TableName>_Update
    <TableName>_Get
    <TableName>_Delete
    """
    
    # Open the database connection
    print 'Connecting to database [%s] on host [%s]' % (dbname, dbhost)
    dbConn = MySQLdb.connect(host=dbhost, user=dbuser, passwd=dbpasswd, db=dbname)
    cur = dbConn.cursor()
    
    # Get a list of all tables in the database
    print 'Reading tables...'
    cur.execute("SHOW TABLES FROM %s" % dbname)
    tables = cur.fetchall()
    for table in tables:
        print 'Generating stored procs for table [%s]...' % table[0]
        
        # Get a list of a columns in the current table
        cur.execute("SHOW COLUMNS FROM %s" % table[0])
        columns = cur.fetchall()
        insertUpdateParms = ''
        getDeleteParms = ''
        whereClause = ''
        insertList = ''
        valuesList = ''
        updateList = ''
        
        for column in columns:
            # Reset some variables
            print '    %s -- %s [%s, %s, %s]' % (column[0], column[1], column[2], column[3], column[4])
            
            # Append the columns to the input parms
            if (len(insertUpdateParms) > 0):
                insertUpdateParms += ',\n'
                insertList += ',\n'
                valuesList += ',\n'
            
            if (len(updateList) > 0):
                updateList += ',\n'
                
            insertUpdateParms += '%sIN ' % column[0]
            if ((column[1][0:3].lower() == 'var') or (column[1][0:3].lower() == 'cha')):
                insertUpdateParms += '%s' % column[1]
            elif (column[1][0:3].lower() == 'enu'):
                insertUpdateParms += 'varchar(50)'
            else:
                insertUpdateParms += (column[1].split('('))[0]
            
            insertList += column[0]
            valuesList += '%sIN' % column[0]
            
            # Generate the key parms that are used for the Get and Delete
            # stored procs, and generate the values for the WHERE clause
            # for the Update, Get and Delete stored procs
            if (column[3].lower() == 'pri'):
                if (len(getDeleteParms) > 0):
                    getDeleteParms += ',\n'
                
                getDeleteParms += '%sIN ' % column[0]
                if (column[1][0:3].lower() == 'var'):
                    getDeleteParms += '%s' % column[1]
                else:
                    getDeleteParms += (column[1].split('('))[0]
                
                if (len(whereClause) > 0):
                    whereClause += ' AND \n'
                whereClause += '%s = %sIN' % (column[0], column[0])
            else:
                updateList += '%s = %sIN' % (column[0], column[0])
        
        #print '---'
        #print insertUpdateParms
        #print '---'
        #print getDeleteParms
        #print '---'
        #print whereClause
        
        #print 'INSERT:'
        #print insertTemplate % (table[0] + '_Insert', table[0] + '_Insert', insertUpdateParms, table[0], insertList, valuesList)
        #print 'UPDATE:'
        #print updateTemplate % (table[0] + '_Update', table[0] + '_Update', insertUpdateParms, table[0], updateList, whereClause)
        #print 'GET:'
        #print getTemplate % (table[0] + '_Get', table[0] + '_Get', getDeleteParms, insertList, table[0], whereClause)
        #print 'DELETE:'
        #print deleteTemplate % (table[0] + '_Delete', table[0] + '_Delete', getDeleteParms, table[0], whereClause)
        
        # Write out the INSERT stored proc
        file = open('./test/' + table[0] + '_Insert', 'w')
        file.write(insertTemplate % (table[0] + '_Insert', table[0] + '_Insert', insertUpdateParms, table[0], insertList, valuesList))
        file.close()
        
        # Write out the UPDATE stored proc
        file = open('./test/' + table[0] + '_Update', 'w')
        file.write(updateTemplate % (table[0] + '_Update', table[0] + '_Update', insertUpdateParms, table[0], updateList, whereClause))
        file.close()
        
        # Write out the GET stored proc
        file = open('./test/' + table[0] + '_Get', 'w')
        file.write(getTemplate % (table[0] + '_Get', table[0] + '_Get', getDeleteParms, insertList, table[0], whereClause))
        file.close()
        
        # Write out the DELETE stored proc
        file = open('./test/' + table[0] + '_Delete', 'w')
        file.write(deleteTemplate % (table[0] + '_Delete', table[0] + '_Delete', getDeleteParms, table[0], whereClause))
        file.close()
    
    return 0

if __name__ == '__main__':
    generateSPs('<SERVER>', '<DATABASE>', '<USER>', '<PASSWD>')

---

Matt

"It is very difficult to educate the educated."

mrunion

Member

From: Jonesborough, TN

Registered: 2007-01-26

Posts: 1,938

Website

Re: Python Script to Generate MySQL Stored Routines

Updated to allow passing command line parameters for configuration:

#!/usr/bin/env python
#
#       spgen.py
#       
#       Copyright 2008 Matt Runion <mrunion@bigpony.com>
#       
#       This program is free software; you can redistribute it and/or modify
#       it under the terms of the GNU General Public License as published by
#       the Free Software Foundation; either version 2 of the License, or
#       (at your option) any later version.
#       
#       This program is distributed in the hope that it will be useful,
#       but WITHOUT ANY WARRANTY; without even the implied warranty of
#       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#       GNU General Public License for more details.
#       
#       You should have received a copy of the GNU General Public License
#       along with this program; if not, write to the Free Software
#       Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
#       MA 02110-1301, USA.

import sys
import getopt
import os
import MySQLdb

# The INSERT template
insertTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    INSERT INTO %s (
        %s
    ) VALUES (
        %s
    );
END$$

DELIMITER ;
"""

# The UPDATE template
updateTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    UPDATE %s SET
        %s
    WHERE
        %s;
END$$

DELIMITER ;
"""

# The GET template
getTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    SELECT
        %s
    FROM %s
    WHERE
        %s;
END$$

DELIMITER ;
"""

# The DELETE template
deleteTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    DELETE FROM %s
    WHERE
        %s;
END$$

DELIMITER ;
"""

def generateSPs(dbhost, dbname, dbuser, dbpasswd, outPath):
    """
    This method reads all the tables from the database and for each of them
    generates the following stored routines:
    
    <TableName>_Insert
    <TableName>_Update
    <TableName>_Get
    <TableName>_Delete
    """
    
    # Open the database connection
    print 'Connecting to database [%s] on host [%s]' % (dbname, dbhost)
    dbConn = MySQLdb.connect(host=dbhost, user=dbuser, passwd=dbpasswd, db=dbname)
    cur = dbConn.cursor()
    
    # Get a list of all tables in the database
    print 'Reading tables...'
    cur.execute("SHOW TABLES FROM %s" % dbname)
    tables = cur.fetchall()
    for table in tables:
        print 'Generating stored procs for table [%s]...' % table[0]
        
        # Get a list of a columns in the current table
        cur.execute("SHOW COLUMNS FROM %s" % table[0])
        columns = cur.fetchall()
        insertUpdateParms = ''
        getDeleteParms = ''
        whereClause = ''
        insertList = ''
        valuesList = ''
        updateList = ''
        
        for column in columns:
            # Reset some variables
            print '    %s -- %s [%s, %s, %s]' % (column[0], column[1], column[2], column[3], column[4])
            
            # Append the columns to the input parms
            if (len(insertUpdateParms) > 0):
                insertUpdateParms += ',\n'
                insertList += ',\n'
                valuesList += ',\n'
            
            if (len(updateList) > 0):
                updateList += ',\n'
                
            insertUpdateParms += '%sIN ' % column[0]
            if ((column[1][0:3].lower() == 'var') or (column[1][0:3].lower() == 'cha')):
                insertUpdateParms += '%s' % column[1]
            elif (column[1][0:3].lower() == 'enu'):
                insertUpdateParms += 'varchar(50)'
            else:
                insertUpdateParms += (column[1].split('('))[0]
            
            insertList += column[0]
            valuesList += '%sIN' % column[0]
            
            # Generate the key parms that are used for the Get and Delete
            # stored procs, and generate the values for the WHERE clause
            # for the Update, Get and Delete stored procs
            if (column[3].lower() == 'pri'):
                if (len(getDeleteParms) > 0):
                    getDeleteParms += ',\n'
                
                getDeleteParms += '%sIN ' % column[0]
                if (column[1][0:3].lower() == 'var'):
                    getDeleteParms += '%s' % column[1]
                else:
                    getDeleteParms += (column[1].split('('))[0]
                
                if (len(whereClause) > 0):
                    whereClause += ' AND \n'
                whereClause += '%s = %sIN' % (column[0], column[0])
            else:
                updateList += '%s = %sIN' % (column[0], column[0])
        
        # Write out the INSERT stored proc
        file = open(os.path.join(outPath,table[0] + '_Insert'), 'w')
        file.write(insertTemplate % (table[0] + '_Insert', table[0] + '_Insert', insertUpdateParms, table[0], insertList, valuesList))
        file.close()
        
        # Write out the UPDATE stored proc
        file = open(os.path.join(outPath,table[0] + '_Update'), 'w')
        file.write(updateTemplate % (table[0] + '_Update', table[0] + '_Update', insertUpdateParms, table[0], updateList, whereClause))
        file.close()
        
        # Write out the GET stored proc
        file = open(os.path.join(outPath,table[0] + '_Get'), 'w')
        file.write(getTemplate % (table[0] + '_Get', table[0] + '_Get', getDeleteParms, insertList, table[0], whereClause))
        file.close()
        
        # Write out the DELETE stored proc
        file = open(os.path.join(outPath,table[0] + '_Delete'), 'w')
        file.write(deleteTemplate % (table[0] + '_Delete', table[0] + '_Delete', getDeleteParms, table[0], whereClause))
        file.close()
    
    return 0
    
def main(argv):
    """
    SPGen reads all the tables from the given database and for each of
    those tables generates the following stored routines:
    
    <TableName>_Insert
    <TableName>_Update
    <TableName>_Get
    <TableName>_Delete
    
    Command line arguments are:
    -?, --help:             Help
    -o, --outputpath:       File output path
    -h, --host:             Database host/server
    -d, --database:         Database name
    -u, --user:             Database user
    -p, --password          Database password
    """
    
    # Set defaults...
    outputPath = os.getcwd()
    host = 'localhost'
    database = ''
    user = ''
    password = ''
    
    # See what command line options we have
    try:
        opts,  args = getopt.getopt(argv[1:], '?o:h:d:u:p:',  ['help', 'outputpath=', 'host=', 'database=', 'user=', 'password='])
    except getopt.GetoptError:
        print main.__doc__
        sys.exit(2)
    
    for opt, arg in opts:
        if opt in ['-?', '--help']:
            print main.__doc__
            sys.exit()
        elif opt in ['-o', '--outputpath']:
            outputPath = arg
        elif opt in ['-h', '--host']:
            host = arg
        elif opt in ['-d', '--database']:
            database = arg
        elif opt in ['-u', '--user']:
            user = arg
        elif opt in ['-p', '--password']:
            password = arg
    
    generateSPs(dbhost=host, dbname=database, dbuser=user, dbpasswd=password, outPath=outputPath)

if __name__ == '__main__':
    main(sys.argv)

---

Matt

"It is very difficult to educate the educated."

mrunion

Member

From: Jonesborough, TN

Registered: 2007-01-26

Posts: 1,938

Website

Re: Python Script to Generate MySQL Stored Routines

I found and fixed a bug with some misplaced parenthesis that forced the path to be made all lower-case.  This was a bad thing if the path had some upper case letters in it!

#!/usr/bin/env python
#
#       spgen.py
#       
#       Copyright 2008 Matt Runion <mrunion@bigpony.com>
#       
#       This program is free software; you can redistribute it and/or modify
#       it under the terms of the GNU General Public License as published by
#       the Free Software Foundation; either version 2 of the License, or
#       (at your option) any later version.
#       
#       This program is distributed in the hope that it will be useful,
#       but WITHOUT ANY WARRANTY; without even the implied warranty of
#       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#       GNU General Public License for more details.
#       
#       You should have received a copy of the GNU General Public License
#       along with this program; if not, write to the Free Software
#       Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
#       MA 02110-1301, USA.

import sys
import getopt
import os
import MySQLdb

# The INSERT template
insertTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    INSERT INTO %s (
        %s
    ) VALUES (
        %s
    );
END$$

DELIMITER ;
"""

# The UPDATE template
updateTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    UPDATE %s SET
        %s
    WHERE
        %s;
END$$

DELIMITER ;
"""

# The GET template
getTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    SELECT
        %s
    FROM %s
    WHERE
        %s;
END$$

DELIMITER ;
"""

# The DELETE template
deleteTemplate = """
DELIMITER $$

DROP PROCEDURE IF EXISTS `%s`$$
CREATE PROCEDURE `%s` (%s)
BEGIN
    DELETE FROM %s
    WHERE
        %s;
END$$

DELIMITER ;
"""

def generateSPs(dbhost, dbname, dbuser, dbpasswd, outPath):
    """
    This method reads all the tables from the database and for each of them
    generates the following stored routines:
    
    <TableName>_Insert
    <TableName>_Update
    <TableName>_Get
    <TableName>_Delete
    """
    
    # Open the database connection
    print 'Connecting to database [%s] on host [%s]' % (dbname, dbhost)
    dbConn = MySQLdb.connect(host=dbhost, user=dbuser, passwd=dbpasswd, db=dbname)
    cur = dbConn.cursor()
    
    # Get a list of all tables in the database
    print 'Reading tables...'
    cur.execute("SHOW TABLES FROM %s" % dbname)
    tables = cur.fetchall()
    for table in tables:
        print 'Generating stored procs for table [%s]...' % table[0]
        
        # Get a list of a columns in the current table
        cur.execute("SHOW COLUMNS FROM %s" % table[0])
        columns = cur.fetchall()
        insertUpdateParms = ''
        getDeleteParms = ''
        whereClause = ''
        insertList = ''
        valuesList = ''
        updateList = ''
        
        for column in columns:
            # Reset some variables
            print '    %s -- %s [%s, %s, %s]' % (column[0], column[1], column[2], column[3], column[4])
            
            # Append the columns to the input parms
            if (len(insertUpdateParms) > 0):
                insertUpdateParms += ',\n'
                insertList += ',\n'
                valuesList += ',\n'
            
            if (len(updateList) > 0):
                updateList += ',\n'
                
            insertUpdateParms += '%sIN ' % column[0]
            if ((column[1][0:3].lower() == 'var') or (column[1][0:3].lower() == 'cha')):
                insertUpdateParms += '%s' % column[1]
            elif (column[1][0:3].lower() == 'enu'):
                insertUpdateParms += 'varchar(50)'
            else:
                insertUpdateParms += (column[1].split('('))[0]
            
            insertList += column[0]
            valuesList += '%sIN' % column[0]
            
            # Generate the key parms that are used for the Get and Delete
            # stored procs, and generate the values for the WHERE clause
            # for the Update, Get and Delete stored procs
            if (column[3].lower() == 'pri'):
                if (len(getDeleteParms) > 0):
                    getDeleteParms += ',\n'
                
                getDeleteParms += '%sIN ' % column[0]
                if (column[1][0:3].lower() == 'var'):
                    getDeleteParms += '%s' % column[1]
                else:
                    getDeleteParms += (column[1].split('('))[0]
                
                if (len(whereClause) > 0):
                    whereClause += ' AND \n'
                whereClause += '%s = %sIN' % (column[0], column[0])
            else:
                updateList += '%s = %sIN' % (column[0], column[0])
        
        # Write out the INSERT stored proc
        file = open(os.path.join(outPath,(table[0] + '_Insert.sql').lower()), 'w')
        file.write(insertTemplate % (table[0] + '_Insert', table[0] + '_Insert', insertUpdateParms, table[0], insertList, valuesList))
        file.close()
        
        # Write out the UPDATE stored proc
        file = open(os.path.join(outPath,(table[0] + '_Update.sql').lower()), 'w')
        file.write(updateTemplate % (table[0] + '_Update', table[0] + '_Update', insertUpdateParms, table[0], updateList, whereClause))
        file.close()
        
        # Write out the GET stored proc
        file = open(os.path.join(outPath,(table[0] + '_Get.sql').lower()), 'w')
        file.write(getTemplate % (table[0] + '_Get', table[0] + '_Get', getDeleteParms, insertList, table[0], whereClause))
        file.close()
        
        # Write out the DELETE stored proc
        file = open(os.path.join(outPath,(table[0] + '_Delete.sql').lower()), 'w')
        file.write(deleteTemplate % (table[0] + '_Delete', table[0] + '_Delete', getDeleteParms, table[0], whereClause))
        file.close()
    
    return 0
    
def main(argv):
    """
    SPGen reads all the tables from the given database and for each of
    those tables generates the following stored routines:
    
    <TableName>_Insert
    <TableName>_Update
    <TableName>_Get
    <TableName>_Delete
    
    Command line arguments are:
    -?, --help:             Help
    -o, --outputpath:       File output path
    -h, --host:             Database host/server
    -d, --database:         Database name
    -u, --user:             Database user
    -p, --password          Database password
    """
    
    # Set defaults...
    outputPath = os.getcwd()
    host = 'localhost'
    database = ''
    user = ''
    password = ''
    
    # See what command line options we have
    try:
        opts,  args = getopt.getopt(argv[1:], '?o:h:d:u:p:',  ['help', 'outputpath=', 'host=', 'database=', 'user=', 'password='])
    except getopt.GetoptError:
        print main.__doc__
        sys.exit(2)
    
    for opt, arg in opts:
        if opt in ['-?', '--help']:
            print main.__doc__
            sys.exit()
        elif opt in ['-o', '--outputpath']:
            outputPath = arg
        elif opt in ['-h', '--host']:
            host = arg
        elif opt in ['-d', '--database']:
            database = arg
        elif opt in ['-u', '--user']:
            user = arg
        elif opt in ['-p', '--password']:
            password = arg
    
    generateSPs(dbhost=host, dbname=database, dbuser=user, dbpasswd=password, outPath=outputPath)

if __name__ == '__main__':
    main(sys.argv)

Last edited by mrunion (2008-11-20 19:33:37)

---

Matt

"It is very difficult to educate the educated."