Saving data to MySQL in Golang

by Kodey

Of course, when we’re doing our data analysis reading from and writing to a database is super important. In this article, I am going to cover how we do that with MySQL. We’ll start at the most simple level and progress up to more complicated methods.

In this first example, we make the database connection using our connection string. Note that I don’t define the host. If you’re using a localhost, you can just leave that bit out.

In this example, we hard code the values that we want to insert into the database table.

    package main

    import (
        "fmt"
        "database/sql"
        _ "github.com/go-sql-driver/mysql"
    )
    
    func main() {
        db, err := sql.Open("mysql", "user:pass@/sotrics")        
        if err != nil {
            panic(err)
        }
        // defer the close till after the main function has finished
        defer db.Close()
    
        // perform a db.Query insert
        insert, err := db.Query("INSERT INTO sqltest VALUES ( 2, 'TEST' )")
    
        // if there is an error inserting, handle it
        if err != nil {
            panic(err.Error())
        }
        // be careful deferring Queries if you are using transactions
        defer insert.Close()
}

In this second example, I’ve defined a couple of variables, which I then pass into the SQL statement. This is a big step forward on the example above.

package main

import (
    "fmt"
    "strconv"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    fmt.Println("Go MySQL Tutorial")

    db, err := sql.Open("mysql", "user:pass@/sotrics")
    fmt.Println(db)
    // if there is an error opening the connection, handle it
    if err != nil {
        panic(err)
    }

    // defer the close till after the main function has finished
    // executing
    defer db.Close()

    s := "x"
    t := 1
    cs := strconv.Itoa(t) + "," + s
    fmt.Println(cs)
    st2 := fmt.Sprintf("INSERT INTO sqltest VALUES ( %d, '%s')", t, s)
    fmt.Println(st2)

    // perform a db.Query insert
    insert, err := db.Query(st2)

    // if there is an error inserting, handle it
    if err != nil {
        panic(err.Error())
    }
    // be careful deferring Queries if you are using transactions
    defer insert.Close()


}

Here, you can see how I’ve implemented this solution in the Twitter script which I have been discussing in recent articles.

package main

import (
    "fmt"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

type user_details struct {
        handle string
        url string
        timezone string
        StatusesCount int64
        location string
        CreatedAt string
        AccountAge float64
        FriendsCount int
        FollowersCount int
        FavouritesCount int
}
func searchProfile(profileName string, user_channel chan string) {
        api := connect()
        searchResult, _ := api.GetUsersShow(profileName, nil)
        FavouritesCount := searchResult.FavouritesCount
        FollowersCount := searchResult.FollowersCount
        FriendsCount := searchResult.FriendsCount
        CreatedAt := searchResult.CreatedAt
        Location := searchResult.Location
        StatusesCount := searchResult.StatusesCount
        TimeZone := searchResult.TimeZone
        URL := searchResult.URL
        handle := searchResult.Name

        acctAge := CalcAge(CreatedAt)

        output := user_details{
                handle : handle,
                url : URL,
                timezone: TimeZone,
                StatusesCount: StatusesCount,
                location: Location,
                CreatedAt: CreatedAt,
                AccountAge: acctAge,
                FriendsCount: FriendsCount,
                FollowersCount: FollowersCount,
                FavouritesCount: FavouritesCount}

        fmt.Println(output)

        db, err := sql.Open("mysql", "user:pass@/sotrics")
        if err != nil {
            panic(err)
        }
        // defer the close till after the main function has finished
        defer db.Close()

        // perform a db.Query insert
        st2 := fmt.Sprintf("INSERT INTO tw_overview2 VALUES ('%s','%s', %d, '%s', '%s', %f, %d, %d, %d)", handle, URL, StatusesCount, Location, CreatedAt, acctAge, FriendsCount, FollowersCount, FavouritesCount)
        fmt.Println(st2)

        // perform a db.Query insert
        insert, err := db.Query(st2)


        // if there is an error inserting, handle it
        if err != nil {
            panic(err.Error())
        }
        // be careful deferring Queries if you are using transactions
        defer insert.Close()

        user_channel <- "Profile Complete"
}

The final thing is, saving data from a dataframe to MySQL, which is really very handy. You can see in the below, we create the DB connection and then simply use the ToSQL function on the dataframe. IMPORTANT: The names and data types in your database table must exactly match those of your QFrame.

                f := qframe.New(map[string]interface{}{
                        "media_included": media,
                        "TweetID": id_list,
                        "random_id": random_id,
                        "Handle": handle_list,
                        "CreatedAt": date_list,
                        "Age": age_list,
                        "FavoriteCount": fav_list,
                        "FavMax": is_max_fav,
                        "RetweetCount": rt_list,
                        "RetweetMax": is_max,
                        "interactionCount": interaction_list,
                        "hour": hour_list,
                        "day": day_list,
                })
                fmt.Println(f)

                datesum := func(xx []int) int {
                        result := 0
                        for _, x := range xx {
                                result += x
                        }
                        return result
                }

        db, err := sql.Open("mysql", "user:pass@/sotrics")
        fmt.Println(db)
        if err != nil {
            panic(err)
        }
        // defer the close till after the main function has finished
        defer db.Close()

        tx, _ := db.Begin()
        f.ToSQL(
            tx,
            qsql.Table("tw_post_detail2"),
            qsql.MySQL(),

   )
   tx.Commit()
Tags:   
Kodey