package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
_ "github.com/lib/pq"
)
var db *sql.DB //连接池
func main() {
initDB()
// Insert(19700618,"laoba",42)
// delete("xiezi")
update(45,"lisi")
// query("laodi")
defer db.Close()
}
//初始化连接池,连接数据库
func initDB() {
psqlInfo := fmt.Sprintf("host=localhost port=5432 user=myusers" +
" password=123456 dbname=postgres sslmode=disable")
// psqlInfo := "xiaohua:123456@tcp(127.0.0.1:5432)/postgres"
var err error
db, err = sql.Open("postgres", psqlInfo)
if err != nil { //dsn格式不正确报错
return
} else {
fmt.Print("dsn格式正确,")
}
err = db.Ping()
if err != nil {
fmt.Println(err)
return
}
fmt.Println("连接数据库成功")
}
//查询数据
func query(n string){
var id,name,age string
rows,err:=db.Query(" select * from users where name=$1",n)
CheckErr(err,"查找失败")
for rows.Next(){
//从数据库读取值复制到指向的值中
err:= rows.Scan(&id,&name,&age)
CheckErr(err,"赋值失败")
}
err = rows.Err()
CheckErr(err,"******row.Err失败******")
fmt.Printf("id:%s name:%s age:%s \n ",id,name,age)
defer rows.Close()
}
//插入操作
func Insert(args... interface{}) {
stmt, err := db.Prepare("INSERT INTO users(id,name,age) VALUES ($1,$2,$3)")
CheckErr(err, "SQL语句设置失败")
_, err = stmt.Exec(args...)
CheckErr(err, "参数添加失败")
query(args[1].(string))
defer stmt.Close()
}
// 删除操作
func delete(args... interface{}) {
stmt, err := db.Prepare("DELETE from users where name=$1")
CheckErr(err, "SQL语句设置失败")
result, err := stmt.Exec(args...)
CheckErr(err, "参数添加失败")
num, err := result.RowsAffected()
CheckErr(err,"删除失败")
if num==1 {
fmt.Printf("删除%s成功,删除行数为%d\n",args[0].(string),num)
}else{
fmt.Printf("删除失败,%s不存在,删除行数为%d\n",args[0].(string),num)
}
defer stmt.Close()
}
//修改
func update(args... interface{}){
query(args[1].(string))
stmt, err := db.Prepare("UPDATE users SET age=$1 where name=$2")
CheckErr(err, "SQL语句设置失败")
result, err := stmt.Exec(args...)
CheckErr(err, "参数添加失败")
num, err := result.RowsAffected()
CheckErr(err,"修改失败")
fmt.Printf("修改成功,修改行数为%d\n",num)
query(args[1].(string))
defer stmt.Close()
}
//用来校验error对象是否为空
func CheckErr(err error,msg string) {
if nil != err {
log.Panicln(msg,err)
}
}
对于pgadmin: