insert_csv_to_db.go

package main

import (
	"database/sql"
	"encoding/csv"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
	"io"
	"log"
	"os"
	"strings"
)

var db *sql.DB
var dbpath = "/Applications/DataGrip.app/Contents/bin/sync_thumb_photo_db"

func init() {
	var err error
	db, err = sql.Open("sqlite3", dbpath)
	if err != nil {
		log.Fatal(err)
	}
}

type RowStruct struct {
	ID string
	Album string
	PhotoUUID string
	FilePath string
	Backup bool
}

func BulkInsert(unsavedRows []*RowStruct) error {
	valueStrings := make([]string, 0, len(unsavedRows))
	valueArgs := make([]interface{}, 0, len(unsavedRows) * 3)
	for _, post := range unsavedRows {
		valueStrings = append(valueStrings, "(?, ?, ?,?,?)")
		valueArgs = append(valueArgs, post.ID)
		valueArgs = append(valueArgs, post.Album)
		valueArgs = append(valueArgs, post.PhotoUUID)
		valueArgs = append(valueArgs, post.FilePath)
		valueArgs = append(valueArgs, post.Backup)
	}
	stmt := fmt.Sprintf("INSERT INTO album_photo (id, photo_album_id, photo_uuid, file_path, backup) VALUES %s", strings.Join(valueStrings, ","))
	_, err := db.Exec(stmt, valueArgs...)
	return err
}

func main() {
	f, _ := os.Open("output.csv")
	defer f.Close()
	r := csv.NewReader(f)
	r.Read()

	rowPackage := []*RowStruct{}

	count := 0

	for {
		record, err := r.Read()

		count += 1

		if count % 2000 == 0{
			fmt.Println(count)
			os.Stdout.Sync()
		}
		if err == io.EOF {
			break
		}

		if err != nil {
			panic(err)
		}

		backup := false
		if record[4] == "true" {
			backup = true
		}

		if len(rowPackage) < 100{
			rowPackage = append(rowPackage,&RowStruct{
				ID:        record[0],
				Album:     record[1],
				PhotoUUID: record[2],
				FilePath:  record[3],
				Backup:    backup,
			})
		}else{
			err = BulkInsert(rowPackage)
			if err != nil{
				panic(err)
			}
			rowPackage = []*RowStruct{}
		}
	}

	err := BulkInsert(rowPackage)
	if err != nil{
		panic(err)
	}

}