mysql dao封装_pymysql DAO简单封装

#-*- coding: UTF-8 -*-

importMySQLdbimportMySQLdb.cursorsimportloggingfrom django.db importconnectionimporttraceback

logger= logging.getLogger('default')importjsonclassDao(object):

_CHART_DAYS= 90

#测试指定的mysql实例连接是否成功

defcheck_mysql_connect(self, ipaddress, mysqlport, username, password):

listDb=[]

conn=None

cursor=None

connectflag=2

try:

conn= MySQLdb.connect(host=ipaddress, port=mysqlport, user=username, passwd=password,

charset='utf8',connect_timeout=10)

cursor=conn.cursor()

sql= "show databases"n=cursor.execute(sql)

listDb= [row[0] for row incursor.fetchall()if row[0] not in ('information_schema', 'performance_schema', 'mysql', 'test')]

connectflag=1

exceptMySQLdb.Warning as w:

traceback.print_exc()#raise Exception(w)

exceptMySQLdb.Error as e:

traceback.print_exc()#raise Exception(e)

finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.commit()

conn.close()returnconnectflag#检查复制同步状态

defcheck_slave_status(self, ipaddress, mysqlport, username, password):

listDb=[]

conn=None

cursor=None

master_host='NOHOST'master_port=0

master_user=''master_log_file=''read_master_log_pos=0

relay_master_log_file=''exec_master_log_pos=0

slave_io_running='No'slave_sql_running='No'seconds_behind_master=-1count=0

io_succesed_count=0

sql_successed_count=0try:

conn= MySQLdb.connect(host=ipaddress, port=mysqlport, user=username, passwd=password,

charset='utf8', connect_timeout=10, cursorclass=MySQLdb.cursors.DictCursor)

cursor=conn.cursor()

sql= "show slave status"n=cursor.execute(sql)for row incursor.fetchall():

count= count +1master_host=row['Master_Host']

master_port=row['Master_Port']

master_user= row['Master_User']

master_log_file= row['Master_Log_File']

read_master_log_pos= row['Read_Master_Log_Pos']

relay_master_log_file= row['Relay_Master_Log_File']

exec_master_log_pos= row['Exec_Master_Log_Pos']

cur_slave_io_running= row['Slave_IO_Running']

cur_slave_sql_running= row['Slave_SQL_Running']

cur_seconds_behind_master= row['Seconds_Behind_Master']if (cur_slave_io_running == 'Yes'):

io_succesed_count= io_succesed_count + 1

if (cur_slave_sql_running == 'Yes'):

sql_successed_count= sql_successed_count + 1

if (cur_seconds_behind_master == 'NULL'):

seconds_behind_master= -1

elif (cur_seconds_behind_master >seconds_behind_master):

seconds_behind_master=cur_seconds_behind_masterif ( io_succesed_count ==count ):

slave_io_running= 'Yes'

if ( sql_successed_count ==count ):

slave_sql_running= 'Yes'

if (count ==0 ):

slave_io_running= 'No'slave_sql_running= 'No'

exceptMySQLdb.Warning as w:

traceback.print_exc()#raise Exception(w)

exceptMySQLdb.Error as e:

traceback.print_exc()#raise Exception(e)

finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.commit()

conn.close()return(master_host,master_port,master_user,master_log_file,read_master_log_pos,relay_master_log_file,exec_master_log_pos,slave_io_running,slave_sql_running,seconds_behind_master)#检查库表数量

defcheck_table_num(self, ipaddress, mysqlport, username, password):

listDb=[]

conn=None

cursor=None

dbnum=0

tablenum=0try:

conn= MySQLdb.connect(host=ipaddress, port=mysqlport, user=username, passwd=password,

charset='utf8', cursorclass=MySQLdb.cursors.DictCursor)

conn.select_db('information_schema')

cursor=conn.cursor()

sql= "select count(*) dbnum from SCHEMATA where SCHEMA_NAME not in ('information_schema','mysql','performance_schema','sys')"effect_row=cursor.execute(sql)

row=cursor.fetchone()

dbnum= row['dbnum']

sql= "select count(*) tablenum from TABLES where TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys')"effect_row=cursor.execute(sql)

row=cursor.fetchone()

tablenum= row['tablenum']exceptMySQLdb.Warning as w:

traceback.print_exc()#raise Exception(w)

exceptMySQLdb.Error as e:

traceback.print_exc()#raise Exception(e)

finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.commit()

conn.close()return(dbnum,tablenum)#连进指定的mysql实例里，读取所有databases并返回

defgetAlldbByCluster(self, masterHost, masterPort, masterUser, masterPassword):

listDb=[]

conn=None

cursor=Nonetry:

conn= MySQLdb.connect(host=masterHost, port=masterPort, user=masterUser, passwd=masterPassword,

charset='utf8')

cursor=conn.cursor()

sql= "show databases"n=cursor.execute(sql)

listDb= [row[0] for row incursor.fetchall()if row[0] not in ('information_schema', 'performance_schema', 'mysql', 'test')]exceptMySQLdb.Warning as w:raiseException(w)exceptMySQLdb.Error as e:raiseException(e)finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.commit()

conn.close()returnlistDb#连进指定的mysql实例里，读取所有tables并返回

defgetAllTableByDb(self, masterHost, masterPort, masterUser, masterPassword, dbName):

listTb=[]

conn=None

cursor=Nonetry:

