Overview: the younger brother"s program can query the sql in the database and write it into excel
performance by entering the database type, database connection string, executing sql or files containing sql. Currently, 25W pieces of data are exported, with 5 fields per row. It takes 1 minute and 33 seconds for the database type oracle, to drive the goracle,excel generation library to use excelize,. The same environment uses python3.6, database-driven cx_Oracle,excel generation library to use the same query statement of pyexcelerate, which takes 51s (yes. I have never done python/ / ~)
Program description: in order to improve execution efficiency, I used a thread of goruntine, to execute sql and generate [] interface {} and load the result into the channel. Another thread constantly took out [] interface {} from the channel and wrote into excel
the possible problems that may exist:
1, golang database query can only be generated one by one, at the same time, the mechanism uses reflection. Unlike python, which can obtain a large amount of data at one time through fetchmany, I do not know whether there will be a performance gap
2. When the field type is date, if the field is empty, the date zero output to excel is very abnormal without isZero judgment (the value is-5XXXXX, shown as-sharp-sharp). So every time you take out a [] interface {}, you need to determine whether the type is a date, and if it is a date, whether it is zero, which may affect the efficiency. Python does not have this problem
Please golang give us some suggestions on optimization. Thank you
the code is as follows:
package main
import (
_ "gopkg.in/goracle.v2"
_ "github.com/asifjalil/cli"
"github.com/jmoiron/sqlx"
"flag"
"fmt"
"github.com/axgle/mahonia"
"strings"
"os"
"strconv"
"io/ioutil"
"time"
"github.com/360EntSecGroup-Skylar/excelize"
"runtime"
)
func DataGetter(db *sqlx.DB,query string,rowChan chan <- []interface{},columnChan chan <- []string){
defer db.Close()
row,err := db.Queryx(query)
if err != nil{
panic(err)
}
defer row.Close()
columns,err := row.Columns()
columnChan <- columns
close(columnChan)
if err !=nil {
panic(fmt.Sprint("failed to add sheet:%s",err.Error()))
}
for row.Next(){
r,err := row.SliceScan()
if err !=nil{
panic("db row query failed")
}
rowChan <- r
}
close(rowChan)
}
func ExcelWriter(sheetHead string,fileName string,rowChan <- chan[]interface{},columnChan <- chan[]string){
cnt := 2
sheetcnt := 1
var r []interface{}
columns := <- columnChan
hasNext := true
excel := excelize.NewFile()
excel.NewSheet(sheetHead)
excel.SetSheetRow(sheetHead,"A1",columns)
//excel.SetSheetRow(sheetHead,"A"+strconv.Itoa(cnt),columns)
for hasNext{
r,hasNext = <- rowChan
for a := 0;a<len(columns);aPP{
t,ok := r[a].(time.Time)
if ok{
if t.IsZero(){
excel.SetCellValue(sheetHead,excelize.ToAlphaString(a)+strconv.Itoa(cnt),"")
}else{
excel.SetCellValue(sheetHead,excelize.ToAlphaString(a)+strconv.Itoa(cnt),t)
}
}else{
excel.SetCellValue(sheetHead,excelize.ToAlphaString(a)+strconv.Itoa(cnt),r[a])
}
}
cnt = cnt + 1
if cnt >= 100000{
excel.NewSheet(sheetHead+strconv.Itoa(sheetcnt))
sheetHead = sheetHead+strconv.Itoa(sheetcnt)
excel.SetSheetRow(sheetHead,"A1",columns)
cnt = 2
sheetcnt = sheetcnt + 1
}
}
excel.SaveAs(fileName+".xlsx")
}
func getConn(dbconn string,dbtype string)(db *sqlx.DB){
if dbtype == "oracle"{
driver := "goracle"
return sqlx.MustOpen(driver,strings.Replace(dbconn,":","/",1))
}else if dbtype == "db2"{
driver := "cli"
userPart := strings.Split(dbconn,"@")[0]
username := strings.Split(userPart,":")[0]
password := strings.Split(userPart,":")[1]
dbPart := strings.Split(dbconn,"@")[0]
dbname := strings.Split(dbPart,"/")[1]
dbip := strings.Split(strings.Split(dbPart,"/")[0],":")[0]
dbport := strings.Split(strings.Split(dbPart,"/")[0],":")[1]
connString := fmt.Sprintf("Driver={IBM DB2 ODBC Driver};Hostname=%s;Port=%s;Protocol=TCPIP;Database=%s;CurrentSchema=%s;UID=%s;PWD=%s;",
dbip,dbport,dbname,username,password)
return sqlx.MustOpen(driver,connString)
}else if dbtype == "postgres"{
driver := "postgres"
connString := "postgres://" + dbconn
return sqlx.MustOpen(driver,connString)
}else{
fmt.Println("dbtype not matched!")
os.Exit(-1)
return
}
}
func main() {
//
dbconn := flag.String("d","",`Database connect string,use "user:password@ip:port/dbname" for db2 or "user:password@tnsname" for oracle`)
dbtype := flag.String("t","","Database type:oracle db2 mysql mssql")
filetype := flag.String("f","xlsx","exported file type:xlsx or txt default:xlsx")
//xlsx require options
sheetname := flag.String("h","Sheet1","sheet name: default Sheet1")
//txt require options
//charset := flag.String("c","utf-8","charset for exported text file:gbk utf-8 and so on")
//separator := flag.String("s","/","separator: default:/")
//sql options
query := flag.String("q","","sql in one line")
sqlfile := flag.String("l","","sqlfile")
filename := flag.String("n",time.Now().Format("20060102150405"),"filename")
flag.Parse()
if *dbconn == "" || *dbtype == "" || *filetype == ""{
flag.Usage()
return
}
if *query == "" && *sqlfile == ""{
flag.Usage()
return
}
if *sqlfile != "" {
sqlbyte,err := ioutil.ReadFile(*sqlfile)
if err != nil{
panic("read sqlfile failed!")
}
utf8 := mahonia.NewEncoder("utf-8")
*query = utf8.ConvertString(string(sqlbyte))
}
runtime.GOMAXPROCS(2)
if *filetype == "xlsx"{
rowChan := make(chan []interface{},50000)
columnsChan := make(chan []string)
db := getConn(*dbconn,*dbtype)
go DataGetter(db,*query,rowChan,columnsChan)
ExcelWriter(*sheetname,*filename,rowChan,columnsChan)
//}else if *filetype == "txt"{
// db := getConn(*dbconn,*dbtype)
// TextFileExporter(db,*charset,*separator,*filename,*query)
//}else{
flag.Usage()
return
}
}