SQLite でクエリ結果を CSV ファイルに保存する

1. 単一の SQLite クエリを CSV ファイルに保存する
2. すべての SQLite クエリを CSV ファイルに保存する
3. CSV ファイルの自動オープン
4. 単一またはすべての SQL クエリを CSV ファイルにコピーする
5. JOIN クエリを使用して出力を CSV ファイルにコピーする

SQLite の目的は、ローカル データベースにデータを保存、読み取り、管理することです。 クエリ言語を使用して、SQLite データベースから必要なデータを取得できます。

取得した結果を保存する必要がある場合があります。 この目的のために、SQLite データベースのクエリ結果を CSV ファイルに保存します。

これを行うには、.once または .output コマンドを結合した .mod コマンドによって SQLite コマンド ライン シェルを使用します。 後で、.system コマンドを使用してそのファイルを開きます。

単一の SQLite クエリを CSV ファイルに保存する

.once コマンドを使用して、1つまたは 1つのクエリのみを CSV ファイルにエクスポートします。 .once コマンドを使用すると、次の SQL ステートメントの結果が CSV ファイルにエクスポートされ、コンソールに戻ります。

以下の例では、列ヘッダーも有効にしています。 次の行では、CSV モードに切り替え、.once コマンドを使用して、次の SQL コマンドを result_of_single_query.csv ファイルにコピーしました。

# for showing headers on the screen
.headers on
# turning on the csv mode
.mode csv
# copying a single query only
.once result_of_single_query.csv
SELECT * FROM Customers;

上記のステートメントの結果は CSV ファイルにエクスポートされます。CSV ファイルに含まれるデータは次のとおりです。 ファイルのエクスポートでヘッダーを無視する場合は、上記の SQL クエリで .headers off を使用します。

CustomerId, CustomerName, Age
1, "Holder", 39
2, "Alex", 42
3, "Bob", 27
4, "John", 74

すべての SQLite クエリを CSV ファイルに保存する

.once コマンドを使用すると、次の SQL クエリのみをエクスポートできます。

また、すべての SQL クエリを CSV ファイルに保存する機能もあります。 .output コマンドを使用して、すべての SQL クエリを CSV ファイルに保存または追加します。

# for copying all the queries to the CSV file
.output save_all_queries.csv
# both these SQL statements get copied to the CSV file
SELECT * FROM Customers;
SELECT * FROM Customers LIMIT 2;

最初に、最初の例とまったく同じように、上記の SQL クエリで出力 CSV ファイルを設定します。 次に、後で 2つの SQL クエリを実行して、SQL クエリ データを CSV ファイルに保存または追加します。

保存したファイルを開くと、ファイルは画面に次の出力を表示します。

CustomerId, CustomerName, Age
1, "Holder", 39
2, "Alex", 42
3, "Bob", 27
4, "John", 74
CustomerId, CustomerName, Age
1, "Holder", 39
2, "Alex", 42

ここでも、SQL ステートメントで .headers off コマンドを使用しませんでした。 したがって、各 SQL ステートメントが実行されると、ヘッダーはファイルにコピーされます。 ヘッダーをもう一度スキップするには、最初に .headers on コマンドを使用し、次に最後のステートメントを実行する前に .headers off コマンドを使用する必要があります。

.headers on
.output save_all_queries.csv
SELECT * FROM Customers;
.headers off
SELECT * FROM Customers LIMIT 2;

上記の SQL ステートメントの結果は次のようになります。

CustomerId, CustomerName, Age
1, "Holder", 39
2, "Alex", 42
3, "Bob", 27
4, "John", 74
1, "Holder", 39
2, "Alex", 42

CSV ファイルの自動オープン

.system コマンドを使用して、CSV ファイルを開きます。 ファイルを開く構文は、システムのタイプによって異なります。

次のコマンドを使用して、さまざまなオペレーティング システムで CSV ファイルを開きます。

Mac オペレーティング システムでは、次のコマンドを使用してファイルを開きます。

.system open query_to_open_file.csv

Windows オペレーティング システムでは、次のコマンドを使用してファイルを開きます。

.system c:/data/query_to_open_file.csv

Unix/Linux オペレーティング システムでは、次のコマンドを使用してファイルを開きます。

.system xdg-query_to_open_file.csv

単一またはすべての SQL クエリを CSV ファイルにコピーする

.once および .output コマンドを使用して、SQL クエリを CSV ファイルに保存または追加することについて説明しました。 この解決策は、SQL コマンド プロンプトを使用している場合に可能です。

しかし、プログラムによってデータを CSV ファイルに保存する再利用可能なスクリプトを試す場合は、次のソース コードを使用します。

# use pandas for data manipulation and analysis
import pandas as pd

# sqlite3 represents the version of the sqlite, which is 3
import sqlite3

connection = sqlite3.connect("present_database.sqlite")
dataframe = pd.read_sql("SELECT * from customers", connection)
dataframe.to_csv("customers.csv", index=False)

read_sql 関数のクエリ部分内で、SQLite データベースから別のテーブル部分を取得するようにカスタマイズできます。 同様に、単一のコマンドを実行して、すべてのクエリを CSV ファイルに保存できます。

# gets all the table
for table in c.execute("SELECT customerName FROM sqlite_master WHERE type='table';").fetchall():
    t = table[0]
    dataframe = pd.read_sql('SELECT * from ' + t, conn)
    dataframe.to_csv(t + '_single_command.csv', index = False)

ここでは、sqlite_master コマンドを使用して、SQLite データベース内のすべてのテーブルを取得します。

JOIN クエリを使用して出力を CSV ファイルにコピーする

単一のテーブル クエリのコピーに限定されません。 代わりに、左または右の JOIN を使用して任意のクエリを複数コピーできます。 以下は、クエリが 2つのテーブルを結合し、クエリを CSV ファイルに出力することを示しています。

.header on
.mode csv
.once /Users/sqlite//customers.csv
SELECT CustomerId, CustomerName, ProductName
FROM Customers AS a
  INNER JOIN Products AS b
  ON a.ProductId = b.ProductId
ORDER BY ProductName;

上記の SQL ステートメントは、以下を CSV ファイルに出力します。

CustomerId, CustomerName, Age
1, "Holder Boxer", 39
2, "Alex Ferguson", 42
3, "Bob Marlet", 27
4, "John Wack", 74