MySQL remote quick Export CSV Format Data tool

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.