Creating and inserting data

Setup

[1]:
import os
import ibis

ibis.options.interactive = True

connection = ibis.sqlite.connect(os.path.join('data', 'geography.db'))

Creating new tables from Ibis expressions

Suppose you have an Ibis expression that produces a table:

[2]:
countries = connection.table('countries')

continent_name = (countries.continent
                           .case()
                           .when('AF', 'Africa')
                           .when('AN', 'Antarctica')
                           .when('AS', 'Asia')
                           .when('EU', 'Europe')
                           .when('NA', 'North America')
                           .when('OC', 'Oceania')
                           .when('SA', 'South America')
                           .else_(countries.continent)
                           .end()
                           .name('continent_name'))

expr = countries[countries.continent, continent_name].distinct()
expr
[2]:
  continent continent_name
0        EU         Europe
1        AS           Asia
2        NA  North America
3        AF         Africa
4        AN     Antarctica
5        SA  South America
6        OC        Oceania

To create a table in the database from the results of this expression, use the connection’s create_table method:

[3]:
connection.create_table('continents', expr)
[4]:
continents = connection.table('continents')
continents
[4]:
  continent continent_name
0        EU         Europe
1        AS           Asia
2        NA  North America
3        AF         Africa
4        AN     Antarctica
5        SA  South America
6        OC        Oceania

Tables can be similarly dropped with drop_table

[5]:
connection.drop_table('continents')

Inserting data into existing tables

Some backends support inserting data into existing tables from expressions. This can be done using connection.insert('table_name', expr).