Is it possible to get the value of a column by name using the GoLang / sql database

Question

All the examples that I saw for using sql.Row , return access values ​​from queries using the position : sql.Rows.scan() require the correct typed variable, it is correctly positioned in the scan() arguments corresponding to the corresponding column to retrieve each returned column value For example, in the following example:

GoDocs based example (with a little mod):

 rows, err := db.Query("SELECT name,age FROM users WHERE age>=50") if err != nil { log.Fatal(err) } for rows.Next() { var name string var age int if err := rows.Scan(&name,&age); err != nil { log.Fatal(err) } fmt.Printf("%s is %d\n", name, age) } if err := rows.Err(); err != nil { log.Fatal(err) } 

&name and &age must be set correctly (columns 0 and 1) for the rows. Scan () to get the right values ​​with the right types.

Over the years of development for production systems, I tried to avoid this practice because it was not reliable: changing the database in the column layout will easily break your code if it is based on column positions.

It is much more reliable to use column names to get values ​​- this isolates you from changes to the database that add or remove columns that messed up your code based on position. For example, in Delphi and C # all data sets, including columns that return values ​​from queries, support FieldByName('age').asInteger or fields['age'].value, etc.

Any way to achieve this in Go? If not, this is a big flaw in supporting the Go database and a major disappointment - it’s not at all safe, as already mentioned.

Edit:

Also (maybe this is a new question). The examples I saw seem to require that you get all the columns returned by the query, or the column positions will be distorted.

Suppose there is a utility request in a locked database that I cannot modify or add, and it retrieves several columns, but I only need one of them for my current task. Based on the current sql.Rows.scan() model, I should get all the values ​​from the query in the application code, even if I don't need them, whereas if I could query for "columnByName" , which would be optional, I could just enter the data I need into your application code. Any work for this?

Answer 1

Yes, this can be done without having to manually match column positions. You can use some third-party libraries for this, such as sqlx or gorp . I would recommend sticking to one of them instead of riding on your own.

Named matching has a small penalty. Named matching does not differ from matching column positions. It just works for you at runtime - perhaps every time you execute a request. This is true in any other language.

Why at runtime? The request is recorded as a string. It must be analyzed to determine the position.

If you want to create your own library, how do you do it yourself?

• Rows.Columns to get the names and positions of columns.

• Passing a fragment of []interface{} pointers to Rows.Scan to get values.

• to reflect. Value and Value.Addr to get a pointer to the target value.

• Value.FieldByName to get the Value for the structure field if you want to map structure fields.

So let's see how it works.

 type Person struct { Id int Name string } rows, err := db.Query("SELECT id, name FROM person;") if err != nil { // handle err log.Fatal(err) } columnNames, err := rows.Columns() // []string{"id", "name"} if err != nil { // handle err log.Fatal(err) } people = make([]Person, 0, 2) for rows.Next() { person := Person{} // person == Person{0, ""} pointers := make([]interface{}, len(columnNames)) // pointers == `[]interface{}{nil, nil}` structVal := reflect.ValueOf(person) for i, colName := range columnNames { fieldVal := structVal.FieldByName(strings.Title(colName)) if !fieldVal.IsValid() { log.Fatal("field not valid") } pointers[i] = fieldVal.Addr().Interface() } // pointers == `[]interface{}{&int, &string}` err := rows.Scan(pointers...) if err != nil { // handle err log.Fatal(err) } // person == Person{1, "John Doe"} people = append(people, person) }