ibis - an idiomatic flavor of SQL for Python programmers.

import ibis

import ibis, pandas, dask, pytest, base64

ibis is a mature open source project that has been in development for ~5 years with 1349 ⭐️ on Github. It provides an interface to SQL for Python programmers and bridges the gap between remote storage & execution systems. These features provide authors the ability to:

1. Write backend independent SQL expressions in Python
2. Access different database connections (eg. sqlite, omnisci, pandas)
3. Visually confirm their SQL queries with directed acyclic graphs (DAG)

ibis is an alternative approach using databases that relies on Python rather than SQL experience. Typically, people would have to learn an entirely new syntax, or flavor of SQL, to perform simple tasks. Now, those with Python abilities can avoid a new learning curve by using ibis for composing and executing expressions in SQL. Using familiar Python syntaxes similar to pandas and dask. ibis assists in formation of SQL expressions by providing visual feedback about each Python object.

This blog focuses on writing SQL expressions in Python and how to compose queries visually using ibis. We'll demonstrate this by using SQLite and Sean Lahman’s baseball database.

Connecting to a database.¶

To get started, we’ll need to establish a database connection. ibis makes it easy to create different connections. Let's go ahead and do this now.

client = ibis.sqlite.connect('lahmansbaseballdb.sqlite')

The client variable above represents our connection to the database. The baseball database we are using has 29 tables, which we can see by running the following code:

>>> client.list_tables()
[...'appearances'...'halloffame', 'homegames', 'leagues', 'managers',...]

It is essential to use the correct ibis client for the file types being used.

Selecting and visualizing tables.¶

Now that we have a connection we can create a data frame from ibis expressions similar to the example above. To demonstrate some of ibis capabilities, we’ll examine information in the halloffame and appearances tables of the database. Let's create those tables now.

halloffame, appearances = client.table('halloffame'), client.table('appearances')

A useful feature of ibis is its ability to representat of our SQL query in a DAG using graphviz. dask users will be familiar with this helpful feature.

At the moment, our table doesn’t hold any data; instead, they are expressions represent operations applied to the data where its located - in the SQLite database. We can prove this by asserting its instance like this:

>>> assert isinstance(halloffame, ibis.expr.types.TableExpr)

sample = halloffame.head()

Looking at the sample expression we can reveal the SQL query we constructed.

>>> str(sample.compile())
'SELECT t0."ID", t0."playerID",...FROM base.halloffame AS t0...LIMIT ? OFFSET ?'

And we can now execute the expression to return a tidy pandas.DataFrame.

>>> assert isinstance(sample.execute(), pandas.DataFrame)

sample.execute()

Filtering and selecting data.¶

As mentioned earlier, ibis uses familiar pandas syntax to build SQL queries. Tho show how to filter and select data we will filter the players from our halloffame table by applying our boolean logic to the halloffame expression.

condition = halloffame.category == 'Player'
players = halloffame[condition]

Joining ibis tables.¶

If we want a single view of the halloffame players and their appearances, we need to join the two tables. To do this, we’ll perform an inner join based on the playerID column in our new players table.

condition = players.playerID == appearances.playerID

The players and appearances tables share columns, but SQL doesn't handle overlapping columns very well. Specifically in our data ('playerID' and 'ID') are in both tables, we will filter the columns from the appearances table, because they appear in the players table. Then we will compose the join. We do this by using a Python list comprehension.

columns = [x for x in appearances.columns if x not in ('playerID', 'ID')]

Now to compose the join.

unmaterialized = players.join(appearances[columns], condition)

Materializing the join¶

Joins, in databases, require a materialized view to proceed with building expressions. Just remember to use the expressions materialize method otherwise ibis will throw an error; demonstrated by the test below.

>>> with pytest.raises(ibis.IbisError): unmaterialized.distinct()

join = unmaterialized.materialize().distinct()  # like 🐼.DataFrame.drop_duplicates

The code above completes the join, and a sample of our joined data is shared in the table below.

ibis supports other join strategies as well. Those are listed below:

>>> [x for x in dir(players) if 'join' in x]
['anti_join', 'any_inner_join', 'any_left_join', 'asof_join', 'cross_join', 'inner_join', 'join', 'left_join', 'outer_join', 'semi_join']

Executing an expression¶

In this section, we'll expand the join in a pandas.DataFrame. We'll answer the question:

How many pitchers have been inducted into the hall of fame?

Some hitters may have also pitched, so we’ll need to filter out those appearances. join.G_p represents the numbers of games a player played as a pitcher and we select players who played mostly as pitchers, or pitched more than 100 games.

pitchers = join[join.G_p > 100]

Group the pitchers based on a condition.

condition = [pitchers.inducted, pitchers.yearID]

And count them annually.

grouped_pitchers = pitchers.groupby(condition).count()

df = grouped_pitchers.execute().set_index('inducted yearID'.split())

What next?¶

That's it! In future posts, we’ll explore more backends and visualize more ibis objects. If you’d like to contribute to ibis, please take a look at OpenTeams or the ibis contributing guide.