MySQL remote quick Export CSV Format Data tool

If you need to reprint, please agree with me.

I once wrote a script that uses select ... into outfile principle to export data, but the script value is suitable for local quick export and does not support remote service, so the following script is written to support remote export. This script supports export file detection and database information checking. If you have good suggestions, please leave a comment. Script overall is relatively simple, I hope to help everyone

scripting language: Python

Version: 2.7

#!/usr/bin/python#-*-coding:utf-8-*-# @author jane.hoo# @date ./ One/ in from__future__ Import divisionimport osimport mysqldbimport timeimport commandsimport loggingimport reimport mathprint /c0>'*******************************'timestamp=time.strftime ("%y%m%d%h%m%s", Time.localtime ()) LogFile='/tmp/myloaddataout.log%s'%Timestamplogging.basicconfig ( level=logging. DEBUG, Format='% (asctime) s% (filename) s[line:% (lineno) d]% (levelname) s% (message) s', Datefmt='%a,%d%b%Y%h:%m:%s', filename='%s'%logfile, FileMode='W') Print'export log records in:%s'%logfile################################################################################################## Define a Streamhandler, print the info level or higher log information to a standard error, and add it to the current log processing object #console=logging. Streamhandler () console.setlevel (logging.info) Formatter= Logging. Formatter ('% (name) -12s:% (levelname) -8s% (message) s') Console.setformatter (formatter) Logging.getlogger ("'). AddHandler (console) ##################################################################################### ############ #python/cygdrive/c/users/jane.hoo/pycharmprojects/loaddata/Mysqlloaddataout.pydb_host='127.0.0.1'Db_user='Test'db_pwd='Test'DB='Test'Db_port=int('3306')classtoolloadout:def __init__ (self,pathfile=0): Self.pathfile=pathfile #得到数据库连接 def getdbconn (Self,db_host=db_host,db_user='Routeload', db_pwd='Routeload', db='test01', db_port='3306'): Logging.debug ('%s:%s:%s:%s:%s'%(Db_host,db_user,db_pwd,db,db_port)) Conn='unlink'        Try: Conn=mysqldb.connect (host=db_host,user=db_user,passwd=db_pwd,port=int(Db_port), db=DB) Logging.info ('Database Connection succeeded') except mysqldb.operationalerror,e:logging.warning ('Database connection Failed! %s'%e)returnConn #关闭数据库连接 def closedbconn (Self,conn=0): Logging.debug ('Closed Database Connection') Conn.close () #检查导出文件 def chekpathfile (self): Pathfile=self.pathfile Check_flag=0iffile_exists=os.path.exists (pathfile)ififfile_exists==1: Check_flag=1Logging.info ('The export file already exists! ')        Else: Path=os.path.split (Pathfile) [0] Ifpath_exists=os.path.isdir (path)ififpath_exists==1: Logging.info ('%s is a valid path'%path)Else: Check_flag=1Logging.info ('%s is not an invalid path'%path)returnCheck_flagif__name__=='__main__': Print'preparing to export ...'Pathfile=raw_input ("Please enter the path of the export file:"). Strip () Cmd_sql=raw_input ('Please enter the statement you want to query:'). Strip () C=toolloadout (pathfile) Pf_check=C.chekpathfile ()ifpf_check==0: Logging.info ('file path Validation pass') ifinputdbmsg=raw_input ('Whether you need to customize database connection information (y| N)? '). Strip ()ififinputdbmsg=='Y': Db_host=raw_input ('Please enter the data address:'). Strip () Db_user=raw_input ('Please enter user name:'). Strip () Db_pwd=raw_input ('Please enter your password:'). Strip () DB=raw_input ('Please enter the database name:'). Strip () Db_port=raw_input ('Please enter the database port:'). Strip ()ifdb_host=="'or db_user=="'or db_pwd=="'or db=="': Logging.info ('The custom database input information is empty.') Check_flag=1If_port=re.match (R"[0-9]", Db_port)ifIf_port:db_port=int(Db_port)Else: Db_port=int('3306') Logging.info ('port format entered incorrectly, default port%s will be used'%db_port) Conn=c.getdbconn (Db_host,db_user,db_pwd,db,db_port)ifSTR (conn)! ='unlink': Selectsql="mysql-a%s-h%s-u%s-p%s-p%s-ss-e '%s; ' | sed ' s/\\t/,/g;s/^//;s/$//;s/\\n//g ' >%s"%(db,db_host,db_user,db_pwd,db_port,cmd_sql,pathfile) Print'Querysql:', SelectsqlTry: Os.system ('%s'%selectsql) except Baseexception,e:logging.info ('error!%s during export of data'%e) c.closedbconn (conn)Else: Logging.info ('failed')    Else: Logging.info ('file path check does not pass, export end')

Jane.hoo Source: Jane.hoo's blog http: //  [life does not set limits, life is endless, tossing not only] your support is the greatest encouragement to bloggers, thank you for your serious reading. The copyright of this article is owned by the author, welcome reprint, but please keep this statement. 

MySQL remote quick Export CSV Format Data tool