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()
