TIL

Making SQL queries on a CSV with SQLite

Shared by avatar for Peter Baumgartner Peter Baumgartner on 

  • tags:
  • #csv
  • #sqlite
  • #python

Want to make SQL queries against a CSV? Python's sqlite-utils is a really cool project which will convert your CSV to SQLite in memory and allow you to query it in one line.

Let's say you have a CSV of CO2 per kWh per year in the city of Austin and you want to know which years were over 1150 pounds of CO2/MWh. You can run this command:

$ sqlite-utils memory ~/Downloads/CO2_per_kWh.csv \
    'SELECT *
     FROM CO2_per_kWh
     WHERE "pounds of CO2/MWh" > 1150'
[{"calendar year": 2005, "pounds of CO2-eq/kWh": 1.17, "pounds of CO2/MWh": 1170},
 {"calendar year": 2007, "pounds of CO2-eq/kWh": 1.18, "pounds of CO2/MWh": 1180},
 {"calendar year": 2008, "pounds of CO2-eq/kWh": 1.16, "pounds of CO2/MWh": 1160},
 {"calendar year": 2011, "pounds of CO2-eq/kWh": 1.18, "pounds of CO2/MWh": 1180}]