golang 查询数据库操作

SQL.Open only creates the DB object, but dies not open any connections to the database. If you want to test your connections you have to execute a query to force opening a connection. The common way for this is to call Ping() on your DB object.

See http://golang.org/pkg/database/sql/#Open and http://golang.org/pkg/database/sql/#DB.Ping

sql.Open()

Open may just validate its arguments without creating a connection to the database. To verify that the data source name is valid, call Ping.

Open()nildataSourceName

To answer your other question:

What is the point of check for errors after this function if it does not return errors?

driverName

DB.Ping()nilDBnilPing()

if db, err := sql.Open("nonexistingdriver", "somesource"); err != nil { fmt.Println("Error creating DB:", err) fmt.Println("To verify, db is:", db) } else { err = db.Ping() if err != nil { fmt.Println("db.Ping failed:", err) } }

Output (try it on the Go Playground):

Error creating DB: sql: unknown driver "nonexistingdriver" (forgotten import?) To verify, db is: <nil>

sql.open(“postgres”, “postgres://postgres:postgres/xxxx”)连接数据库出错的时候，也不会报错， 很奇怪，那这种错误是怎么处理的呢？

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
	"log"
)

var db *sql.DB

func main() {

	defer func() {
		fmt.Println(recover())
	}()
	var ss string
	var err error
	// var err error
	if db != nil {
		db.Close()
	} else {
		db, err = sql.Open("postgres", "postgres://postgres:postgres@127.0.0.1/xinyi?sslmode=disable")
		if err != nil {
			log.Println("Can't connect to postgresql database")
		} else {
			err = db.Ping()
			if err != nil {
				fmt.Println("db.Ping failed:", err)
			}
		}
		err = db.QueryRow("select value from configs where key =$1", "line1_batch").Scan(&ss)
		if err != nil {
			log.Println("query error")
		}
		fmt.Println(ss)
	}

}

—————————————————————————————————–

SQL Drivers

Go’s standard library was not built to include any specific database drivers. Here is a list of available third party SQL drivers http://golang.org/s/sqldrivers .

Setup

First we will need to import the packages that our program will use.

import (
  “database/sql”
  _ “github.com/lib/pq”
)

Here, we import the “database/sql” library which provides a generic interface for working with SQL databases. The second import, _”github.com/lib/pq”, is the actual postgresql driver. The underscore before the library means that we import pq without side effects. Basically, it means Go will only import the library for its initialization. For pq, the initialization registers pq as a driver for the SQL interface.

Open

Next we will need to open the database. It is important to note that calling “Open” does not open a connection to the database. The return from “Open” is a DB type and an error. The DB type represents a pool of connections which the sql package manages for you.

db, err := sql.Open(“postgres”,”user=Arnold dbname=TotalRecall sslmode=disable”)

“Open” returns an error which validates the arguments of a database open

if err != nil {
  log.Fatal(“Error: The data source arguments are not valid”)
}

Ping

Since the error returned from “Open” does not check if the datasource is valid calling Ping on the database is required

err = db.Ping()

if err != nil {
  log.Fatal(“Error: Could not establish a connection with the database”)
}

Prepare

Once the DB has been set up, we can start safely preparing query statements. “Prepare” does not execute the statement.

queryStmt, err := db.Prepare(“SELECT name FROM users WHERE id=$1”)

if err != nil {
  log.Fatal(err)
}

QueryRow

We can now “QueryRow” off of the prepared statement and store the returned row’s first column into the “name string”. “QueryRow” only queries for one row.

var name string

err = queryStmt.QueryRow(15).Scan(&name)

In addition, a common error check is for “No Rows”. Some programs handle “No Rows” differently from other scanning errors. Errors like this are specific to the library, not Go in general.

if err == sql.ErrNoRows {
  log.Fatal(“No Results Found”)
}

if err != nil {
  log.Fatal(err)
}

You can also skip explicitly preparing your Query statements.

var lastName string

err = db.QueryRow(“SELECT last_name FROM users WHERE id=$1”, 15).Scan(&lastName)

if err == sql.ErrNoRows {
  log.Fatal(“No Results Found”)
}

if err != nil {
  log.Fatal(err)
}

Query

We can also handle a Query that returns multiple rows and stores the result into a “names” slice. In the code below you will see “rows.Next”, which moves the cursor to the next result row. If there is no next row or error preparing the next row, a false will be returned.

var names []string

rows, err := queryStmt.Query(15)
defer rows.Close()

for rows.Next() {
  var name string

  if err := rows.Scan(&name); err != nil {
    log.Fatal(err)
  }
  names = append(names, name)
}

This next check is for any errors encountered during the iteration.

err = rows.Err()

if err != nil {
  log.Fatal(err)
}

Conclusion

Golang’s standard sql package is extremely simple, yet powerful. This post covers the basics of the sql package. If you would like to learn more, visit the official docs at: http://golang.org/pkg/database/sql. Feel free to leave any comments or questions.