如何使用 Squirrel 将 SQL 查询的结果存储在 CSV 文件中？

How can I store the result of a SQL query in a CSV file using Squirrel?

提问by alex

Version 3.0.3. It's a fairly large result-set, around 3 million rows.

版本 3.0.3。这是一个相当大的结果集，大约有 300 万行。

回答by Rod

Martin pretty much has this right.

马丁几乎有这个权利。

The TL/DR version is that you need the "SQLScripts" plugin (which is one of the "standard' plugins), and then you can select these menu options: Session> Scripts> Store Result of SQL in File

TL/DR 版本是你需要“SQLScripts”插件（这是“标准”插件之一），然后你可以选择这些菜单选项：Session> Scripts>Store Result of SQL in File

I'm looking at version 3.4. I don't know when this feature was introduced but you may need to upgrade if you don't have and cannot install the SQLScripts plugin.

我在看 3.4 版。我不知道这个功能是什么时候引入的，但如果你没有并且无法安装 SQLScripts 插件，你可能需要升级。

Instructions for installing a new plugin can be found at: http://squirrel-sql.sourceforge.net/user-manual/quick_start.html#plugins

可以在以下位置找到安装新插件的说明：http: //squirrel-sql.sourceforge.net/user-manual/quick_start.html#plugins

But if you're performing a fresh install of Squirrel you can simply select the "SQLScripts" plugin during the installation.

但是，如果您正在执行 Squirrel 的全新安装，您可以在安装过程中简单地选择“SQLScripts”插件。

Here's the long version:

这是长版：

---

---

1. Run the query

   Connect to the database. Click on the SQLtab. Enter your query. Hit the run button (or Ctrl-Enter).

   You should see the first 100 rows or so in the results area in the bottom half of the pane (depending upon how you've configured the Limit Rowsoption).

2. Export the fullresults

   Open the Sessionmenu. Select the Scriptsitem (nearly at the bottom of this long menu). Select Store Result of SQL in File.

   This opens a dialog box where you can configure your export. Make sure you check Export the complete result setto get everything.

1. 运行查询

   连接到数据库。单击SQL选项卡。输入您的查询。点击运行按钮（或Ctrl-Enter）。

   您应该会在窗格下半部分的结果区域中看到前 100 行左右（取决于您配置Limit Rows选项的方式）。

2. 导出完整结果

   打开Session菜单。选择Scripts项目（几乎在这个长菜单的底部）。选择Store Result of SQL in File。

   这将打开一个对话框，您可以在其中配置导出。确保您检查Export the complete result set以获取所有内容。

---

---

I haven't tried this with a 3 million row result set, but I have noticed that Squirrel seems to stream the data to disk (rather than reading it all into memory before writing), so I don't see any reason why it wouldn't work with an arbitrarily large file.

我还没有用 300 万行的结果集尝试过这个，但我注意到 Squirrel 似乎将数据流式传输到磁盘（而不是在写入之前将其全部读入内存），所以我看不出任何原因' 不适用于任意大的文件。

Note that you can export directly to a file by using Ctrl-Tto invoke the tools popup and selecting sql2file.

请注意，您可以通过使用Ctrl-T调用工具弹出窗口并选择 来直接导出到文件sql2file。

回答by Martin Vysny

I have found a way to do this, there is a nice support for this in Squirrel. Run the SQL select (the 100 row limit will be ignored by the exporter, don't worry). Then, in the main menu, choose Session, Scripts, Store Result of SQL in File. This functionality may not be present by default, it may be present in some standard plugin (but not installed by default). I don't know which plugin though.

我找到了一种方法来做到这一点，Squirrel 对此提供了很好的支持。运行 SQL select（导出器将忽略 100 行限制，不用担心）。然后，在主菜单中，选择会话、脚本、将 SQL 的结果存储在文件中。默认情况下可能不存在此功能，它可能存在于某些标准插件中（但默认情况下未安装）。不过不知道是哪个插件。

回答by thetaiko

Run from your GUI:

从您的 GUI 运行：

COPY (SELECT * FROM some_table) TO '/some/path/some_table.csv' WITH CSV HEADER

COPY (SELECT * FROM some_table) TO '/some/path/some_table.csv' WITH CSV HEADER

回答by Michal Vician

I also wanted to export results of SQL query to CSV file using SquirrelSQL. However according to changes fileit seems that this functionality is not supported even in SquirrelSql 3.3.0.

我还想使用 SquirrelSQL 将 SQL 查询的结果导出到 CSV 文件。但是，根据更改文件，即使在 SquirrelSql 3.3.0 中似乎也不支持此功能。

So far I was able to export only data shown in 'result table' of SQL query by right click on the table > Export to CSV. The table size is by default 100 rows and so is the CSV export. You may change the table size in Session Properties > SQL > Limit rows. E.g. change the size to 10000 and your export will also contain 10000 rows. The question is, how will SquirrelSql deal with really big result sets (millions of rows)...

到目前为止，我只能通过右键单击表 > 导出到 CSV 来导出 SQL 查询的“结果表”中显示的数据。表格大小默认为 100 行，CSV 导出也是如此。您可以在会话属性 > SQL > 限制行中更改表大小。例如，将大小更改为 10000，您的导出也将包含 10000 行。问题是，SquirrelSql 将如何处理非常大的结果集（数百万行）...

回答by user2649863

Using Squirrel 3.5.0 The "Store SQL result as file" is great if you only have a simple Select query. A more complex one with parameters wont work. Even trying to export a result of 600,000+ rows to a CSV file can also fail.

使用 Squirrel 3.5.0 如果您只有一个简单的 Select 查询，“将 SQL 结果存储为文件”非常有用。一个更复杂的参数将不起作用。即使尝试将 600,000 多行的结果导出到 CSV 文件也可能失败。