将查询参数添加到PostgreSQL查询时出现错误。

huangapple
go评论118阅读模式
英文:

Error when add a query parameter to postgres query

问题

当我写下这段代码时：

  1. err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=1").
  2.     Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)

一切都正常工作。但是我希望它不仅仅获取id=1的页面，而是可以动态地获取。

所以我写下了：

  1. err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=?", pageID).
  2.     Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)

但是我得到了一个错误：

  1. GolangdProjects go run test.go
  2. 1
  3. 2017/04/13 11:29:57 Couldn't get page: 1
  4. exit status 1

完整的代码如下：

  1. package main
  3. import (
  4.   "database/sql"
  5.   "fmt"
  6.   _ "github.com/lib/pq"
  7.   "github.com/gorilla/mux"
  8.   "log"
  9.   "net/http"
  10. )
  12. const (
  13.   DBHost = "localhost"
  14.   DBPort = ":5432"
  15.   DBUser = "nirgalon"
  16.   DBPass = ""
  17.   DBDbase = "cms"
  18.   PORT = ":8080"
  19. )
  21. var database *sql.DB
  23. type Page struct {
  24.   Title string
  25.   Content string
  26.   Date string
  27. }
  29. func ServePage(w http.ResponseWriter, r *http.Request) {
  30.   vars := mux.Vars(r)
  31.   pageID := vars["id"]
  32.   thisPage := Page{}
  33.   fmt.Println(pageID)
  34.   err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=1").Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)
  35.   if err != nil {
  36.     log.Fatal("Couldn't get page: " + pageID)
  37.     log.Fatal(err.Error)
  38.   }
  39.   html := `<html><head><title>` + thisPage.Title + `</title></head><body><h1>` + thisPage.Title + `</h1><div>` + thisPage.Content + `</div></body></html>`
  40.   fmt.Fprintln(w, html)
  41. }
  43. func main() {
  44.   db, err := sql.Open("postgres", "user=nirgalon dbname=cms sslmode=disable")
  45.   if err != nil {
  46.     log.Println("Couldn't connnect to db")
  47.     log.Fatal(err)
  48.   }
  49.   database = db
  51.   routes := mux.NewRouter()
  52.   routes.HandleFunc("/page/{id:[0-9]+}", ServePage)
  53.   http.Handle("/", routes)
  54.   http.ListenAndServe(PORT, nil)
  55. }
英文:

When I write the code:

  1. err := database.QueryRow(&quot;SELECT page_title,page_content,page_date FROM pages WHERE id=1&quot;).
  2. Scan(&amp;thisPage.Title, &amp;thisPage.Content, &amp;thisPage.Date)

Everything works fine. But I want it to not just get the page with id=1, but to be dynamic.

So I write:

  1. err := database.QueryRow(&quot;SELECT page_title,page_content,page_date FROM pages WHERE id=?&quot;, pageID).
  2. Scan(&amp;thisPage.Title, &amp;thisPage.Content, &amp;thisPage.Date)

But I get an error:

  1. GolangdProjects go run test.go  
  2. 1  
  3. 2017/04/13 11:29:57 Couldn&#39;t get page: 1  
  4. exit status 1

The full code:

  1. package main
  2. import (
  3. &quot;database/sql&quot;
  4. &quot;fmt&quot;
  5. _ &quot;github.com/lib/pq&quot;
  6. &quot;github.com/gorilla/mux&quot;
  7. &quot;log&quot;
  8. &quot;net/http&quot;
  9. )
  10. const (
  11. DBHost = &quot;localhost&quot;
  12. DBPort = &quot;:5432&quot;
  13. DBUser = &quot;nirgalon&quot;
  14. DBPass = &quot;&quot;
  15. DBDbase = &quot;cms&quot;
  16. PORT = &quot;:8080&quot;
  17. )
  18. var database *sql.DB
  19. type Page struct {
  20. Title string
  21. Content string
  22. Date string
  23. }
  24. func ServePage(w http.ResponseWriter, r *http.Request) {
  25. vars := mux.Vars(r)
  26. pageID := vars[&quot;id&quot;]
  27. thisPage := Page{}
  28. fmt.Println(pageID)
  29. err := database.QueryRow(&quot;SELECT page_title,page_content,page_date FROM pages WHERE id=1&quot;).Scan(&amp;thisPage.Title, &amp;thisPage.Content, &amp;thisPage.Date)
  30. if err != nil {
  31. log.Fatal(&quot;Couldn&#39;t get page: &quot; + pageID)
  32. log.Fatal(err.Error)
  33. }
  34. html := `&lt;html&gt;&lt;head&gt;&lt;title&gt;` + thisPage.Title + `&lt;/title&gt;&lt;/head&gt;&lt;body&gt;&lt;h1&gt;` + thisPage.Title + `&lt;/h1&gt;&lt;div&gt;` + thisPage.Content + `&lt;/div&gt;&lt;/body&gt;&lt;/html&gt;`
  35. fmt.Fprintln(w, html)
  36. }
  37. func main() {
  38. db, err := sql.Open(&quot;postgres&quot;, &quot;user=nirgalon dbname=cms sslmode=disable&quot;)
  39. if err != nil {
  40. log.Println(&quot;Couldn&#39;t connnect to db&quot;)
  41. log.Fatal(err)
  42. }
  43. database = db
  44. routes := mux.NewRouter()
  45. routes.HandleFunc(&quot;/page/{id:[0-9]+}&quot;, ServePage)
  46. http.Handle(&quot;/&quot;, routes)
  47. http.ListenAndServe(PORT, nil)
  48. }

答案1

得分: 1

psql驱动程序在参数中使用$1、$2等：

  1. database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id = $1", pageID)
英文:

psql driver uses $1, $2, etc for params:

  1. database.QueryRow(&quot;SELECT page_title,page_content,page_date FROM pages WHERE id = $1&quot;, pageID)

答案2

得分: 0

QueryRow接受类型为interface{}的可变参数。可能是pageID被插入为字符串（由mux.Vars返回），导致查询格式不正确，如下所示：

  1. "SELECT page_title,page_content,page_date FROM pages WHERE id='1'"

尝试将pageID转换为整数：

  1. id := vars["id"]
  2. pageID := strconv.Atoi(id)
英文:

QueryRow takes var args of type interface{}. It could be that pageID is being interpolated as a string (as returned by mux.Vars), resulting in a query that's incorrectly formatted as:

  1. &quot;SELECT page_title,page_content,page_date FROM pages WHERE id=&#39;1&#39;&quot;

Try converting pageID to int:

  1. id := vars[&quot;id&quot;]
  2. pageID := strconv.Atoi(id)

huangapple
  • 本文由 发表于 2017年4月13日 16:38:33
  • 转载请务必保留本文链接：https://go.coder-hub.com/43387383.html
  • go
  • postgresql
  • pq
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定