Use MySQLdb Python module to apply database migration

  • Posted on: 7 April 2013
  • By: Zhijun Chen

The Python MySQLdb provides great supports to facilitate database migration. Here is an example of connecting to MySQL database:

import MySQLdb
import sys

from optparse import OptionParser

def main():
    usage = 'usage: %prog [options]'
    parser = OptionParser(usage)
    parser.add_option("-n", "--hostname", dest="hostname",
                      help="The hostname of the database to connect to.")
    parser.add_option('-u', '--user', dest='user', type='string',
                      help='The user to use when connecting to the database.')
    parser.add_option('-p', '--password', dest='password', type='string',
                      help='The password of the db user.')
    parser.add_option('-d', '--database', dest='db', type='string',
                      help='The name of the database in the host.')

    (options, args) = parser.parse_args()

    if (options.hostname is None or options.user is None or options.password is None or options.db is None):
        parser.print_help()
        sys.exit()

    conn = MySQLdb.connect (host=options.hostname,
                            user=options.user,
                            passwd=options.password,
                            db=options.db)
    cursor = conn.cursor()
    # perform DDL/DML migration
    _add_constraint_to_tables(cursor)

    # perform TCL operations
    conn.commit()
    conn.close()

if __name__ == "__main__":
    main()
* Useful methods in the module:
  1. cursor.execute(operation)

    This method executes a sql command, e.g. cursor.execute('SELECT * FROM USER WHERE id = %s' % id)

  2. cursor.fetchone()

    This method retrieves the next row of a query result set, returning a single sequence, or None when no more data is available.

  3. cursor.fetchall()

    The method fetches all or remaining rows of a query result set, returning a list of tuples. An empty list is returned when no rows are (anymore) available.

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.