使用database/sql在golang中从mysql检索记录。

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

Retrieve a record from mysql in golang using database/sql

问题

我正在尝试从MySQL表中检索记录并将其转换为JSON。但是我遇到了一个错误，错误信息是：“.\main.go:67: :=左侧没有新变量”。我是Golang的新手，请帮助我解决这个错误！以下是我的代码：

  1. func GetUser(w http.ResponseWriter, r *http.Request) {
  3.   urlParams := mux.Vars(r)
  4.   id := urlParams["id"]
  5.   ReadUser := User{}
  6.   con, err := sql.Open("mysql", "root:YES@/social_network?charset=utf8")
  8.   err := con.QueryRow("select * from users where user_id=?", id).Scan(&ReadUser.ID, &ReadUser.Name, &ReadUser.First, &ReadUser.Last, &ReadUser.Email)
  9.   switch {
  10.       case err == sql.ErrNoRows:
  11.               fmt.Fprintf(w, "No such user")
  12.       case err != nil:
  13.               log.Fatal(err)
  14.       default:
  15.         output, _ := json.Marshal(ReadUser)
  16.         fmt.Fprintf(w, string(output))
  17.   }
  18. }
  20. func main() {
  22.   gorillaRoute := mux.NewRouter()
  23.   gorillaRoute.HandleFunc("/api/user/create", CreateUser)
  24.   gorillaRoute.HandleFunc("/api/user/read/:id", GetUser)
  25.   http.Handle("/", gorillaRoute)
  26.   http.ListenAndServe(":8080", nil)
  27. }

这是我的新代码，但仍然无法从数据库表中获取记录，请帮助我找出错误或我做错了什么？

  1. package main
  3. import (
  4. 	"database/sql"
  5. 	"encoding/json"
  6. 	"fmt"
  7. 	"log"
  8. 	"net/http"
  10. 	_ "github.com/go-sql-driver/mysql"
  11. 	"github.com/gorilla/mux"
  12. )
  14. type API struct {
  15. 	Message string `json:"message"`
  16. }
  17. type User struct {
  18. 	ID    int    `json:"id"`
  19. 	Name  string `json:"username"`
  20. 	Email string `json:"email"`
  21. 	First string `json:"first"`
  22. 	Last  string `json:"last"`
  23. }
  25. func Hello(w http.ResponseWriter, r *http.Request) {
  27. 	// urlParams := mux.Vars(r)
  28. 	// name := urlParams["user"]
  29. 	HelloMessage := "User Creation page"
  31. 	message := API{HelloMessage}
  32. 	output, err := json.Marshal(message)
  34. 	if err != nil {
  35. 		fmt.Println("Something went wrong!")
  36. 	}
  38. 	fmt.Fprintf(w, string(output))
  40. }
  42. //POST A USER INTO DB
  43. func CreateUser(w http.ResponseWriter, r *http.Request) {
  44. 	NewUser := User{}
  45. 	NewUser.Name = r.FormValue("user")
  46. 	NewUser.Email = r.FormValue("email")
  47. 	NewUser.First = r.FormValue("first")
  48. 	NewUser.Last = r.FormValue("last")
  49. 	output, err := json.Marshal(NewUser)
  50. 	fmt.Println(string(output))
  51. 	if err != nil {
  52. 		fmt.Println("Something went wrong!")
  53. 	}
  54. 	con, err := sql.Open("mysql", "root:YES@/social_network?charset=utf8")
  55. 	sqlQuery := "INSERT INTO users set user_nickname='" + NewUser.Name + "', user_first='" + NewUser.First + "', user_last='" + NewUser.Last + "', user_email='" + NewUser.Email + "'"
  56. 	q, err := con.Exec(sqlQuery)
  57. 	if err != nil {
  58. 		fmt.Println(err)
  59. 	}
  60. 	fmt.Println(q)
  61. }
  63. //GET USERS FROM DB
  64. func GetUsers(w http.ResponseWriter, r *http.Request) {
  66. 	db, err := sql.Open("mysql", "root:YES@/social_network?charset=utf8")
  67. 	if err != nil {
  68. 		panic(err)
  69. 	}
  70. 	err = db.Ping()
  71. 	if err != nil {
  72. 		panic(err)
  73. 	}
  74. 	defer db.Close()
  75. 	rows, err := db.Query("select * from users ")
  76. 	if err != nil {
  77. 		log.Fatal(err)
  78. 	}
  79. 	defer rows.Close()
  80. 	var rowBuf, _ = rows.Columns()
  81. 	var cols = make([]string, len(rowBuf))
  82. 	copy(cols, rowBuf)
  83. 	fmt.Println(rowBuf)
  84. 	var vals = make([]interface{}, len(rowBuf))
  85. 	for i, _ := range rowBuf {
  86. 		vals[i] = &rowBuf[i]
  87. 	}
  88. 	for rows.Next() {
  89. 		err := rows.Scan(vals...)
  90. 		if err != nil {
  91. 			log.Fatal(err)
  92. 		}
  93. 		var m = map[string]interface{}{}
  94. 		for i, col := range cols {
  95. 			m[col] = vals[i]
  96. 		}
  97. 		obj, _ := json.Marshal(m)
  98. 		//
  99. 		fmt.Fprintf(w, string(obj))
  100. 	}
  101. 	err = rows.Err()
  102. 	if err != nil {
  103. 		log.Fatal(err)
  104. 	}
  105. }
  106. func GetUser(w http.ResponseWriter, r *http.Request) {
  107. 	urlParams := mux.Vars(r)
  108. 	id := urlParams["id"]
  109. 	ReadUser := User{}
  110. 	db, err := sql.Open("mysql", "root:YES@/social_network?charset=utf8")
  111. 	stmt := db.QueryRow("select * from users where id = ?", id)
  112. 	if err != nil {
  113. 		log.Fatal(err)
  114. 	}
  116. 	err = stmt.Scan(&ReadUser.ID, &ReadUser.Name, &ReadUser.First, &ReadUser.Last, &ReadUser.Email)
  117. 	if err != nil {
  118. 		log.Fatal(err)
  119. 	}
  121. 	result, err := json.Marshal(ReadUser)
  122. 	fmt.Fprintf(w, string(result))
  123. }
  124. func main() {
  126. 	gorillaRoute := mux.NewRouter()
  127. 	gorillaRoute.HandleFunc("/api/user/create", CreateUser)
  128. 	gorillaRoute.HandleFunc("/api/user/read", GetUsers)
  129. 	gorillaRoute.HandleFunc("/api/user/:id", GetUser)
  130. 	http.Handle("/", gorillaRoute)
  131. 	http.ListenAndServe(":8080", nil)
  132. }

