通过命令行插入SQL语句，无需重新打开与远程数据库的连接 [英] Insert SQL statements via command line without reopening connection to remote database
问题描述
我有大量的数据文件要处理并存储在远程数据库中。
I have a large amount of data files to process and to be stored in the remote database. Each line of a data file represents a row in the database, but must be formatted before inserting into the database.
我的第一个解决方案是通过编写bash脚本来处理数据文件并生成SQL数据文件，然后将转储SQL文件导入数据库。这个解决方案似乎太慢了，你可以看到涉及到创建中间SQL文件的额外步骤。
My first solution was to process data files by writing bash scripts and produce SQL data files, and then import the dump SQL files into the database. This solution seems to be too slow and as you can see involves an extra step of creating intermediary SQL file.
我的第二个解决方案是编写bash脚本，的数据文件，创建并且 INSERT INTO ... 语句并将SQL语句发送到远程数据库：
My second solution was to write bash scripts that while processing each line of the data file, creates and INSERT INTO ... statement and sends the SQL statement to the remote database:
echo sql_statement | psql -h remote_server -U用户名-d数据库
不创建SQL文件。然而，这个解决方案有一个主要问题，我正在搜索一个建议：
每次我必须重新连接到远程数据库以插入一行。
i.e. does not create SQL file. This solution, however, has one major issue that I am searching an advice on:
Each time I have to reconnect to the remote database to insert one single row.
有没有办法连接到远程数据库，保持连接，然后管道或发送插入SQL语句，而不创建一个巨大的SQL文件？
Is there a way to connect to the remote database, stay connected and then "pipe" or "send" the insert-SQL-statement without creating a huge SQL file?
推荐答案
回答您的实际问题
strong>是。您可以使用命名管道而不是创建文件。考虑下面的演示。
Answer to your actual question
Yes. You can use a named pipe instead of creating a file. Consider the following demo.
在我的数据库事件中创建一个模式 x 测试：
Create a schema x in my database event for testing:
-- DROP SCHEMA x CASCADE;
CREATE SCHEMA x;
CREATE TABLE x.x (id int, a text);
从shell中创建命名管道（fifo），如下所示：
Create a named pipe (fifo) from the shell like this:
postgres@db:~$ mkfifo --mode=0666 /tmp/myPipe
1）使用命名管道调用SQL命令 COPY 在服务器上：
Either 1) call the SQL command COPY using a named pipe on the server:
postgres@db:~$ psql event -p5433 -c "COPY x.x FROM '/tmp/myPipe'"
这将获得独占锁在数据库中的表 xx 上。连接保持打开，直到fifo获取数据。小心不要把这个打开太久！您可以在填写管道后调用此，以最小化阻止时间。您可以选择事件的顺序。一旦两个进程绑定到管道，该命令就会执行。第一个等待第二个。
This will acquire an exclusive lock on the table x.x in the database. The connection stays open until the fifo gets data. Be careful not to leave this open for too long! You can call this after you have filled the pipe to minimize blocking time. You can chose the sequence of events. The command executes as soon as two processes bind to the pipe. The first waits for the second.
或 2），您可以从客户端上的管道执行SQL：
Or 2) you can execute SQL from the pipe on the client:
postgres@db:~$ psql event -p5433 -f /tmp/myPipe
这更适合您的情况。此外，在执行SQL之前，没有表锁被锁定。
This is better suited for your case. Also, no table locks until SQL is executed in one piece.
Bash将被阻止。它正在等待对管道的输入。要从一个bash实例执行此操作，您可以将等待进程发送到后台。像这样：
Bash will appear blocked. It is waiting for input to the pipe. To do it all from one bash instance, you can send the waiting process to the background instead. Like this:
postgres@db:~$ psql event -p5433 -f /tmp/myPipe 2>&1 &
无论哪种方式，从相同的bash或不同的
演示 1）的三行：
postgres@db:~$ echo '1 foo' >> /tmp/myPipe; echo '2 bar' >> /tmp/myPipe; echo '3 baz' >> /tmp/myPipe;
（注意使用制表符作为分隔符或指示 COPY 使用 WITH DELIMITER'delimiter_character'接受不同的分隔符）
这将使用COPY命令触发待执行的psql执行并返回：
(Take care to use tabs as delimiters or instruct COPY to accept a different delimiter using WITH DELIMITER 'delimiter_character')
That will trigger the pending psql with the COPY command to execute and return:
COPY 3
演示 2）：
postgres@db:~$ (echo -n "INSERT INTO x.x VALUES (1,'foo')" >> /tmp/myPipe; echo -n ",(2,'bar')" >> /tmp/myPipe; echo ",(3,'baz')" >> /tmp/myPipe;)
INSERT 0 3
完成后删除命名管道：
postgres@db:~$ rm /tmp/myPipe
检查成功：
event=# select * from x.x;
id | a
----+-------------------
1 | foo
2 | bar
3 | baz
上述代码的实用链接
使用postgres读取压缩文件使用命名管道
命名管道简介
运行bash脚本的最佳做法背景
对于批量 INSERT ，您有比单独的 INSERT 。使用以下语法：
For bulk INSERT you have better solutions than a separate INSERT per row. Use this syntax variant:
INSERT INTO mytable (col1, col2, col3) VALUES
(1, 'foo', 'bar')
,(2, 'goo', 'gar')
,(3, 'hoo', 'har')
...
;
将您的语句写入文件并执行一个批处理 INSERT 像这样：
Write your statements to a file and do one mass INSERT like this:
psql -h remote_server -U username -d database -p 5432 -f my_insert_file.sql
（5432或db-cluster正在监听的任何端口）
my_insert_file.sql 可以保存多个SQL语句。事实上，通常的做法是恢复/部署整个数据库。请参阅手册 -f 参数，或在bash中： man psql 。
(5432 or whatever port the db-cluster is listening on)
my_insert_file.sql can hold multiple SQL statements. In fact, it's common practise to restore / deploy whole databases like that. Consult the manual about the -f parameter, or in bash: man psql.
或者，如果您可以将（压缩）文件传输到服务器，则可以使用 COPY 可以更快地插入（解压缩）数据。
Or, if you can transfer the (compressed) file to the server, you can use COPY to insert the (decompressed) data even faster.
您还可以进行部分或全部处理PostgreSQL。为此，您可以 COPY TO （或 INSERT INTO ）临时表，并使用纯SQL语句来准备，最后INSERT / UPDATE你的表。我做了很多。注意临时表存活并且与会话一起死。
You can also do some or all of the processing inside PostgreSQL. For that you can COPY TO (or INSERT INTO) a temporary table and use plain SQL statements to prepare and finally INSERT / UPDATE your tables. I do that a lot. Be aware that temporary tables live and die with the session.
您可以使用像 pgAdmin 这样的GUI来进行舒适的操作。在SQL编辑器窗口中的会话保持打开，直到您关闭窗口。 （因此，临时表存在，直到您关闭窗口。）
You could use a GUI like pgAdmin for comfortable handling. A session in an SQL Editor window remains open until you close the window. (Therefore, temporary tables live until you close the window.)
这篇关于通过命令行插入SQL语句，无需重新打开与远程数据库的连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
