英文:
What am I not getting about Go sql query with variables?
问题
我是你的中文翻译助手，以下是你要翻译的内容：
我刚开始学习Go语言，并且开始编写一些PostgreSQL查询，但是运气不太好。
我有一个包，里面只包含一些数据库查询。以下是我的代码。
main.go
package main
import (
"fmt"
)
func main() {
fmt.Println("Querying data")
myqueries.SelectAll("mytable")
}
myqueries.go
package myqueries
import (
"database/sql"
"fmt"
)
func SelectAll(table string) {
db, err := sql.Open("postgres","user=postgres dbname=mydb sslmode=disable")
if err != nil {
fmt.Println(err)
}
defer db.Close()
rows, err := db.Query("SELECT * FROM $1", table)
if err != nil {
fmt.Println(err)
} else {
PrintRows(rows)
}
}
func PrintRows(rows *sql.Rows) {
for rows.Next() {
var firstname string
var lastname string
err := rows.Scan(&firstname, &lastname)
if err != nil {
fmt.Println(err)
}
fmt.Println("first name | last name")
fmt.Println("%v | %v\n", firstname, lastname)
}
}
我得到的错误是pq: syntax error at or near "$1"，这个错误来自于myqueries.go文件中的db.Query。
我尝试了几种变化，但是还没有成功。希望能得到帮助。
英文:
I'm brand new to Go, and I've started working on some postgres queries, and I'm having very little luck.
I have a package that's just going to have some database queries in it. Here's my code.
main.go
package main
import (
"fmt"
)
func main() {
fmt.Println("Querying data")
myqueries.SelectAll("mytable")
}
myqueries.go
package myqueries
import (
"database/sql"
"fmt"
)
func SelectAll (table string) {
db, err := sql.Open("postgres","user=postgres dbname=mydb sslmode=disable")
if err != nil {
fmt.Println(err)
}
defer db.Close()
rows, err := db.Query("SELECT * FROM $1", table)
if err != nil {
fmt.Println(err)
} else {
PrintRows(rows)
}
}
func PrintRows(rows *sql.Rows) {
for rows.Next() {
var firstname string
var lastname string
err := rows.Scan(&firstname, &lastname)
if err != nil {
fmt.Println(err)
}
fmt.Println("first name | last name")
fmt.Println("%v | %v\n", firstname, lastname)
}
}
The error I get is pq: syntax error at or near "$1"
which is from myqueries.go file in the db.Query.
I've tried several variations of this, but nothing has worked yet. Any help is appreciated.
答案1
得分: 6
看起来你正在使用基于https://github.com/lib/pq的库，根据错误信息和它的文档所说：
pq使用Postgres原生的序数标记，如上所示
我从未听说过任何允许在除了值之外的任何地方使用参数化值的数据库引擎。我认为你需要使用字符串拼接来解决这个问题。我现在没有可用的Go编译器，但你可以尝试像这样的代码。因为你通过拼接插入了表名，所以你需要对其进行清理。pq.QuoteIdentifier应该能够帮助你。
func SelectAll(table string) {
db, err := sql.Open("postgres", "user=postgres dbname=mydb sslmode=disable")
if err != nil {
fmt.Println(err)
}
defer db.Close()
table = pq.QuoteIdentifier(table)
rows, err := db.Query(fmt.Sprintf("SELECT * FROM %v", table))
if err != nil {
fmt.Println(err)
} else {
PrintRows(rows)
}
}
编辑：感谢hobbs指出了pq.QuoteIdentifier。
英文:
It looks like you are using https://github.com/lib/pq based on the error message and it's docs say that
> pq uses the Postgres-native ordinal markers, as shown above
I've never known a database engine that allows the parameterized values in anything other than values. I think you are going to have to resort to string concatenation. I don't have a Go compiler available to me right now, but try something like this. Because you are inserting the table name by concatination, you need it sanitized. pq.QuoteIdentifier should be able to help with that.
func SelectAll (table string) {
db, err := sql.Open("postgres","user=postgres dbname=mydb sslmode=disable")
if err != nil {
fmt.Println(err)
}
defer db.Close()
table = pq.QuoteIdentifier(table)
rows, err := db.Query(fmt.Sprintf("SELECT * FROM %v", table))
if err != nil {
fmt.Println(err)
} else {
PrintRows(rows)
}
}
EDIT: Thanks to hobbs to pointing out pq.QuoteIdentifier
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论