- Notifications
You must be signed in to change notification settings - Fork2.3k
Examples
A detailed introduction to thedatabase/sql package is available here:http://go-database-sql.org/
First, you should understand that asql.DB isnot a connection. When you usesql.Open() you get a handle for a database. The database/sql package manages a pool of connections in the background, and doesn't open any connections until you need them.Thereforesql.Open() doesn't directly open a connection. As a result,sql.Open() does not return an error, if the server isn't available or the connection data (Username, Password) isn't correct. If you want to check this before making queries (e.g at application startup) you can usedb.Ping().
db,err:=sql.Open("mysql","user:password@/dbname")iferr!=nil {panic(err.Error())// Just for example purpose. You should use proper error handling instead of panic}deferdb.Close()// Open doesn't open a connection. Validate DSN data:err=db.Ping()iferr!=nil {panic(err.Error())// proper error handling instead of panic in your app}// Use the DB normally, execute the querys etc[...]
Assume an empty table with the following layout:
+--------------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------------+---------+------+-----+---------+-------+| number | int(11) | NO | PRI | NULL | || squareNumber | int(11) | NO | | NULL | |+--------------+---------+------+-----+---------+-------+In this example we prepare two statements - one for inserting tuples (rows) and one to query.
package mainimport ("database/sql""fmt"_"github.com/go-sql-driver/mysql")funcmain() {db,err:=sql.Open("mysql","user:password@/database")iferr!=nil {panic(err.Error())// Just for example purpose. You should use proper error handling instead of panic}deferdb.Close()// Prepare statement for inserting datastmtIns,err:=db.Prepare("INSERT INTO squareNum VALUES( ?, ? )")// ? = placeholderiferr!=nil {panic(err.Error())// proper error handling instead of panic in your app}deferstmtIns.Close()// Close the statement when we leave main() / the program terminates// Prepare statement for reading datastmtOut,err:=db.Prepare("SELECT squareNumber FROM squarenum WHERE number = ?")iferr!=nil {panic(err.Error())// proper error handling instead of panic in your app}deferstmtOut.Close()// Insert square numbers for 0-24 in the databasefori:=0;i<25;i++ {_,err=stmtIns.Exec(i, (i*i))// Insert tuples (i, i^2)iferr!=nil {panic(err.Error())// proper error handling instead of panic in your app}}varsquareNumint// we "scan" the result in here// Query the square-number of 13err=stmtOut.QueryRow(13).Scan(&squareNum)// WHERE number = 13iferr!=nil {panic(err.Error())// proper error handling instead of panic in your app}fmt.Printf("The square number of 13 is: %d",squareNum)// Query another number.. 1 maybe?err=stmtOut.QueryRow(1).Scan(&squareNum)// WHERE number = 1iferr!=nil {panic(err.Error())// proper error handling instead of panic in your app}fmt.Printf("The square number of 1 is: %d",squareNum)}
Maybe you already encountered this error:sql: Scan error on column index 1: unsupported driver -> Scan pair: <nil> -> *string
Normally you would usesql.NullString in such a case. But sometimes you don't care if the value isNULL, you just want to treat it as an empty string.
You can do this with a small workaround, which takes advantage of the fact, that anil-[]byte gets converted to an empty string.Instead of using*string as arows.Scan(...) destination, you simple use*[]byte (or*sql.RawBytes), which can take thenil value:
[...]varcol1,col2 []bytefor rows.Next() {// Scan the value to []byteerr= rows.Scan(&col1,&col2)iferr!=nil {panic(err.Error())// Just for example purpose. You should use proper error handling instead of panic}// Use the string valuefmt.Println(string(col1),string(col2))}
package mainimport ("database/sql""fmt"_"github.com/go-sql-driver/mysql")funcmain() {// Open database connectiondb,err:=sql.Open("mysql","user:password@/dbname")iferr!=nil {panic(err.Error())// Just for example purpose. You should use proper error handling instead of panic}deferdb.Close()// Execute the queryrows,err:=db.Query("SELECT * FROM table")iferr!=nil {panic(err.Error())// proper error handling instead of panic in your app}// Get column namescolumns,err:=rows.Columns()iferr!=nil {panic(err.Error())// proper error handling instead of panic in your app}// Make a slice for the valuesvalues:=make([]sql.RawBytes,len(columns))// rows.Scan wants '[]interface{}' as an argument, so we must copy the// references into such a slice// See http://code.google.com/p/go-wiki/wiki/InterfaceSlice for detailsscanArgs:=make([]interface{},len(values))fori:=rangevalues {scanArgs[i]=&values[i]}// Fetch rowsforrows.Next() {// get RawBytes from dataerr=rows.Scan(scanArgs...)iferr!=nil {panic(err.Error())// proper error handling instead of panic in your app}// Now do something with the data.// Here we just print each column as a string.varvaluestringfori,col:=rangevalues {// Here we can check if the value is nil (NULL value)ifcol==nil {value="NULL"}else {value=string(col)}fmt.Println(columns[i],": ",value)}fmt.Println("-----------------------------------")}iferr=rows.Err();err!=nil {panic(err.Error())// proper error handling instead of panic in your app}}
Feel free to contribute your own examples!