希望这可以帮助你找到错误并解决问题！

英文:

I'm trying to retrieve a record from mysql table and marshal it to json.
But i gotted the error that says : ".\main.go:67: no new variables on left sife of :="
I'm new in Golang Plz! help me to solve out this error!
My Code is :

  1. func GetUser(w http.ResponseWriter, r *http.Request) {
  2. urlParams   := mux.Vars(r)
  3. id       := urlParams["id"]
  4. ReadUser := User{}
  5. con, err := sql.Open("mysql", "root:YES@/social_network?charset=utf8")
  6. err := con.QueryRow("select * from users where user_id=?",id).Scan(&ReadUser.ID, &ReadUser.Name, &ReadUser.First, &ReadUser.Last, &ReadUser.Email )
  7. switch {
  8. case err == sql.ErrNoRows:
  9. fmt.Fprintf(w,"No such user")
  10. case err != nil:
  11. log.Fatal(err)
  12. default:
  13. output, _ := json.Marshal(ReadUser)
  14. fmt.Fprintf(w,string(output))
  15. }
  16. }

and Routes in main:

  1. func main() {
  2. gorillaRoute := mux.NewRouter()
  3. gorillaRoute.HandleFunc("/api/user/create", CreateUser)
  4. gorillaRoute.HandleFunc("/api/user/read/:id", GetUser)
  5. http.Handle("/", gorillaRoute)
  6. http.ListenAndServe(":8080", nil)
  7. }

Here is my new Code , but again not able to get record from database table, plz help me to figure out the error OR what things i do wrong?
package main

  1. import (
  2. "database/sql"
  3. "encoding/json"
  4. "fmt"
  5. "log"
  6. "net/http"
  7. _ "github.com/go-sql-driver/mysql"
  8. "github.com/gorilla/mux"
  9. )
  10. type API struct {
  11. Message string "json:message"
  12. }
  13. type User struct {
  14. ID    int    "json:id"
  15. Name  string "json:username"
  16. Email string "json:email"
  17. First string "json:first"
  18. Last  string "json:last"
  19. }
  20. func Hello(w http.ResponseWriter, r *http.Request) {
  21. // urlParams := mux.Vars(r)
  22. // name := urlParams["user"]
  23. HelloMessage := "User Creation page"
  24. message := API{HelloMessage}
  25. output, err := json.Marshal(message)
  26. if err != nil {
  27. fmt.Println("Something went wrong!")
  28. }
  29. fmt.Fprintf(w, string(output))
  30. }
  31. //POST A USER INTO DB
  32. func CreateUser(w http.ResponseWriter, r *http.Request) {
  33. NewUser := User{}
  34. NewUser.Name = r.FormValue("user")
  35. NewUser.Email = r.FormValue("email")
  36. NewUser.First = r.FormValue("first")
  37. NewUser.Last = r.FormValue("last")
  38. output, err := json.Marshal(NewUser)
  39. fmt.Println(string(output))
  40. if err != nil {
  41. fmt.Println("Something went wrong!")
  42. }
  43. con, err := sql.Open("mysql", "root:YES@/social_network?charset=utf8")
  44. sqlQuery := "INSERT INTO users set user_nickname='" + NewUser.Name + "', user_first='" + NewUser.First + "', user_last='" + NewUser.Last + "', user_email='" + NewUser.Email + "'"
  45. q, err := con.Exec(sqlQuery)
  46. if err != nil {
  47. fmt.Println(err)
  48. }
  49. fmt.Println(q)
  50. }
  51. //GET USERS FROM DB
  52. func GetUsers(w http.ResponseWriter, r *http.Request) {
  53. db, err := sql.Open("mysql", "root:YES@/social_network?charset=utf8")
  54. if err != nil {
  55. panic(err)
  56. }
  57. err = db.Ping()
  58. if err != nil {
  59. panic(err)
  60. }
  61. defer db.Close()
  62. rows, err := db.Query("select * from users ")
  63. if err != nil {
  64. log.Fatal(err)
  65. }
  66. defer rows.Close()
  67. var rowBuf, _ = rows.Columns()
  68. var cols = make([]string, len(rowBuf))
  69. copy(cols, rowBuf)
  70. fmt.Println(rowBuf)
  71. var vals = make([]interface{}, len(rowBuf))
  72. for i, _ := range rowBuf {
  73. vals[i] = &rowBuf[i]
  74. }
  75. for rows.Next() {
  76. err := rows.Scan(vals...)
  77. if err != nil {
  78. log.Fatal(err)
  79. }
  80. var m = map[string]interface{}{}
  81. for i, col := range cols {
  82. m[col] = vals[i]
  83. }
  84. obj, _ := json.Marshal(m)
  85. //
  86. fmt.Fprintf(w, string(obj))
  87. }
  88. err = rows.Err()
  89. if err != nil {
  90. log.Fatal(err)
  91. }
  92. }
  93. func GetUser(w http.ResponseWriter, r *http.Request) {
  94. urlParams := mux.Vars(r)
  95. id := urlParams["id"]
  96. ReadUser := User{}
  97. db, err := sql.Open("mysql", "root:YES@/social_network?charset=utf8")
  98. stmt := db.QueryRow("select * from users where id = ?", id)
  99. if err != nil {
  100. log.Fatal(err)
  101. }
  102. err = stmt.Scan(&ReadUser.ID, &ReadUser.Name, &ReadUser.First, &ReadUser.Last, &ReadUser.Email)
  103. if err != nil {
  104. log.Fatal(err)
  105. }
  106. result, err := json.Marshal(ReadUser)
  107. fmt.Fprintf(w, string(result))
  108. }
  109. func main() {
  110. gorillaRoute := mux.NewRouter()
  111. gorillaRoute.HandleFunc("/api/user/create", CreateUser)
  112. gorillaRoute.HandleFunc("/api/user/read", GetUsers)
  113. gorillaRoute.HandleFunc("/api/user/:id", GetUser)
  114. http.Handle("/", gorillaRoute)
  115. http.ListenAndServe(":8080", nil)
  116. }

答案1

得分: 2

将err :=中的冒号:移除，因为你正在给现有变量赋新值。

英文:

Remove the colon : from err := as you are assigning a new value to existing variable.

huangapple
  • 本文由 发表于 2015年6月11日 15:45:37
  • 转载请务必保留本文链接：https://go.coder-hub.com/30774681.html
  • database
  • go
  • mysql
匿名

发表评论

匿名网友

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

确定