Run SQL Query on CSV File Contents From the Command Line

1. Introduction

In this tutorial, we’ll explore how to run SQL queries directly on CSV files from the command line. This capability provides a powerful way to analyze and manipulate data stored in CSV format without extensive programming or database setup. Furthermore, we’ll discuss different tools and methods for executing SQL queries on CSV files.

2. Using SQLite

We can use SQLite, a lightweight database engine, to perform SQL operations in memory or on disk. Let’s explore how to use SQLite for querying CSV files.

2.1. Installing SQLite

To get started, we need to install SQLite. The installation can be done through package managers or by downloading SQLite directly from its official website.

For example, let’s install SQLite on a Debian-based Linux machine using apt-get:

$ sudo apt-get install sqlite3
[sudo] password for kali: 
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
...Copy

Once installed, we can verify the installation by checking the version:

$ sqlite3 --version
3.46.0 2024-05-23 13:25:27 96c92aba00c8375bc32fafcdf12429c58bd8aabfcadab6683e35bbb9cdebalt1 (64-bit)Copy

Now, we’re ready to import the CSV data into SQLite.

2.2. Importing CSV Data Into SQLite

For illustration, we use a CSV file named data.csv:

$ cat data.csv
name,sex,age,weight,height
Alex,M,41,74,170
Bert,M,42,66,166
Dave,M,39,72,167
Elly,F,30,70,124
Luke,M,34,72,163
Omar,M,38,69,145
Page,F,31,67,135
Ruth,F,28,65,131Copy

Next, let’s import this data into SQLite:

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import data.csv my_table'
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
sqlite>Copy

The :memory: part of this command creates a temporary database in RAM. Then, ‘.mode csv’ sets the input to CSV. Finally, ‘.import data.csv my_table’ imports the contents of data.csv into a table named my_table.

2.3. Running SQL Queries

Now that we have the data in SQLite, let’s run some SQL queries.

For example, let’s get the average weight of all individuals:

sqlite> SELECT AVG(weight) FROM my_table;
69.375Copy

Additionally, let’s also group by gender and count the number of individuals:

sqlite> SELECT sex, COUNT(*) FROM my_table GROUP BY sex;
F,3
M,5
Copy

Alternatively, we can run the SQL query non-interactively:

$ sqlite3 :memory: -cmd '.mode csv' -cmd '.import data.csv my_table' 'SELECT sex, COUNT(*) FROM my_table GROUP BY sex;'
F,3
M,5
Copy

We obtain the same result as before.

3. Using Python

Besides SQLite, we can use Python libraries like pandas, pandasql, or duckdb to query CSV files.

3.1. Querying With pandas and pandasql

To run SQL queries using Python, we can leverage the pandas library for data manipulation and pandasql for writing SQL-like queries. However, it’s important to note that recent updates to SQLAlchemy may cause compatibility issues with pandasql.

First, we need to install the required libraries. If they’re not installed, we can do so via pip. Additionally, for compatibility with pandasql, we need to install an older version of SQLAlchemy:

$ pip install pandas pandasql sqlalchemy==1.4.46Copy

Once the libraries are installed, we can write a Python script, example_csv.py, to load the CSV file and run SQL queries:

$ cat example_csv.py
import pandas as pd
from pandasql import sqldf

# Load the CSV file into a DataFrame
df = pd.read_csv('data.csv')

# Define the query function
pysqldf = lambda q: sqldf(q, globals())

# Example 1: Getting average weight
avg_weight_query = "SELECT AVG(weight) AS avg_weight FROM df"
avg_weight = pysqldf(avg_weight_query)
print(avg_weight)

# Example 2: Count individuals by gender
count_gender_query = "SELECT sex, COUNT(*) AS count FROM df GROUP BY sex"
count_gender = pysqldf(count_gender_query)
print(count_gender)Copy

This script reads data.csv into a DataFrame and enables us to execute SQL queries using pandasql.

Next, we can run the Python script on the command line with the python command:

$ python example_csv.py
   avg_weight
0     69.375
  sex  count
0   F      3
1   M      5Copy

Consequently, the output shows the average weight and gender count using the CSV data.

3.2. Querying With DuckDB

Another powerful alternative for querying CSV files is DuckDB, which offers efficient analytical capabilities.

To use DuckDB, we first need to install it. If using Python, we can install it via pip:

$ pip install duckdb
Copy

Once installed, we write a Python script named example2_csv.py that uses the duckdb library:

$ cat example2_csv.py
import duckdb

# Run a query directly on the CSV file
query = "SELECT AVG(weight) AS avg_weight FROM read_csv_auto('data.csv')"
avg_weight = duckdb.query(query).to_df()
print(avg_weight)

# Another query to count individuals by gender
count_gender_query = "SELECT sex, COUNT(*) AS count FROM read_csv_auto('data.csv') GROUP BY sex"
count_gender = duckdb.query(count_gender_query).to_df()
print(count_gender)Copy

Finally, we can execute the script from the command line:

$ python example2_csv.py
   avg_weight
0     69.375
  sex  count
0   F      3
1   M      5
Copy

As expected, the output displays the average weight and the count of individuals grouped by gender.

4. Conclusion

In this article, we explored various methods for running SQL queries on CSV files. These methods include using SQLite for quick SQL operations, as well as Python libraries like pandas and pandasql, and DuckDB for more complex data manipulation.

Each approach offers unique advantages for data analysis, allowing for efficient querying without the overhead of setting up a full database system.