How-to write Python for MySQL

First of all, you need to install the Python driver for MySQL module, MySQL-python. The easiest way is to use pip or easy_install to install it.

$ pip install MySQL-python
or
$ easy_install MySQL-python

Then, you can start to write scripts. Here is an example:

#!/usr/bin/python
'''
Create a user
'''
import MySQLdb as mdb


    def __do_create_mysqluser(self):
        isSuccess = False
        
        mysql_pass = self.newuser
        default_db = self.newuser
        mysql_db = 'mysql'
        privileges = self.privileges       
        
        create_mysqluser = "create user %s identified by '%s'" \
                            % (self.newuser, mysql_pass)
        create_db_sql = "create database  %s " % default_db
        grant_mysql = "grant all on %s.* to %s"           \
                       % (default_db, self.newuser)
        if privileges:
            for privilege in privileges.strip().split(';'):
                if privilege:
                    grant_mysql += "; Grant %s to %s" % (privilege, self.newuser)
        check_mysqluser_sql = "select count(*) from mysql.user \
                               where user = '%s' "       \
                               % (self.newuser, )
        check_db_exist_sql = "select count(*) from information_schema.SCHEMATA \
                                where SCHEMA_NAME='%s';" \
                               % default_db
                     
        try:
            #connect to target DB.
            conn = mdb.connect(self.vip, self.sysuser, self.syspass, mysql_db)
            cursor = conn.cursor()
            
            #1: verify whether db exists
            cursor.execute(check_db_exist_sql)
            number = cursor.fetchone()
            if number[0] > 0:
                raise DbUserCreateError('DbUserCreateError: In Mysql DB [' + 
                                        default_db + 
                                         '] Already Exists.')
            #2: check if user already exist.
            cursor.execute(check_mysqluser_sql)
            number = cursor.fetchone()
            if number[0] > 0:
                raise DbUserCreateError('DbUserCreateError: In Mysql DB [' + 
                                        default_db + '] , User ['+ 
                                        self.newuser + '] Already Exists.')                   
            
            #3 create user
            cursor.execute(create_mysqluser)
            #4 create db
            cursor.execute(create_db_sql)
            #5 grant privileges
            cursor.execute(grant_mysql)
             
        except mdb.Error as e: 
            msg = "Mysql Error %d: %s" % (e.args[0], e.args[1])
            self.logger.error(e)
            raise DbUserCreateError("DbUserCreateError: "+msg)
        else:
            isSuccess = True
            self.logger.info('Create Mysql User&DB[' + self.newuser + '] Success!')
        finally:
            try:
                cursor.close()
                conn.close()
            except:
                self.logger.error('mdb.connect or conn.cursor')
        return { 
                'username': self.newuser,
                'password': mysql_pass,
                'db': default_db,
                'success' : isSuccess ,
                }


    def __do_drop_mysqluser(self, db_item):
        sysuser = db_item['admin_user']
        syspass = db_item['admin_passwd']
        vip = db_item['vip']
        port = db_item['db_port']
        olduser = db_item['olduser']
        
        check_mysqluser_sql = "select count(*) from mysql.user \
                               where user = '%s' "       \
                               % (olduser)
        check_db_exist_sql = "select count(*) from information_schema.SCHEMATA \
                                where SCHEMA_NAME='%s';" \
                               % olduser
        drop_user_sql = "drop user %s" % olduser
        drop_db_sql ="drop database %s" % olduser
        try:
            conn = mdb.connect(vip, sysuser, syspass)
            cursor = conn.cursor()
            #1 drop mysql user
            try:
                cursor.execute(check_mysqluser_sql)
                number = cursor.fetchone()
                if number[0] > 0:
                    cursor.execute(drop_user_sql)
            except mdb.Error as e:
                msg = "Error %d: %s" % (e.args[0], e.args[1])
                #self.logger.error(e)
                raise DbUserDropError("MysqlDbUserDropError:" + msg)
            else:
                self.logger.info('Drop Mysql User [' + olduser + '] Success!')
            
            #2 drop mysql db    
            try:
                cursor.execute(check_db_exist_sql)
                number = cursor.fetchone()
                if number[0] > 0:
                    cursor.execute(drop_db_sql)
            except mdb.Error as e:
                msg = "Error %d: %s" % (e.args[0], e.args[1])
                #self.logger.error(e)
                raise DbUserDropError("MysqlDbUserDropError:" + msg)
            else:
                self.logger.info('Drop Mysql DB [' + olduser + '] Success!')
                
                        
        except mdb.Error as e:
            msg = "Error %d: %s" % (e.args[0], e.args[1])
            self.logger.error(e)
            raise DbUserDropError("DbUserDropError:" + msg)
        finally:
            try:
                cursor.close()
                conn.close()
            except:
                self.logger.error('mdb.connect or conn.cursor')

...

Of course, it’s a quite simple function. But it’s a good and great start 🙂

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: