StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Turns out there's an <code>sqlx.StructScan(rows, &destSlice)</code> <em>function</em> that will do your inner loop, given a slice of the appropriate type. The <code>sqlx</code> docs refer to caching results of reflection operations, so it may have some additional optimizations compared to writing one.</p> <p>Sounds like the immediate question you're actually asking is "how do I get something out of my <code>reflect.Value</code> that <code>rows.StructScan</code> will accept?" And the direct answer is <code>reflect.Interface(target)</code>; it should return an <code>interface{}</code> representing an <code>*Orange</code> you can pass directly to <code>StructScan</code> (no additional <code>&</code> operation needed). Then, I think <code>targets = reflect.Append(targets, target.Indirect())</code> will turn your <code>target</code> into a <code>reflect.Value</code> representing an <code>Orange</code> and append it to the slice. <code>targets.Interface()</code> should get you an <code>interface{}</code> representing an <code>[]Orange</code> that <code>json.Marshal</code> understands. I say all these 'should's and 'I think's because I haven't tried that route.</p> <p>Reflection, in general, is verbose and slow. Sometimes it's the best or only way to get something done, but it's often worth looking for a way to get your task done without it when you can.</p> <p>So, if it works in your app, you can also convert <code>Rows</code> straight to JSON, without going through intermediate structs. Here's a sample program (requires <code>sqlite3</code> of course) that turns <code>sql.Rows</code> into <code>map[string]string</code> and then into JSON. (Note it doesn't try to handle <code>NULL</code>, represent numbers as JSON numbers, or generally handle anything that won't fit in a <code>map[string]string</code>.)</p> <pre><code>package main import ( _ "code.google.com/p/go-sqlite/go1/sqlite3" "database/sql" "encoding/json" "os" ) func main() { db, err := sql.Open("sqlite3", "foo") if err != nil { panic(err) } tryQuery := func(query string, args ...interface{}) *sql.Rows { rows, err := db.Query(query, args...) if err != nil { panic(err) } return rows } tryQuery("drop table if exists t") tryQuery("create table t(i integer, j integer)") tryQuery("insert into t values(?, ?)", 1, 2) tryQuery("insert into t values(?, ?)", 3, 1) // now query and serialize rows := tryQuery("select * from t") names, err := rows.Columns() if err != nil { panic(err) } // vals stores the values from one row vals := make([]interface{}, 0, len(names)) for _, _ = range names { vals = append(vals, new(string)) } // rowMaps stores all rows rowMaps := make([]map[string]string, 0) for rows.Next() { rows.Scan(vals...) // now make value list into name=>value map currRow := make(map[string]string) for i, name := range names { currRow[name] = *(vals[i].(*string)) } // accumulating rowMaps is the easy way out rowMaps = append(rowMaps, currRow) } json, err := json.Marshal(rowMaps) if err != nil { panic(err) } os.Stdout.Write(json) } </code></pre> <p>In theory, you could build this to do fewer allocations by not reusing the same <code>rowMap</code> each time and using a <code>json.Encoder</code> to append each row's JSON to a buffer. You could go a step further and not use a <code>rowMap</code> at all, just the lists of names and values. I should say I haven't compared the speed against a <code>reflect</code>-based approach, though I know <code>reflect</code> is slow enough it might be worth comparing them if you can put up with either strategy.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload