Python export data from MySQL guide Excel

# Coding:utf8

Import Sys

Reload (SYS)

Sys.setdefaultencoding (' UTF8 ')

# Author: ' Zkx '

# Date: ' 2018/3/11 '

# Desc: Export data from database to Excel data table

#已封装, you can use it directly, just change the SQL statement

Import XLWT

Import MySQLdb

def export (Host,user,password,dbname,table_name,outputpath):

conn = MySQLdb.connect (host,user,password,dbname,charset= ' UTF8 ')

cursor = Conn.cursor ()

#时间戳相减, convert last seconds to Excel time format to be unified ("%y-%m-%d%h:%i:%s"), otherwise exported to Excel is null

#concat (Floor (lasto/1000-first/1000)/3600), "hours", Floor (mod ((last/1000-first/1000), 3600)/60), "Min", round (mod ((last/1000-first/1000), 3600), 60)), "seconds")

Count = Cursor.execute (' Select Node,nodealias,alertgroup,from_unixtime (firstoccurrence/1000, "%y-%m-%d%H:%i:%S") Zuizao,from_unixtime (lastoccurrence/1000, "%y-%m-%d%h:%i:%s") Zuiwan, (lastoccurrence/1000-firstoccurrence/1000) Shijiancha from ' +table_name+ ' where alertgroup= "Oidtablemonitor-mdtemp" and Date_sub (Curdate (), INTERVAL 7 day) <= DATE (From_unixtime (firstoccurrence/1000, "%y-%m-%d%h:%i:%s"))

Print Count

# Resetting the position of the cursor

Cursor.scroll (0,mode= ' absolute ')

# Search All Results

Results = Cursor.fetchall ()

# Get the data field name inside MySQL

Fields = Cursor.description

Workbook = xlwt. Workbook ()

Sheet = workbook.add_sheet (' Table_ ' +table_name,cell_overwrite_ok=true)

# Write the field information

For field in range (0,len):

Sheet.write (0,field,fields[field][0])

# Get and write data segment information

row = 1

Col = 0

For row in range (1,len (results) +1):

For Col in Range (0,len):

Sheet.write (row,col,u '%s '%results[row-1][col])

Workbook.save (OutputPath)

# test

if __name__ = = "__main__":

#mysql-ip, user, password, to execute the library, to query the table, store the path

Export (' 12.16.15.14 ', ' eccom ', ' eco ', ' nete ', ' ftnt ', R '/opt/datest.xls ')

Python export data from MySQL guide Excel