Go crawl Web data and save to MySQL and return JSON data < three >

This is a creation in Article, where the information may have evolved or changed.

The previous section primarily implemented the use of Goquery to fetch data from a picture site http://www.gratisography.com/. The main grab pictures of,,, data-original width height alt , type five data. So you need to create the database and the corresponding table, on the Mac I use the Sequel Pro database management software, connect to create a new database indiepic , and then create the table gratisography :

CREATE TABLE `gratisography` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `img_url` varchar(255) DEFAULT NULL,  `type_name` varchar(50) DEFAULT NULL,  `title` varchar(255) DEFAULT NULL,  `width` int(11) DEFAULT NULL,  `height` int(11) DEFAULT NULL,  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=388 DEFAULT CHARSET=utf8;

Once you have created the database, you start using go to connect to the database, and so on. Use Go-mysql-driver is a lightweight and fast mysql-driver for Go's (Golang) Database/sql package in Go

Document: Http://godoc.org/github.com/go-sql-driver/mysql

You need to get the package by using the following command before using it:

go get github.com/go-sql-driver/mysql

It is then database.go introduced in:

package crawldataimport (    "database/sql"    _ "github.com/go-sql-driver/mysql")

Then write a method to open the database OpenDatabase :

package crawldataimport (    "database/sql"    _ "github.com/go-sql-driver/mysql")func OpenDatabase() (*sql.DB, error) {    // 连接数据库    db, err := sql.Open("mysql", "root:mysql@tcp(xxx.xx.xx.xxx:3306)/databaseName?charset=utf8")    if err != nil {        return nil, err    }    return db, nil}

The previous section has written the insertdata (&imagedatas) method in crawldata.go , but it is a comment, and you need to implement the method in the file first.

Package Crawldataimport ("Database/sql", "FMT" _ "Github.com/go-sql-driver/mysql" "StrConv" s "strings") fun C OpenDatabase () (*sql. DB, error) {//Connect database db, err: = SQL.    Open ("MySQL", "root:mysql@tcp (xxx.xx.xx.xxx:3306)/databasename?charset=utf8") if err! = Nil {return nil, err  } return DB, nil}/* the function stores the obtained data to the database */func InsertData (datas *imagedatas) {imagedatas: = *datas//Connection db, ERR: = OpenDatabase () if err! = Nil {fmt. Printf (S.join ([]string{"Connection to database failed", err. Error ()}, "--")} Defer db. Close () for I: = 0; I < Len (Imagedatas); i++ {imageData: = Imagedatas[i]//Prepare statement for inserting data imgins, err: = db. Prepare ("INSERT into Gratisography (Img_url, type_name, title, width, height) VALUES (?,?,?,?,?)")//? = Placeholder If err! = Nil {fmt. Println (S.join ([]string{"Assemble data format", Err. Error ()}, "--")} defer Imgins.close ()//Close the statementWhen we leave Main () img, err: = Imgins.exec (S.join ([]string{"http://www.gratisography.com", imagedata.src}, "/"), IMAGEDATA.TP, Imagedata.title, Imagedata.width, imagedata.height) if err! = Nil {fmt. Println (S.join ([]string{"Insert data Failed", err. Error ()}, "--")} else {success, _: = img. Lastinsertid ()//number into a string, success is the value of the int64 type, need to be converted to int, the online Itoa64 () does not exist in StrConv package Insertid: = StrConv. Itoa (int (success)) FMT. Println (S.join ([]string{"successfully inserted data:", Insertid}, "\t-->\t")}}}}

This completes the data crawl and into the database, switches to the directory in the command line $GOPATH/src/indiepic , and then runs:

go run indiepic.go

You can then see that the data is stored in the database.
This only achieves data acquisition, but needs to use go to provide json interfaces externally, the next section completes the data acquisition and returns the JSON data using the Web framework.