🗣SQL queries against csv file with csvkit
csvkit is a command line toolkit for converting and working with CSV files. This utility allows you to perform the following operations in plain Python:
• Convert Excel and JSON files to CSV
• Display only column names
• Slice data
• change the order of columns
• find rows with matching cells
• convert CSV to JSON
• generate summary statistics
• refer to CSV using SQL queries
• import data into databases and extract from them
• parse CSV data
• work with column delimiters
The pip package manager will help you install csvkit: pip install csvkit
And the syntax for accessing a CSV file via an SQL query on the command line will look like this:
csvsql --query "SQL Query Here - source file name as table name (without .CSV)" source_filename> target_filename
To use this in your Python script you should
1) first import CSVSQL from csvkit utility
from csvkit.utilities.csvsql import CSVSQL
2) further define the arguments as a list of values, for example:
args = ['--query', 'select distinct manufacturer from playground', 'payground.csv']
3) then call CSVSQL with arguments
result = CSVSQL (args)
3) finally, the results can be shown
print (result.main ())
https://csvkit.readthedocs.io/en/latest/index.html
https://medium.com/data-engineering-ramstkp/sql-queries-on-csv-using-python-24a472fe53b1