Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand Golang hand-in-hand PostgreSQL addition, deletion, modification and search + fast scan field of unwritten structure". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Driver installation

Execute the following command to install the postgresql driver

Go get github.com/lib/pq

Features supported by the pq package

SSL

As a driver, handle connection-related operations in conjunction with database/sql

Scan time time.Time type, such as timestamp [tz], time [tz], date

Scan binary blobs objects (Blob is an in-memory data buffer that matches ID and byte slices of type strign), such as bytea

Hstore data type support for PostgreSQL

Support for COPY FROM

The pq.ParseURL method is used to convert urls to the connection string of sql.Open

Support for many libpq library compatible environment variables

Support for Unix socket

Support notifying Notifications, such as LISTEN/NOTIFY

Support for pgpass

GSS (Kerberos) authentication

Connection string parameter

The pq package is similar to libpq (libpq is a low-level interface written in C, which provides low-level PostgreSQL support for other high-level languages such as Cruise PerlMagne PythonJournal TCL and ECPG). When establishing a connection, you need to provide connection parameters, and some parameters that support libpq also support pq. In addition, pq allows you to specify run-time parameters (such as search_path or work_mem) in the connection string, while libpq cannot specify run-time parameters in the connection string, only in the option parameter.

Pq package in order to be compatible with libpq package, the following connection parameters are supported

* dbname-database name that needs to be connected * user-username to be used * password-password of the user * host-postgresql host to be connected, unix domain name socket starts with /, default is localhost * port-postgresql bound port (default 5432) * sslmode-whether to use SSL (require is enabled by default, SSL is not enabled by libpq package) * fallback_application_name-in case of failure You can provide an application name to track. * connect_timeout-the maximum number of seconds to wait for the connection, 0 or not specified, indicating the wait for uncertain time * sslcert-certificate file location, the file must contain PEM-encoded data * sslkey-key file location, the file must contain PEM-encoded data * sslrootcert-root certificate file location, the file must contain PEM-encoded data

Sslmode supports the mode.

* disable-disable SSL * require-always use SSL (skip authentication) * verify-ca-always use SSL (verify that the certificate provided by the server is signed by a trusted CA) * verify-full-always use SSL (verify that the certificate provided by the server is signed by a trusted CA and verify that the server hostname matches the hostname in the certificate)

For more connection string parameters, please refer to the official documentation

For parameters that contain spaces, you need to use single quotes, such as:

"user=pqgotest password='with spaces'"

Escape using a backslash, such as:

"user=space\ man password='it\ 's valid'"

Note: if you want to set the client_encoding connection parameter (which is used to set the encoding of the connection), it must be set to "UTF8" to match the Postgres. Setting to another value will cause an error.

In addition to the above parameters, you can also set runtime parameters in the connection string through the background. For more detailed runtime parameters, please refer to runtime-config.

Most of the environment variables that support libpq also support the pq package. For more information, please refer to libpq-envars. If the environment variable is not set and the connection string does not provide this parameter, the program crashes and exits, and the string parameter takes precedence over the environment variable.

Complete "add, delete, modify and check" sample code

