python3 mysql查询_使用Python3导出MySQL查询数据

#!/usr/bin/env python#-*- coding: utf-8 -*-#=============================================================================#FileName:#Desc:#Author:#Email:#HomePage:#Version:#LastChange:#History:#=============================================================================

importpymysqlimporttracebackimportloggingimportxlwtimportdatetime

logger= logging.getLogger(__name__)classMySQLServer(object):def __init__(self, mysql_host,

mysql_user,

mysql_password,

mysql_port=3306,

database_name="mysql",

mysql_charset="utf8",

connect_timeout=60):

self.mysql_host=mysql_host

self.mysql_user=mysql_user

self.mysql_password=mysql_password

self.mysql_port=mysql_port

self.connect_timeout=connect_timeout

self.mysql_charset=mysql_charset

self.database_name=database_namedef get_connection(self, return_dict=False):"""获取当前服务器的MySQL连接

:return:"""

ifreturn_dict:

conn=pymysql.connect(

host=self.mysql_host,

user=self.mysql_user,

passwd=self.mysql_password,

port=self.mysql_port,

connect_timeout=self.connect_timeout,

charset=self.mysql_charset,

db=self.database_name,

cursorclass=pymysql.cursors.DictCursor

)else:

conn=pymysql.connect(

host=self.mysql_host,

user=self.mysql_user,

passwd=self.mysql_password,

port=self.mysql_port,

connect_timeout=self.connect_timeout,

charset=self.mysql_charset,

db=self.database_name,

cursorclass=pymysql.cursors.Cursor

)returnconndef mysql_exec(self, mysql_script, mysql_paras=None):

conn=None

cursor=Nonetry:

message= "在服务器{0}上执行脚本:{1},参数为:{2}".format(

self.mysql_host, mysql_script, str(mysql_paras))

logger.debug(message)

conn=self.get_connection()

cursor=conn.cursor()if mysql_paras is notNone:

cursor.execute(mysql_script, mysql_paras)else:

cursor.execute(mysql_script)

conn.commit()exceptException as ex:

warning_message= """execute script:{mysql_script}

execute paras:{mysql_paras},

execute exception:{mysql_exception}

execute traceback:{mysql_traceback}""".format(

mysql_script=mysql_script,

mysql_paras=str(mysql_paras),

mysql_exception=str(ex),

mysql_traceback=traceback.format_exc()

)

logger.warning(warning_message)raiseException(str(ex))finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.close()defmysql_exec_many(self, script_items):

conn=None

cursor=Nonetry:

conn=self.get_connection()

cursor=conn.cursor()for script_item inscript_items:

sql_script, sql_paras=script_item

message= "在服务器{0}上执行脚本:{1},参数为:{2}".format(

self.mysql_host, sql_script, str(sql_paras))

logger.debug(message)if sql_paras is notNone:

cursor.execute(sql_script, sql_paras)else:

cursor.execute(sql_script)

conn.commit()exceptException as ex:

logger.warning("execute exception:{0} \n {1}".format(str(ex), traceback.format_exc()))raiseException(str(ex))finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.close()def mysql_query(self, mysql_script, mysql_paras=None, return_dict=False):

conn=None

cursor=Nonetry:

message= "在服务器{0}上执行脚本:{1},参数为:{2}".format(

self.mysql_host, mysql_script, str(mysql_paras))

logger.debug(message)

conn= self.get_connection(return_dict=return_dict)

cursor=conn.cursor()if mysql_paras is notNone:

cursor.execute(mysql_script, mysql_paras)else:

cursor.execute(mysql_script)

exec_result=cursor.fetchall()

conn.commit()returnexec_resultexceptException as ex:

warning_message= """execute script:{mysql_script}

execute paras:{mysql_paras},

execute exception:{mysql_exception}

execute traceback:{mysql_traceback}""".format(

mysql_script=mysql_script,

mysql_paras=str(mysql_paras),

mysql_exception=str(ex),

mysql_traceback=traceback.format_exc()

)

logger.warning(warning_message)raiseException(str(ex))finally:if cursor is notNone:

cursor.close()if conn is notNone:

conn.close()classExeclExporter(object):

@classmethoddefexport_excel(cls, file_path, row_items):try:

work_book=xlwt.Workbook()

work_sheet= work_book.add_sheet(‘sheet01‘, cell_overwrite_ok=True)

column_items=[]if len(row_items) >0:

first_row=row_items[0]

column_items=list(first_row.keys())for column_index inrange(0, len(column_items)):

column_name=column_items[column_index]

work_sheet.write(0, column_index, column_name)for row_index in range(1, len(row_items) + 1):

row_item= row_items[row_index - 1]for column_index inrange(0, len(column_items)):

column_name=column_items[column_index]

work_sheet.write(row_index, column_index, row_item[column_name])

work_book.save(file_path)exceptException as ex:

logger.warning("执行异常,异常信息：{0}\n堆栈信息：{1}".format(

str(ex),

traceback.format_exc()

))defexport_excel():

mysql_server=MySQLServer(

mysql_host="192.168.199.194",

mysql_port=3306,

mysql_user=‘admin‘,

mysql_password=‘admin‘,

database_name=‘demo01‘,

mysql_charset=‘utf8‘)

sql_script= """select * from tb001 limit 10;"""row_items= mysql_server.mysql_query(mysql_script=sql_script, return_dict=True)

file_path= "./" + datetime.datetime.now().strftime("%Y%m%d%H%M%S.xls")

ExeclExporter.export_excel(

file_path=file_path,

row_items=row_items)if __name__ == ‘__main__‘:

export_excel()