conn= MySQLdb.connect(host=masterHost, port=masterPort, user=masterUser, passwd=masterPassword, db=dbName,

charset='utf8')

cursor=conn.cursor()

sql= "show tables"n=cursor.execute(sql)

listTb= [row[0] for row incursor.fetchall()if row[0] not in('test')]exceptMySQLdb.Warning as w:raiseException(w)exceptMySQLdb.Error as e:raiseException(e)finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.commit()

conn.close()returnlistTb#连进指定的mysql实例里，读取所有Columns并返回

defgetAllColumnsByTb(self, masterHost, masterPort, masterUser, masterPassword, dbName, tbName):

listCol=[]

conn=None

cursor=Nonetry:

conn= MySQLdb.connect(host=masterHost, port=masterPort, user=masterUser, passwd=masterPassword, db=dbName,

charset='utf8')

cursor=conn.cursor()

sql= "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s';" %(

dbName, tbName)

n=cursor.execute(sql)

listCol= [row[0] for row incursor.fetchall()]exceptMySQLdb.Warning as w:raiseException(w)exceptMySQLdb.Error as e:raiseException(e)finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.commit()

conn.close()returnlistCol#连进指定的mysql实例里，执行sql并返回

def mysql_query(self, masterHost, masterPort, masterUser, masterPassword, dbName, sql, limit_num=0):

result= {'column_list': [], 'rows': [], 'effect_row': 0}

conn=None

cursor=Nonetry:

conn= MySQLdb.connect(host=masterHost, port=masterPort, user=masterUser, passwd=masterPassword, db=dbName,

charset='utf8')

cursor=conn.cursor()

effect_row=cursor.execute(sql)if int(limit_num) >0:

rows= cursor.fetchmany(size=int(limit_num))else:

rows=cursor.fetchall()

fields=cursor.description

column_list=[]iffields:for i infields:

column_list.append(i[0])

result={}

result['column_list'] =column_list

result['rows'] =rows

result['effect_row'] =effect_rowexceptMySQLdb.Warning as w:

logger.warning(str(w))

result['Warning'] =str(w)exceptMySQLdb.Error as e:

logger.error(str(e))

result['Error'] =str(e)finally:if cursor is notNone:

cursor.close()if conn is notNone:try:

conn.rollback()

conn.close()except:

conn.close()returnresult#连进指定的mysql实例里，执行sql并返回

defmysql_execute(self, masterHost, masterPort, masterUser, masterPassword, dbName, sql):

result={}

conn=None

cursor=Nonetry:

conn= MySQLdb.connect(host=masterHost, port=masterPort, user=masterUser, passwd=masterPassword, db=dbName,

charset='utf8')

cursor=conn.cursor()

effect_row=cursor.execute(sql)#result = {}

#result['effect_row'] = effect_row

conn.commit()exceptMySQLdb.Warning as w:

logger.warning(str(w))

result['Warning'] =str(w)exceptMySQLdb.Error as e:

logger.error(str(e))

result['Error'] =str(e)finally:if result.get('Error') or result.get('Warning'):

conn.close()elif cursor is notNone:

cursor.close()

conn.close()returnresultdefgetWorkChartsByMonth(self):

cursor=connection.cursor()

sql= "select date_format(create_time, '%%m-%%d'),count(*) from sql_workflow where create_time>=date_add(now(),interval -%s day) group by date_format(create_time, '%%m-%%d') order by 1 asc;" %(

Dao._CHART_DAYS)

cursor.execute(sql)

result=cursor.fetchall()returnresultdefgetWorkChartsByPerson(self):

cursor=connection.cursor()

sql= "select engineer, count(*) as cnt from sql_workflow where create_time>=date_add(now(),interval -%s day) group by engineer order by cnt desc limit 50;" %(

Dao._CHART_DAYS)

cursor.execute(sql)

result=cursor.fetchall()returnresult#取出otter中的频道、管道、cannal相关信息

defget_otter_pipeline_infos(self, ipaddress, mysqlport, username, password,dbname):

otter_pipeline_info_dict_list=[]

conn=None

cursor=Nonetry:

conn= MySQLdb.connect(host=ipaddress, port=mysqlport, user=username, passwd=password,db=dbname,

charset='utf8', connect_timeout=10, cursorclass=MySQLdb.cursors.DictCursor)

cursor=conn.cursor()

sql= """SELECT c.ID channelid ,c.`NAME` channelname ,p.ID pipelineid ,p.`NAME` pipelinename ,p.PARAMETERS pipelineparams

FROM CHANNEL c,PIPELINE p

where c.id = p.CHANNEL_ID

order by c.ID"""n=cursor.execute(sql)for row incursor.fetchall():

otter_pipeline_info_dict={}

otter_pipeline_info_dict["channelid"] = row['channelid']

otter_pipeline_info_dict["channelname"] = row['channelname']

otter_pipeline_info_dict["pipelineid"] = row['pipelineid']

otter_pipeline_info_dict["pipelinename"] = row['pipelinename']

pipelineparams=row['pipelineparams']

jsonmsg=json.loads(pipelineparams)

canalname=jsonmsg['destinationName']

otter_pipeline_info_dict["canalname"] =canalname

otter_pipeline_info_dict_list.append(otter_pipeline_info_dict)exceptMySQLdb.Warning as w:

traceback.print_exc()#raise Exception(w)

exceptMySQLdb.Error as e:

traceback.print_exc()#raise Exception(e)

finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.commit()

conn.close()return otter_pipeline_info_dict_list