How To Use SQL To Query CSV Files Directly

Writing this to share a cool library I recently found out about.

How I used to do it

• Pandas

• Dumping the CSV content into SQL, then querying using SQL

How I do it now — DuckDB

DuckDB is an open-source, easy-to-use OLAP database. It is pretty powerful, and optimized for use without applications without the hassle of needing to set up a server.

One of its many capabilities is that it allows us to query CSV files directly using SQL, as if the CSV file’s contents has already been added to an SQL database.

A simple example

A simple dog.csv

name,age,gender
rocky,4,m
fifi,5,f
baaron,6,m

Some duckdb code:

Using minimal setup code, we can query a CSV file in Python using SQL!

Some things to note:

• to install, pip install duckdb

• We need to use the special read_csv_auto() function from duckdb

• Our .execute(...).fetchdf() method returns a pandas DataFrame

• We didn’t have to set up any server for this — we just need Python and a CSV file.

Example — table joins

Let’s say we want to write more complicated queries.

We have owners.csv:

id,name
1,alex
2,bob
3,charlie

And dogs.csv:

name,age,gender,owner_id
rocky,4,m,1
fifi,5,f,1
baaron,6,m,2

Now, let’s use DuckDB to do a table join:

But why not just use pandas?

Purely personal preference, but SQL is more intuitive to me, so I choose to use SQL.

DuckDB For JavaScript

If you want to expose your data to the public to query using SQL, DuckDB allows you to do that without having to set up a server!

• somehow serve your static CSV files (I do that in my public folder lol)

• follow steps at https://duckdb.org/docs/stable/clients/wasm/overview.html

• The public can now easily query your CSV files using SQL

• We don’t even need a backend if we really don’t want to set it up

Conclusion

Hopefully this was clear and easy to understand

If You Wish To Support Me As A Writer

• Buy my book — 256 Python Things I Wish I Knew Earlier at https://payhip.com/b/xpRco

• Subscribe to my Substack newsletter at

• — I publish useful Python tips weekly

• Clap 50 times for this story

• Leave a comment telling me your thoughts

• Highlight your favourite part of the story

Thank you — these tiny actions go a long way, and I really appreciate it.

LinkedIn: https://www.linkedin.com/company/the-python-rabbithole