Overview

faro is a fast, simple, and intuitive SQL-driven data analysis library for Python. It is built on top of sqlite and is intended to complement the existing data analysis packages in the Python eco-system, such as numpy, pandas, and matplotlib by providing easy interoperability between them. It also integrates with Jupyter by default to provide readable and interactive displays of queries and tables.

Usage

Create a Database object and give it a name.

from faro import Database

db = Database('transportation')

To add tables to the in-memory database, simply specify the name of the file. Supported file types include: csv, json, and xlsx. add_table inserts the contents of a file into a new table within the database. It can automatically detect the filetype and parse the file contents accordingly. In this example we load two different tables, one in csv format, and the other in json format.

db.add_table('cars.json', name='cars')
db.add_table('airports.csv', name='airports')

We can also directly pass pandas.DataFrame or faro.Table objects to be added to the database. A helpful pattern when dealing with more complex parsing for a specific file is to read it into memory using pandas then add the DataFrame to the faro.Database.

buses = pd.DataFrame({
  'id' : [1, 2, 3, 4, 5],
  'from' : ['Houston', 'Atlanta', 'Chicago', 'Boston', 'New York'],
  'to' : ['San Antonio', 'Charlotte', 'Milwaukee', 'Cape Cod', 'Buffalo']
})

db.add_table(buses, name='buses')

Alternatively, we can directly assign to a table name as a property of the table object. Using this method, however, will also replace the entire table as opposed to the options offered by add_table()

db.table.buses = buses

We can now query against any table in the database using pure SQL, and easily interact with the results in a Jupyter Notebook.

sql = """
SELECT iata,
       name,
       city,
       state
  FROM airports
 WHERE country = 'USA'
 LIMIT 5
"""
db.query(sql)

	iata	name	city	state
0	00M	Thigpen	Bay Springs	MS
1	00R	Livingston Municipal	Livingston	TX
2	00V	Meadow Lake	Colorado Springs	CO
3	01G	Perry-Warsaw	Perry	NY
4	01J	Hilliard Airpark	Hilliard	FL

If we want to interact with the data returned by the query, we can easily transform it into whatever data type is most convenient for the situation. Supported type conversions include: List[Tuple], Dict[List], numpy.ndarray, and pandas.DataFrame.

table = db.query(sql)
type(table)
>>> faro.table.Table

df = table.to_dataframe()
type(df)
>>> pandas.core.frame.DataFrame

matrix = table.to_numpy()
type(matrix)
>>> numpy.ndarray

We can also interact with the tables in our database by accessing them as properties of the table object. For example:

db.table.buses

	id	from	to
1	1	Houston	San Antonio
2	2	Atlanta	Charlotte
3	3	Chicago	Milwaukee
4	4	Boston	Cape Cod
5	5	New York	Buffalo