Package main import ("database/sql", "encoding/json", "fmt" _ "github.com/lib/pq"log") const (/ / Initialize connection constants. HOST = "172.16.xx.xx" PORT = 31976 DATABASE = "postgres" USER = "postgres" PASSWORD = "xxx") func checkError (err error) {if err! = nil {panic (err)}} type Db struct {db * sql.DB} / / create table func (this * Db) CreateTable () {/ / take fruit inventory list inventory as an example / / Drop previous table of same name if one exists. Delete the list table _, err: = this.db.Exec ("DROP TABLE IF EXISTS inventory;") checkError (err) fmt.Println ("Finished dropping table (if existed)") / / Create table. Create a table and specify the id, name, quantity (quantity) fields, where id is the primary key _, err = this.db.Exec ("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR (50), quantity INTEGER);") checkError (err) fmt.Println ("Finished creating table")} / / delete table func (this * Db) DropTable () {/ / take the fruit inventory list inventory as an example / / Drop previous table of same name if one exists. Delete the table _, err: = this.db.Exec ("DROP TABLE IF EXISTS inventory;") checkError (err) fmt.Println ("Finished dropping table (if existed)")} / / add data func (this * Db) Insert () {/ / Insert some data into table. Insert 3 fruit data sql_statement: = "INSERT INTO inventory (name, quantity) VALUES ($1, $2) "_, err: = this.db.Exec (sql_statement," banana ", 150) checkError (err) _, err = this.db.Exec (sql_statement," orange ", 154) checkError (err) _, err = this.db.Exec (sql_statement," apple " CheckError (err) fmt.Println ("Inserted 3 rows of data")} / / read data / check data func (this * Db) Read () {/ / read data / / Read rows from table. Var id int var name string var quantity int sql_statement: = "SELECT * from inventory;" rows, err: = this.db.Query (sql_statement) checkError (err) defer rows.Close () for rows.Next () {switch err: = rows.Scan (& id, & name, & quantity) Err {case sql.ErrNoRows: fmt.Println ("No rows were returned") case nil: fmt.Printf ("Data row = (% d,% s,% d)\ n", id, name, quantity) default: checkError (err)}} / / Update data func (this * Db) Update () {/ / Modify some data in table. Sql_statement: = "UPDATE inventory SET quantity = $2 WHERE name = $1;" _, err: = this.db.Exec (sql_statement, "banana", 200) checkError (err) fmt.Println ("Updated 1 row of data")} / / delete data func (this * Db) Delete () {/ / Delete some data from table. Sql_statement: = "DELETE FROM inventory WHERE name = $1 "_, err: = this.db.Exec (sql_statement," orange ") checkError (err) fmt.Println (" Deleted 1 row of data ")} / / data is serialized into Json string to facilitate manual viewing of func Data2Json (anyData interface {}) string {JsonByte, err: = json.Marshal (anyData) if err! = nil {log.Printf (" data serialization to json error:\ n% s\ n " Err.Error () return ""} return string (JsonByte)} / / Multi-line data parsing func QueryAndParseRows (Db * sql.DB, queryStr string) [] map [string] string {rows, err: = Db.Query (queryStr) defer rows.Close () if err! = nil {log.Printf ("query error:\ nSQL:\ n% s, error details\ n", queryStr, err.Error () return nil} cols _: = rows.Columns () / / column name if len (cols) > 0 {var ret [] map [string] string / / defines the returned mapping slice variable ret for rows.Next () {buff: = make ([] interface {}, len (cols)) data: = make ([] [] byte, len (cols)) / / the null value in the database can be scanned to for I in bytes. _: = range buff {buff [I] = & data [I]} rows.Scan (buff...) / / scan into the buff interface It is actually in the string type data / / storing each line of data in the array dataKv: = make (map [string] string, len (cols)) for k, col: = range data {/ / k is index Col is the corresponding value / / fmt.Printf ("% 30s:\ t% s\ n", cols [k], col) dataKv [cols [k]] = string (col)} ret = append (ret, dataKv)} log.Printf ("return multi-element array:\ n% s", Data2Json (ret)) return ret} else {return nil} / / single-row data parsing query database Parsing query results Support dynamic row count parsing func QueryAndParse (Db * sql.DB, queryStr string) map [string] string {rows, err: = Db.Query (queryStr) defer rows.Close () if err! = nil {log.Printf ("query error:\ nSQL:\ n% s, error details\ n", queryStr, err.Error () return nil} / / rows, _: = Db.Query ("SHOW VARIABLES LIKE'% data%'") cols _: = rows.Columns () if len (cols) > 0 {buff: = make ([] interface {}, len (cols)) / / temporary slice data: = make ([] [] byte, len (cols)) / / save data slice dataKv: = make (mapping [string] string, len (cols)) for I _: = range buff {buff [I] = & data [I]} for rows.Next () {rows.Scan (buff...) / /. Is required} for k, col: = range data {dataKv [cols [k]] = string (col) / / fmt.Printf ("% 30s:\ t% s\ n", cols [k], col)} log.Printf ("return single row data Map:\ n% s", Data2Json (dataKv)) return dataKv} else {return nil} func main () {/ / Initialize connection string. Initialize the connection string, parameters include host, port, user name, password, database name, SSL mode (disabled), timeout var connectionString string = fmt.Sprintf ("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable connect_timeout=3", HOST, PORT, USER, PASSWORD, DATABASE) / / Initialize connection object. Initialize the connection object, and the driver is named postgres db, err: = sql.Open ("postgres", connectionString) defer db.Close () checkError (err) postgresDb: = Db {db: db } err = postgresDb.db.Ping () / / Connectivity check checkError (err) fmt.Println ("Successfully created connection to database") postgresDb.CreateTable () / / create table postgresDb.Insert () / / insert data postgresDb.Read () / / query data QueryAndParseRows (postgresDb.db, "SELECT * data" ") / / directly query and parse multi-row data QueryAndParse (postgresDb.db," SHOW DateStyle; ") / / directly query and parse single-row data postgresDb.Update () / / modify / update data postgresDb.Read () postgresDb.Delete () / delete data postgresDb.Read () postgresDb.DropTable ()}

The running result of executing go run main.go is as follows:

Successfully created connection to database Finished dropping table (if existed) Finished creating table Inserted 3 rows of data Data row = (1, banana) Data row = (2, orange, 15) Data row = (3, apple, 100) A multi-element array is returned at 22:13:33 on 2020-12-15: [{"id": "1", "name": "banana", "quantity": "150"}, {"id": "2", "name": "orange", "quantity": "154"}, {"id": "3") "name": "apple", "quantity": "100"}] 22:13:33 on 2020-12-15, return single row data Map: {"DateStyle": "ISO, MDY"} Updated 1 row of data Data row = (2, orange, 15 4) Data row = (3, apple, 100) Data row = (1, banana, 200) Deleted 1 row of data Data row = (3, apple, 100) Data row = (1, banana) 200) Finished dropping table (if existed) "how to understand Golang hand-in-hand PostgreSQL addition, deletion, modification and search + fast scan field of unwritten structure" ends here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.