Convert Select query obtained from Postgre with Go to JSON

Thing you want to do

--I want to convert the entire query obtained by the SELECT statement to JSON --It seems that the scan of the sql package is processing line by line, so you need to create an array in the loop (?) --Convert from array with json package --The reference code is on the MS site

code

main.go


package main

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

const (
    // Initialize connection constants.
    HOST     = "127.0.0.1"
    DATABASE = "testdb"
    USER     = "root"
    PASSWORD = "password"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {

    // Initialize connection string.
    var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=disable", HOST, USER, PASSWORD, DATABASE)

    // Initialize connection object.
    db, err := sql.Open("postgres", connectionString)
    checkError(err)

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database")

    // Read rows from table.
    var id int
    var name string
    var quantity int

    sql_statement := "SELECT * from inventory;"
    rows, err := db.Query(sql_statement)
    checkError(err)
    defer rows.Close()

    //Define structure
    type data struct {
        ID int
        Name string
        Quantity int
    }

    //Declare array type
    var fruit []data

    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)

            //Add array line by line
            fruit = append(fruit,data{
                ID: id,
                Name: name,
                Quantity: quantity,
            })
        default:
            checkError(err)
        }
    }

    fruitjson, _ := json.Marshal(fruit)
    fmt.Println(string(fruitjson))

}

result

yuta:~/postgre $ go run main.go 
Successfully created connection to database
Data row = (1, banana, 150)
Data row = (2, orange, 154)
Data row = (3, apple, 100)
Data row = (4, test, 100)
[{"ID":1,"Name":"banana","Quantity":150},{"ID":2,"Name":"orange","Quantity":154},{"ID":3,"Name":"apple","Quantity":100},{"ID":4,"Name":"test","Quantity":100}]

reference

https://docs.microsoft.com/ja-jp/azure/postgresql/connect-go

Recommended Posts

Convert Select query obtained from Postgre with Go to JSON
Convert from PDF to CSV with pdfplumber
Convert Excel data to JSON with python
Convert array (struct) to json with golang
Convert 202003 to 2020-03 with pandas
Convert json to excel
How to convert JSON file to CSV file with Python Pandas
Connect to Postgresql with GO
Generate URL query from JSON
Convert color space from RGB to CIELAB with PIL (Pillow)
[Python] Convert CSV file uploaded to S3 to JSON file with AWS Lambda
Convert PIL format images read from form with Django to base64 format
Convert Tweepy Status object to JSON
Create folders from '01' to '12' with python
Convert list to DataFrame with python
Convert sentences to vectors with gensim
Convert from pdf to txt 2 [pyocr]
How to convert from .mgz to .nii.gz
Convert PDF to image with ImageMagick
Convert memo at once with Python 2to3
How to easily convert format from Markdown
Script to generate directory from json file
Convert (compress) formatted JSON string to 1-line JSON
Convert character strings to features with RoBERTa
Convert Hiragana to Romaji with Python (Beta)
Convert from katakana to vowel kana [python]
Convert FX 1-minute data to 5-minute data with Python
Convert PDF files to PNG files with GIMP
Convert HEIC files to PNG files with Python
Convert Chinese numerals to Arabic numerals with Python
AtCoder Green tried to solve with Go
Convert from Markdown to HTML in Python
Sample to convert image to Wavelet with Python
How to execute a query from psycopg2 built with SAM to Postgres launched with Docker