Connecting to a Database

sql2csv: is tool in csvkit that allows you to pull data from the database and convert it to csv. It executes an SQL query on a large variety of SQL databases (e.g MS SQL, MySQL, Oracle, PostgreSQL, Sqlite).

Documentation

sql2csv -h

sql2csv: querying against the database

Sample Syntax

csvsql Documentation

• applied SQL statements to one or more CSV files

• creates an in-memory SQL database that temporarily hosts the file being processed

• suitable for small to medium files only

csvsql -h #for full documentation

# Store SQL query as shell variable
sql_query="SELECT ma.*, p.popularity FROM Spotify_MusicAttributes ma INNER JOIN Spotify_Popularity p ON ma.track_id = p.track_id"

# Join 2 local csvs into a new csv using the saved SQL
csvsql --query "$sql_query" Spotify_MusicAttributes.csv Spotify_Popularity.csv > Spotify_FullData.csv

# Preview newly created file
csvstat Spotify_FullData.csv

Pushing data back to database

to upload local file to the database

Recap

# Store SQL for querying from SQLite database 
sqlquery_pull="SELECT * FROM SpotifyMostRecentData"

# Apply SQL to save table as local file 
sql2csv --db "sqlite:///SpotifyDatabase.db" --query "$sqlquery_pull" > SpotifyMostRecentData.csv

# Store SQL for UNION of the two local CSV files
sqlquery_union="SELECT * FROM SpotifyMostRecentData UNION ALL SELECT * FROM Spotify201812"

# Apply SQL to union the two local CSV files and save as local file
csvsql     --query "$sqlquery_union" SpotifyMostRecentData.csv Spotify201812.csv > UnionedSpotifyData.csv

# Push UnionedSpotifyData.csv to database as a new table
csvsql --db "sqlite:///SpotifyDatabase.db" --insert UnionedSpotifyData.csv