Reading Database Rows into map[string]string

Question

I would like to read results from a simple SQL table like the following

customer	key
A	12345
B	6789

Now, I'd like to structure a map[string]string which has key value pairs equal to the row values like such:

map[A:12345, B:6789]

However, I am having trouble just taking the values out of the query result and dynamically creating the key-val pairs. I'll provide a rough outline of the code (the SQL connection stuff is not an issue, I have that figured out)

import "database/sql"

func main() {

    // Some code to connect to MSSQL...

    db, _ := sql.Open("mssql", connString)

    stmt := "SELECT customer, key from tbl"
    rows, _ := db.Query(stmt)
    defer rows.Close()

    data := make(map[string]string)
    for rows.Next() {
        // Need help here grabbing each row pair and adding them to my map...
    }
}

I'm open to trying to do this with an Empty struct as well, where the fields become the first column of my result set (dynamic) and the value becomes the 2nd column of my result set.

You can use rows.ColumnTypes to get info about the columns, then use reflection to create instances of the correct types for the values to be scanned into, similar to this: stackoverflow.com/a/61728101/965900 (note: you'll need []map[string]string to hold multiple rows) — mkopriva
– mkopriva, Commented Jan 26, 2023 at 13:38
I just want a single map result. I'm basically planning on using it as a lookup in future operations where I can reference the key and obtain the value for purpose of making API calls. I don't necessarily want a map for every single row. — Coldchain9
– Coldchain9, Commented Jan 26, 2023 at 13:46

GGP · Accepted Answer · 2023-01-26 14:14:30Z

2

You could temporarily store the values in two variables and then store them in the map:

func main() {
    stmt := "SELECT customer, key from tbl"
    rows, _ := db.Query(stmt)
    defer rows.Close()

    data := make(map[string]string)
    var (
        consumer string
        key string
    )
    for rows.Next() {
        if err := rows.Scan(&consumer, &key); err != nil {
            // Handle err
        }

        data[consumer] = key 
    }

    for k, v := range data {
        fmt.Println(k, v)
    }
    // "A" "12345"
    // "B" "6789"

}

Note that the consumer and key variables are allocated outside of the loop so we can re-use them. Even if the values are empty strings, the variables will be overwritten on each iteration.

answered Jan 26, 2023 at 14:14

GGP

3745 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Coldchain9 Over a year ago

Worked beautifully. Knowing that I can .Scan() into other items (I've typically seen examples just using structs) is super valuable!

Collectives™ on Stack Overflow

Reading Database Rows into map[string]string

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related