How-to write Python for Oracle

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

$ pip install cx_Oracle
or
$ easy_install cx_Oracle

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

#!/usr/bin/python
'''
Create a user, drop a user
'''

    def __do_create_oracleuser(self):
        isSuccess = False
        
        #password equal to username
        oracle_pass = self.newuser  
        privileges = self.privileges

        check_user_sql = "select count(*) from dba_users \
                          where username = upper('%s') " \
                          % (self.newuser, )                
        get_temptbs_sql = '''select decode(PROPERTY_VALUE,'SYSTEM', tablespace_name, PROPERTY_VALUE)
                            from 
                            (select tablespace_name 
                            from (select tablespace_name,sum(bytes) 
                                  from dba_temp_files 
                                  group by tablespace_name 
                                  order by 2 desc)
                             where rownum=1
                            ) a,
                            (select PROPERTY_VALUE 
                             FROM DATABASE_PROPERTIES 
                             where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'
                            ) b'''
        create_user_sql = "create user %s identified by %s        \
                           default tablespace %s quota %dG on %s" \
                           % (self.newuser, oracle_pass, self.tablespace,
                              self.space, self.tablespace )
        #permission = "connect, resource"
        #grant_sql = "grant %s to %s" % (permission, self.newuser)
         
        try:
            source = cx_Oracle.makedsn(self.vip, self.port, self.sid)
            db =  cx_Oracle.connect(self.sysuser,self.syspass, source)
            cursor = db.cursor()
            #1 check user exist
            cursor.execute(check_user_sql)
            count = cursor.fetchone()     
            if count[0] > 0:
                raise DbUserCreateError('DbUserCreateError: In Oracle DB User [' + 
                                    self.newuser + '] Already Exists.')        
            cursor.execute(get_temptbs_sql)
            temptbs = cursor.fetchone()
            create_user_sql += " temporary tablespace %s" %temptbs;
            cursor.execute(create_user_sql)
            if privileges:
                for privilege in privileges.strip().split(';'):
                    if privilege:
                        privilege_sql = "grant %s to %s" % (privilege, self.newuser)
                        cursor.execute(privilege_sql)
            
        except cx_Oracle.DatabaseError as exc:
            error, = exc.args
            self.logger.error(exc)
            raise DbUserCreateError(error) 
        else:
            isSuccess = True
            self.logger.info('Create Oracle Db User [' + self.newuser + '] Success!')
        finally:
            try:
                cursor.close()           
                db.close()
            except:
                self.logger.error('cx_Oracle.connect or db.cursor')
            
        return { 
                'username': self.newuser,
                'password' : oracle_pass,
                'db' : self.sid,
                'success' : isSuccess ,
                } 


    def __do_drop_oracleuser(self, db_item):
        sysuser = db_item['admin_user']
        syspass = db_item['admin_passwd']
        vip = db_item['vip']
        port = db_item['db_port']
        sid = db_item['sid']
        olduser = db_item['olduser']

        check_user_sql = "select count(*) from dba_users \
                          where username = upper('%s') " \
                          % (olduser)  
        drop_sql = "drop user %s cascade" % olduser
        #TO-DO: lock user when decomm
        #lock_sql = "alter user %s account lock" % olduser
        try:
            
            source = cx_Oracle.makedsn(vip, port, sid)
            db =  cx_Oracle.connect(sysuser, syspass, source)
            cursor = db.cursor()
            #drop oracle user
            try:
                #1 check user exist
                cursor.execute(check_user_sql)
                count = cursor.fetchone()     
                if count[0] > 0:
                    cursor.execute(drop_sql)
               
            except cx_Oracle.DatabaseError as exc:
                #self.logger.error(exc)
                error, = exc.args
                raise DbUserDropError("OracleDbUserDropError:" + error)
            else:
                self.logger.info('Drop Oracle User[' + olduser + '] Success!')     
            
        except cx_Oracle.DatabaseError as exc:
            self.logger.error(exc)
            error, = exc.args
            raise DbUserDropError("OracleDbUserDropError:" + error)
        finally:
            try:
                cursor.close()           
                db.close()
            except:
                self.logger.error('cx_Oracle.connect or db.cursor')


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

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 🙂

