Skip to content

Instantly share code, notes, and snippets.

@mathisve
Last active March 15, 2024 04:28
Show Gist options
  • Save mathisve/fd76f47dc5ff266fb77534e0e90eb586 to your computer and use it in GitHub Desktop.
Save mathisve/fd76f47dc5ff266fb77534e0e90eb586 to your computer and use it in GitHub Desktop.
Golang script to rapidly insert data into a MySQL database
package main
import (
"bufio"
"encoding/json"
"fmt"
"io/ioutil"
"os"
"sync"
// "math/rand"
"unicode/utf8"
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
type Item struct {
ID string `json:"id"`
Shop string `json:"shop"`
Country string `json:"country"`
Location string `json:"location"`
DownloadedOn int `json:"downloaded_on"`
Language string `json:"language"`
Name string `json:"name"`
Brand string `json:"brand"`
Eans []string `json:"eans"`
Categories []string `json:"categories"`
Nutriscore string `json:"nutriscore"`
Prices []Prices `json:"prices"`
}
type Prices struct {
Regular float64 `json:"regular"`
Promo float64 `json:"promo"`
Unit string `json:"unit"`
}
var (
glob_items []string
lock sync.Mutex
)
func main() {
glob_items = GetItems()
for i := 0; i <= 100; i++ {
go Inserter(i)
}
wg := sync.WaitGroup{}
wg.Add(1)
wg.Wait()
}
func GetItems() []string {
var items []string
folder := "data"
files, _ := ioutil.ReadDir(folder)
for _, file := range files {
jsonFile, err := os.Open("data/" + file.Name())
if err != nil {
panic(err)
}
scanner := bufio.NewScanner(jsonFile)
for scanner.Scan() {
items = append(items, scanner.Text())
}
jsonFile.Close()
fmt.Printf("Collecting items from JSON files: %v\n", len(items))
}
return items
}
func GetItem() (item string, empty bool) {
lock.Lock()
if len(glob_items) > 0 {
item, glob_items = glob_items[len(glob_items)-1], glob_items[:len(glob_items)-1]
lock.Unlock()
fmt.Println(len(glob_items))
return item, false
}
lock.Unlock()
return item, true
}
func Inserter(goroutine int) {
db, err := sql.Open("mysql", "USER:PASSWORD@tcp(HOST)/DATABASE")
defer db.Close()
if err != nil {
panic(err)
}
var empty bool
var itemstring string
for {
itemstring, empty = GetItem()
if empty {
break
}
var item Item
if !utf8.ValidString(itemstring) {
fmt.Println("Something wrong with utf8 encoding!")
fmt.Println(itemstring)
continue
} else {
json.Unmarshal([]byte(itemstring), &item)
}
q := "INSERT INTO `products` (downloaded_on, shop_id, shop, country, location, language, name, brand, nutriscore) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);"
insert, err := db.Prepare(q)
if err != nil {
fmt.Println(err)
}
resp, err := insert.Exec(item.DownloadedOn, item.ID, item.Shop, item.Country, item.Location, item.Language, item.Name, item.Brand, item.Nutriscore)
insert.Close()
if err != nil {
fmt.Println(err)
}
lastInsertId, err := resp.LastInsertId()
if lastInsertId == 0 {
continue
}
if err != nil {
fmt.Println(item)
fmt.Println(err)
return
}
for _, ean := range item.Eans {
q := "INSERT INTO `eans` (product_id, ean) VALUES (?, ?);"
insert, _ := db.Prepare(q)
_, err = insert.Exec(lastInsertId, ean)
insert.Close()
}
for _, category := range item.Categories {
q := "INSERT INTO `categories` (product_id, category) VALUES (?, ?)"
insert, err := db.Prepare(q)
if err != nil {
fmt.Println(err)
}
_, err = insert.Exec(lastInsertId, category)
insert.Close()
if err != nil {
fmt.Println(err)
}
}
for _, prices := range item.Prices {
q := "INSERT INTO `prices` (product_id, regular, promo, unit) VALUES (?, ?, ?, ?)"
insert, _ := db.Prepare(q)
_, err = insert.Exec(lastInsertId, prices.Regular, prices.Promo, prices.Unit)
insert.Close()
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment