Pulling data from databases
Data Processing in Shell
Susan Sun
Data Person
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
usage: sql2csv [-h] [-v] [-l] [-V] [--db CONNECTION_STRING] [--query QUERY]
[-e ENCODING] [-H]
[FILE]
sql2csv: querying against the database
Sample syntax:
sql2csv --db "sqlite:///SpotifyDatabase.db" \
--query "SELECT * FROM Spotify_Popularity" \
> Spotify_Popularity.csv
1. Establishing database connection:
--dbis followed by the database connection string- SQLite: starts with
sqlite:///and ends with.db - Postgres & MySQL: starts with
postgres:///ormysql:///and with no.db
sql2csv: querying against the database
Sample syntax:
sql2csv --db "sqlite:///SpotifyDatabase.db" \
--query "SELECT * FROM Spotify_Popularity" \
> Spotify_Popularity.csv
2. Querying against the database:
--queryis followed by the SQL query string- Use SQL syntax compatible with the database
- Write query in one line with no line breaks
sql2csv: querying against the database
Sample syntax:
sql2csv --db "sqlite:///SpotifyDatabase.db" \
--query "SELECT * FROM Spotify_Popularity" \
> Spotify_Popularity.csv
3. Saving the output:
>: re-directs output to new local CSV file- Otherwise, will only print query results to console
Let's practice!
Data Processing in Shell
