postgresql 如何使用 goLang 执行 sql 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38998267/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute a sql file using goLang
提问by codec
I have .sql file which has lots of database creation , deletion , population stuff. Is it possible to have a go function which can excute a sql file. I am using postgres as my database and using lib/pq driver for all database transactions. But I am open to any library for executing this sql file in my golang project.
我有 .sql 文件，其中包含大量的数据库创建、删除、填充内容。是否有可能有一个可以执行 sql 文件的 go 函数。我使用 postgres 作为我的数据库，并为所有数据库事务使用 lib/pq 驱动程序。但是我对在我的 golang 项目中执行这个 sql 文件的任何库都持开放态度。
回答by carusyte
I found dotsql in search of similar demand. You can load named sql statements/prepare statements from a specific file and execute.
我找到了 dotsql 来寻找类似的需求。您可以从特定文件加载命名的 sql 语句/准备语句并执行。
// Get a database handle
db, err := sql.Open("sqlite3", ":memory:")
// Loads queries from file
dot, err := dotsql.LoadFromFile("queries.sql")
// Run queries
res, err := dot.Exec(db, "create-users-table")
res, err := dot.Exec(db, "create-user", "User Name", "[email protected]")
rows, err := dot.Query(db, "find-users-by-email", "[email protected]")
row, err := dot.QueryRow(db, "find-one-user-by-email", "[email protected]")
stmt, err := dot.Prepare(db, "drop-users-table")
result, err := stmt.Exec()
回答by Mayank Patel
You can use os/execpackage of standard library. No database driver is required. Code would look something like this for postgreSQL:
您可以使用os/exec标准库包。不需要数据库驱动程序。对于 postgreSQL，代码看起来像这样：
cmd := exec.Command("psql", "-U", psqlUser, "-h", psqlHost, "-d", psqlDBName, "-a", "-f", sqlFilePath)
var out, stderr bytes.Buffer
cmd.Stdout = &out
cmd.Stderr = &stderr
err := cmd.Run()
if err != nil {
log.Fatalf("Error executing query. Command Output: %+v\n: %+v, %v", out.String(), stderr.String(), err)
}
回答by Pavel Kazhevets
You can just split file into separate requests and execute them one-by-one:
您可以将文件拆分为单独的请求并一一执行：
file, err := ioutil.ReadAll("/path/to/file")
if err != nil {
// handle error
}
requests := strings.Split(string(file), ";")
for _, request := range requests {
result, err := db.Exec(request)
// do whatever you need with result and error
}
回答by kovac
It's too much trouble if you are going to execute it using a command line. You have to deal with issues like setting your passwords, making sure the path variables are properly set, etc. I think the bets way is to use the database driver and just call it using Go.
如果你要使用命令行来执行它，那就太麻烦了。您必须处理诸如设置密码、确保正确设置路径变量等问题。我认为最好的方法是使用数据库驱动程序并使用 Go 调用它。
In the following example, I'm using pgx implementation of sql driverfor Postgres. You can do it with any driver implementation of your choice.
在以下示例中，我使用了 Postgres的 sql 驱动程序的 pgx 实现。您可以使用您选择的任何驱动程序实现来实现。
path := filepath.Join("path", "to", "script.sql")
c, ioErr := ioutil.Readfile(path)
if ioErr != nil {
// handle error.
}
sql := string(c)
_, err := *pgx.Conn.Exec(sql)
if err != nil {
// handle error.
}
Explanation:
解释：
- Get the path to your sql script in a os agnostic way.
- Read the content of the file to string.
- Execute the statements in the file using the sql driver.
- 以与操作系统无关的方式获取 sql 脚本的路径。
- 将文件内容读取为字符串。
- 使用sql驱动执行文件中的语句。
