Problem with database/sql in Go

Hello :raised_hand:
I wrote a code that takes a lot of data and this big data should be checked piece by piece if record exists in the database just passed, but if record doesn’t exists in the database, it should save record.

The problem here is that this code works to some extent, but after a while it starts saving duplicate records, can anybody help me?

func CreateRecords(cryptoFilteredResult []*gjson.CryptoFiltered) {
	db, _ := sql.Open("sqlite3", "database/database.db")

	db.Exec("CREATE TABLE IF NOT EXISTS CryptoAll (id INTEGER PRIMARY KEY AUTOINCREMENT, Symbol TEXT, Price REAL)")

	var i int
	for i < len(cryptoFilteredResult) {
		cryptoAll := CryptoAll{
			Crypto: cryptoFilteredResult[i],
		}

		rows, _ := db.Query("SELECT Symbol FROM CryptoAll WHERE Symbol = ?", cryptoAll.Crypto.Symbol)

		if rows.Next() { // true
			fmt.Println("1 - Record Exist")
		} else { // false
			db.Exec("INSERT INTO CryptoAll (Symbol, Price) VALUES (?, ?)", cryptoAll.Crypto.Symbol, cryptoAll.Crypto.Price)
			aux.AppendFile("log/program-log.txt", cryptoAll.Crypto.Symbol, cryptoAll.Crypto.Price) // record a simple log
			fmt.Println("0 - Record Created")
		}

		i++
	}
}

I use a powerful API and a Web Socket (gorilla/websocket) and Unmarshal the json data,
Tell me if you want more information or code :+1:

Try this

func CreateRecords(cryptoFilteredResult []*gjson.CryptoFiltered) {
db, _ := sql.Open("sqlite3", "database/database.db")
defer db.Close()

db.Exec("CREATE TABLE IF NOT EXISTS CryptoAll (id INTEGER PRIMARY KEY AUTOINCREMENT, Symbol TEXT, Price REAL)")

var symbol int
for i:=0; i < len(cryptoFilteredResult); i++ {
	cryptoAll := CryptoAll{
		Crypto: cryptoFilteredResult[i],
	}
	
	row := db.QueryRow("SELECT Symbol FROM CryptoAll WHERE Symbol = ? LIMIT 1", cryptoAll.Crypto.Symbol)
	switch err := row.Scan(&symbol); err {
		case sql.ErrNoRows:
		   fmt.Println("0 - Record Created")
		   db.Exec("INSERT INTO CryptoAll (Symbol, Price) VALUES (?, ?)", cryptoAll.Crypto.Symbol, cryptoAll.Crypto.Price)
		   aux.AppendFile("log/program-log.txt", cryptoAll.Crypto.Symbol, cryptoAll.Crypto.Price) // record a simple log
		case nil:
		   fmt.Println("1 - Record Exist")
		default:
		   panic(err)		
	}
}

}
`

1 Like

Thanks, It works.
I changed it once and it worked properly!

...
var symbol string
...

That is really work. Thanks!

1 Like

This post was flagged by the community and is temporarily hidden.