Go学习第九天

使用sqlite3

package mainimport ("database/sql""fmt"_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx"_ "github.com/mattn/go-sqlite3""log""time"
)var schema = `
CREATE TABLE person (first_name text,last_name text,email text
);CREATE TABLE place (country text,city text NULL,telcode integer
)`type Person struct {FirstName string `db:"first_name"`LastName  string `db:"last_name"`Email     string
}type Place struct {Country stringCity    sql.NullStringTelCode int
}func main() {// this Pings the database trying to connect// use sqlx.Open() for sql.Open() semantics//dsn := "user:password@tcp(127.0.0.1:3306)/go-study?charset=utf8&parseTime=True"//db, err := sqlx.Connect("mysql", dsn)db, err := sqlx.Connect("sqlite3", "__deleteme.db")if err != nil {log.Fatalln(err)}db.SetConnMaxLifetime(time.Second * 10)db.SetMaxOpenConns(20) // 设置与数据库建立连接的最大数目db.SetMaxIdleConns(10) // 设置连接池中的最大闲置连接数// exec the schema or fail; multi-statement Exec behavior varies between// database drivers;  pq will exec them all, sqlite3 won't, ymmvdb.MustExec(schema)tx := db.MustBegin()tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net")tx.MustExec("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "1")tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Hong Kong", "852")tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "65")// Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &persontx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})tx.Commit()// Query the database, storing results in a []Person (wrapped in []interface{})people := []Person{}db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")jason, john := people[0], people[1]fmt.Printf("%#v\n%#v", jason, john)// Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}// Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@gmail.net"}// You can also get a single result, a la QueryRowjason = Person{}err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")fmt.Printf("%#v\n", jason)// Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}// if you have null fields and use SELECT *, you must use sql.Null* in your structplaces := []Place{}err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")if err != nil {fmt.Println(err)return}usa, singsing, honkers := places[0], places[1], places[2]fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers)// Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}// Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}// Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}// Loop through rows using only one structplace := Place{}rows, err := db.Queryx("SELECT * FROM place")for rows.Next() {err := rows.StructScan(&place)if err != nil {log.Fatalln(err)}fmt.Printf("%#v\n", place)}// Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}// Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}// Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}// Named queries, using `:name` as the bindvar.  Automatic bindvar support// which takes into account the dbtype based on the driverName on sqlx.Open/Connect_, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`,map[string]interface{}{"first": "Bin","last":  "Smuth","email": "bensmith@allblacks.nz",})// Selects Mr. Smith from the databaserows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "Bin"})// Named queries can also use structs.  Their bind names follow the same rules// as the name -> db mapping, so struct fields are lowercased and the `db` tag// is taken into consideration.rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:first_name`, jason)// batch insert// batch insert with structspersonStructs := []Person{{FirstName: "Ardie", LastName: "Savea", Email: "asavea@ab.co.nz"},{FirstName: "Sonny Bill", LastName: "Williams", Email: "sbw@ab.co.nz"},{FirstName: "Ngani", LastName: "Laumape", Email: "nlaumape@ab.co.nz"},}_, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email)VALUES (:first_name, :last_name, :email)`, personStructs)// batch insert with mapspersonMaps := []map[string]interface{}{{"first_name": "Ardie", "last_name": "Savea", "email": "asavea@ab.co.nz"},{"first_name": "Sonny Bill", "last_name": "Williams", "email": "sbw@ab.co.nz"},{"first_name": "Ngani", "last_name": "Laumape", "email": "nlaumape@ab.co.nz"},}_, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email)VALUES (:first_name, :last_name, :email)`, personMaps)
}