继Golang学习系列第三天https://www.cnblogs.com/dongguangming/p/13311198.html：数组、切片、Map、结构体、指针、函数、接口类型、channel通道，今天开始学习golang操作数据库，以PostgreSQL为例。

 

0.  安装PostgreSQL 数据库

可以参考PostgreSQL官网https://www.postgresql.org/download/linux/redhat/安装该数据库

特别需要说明的是，安装完成后，自动建立了一个名为postgres的用户，默认密码为空；同时也自动创建了一个名字叫postgres的数据库。

0.1、修改默认生成的数据库用户postgres的密码。

​

把密码设置为12345678.

0.2  创建示例数据库

测试数据库名可以自取，

​

然后建一张测试表让golang使用

CREATE TABLE users ( id serial PRIMARY KEY, email VARCHAR (355) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL);insert into users(id,email,password) values(1,'1056764180@qq,com','12345678');insert into users(id,email,password) values(2,'10567@qq,com','1234567890');insert into users(id,email,password) values(3,'10567567@qq,com','12345678908');

0.3  开启远程访问

由于数据库和应用程序不在同一机器上，故数据库要开启远程访问功能

修改配置文件，即

vim /var/lib/pgsql/12/data/postgresql.conf

找到listen_adderess配置项设为*

​

继续修改另一配置文件，即

vim /var/lib/pgsql/12/data/pg_hba.conf

 在# IPv4 local connections:处追加客户端的连接信息

​

重启postgresql服务

systemctl restart postgresql-12

最后客户端测试连接

 ​

 

1.   golang操作数据库

连接数据库会使用第三方驱动包，由于墙的缘故，可以先设置一下代理

go env -w GO111MODULE=ongo env -w GOPROXY=https://mirrors.aliyun.com/goproxy/,direct

就以基本的增删改查数据，记录如何使用go操作数据库

1. 1  Select查询数据

新建postgres.go项目，键入以下测试连接数据库的代码

