쿼리 결과를 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 명령을 사용하여 하나 또는 하나의 쿼리만 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 파일을 설정합니다. 그런 다음 나중에 두 개의 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 파일로 복사

SQL 쿼리를 CSV 파일에 저장하거나 추가하는 데 .once 및 .output 명령이 사용된다는 점에 대해 논의했습니다. 이 솔루션은 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을 사용하여 쿼리를 여러 개 복사할 수 있습니다. 아래는 쿼리가 두 개의 테이블을 조인하고 쿼리를 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