Creating and Inserting Data¶

Setup¶

In [1]:

!curl -LsS -o $TEMPDIR/geography.db 'https://storage.googleapis.com/ibis-tutorial-data/geography.db'

!curl -LsS -o $TEMPDIR/geography.db 'https://storage.googleapis.com/ibis-tutorial-data/geography.db'

In [2]:

import os
import tempfile

import ibis

ibis.options.interactive = True

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

import os import tempfile import ibis ibis.options.interactive = True connection = ibis.sqlite.connect( os.path.join(tempfile.gettempdir(), 'geography.db') )

/nix/store/15c5ssh54syvbiv9waav5gcb2r8n0800-python3-3.10.5-env/lib/python3.10/site-packages/pyproj/__init__.py:91: UserWarning: Valid PROJ data directory not found. Either set the path using the environmental variable PROJ_LIB or with `pyproj.datadir.set_data_dir`.
  warnings.warn(str(err))

Creating new tables from Ibis expressions¶

Suppose you have an Ibis expression that produces a table:

In [3]:

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

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

Out[3]:

	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:

In [4]:

connection.create_table('continents', expr)

connection.create_table('continents', expr)

In [5]:

continents = connection.table('continents')
continents

continents = connection.table('continents') continents

Out[5]:

	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

In [6]:

connection.drop_table('continents')

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).

---

Last update: May 3, 2022