将数据导入后灰色环境并导出到 CSV 格式

Question

我正在尝试使用postgresql（其中数据库位于远程主机）进行csv导入和导出数据。通常我会使用命令来做，但我需要能够通过Go来做到这一点，在那里我无法访问shell或没有安装psql的系统。psql\copy <table> from <local path> ...\copy <table> to <local path> ...

数据本身应该非常轻（可能<2 MB的数据），因此我尽量不实现任何跟踪表中列的结构/模式。导入数据库时，我想用库/代码来推断表的架构，并将数据推送到表中。

关于如何实现这一点的任何建议？我不确定是否有任何Go或或允许这样做，而无法指定列。对此有什么建议吗？database/sqlpgxpq

编辑：

我最终使用 https://github.com/joho/sqltocsv 进行数据库导出，这非常简单，我不必定义任何模式/结构。

我没有代码，但我尝试并意识到我需要为它定义一些结构/模式。gorm

Answer 1

查看此页面：&nbsp;https://github.com/chop-dbhi/sql-importer自动创建表唯一性，而不是空检测。支持宽度超过 1600 列的 CSV 文件（帖子限制）

Answer 2

我找到了用包装来做这件事的方法（多亏了@Gustavo川本的建议）。这是我的导入和导出：pgxpackage mainimport (&nbsp; &nbsp; "fmt"&nbsp; &nbsp; "os"&nbsp; &nbsp; "github.com/jackc/pgx")func main() {&nbsp; &nbsp; pgxConConfig := pgx.ConnConfig{&nbsp; &nbsp; &nbsp; &nbsp; Port:&nbsp; &nbsp; &nbsp;5432,&nbsp; &nbsp; &nbsp; &nbsp; Host:&nbsp; &nbsp; &nbsp;"remote_host",&nbsp; &nbsp; &nbsp; &nbsp; Database: "db_name",&nbsp; &nbsp; &nbsp; &nbsp; User:&nbsp; &nbsp; &nbsp;"my_user",&nbsp; &nbsp; &nbsp; &nbsp; Password: "my_password",&nbsp; &nbsp; }&nbsp; &nbsp; conn, err := pgx.Connect(pgxConConfig)&nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; panic(err)&nbsp; &nbsp; }&nbsp; &nbsp; defer conn.Close()&nbsp; &nbsp; tables := []string{"table1", "table2", "table3",}&nbsp; &nbsp; import_dir := "/dir_to_import_from"&nbsp; &nbsp; export_dir := "/dir_to_export_to"&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; for _, t := range tables {&nbsp; &nbsp; &nbsp; &nbsp; f, err := os.OpenFile(fmt.Sprintf("%s/table_%s.csv", import_dir, t), os.O_RDONLY, 0777)&nbsp; &nbsp; &nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; f.Close()&nbsp; &nbsp; &nbsp; &nbsp; err = importer(conn, f, t)&nbsp; &nbsp; &nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; break&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; fmt.Println("&nbsp; Done with import and doing export")&nbsp; &nbsp; &nbsp; &nbsp; ef, err := os.OpenFile(fmt.Sprintf("%s/table_%s.csv", export_dir, t), os.O_CREATE|os.O_WRONLY, 0777)&nbsp; &nbsp; &nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fmt.Println("error opening file:", err)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; ef.Close()&nbsp; &nbsp; &nbsp; &nbsp; err = exporter(conn, ef, t)&nbsp; &nbsp; &nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; break&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}func importer(conn *pgx.Conn, f *os.File, table string) error {&nbsp; &nbsp; res, err := conn.CopyFromReader(f, fmt.Sprintf("COPY %s FROM STDIN DELIMITER '|' CSV HEADER", table))&nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; return err&nbsp; &nbsp; }&nbsp; &nbsp; fmt.Println("==> import rows affected:", res.RowsAffected())&nbsp; &nbsp; return nil}func exporter(conn *pgx.Conn, f *os.File, table string) error {&nbsp; &nbsp; res, err := conn.CopyToWriter(f, fmt.Sprintf("COPY %s TO STDOUT DELIMITER '|' CSV HEADER", table))&nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; return fmt.Errorf("error exporting file: %+v", err)&nbsp; &nbsp; }&nbsp; &nbsp; fmt.Println("==> export rows affected:", res.RowsAffected())&nbsp; &nbsp; return nil}