How-to write Python for Cassandra

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

$ pip install pycassa
or
$ easy_install pycassa

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

#!/usr/bin/python
'''
Create a user
'''
import pycassa
from pycassa.system_manager import SystemManager

    def __do_create_cassandrauser(self):
        isSuccess = False
        
        cassandra_user = self.newuser
        cassandra_pass = self.newuser
        cassandra_db = self.newuser
        cred = None
        if(self.sysuser is not None and self.syspass is not None) :
            cred = {'username' : self.sysuser, 'password' : self.syspass}          
        
        try:
            #connect to cassandra

            '''
                before connect, modify the connect string as:
                "['vip:port','vip2:port'...]" so that this can be used in server list
                the vip str should be like "vip1,vip2,vip3",
                and we regard all port are the same.
                SystemManager.server didn't support a array of server list now, only ConnectionPool.server_list support
                So I will go through the vip list and connect to an available host
            '''
            vip_list = self.vip.split(',')            
            for myvip in vip_list:
                try:
                    cons = myvip + ':' + str(self.port)
                    sm = SystemManager(server=cons, credentials=cred)
                    break
                except: 
                    pass
            
            #c = ConnectionPool(keyspace='system', server_list=con, credentials=cred)
                        
            #check if user exist: by check if database name exist(database name and username are the same) 
            database_names = sm.list_keyspaces()
            if cassandra_db in database_names:
                raise DbUserCreateError('DbUserCreateError: In Remote Oracle DB User [' + 
                                        cassandra_user + '] Already Exists.')

            sm.create_keyspace(cassandra_db, pycassa.system_manager.NETWORK_TOPOLOGY_STRATEGY, {'DC1': '1', 'DC2': '1'})
        
        except Exception as e:            
            self.logger.error(e)
            raise DbUserCreateError(str(e))
        else:
            isSuccess = True
            self.logger.info('Create Cassandra Keyspace[' + cassandra_user + '] Success!')
        finally:
            try:
                sm.close()
            except Exception as e:
                self.logger.error(e)
        
        return { 
                'username': cassandra_user,
                'password': cassandra_pass,
                'db': cassandra_db,
                'success' : isSuccess ,
               }

        

    def __do_drop_cassandrauser(self, db_item):
        sysuser = db_item['admin_user']
        syspass = db_item['admin_passwd']
        vip = db_item['vip']
        port = db_item['db_port']
        sid = db_item['sid']
        olduser = db_item['olduser']
        cred = None
        if(sysuser is not None and syspass is not None) :
            cred = {'username' : sysuser, 'password' : syspass}                

        try:
            '''
                before connect, modify the connect string as:
                "['vip:port','vip2:port'...]" so that this can be used in ConnectionPool()
                the vip str should be like "vip1,vip2,vip3",
                and we regard all port are the same.
                SystemManager.server didn't support a array of server list now, only ConnectionPool.server_list support
            '''            
            vip_list = vip.split(',')            
            for myvip in vip_list:
                try:
                    cons = myvip + ':' + str(port)
                    sm = SystemManager(server=cons, credentials=cred)
                    break
                except: 
                    pass            
                       
            sm = SystemManager(server=cons, credentials=cred)
            
            #check if user exist: by check if database name exist(database name and username are the same) 
            database_names = sm.list_keyspaces()
            if olduser in database_names:
                sm.drop_keyspace(olduser)

        except Exception as e:
            self.logger.error(e)
            raise DbUserDropError(str(e)) 
        else:
            self.logger.info('Drop Cassandra Keyspace [' + olduser + '] Success!')
        finally:
            sm.close()
            
...

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