Pulling data from databases

• sql2csv : documentation

  sql2csv:

  • executes an SQL query on a large variety of SQL databases (e.g. MS SQL, MySQL, Oracle, PostgreSQL, Sqlite)
  • outputs the result to a CSV file

  Documentation

        sql2csv -h
  

• sql2csv: querying against the database

  Sample Syntax:

        sql2csv --db 'sqlite:///SpotifyDatabase.db' \
                --query 'SELECT * FROM Spotify_Popularity' \
                > Spotify_Popularity.csv
  

  1. Establishing database connection:
     • --db is followed by the database connection string
     • SQLITE: starts with sqlite:/// and ends with .db
     • Postgres & MySQL starts with postgres:/// or mysql:/// and with no .db
  2. Querying against the database:
     • --query is followed by the SQL query string
     • Use SQL syntax compatible with the database
     • Write query in one line with no line breaks
  3. Saving the output:
     • >: re-directs output to new local CSV file
     • Otherwise, will only print query results to console

---

Manipulating data using SQL syntax

• csvsql: documentation

  csvsql:

  • applies 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

  Documentation:

        csvsql -h
  

• csvsql: applying SQL to a local CSV file

  Sample Syntax:

        csvsql --query "SELECT * FROM Spotify_MusicAtributes LIMIT 1"  Spotify_MusicAttributes.csv 
  

        csvsql --query "SELECT * FROM Spotify_MusicAtributes LIMIT 1"  Spotify_MusicAttributes.csv  | csvlook
  

        csvsql --query "SELECT * FROM Spotify_MusicAtributes LIMIT 1"  Spotify_MusicAttributes.csv  > OneSongFile.csv
  

• csvsql: joining CSVs using SQL syntax

  Sample Syntax:

        csvsql --query "SELECT * FROM file_a INNER JOIN file_b..." file_a.csv file_b.csv
  

  Note:

  • SQL Query must be written in one line, no breaks
  • Indicate CSV files in order of appearence in SQL

• csvsql: documentation

  csvsql:

  • execute SQL statements directly on a database
  • supports both creating tables and inserting data. More option arguements:
  • --insert
  • --db
  • --no-inference & --no-constraints

• csvsql: pushing data back to database

  Sample Syntax:

        csvsql --db "sqlite:///SpotifyDatabase.db" \
            --insert Spotify_MusicAttributes.csv
  

  Note:

  1. Line break is used to keep code clean and readable
  2. Use three forward slashes to initiate database name
  3. End with file extension .db for SQLITE database

        csvsql --no-inference --no-constraints \
                --db "sqlite:///SpotifyDatabase.sb" \
                --insert Spotify_MusicAttributes.csv