如何从STDIN中将行导入Postgresql? [英] How do I import rows to Postgresql from STDIN?
问题描述
在Python中,我有以下内容,可以在不使用文件的情况下将行批量加载到Postgresql:
In Python I have the following that will bulk-load rows to Postgresql without using a file:
import csv
import subprocess
mylist, keys = [{'name': 'fred'}, {'name': 'mary'}], ['name']
p = subprocess.Popen(['psql', 'mydb', '-U', 'openupitsme', '-h', 'my.ip.address', '--no-password', '-c',
'\COPY tester(%s) FROM STDIN (FORMAT CSV)' % ', '.join(keys),
'--set=ON_ERROR_STOP=false'
], stdin=subprocess.PIPE
)
for d in mylist:
dict_writer = csv.DictWriter(p.stdin, keys, quoting=csv.QUOTE_MINIMAL)
dict_writer.writerow(d)
p.stdin.close()
我试图在Go中完成相同的操作。我目前正在将行写入文件,然后导入它们,然后删除该文件。我想像我在Python中那样从STDIN导入行。我有:
I am trying to accomplish the same in Go. I am currently writing the rows to a file then importing them and then deleting that file. I'd like to import the rows from STDIN like I do in Python. I have:
package main
import (
"database/sql"
"log"
"os"
"os/exec"
_ "github.com/lib/pq"
)
var (
err error
db *sql.DB
)
func main() {
var err error
fh := "/path/to/my/file.txt"
f, err := os.Create(fh)
if err != nil {
panic(err)
}
defer f.Close()
defer os.Remove(fh)
rows := []string{"fred", "mary"}
for _, n := range rows {
_, err = f.WriteString(n + "\n")
if err != nil {
panic(err)
}
}
// dump to postgresql
c := exec.Command("psql", "mydb", "-U", "openupitsme", "-h", "my.ip.address", "--no-password",
"-c", `\COPY tester(customer) FROM `+fh)
if out, err := c.CombinedOutput(); err != nil {
log.Println(string(out), err)
}
}
编辑:
更进一步,但这不是插入记录:
A bit further along but this is not inserting records:
keys := []string{"link", "domain"}
records := [][]string{
{"first_name", "last_name"},
{"Rob", "Pike"},
{"Ken", "Thompson"},
{"Robert", "Griesemer"},
}
cmd := exec.Command("psql")
stdin, err := cmd.StdinPipe()
if err != nil {
log.Println(err)
}
stdout, err := cmd.StdoutPipe()
if err != nil {
log.Println(err)
}
if err := cmd.Start(); err != nil {
log.Println(err)
}
go func() {
_, err = io.WriteString(stdin, "search -U meyo -h 1.2.3.4 -p 1111 --no-password -c ")
if err != nil {
log.Println(err)
}
_, err := io.WriteString(stdin, fmt.Sprintf("COPY links(%s) FROM STDIN (FORMAT CSV)", strings.Join(keys, ",")))
if err != nil {
log.Println(err)
}
w := csv.NewWriter(stdin)
if err := w.WriteAll(records); err != nil {
log.Fatalln("error writing record to csv:", err)
}
w.Flush()
if err := w.Error(); err != nil {
log.Fatal(err)
}
if err != nil {
log.Println(err)
}
stdin.Close()
}()
done := make(chan bool)
go func() {
_, err := io.Copy(os.Stdout, stdout)
if err != nil {
log.Fatal(err)
}
stdout.Close()
done <- true
}()
<-done
if err := cmd.Wait(); err != nil {
log.Println(err, cmd.Args, stdout)
}
没有记录被插入,我得到一个无用的错误:
No records are inserted and I get a non-helpful error:
exit status 2
推荐答案
下面的代码应该指向你想要去的方向: p>
The following code should point you in the direction you want to go:
package main
import (
"fmt"
"log"
"os"
"os/exec"
"strings"
)
func main() {
keys := []string{"customer"}
sqlCmd := fmt.Sprintf("COPY tester(%s) FROM STDIN (FORMAT CSV)", strings.Join(keys, ","))
cmd := exec.Command("psql", "<dbname>", "-U", "<username>", "-h", "<host_ip>", "--no-password", "-c", sqlCmd)
cmd.Stdin = os.Stdin
output, _ := cmd.CombinedOutput()
log.Println(string(output))
}
如果这些键需要动态显示,您可以从 os.Args
。
If the keys need to be dynamic you can harvest them from os.Args
.
请注意,如果您打算使用psql命令,则不需要导入数据库/ sql或LIB / PQ。如果您对使用lib / pq感兴趣,请查看 Bulk Imports 在lib / pq文档中。
Please note that if you plan to use the psql command then you don't need to import database/sql or lib/pq. If you are interested in using lib/pq then have a look at Bulk Imports in the lib/pq documentation.
这篇关于如何从STDIN中将行导入Postgresql?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!