package mainimport (	"database/sql"	"fmt"	"log"	_ "github.com/lib/pq" //_ "github.com/bmizerany/pq"  )const (	// TODO fill this in directly or through environment variable	// Build a DSN e.g. postgres://username:password@url.com:5432/dbName	DB_DSN = "postgres://postgres:12345678@192.168.8.200:5432/douyin?sslmode=disable")type User struct {	ID  int	Email string	Password string}func main() {	// Create DB pool	//db, err := sql.Open("postgres", "host=192.168.8.200 port=5432 user=postgres password=12345678 dbname=douyin sslmode=disable")	db, err := sql.Open("postgres",DB_DSN)  if err != nil {		log.Fatal("Failed to open a DB connection: ", err)	}	defer db.Close()	// Create an empty user and make the sql query (using $1 for the parameter)	var myUser User	userSql := "SELECT id, email, password FROM users WHERE id = $1"	err = db.QueryRow(userSql, 1).Scan(&myUser.ID, &myUser.Email, &myUser.Password)	if err != nil {		log.Fatal("Failed to execute query: ", err)	}	fmt.Printf("你好 邮箱：%s, 密码：%s, 欢迎回来!\n", myUser.Email, myUser.Password)}

然后创建一个模块依赖文件

go mod init pluginModel

安装具体的依赖包

go get github.com/lib/pq

 

 最后运行测试代码

[root@master goworkspace]# go run postgres.go

 从数据库查询id等于1的记录，如图

​

和数据库里的数据是对应的

​

 

1.2   增加数据

接上1.1示例代码，稍作更改即可，文件命名为postgres-create.go

package mainimport (	"database/sql"	"fmt"	"log"	_ "github.com/lib/pq"  //_ "github.com/bmizerany/pq"  )const (	// TODO fill this in directly or through environment variable	// Build a DSN e.g. postgres://username:password@url.com:5432/dbName	DB_DSN = "postgres://postgres:12345678@192.168.8.200:5432/douyin?sslmode=disable")type User struct {	ID  int	Email string	Password string}func main() {	// Create DB pool	//db, err := sql.Open("postgres", "host=192.168.8.200 port=5432 user=postgres  password=12345678 dbname=douyin sslmode=disable")	db, err := sql.Open("postgres",DB_DSN)  if err != nil {		log.Fatal("Failed to open a DB connection: ", err)	}	defer db.Close()  //创建一个用户，预要插入到数据库里 var user User = User{ID:4,Email:"110@qq.com",Password:"1234567890"} //执行插入操作 _, err = db.Exec("INSERT INTO users (id,email,password) VALUES($1,$2,$3)",  user.ID,user.Email,user.Password) if err != nil {  log.Fatal(err) } //打印日志 log.Printf("create ok!!!")	//测试数据是否插入成功，执行具体的查询语句	var myUser User	userSql := "SELECT id, email, password FROM users WHERE id = $1"  //设置查询参数为4，即创建数据时的ID值	err = db.QueryRow(userSql, 4).Scan(&myUser.ID, &myUser.Email,  &myUser.Password)	if err != nil {		log.Fatal("Failed to execute query: ", err)	}  //输出查询结果	fmt.Printf("hello email: %s, password: %s, welcome back!\n",  myUser.Email,myUser.Password)}

执行程序代码，输出结果

​

 

1.3  update修改数据

接上1.2示例代码，稍作更改即可，文件命名为postgres-update.go

package mainimport (	"database/sql"	"fmt"	"log"	_ "github.com/lib/pq"  //_ "github.com/bmizerany/pq"  )const (	// TODO fill this in directly or through environment variable	// Build a DSN e.g. postgres://username:password@url.com:5432/dbName	DB_DSN = "postgres://postgres:12345678@192.168.8.200:5432/douyin?sslmode=disable")type User struct {	ID  int	Email string	Password string}func main() {	// Create DB pool	//db, err := sql.Open("postgres", "host=192.168.8.200 port=5432 user=postgres  password=12345678 dbname=douyin sslmode=disable")	db, err := sql.Open("postgres",DB_DSN)  if err != nil {		log.Fatal("Failed to open a DB connection: ", err)	}	defer db.Close()  //创建一个用户，预要通过主键更改到数据库里 var user User = User{ID:4,Email:"dong@qq.com",Password:"abcdedf120"} //执行更改操作 _, err = db.Exec("UPDATE users SET email=$1, password=$2 where id=$3", user.Email,user.Password,user.ID) if err != nil {  log.Fatal(err) } //打印日志 log.Printf("update ok!!!")	//测试数据是否更改成功，执行具体的查询语句	var myUser User	userSql := "SELECT id, email, password FROM users WHERE id = $1"  //设置查询参数为4，即要更改数据的ID值	err = db.QueryRow(userSql, 4).Scan(&myUser.ID, &myUser.Email,  &myUser.Password)	if err != nil {		log.Fatal("Failed to execute query: ", err)	}  //输出查询结果	fmt.Printf("hello email: %s, password: %s, welcome back!\n",  myUser.Email,myUser.Password)}

 

执行程序代码，输出结果

​

 

1.4   delete删除数据记录

接上1.3示例代码，稍作更改即可，文件命名为postgres-delete.go

 

package mainimport (	"database/sql"	"fmt"	"log"	_ "github.com/lib/pq"  //_ "github.com/bmizerany/pq"  )const (	// TODO fill this in directly or through environment variable	// Build a DSN e.g. postgres://username:password@url.com:5432/dbName	DB_DSN = "postgres://postgres:12345678@192.168.8.200:5432/douyin?sslmode=disable")type User struct {	ID  int	Email string	Password string}func main() {	// Create DB pool	//db, err := sql.Open("postgres", "host=192.168.8.200 port=5432 user=postgres  password=12345678 dbname=douyin sslmode=disable")	db, err := sql.Open("postgres",DB_DSN)  if err != nil {		log.Fatal("Failed to open a DB connection: ", err)	}	defer db.Close()  //执行更改操作 _, err = db.Exec("DELETE FROM users where id=$1", 4) if err != nil {  log.Fatal(err) } //打印日志 log.Printf("delete ok!!!")	//测试数据是否更改成功，执行具体的查询语句	var myUser User	userSql := "SELECT id, email, password FROM users WHERE id = $1"  //设置查询参数为4，即要更改数据的ID值	err = db.QueryRow(userSql, 4).Scan(&myUser.ID, &myUser.Email,  &myUser.Password)	if err != nil {		log.Fatal("Failed to execute query: ", err)	}  //输出查询结果	fmt.Printf("hello email: %s, password: %s, welcome back!\n",  myUser.Email,myUser.Password)}

执行以上程序代码，执行输出结果

​

 

至此到这里关于golang操作数据库postgresql就告一段落了，收工。

代码已上传到github：https://github.com/dongguangming/golang-learn/tree/master/go-postgresql

注：由于我没有用可视化编程工具，是用vi编写的go代码，请你们自行排版其结构。

 

参考：

1. 1.  Postgresql 密码设置&nbsp find module providing package github.com/xxx: working directory is not part of a module  https://www.sunzhongwei.com/cannot-find-module-providing-package-githubcomxxx-working-directory-is-not-part-of-a-module

   2. SSL is not enabled on the server https://stackoverflow.com/questions/21959148/ssl-is-not-enabled-on-the-server

   3. Resolve "FATAL:no pg_hba.conf entry for host" Error when you Connect from PGAdmin4 https://www.cisco.com/c/en/us/support/docs/cloud-systems-management/cloudcenter/212585-resolve-fatal-no-pg-hba-conf-entry-for.html

   4. Connect to PostgreSQL and Run a Query https://golangcode.com/postgresql-connect-and-query/

   5. golang postgresql CRUD  https://www.cnblogs.com/ibgo/p/6010245.html 

   6.  

Golang学习系列第四天：操作数据库PostgreSQL
文章转载：http://www.shaoqun.com/a/463258.html

---