Tables and Data Operations

This documentation page is also available as an interactive notebook. You can launch the notebook in Kaggle or Colab, or download it for use with an IDE or local Jupyter installation, by clicking one of the above links.

Section 1: Tables and Data Operations

Welcome to Section 1 of the Pixeltable Fundamentals tutorial, Tables and Data Operations. In this section, we’ll learn how to:

Create and manage tables: Understand Pixeltable’s table structure, create and modify tables, and work with table schemas
Manipulate data: Insert, update, and delete data within tables, and retrieve data from tables into Python variables
Filter and select data: Use where(), select(), and order_by() to query for specific rows and columns
Import data from CSV files and other file types

First, let’s ensure the Pixeltable library is installed in your environment.

%pip install -qU pixeltable

Tables

All data in Pixeltable is stored in tables. At a high level, a Pixeltable table behaves similarly to an ordinary SQL database table, but with many additional capabilities to support complex AI workflows. We’ll introduce those advanced capabilities gradually throughout this tutorial; in this section, the focus is on basic table and data operations. Tables in Pixeltable are grouped into directories, which are simply user-defined namespaces. The following command creates a new directory, fundamentals, which we’ll use to store the tables in our tutorial.

import pixeltable as pxt

# First we delete the `fundamentals` directory and all its contents (if
# it exists), in order to ensure a clean environment for the tutorial.
pxt.drop_dir('fundamentals', force=True)

# Now we create the directory.
pxt.create_dir('fundamentals')

Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/Users/asiegel/.pixeltable/pgdata
Created directory ‘fundamentals’.

Now let’s create our first table. To create a table, we must give it a name and a schema that describes the table structure. Note that prefacing the name with fundamentals causes it to be placed in our newly-created directory.

films_t = pxt.create_table('fundamentals.films', {
    'film_name': pxt.String,
    'year': pxt.Int,
    'revenue': pxt.Float
})

Created table ‘films’.

To insert data into a table, we use the insert() method, passing it a list of Python dicts.

films_t.insert([
    {'film_name': 'Jurassic Park', 'year': 1993, 'revenue': 1037.5},
    {'film_name': 'Titanic', 'year': 1997, 'revenue': 2257.8},
    {'film_name': 'Avengers: Endgame', 'year': 2019, 'revenue': 2797.5}
])

Inserting rows into `films`: 3 rows [00:00, 572.84 rows/s]
Inserted 3 rows with 0 errors.
3 rows inserted, 3 values computed.

If you’re inserting just a single row, you can use an alternate syntax that is sometimes more convenient.

films_t.insert([{'film_name': 'Inside Out 2', 'year': 2024, 'revenue': 1462.7}])

Inserting rows into `films`: 1 rows [00:00, 318.76 rows/s]
Inserted 1 row with 0 errors.
1 row inserted, 1 value computed.

We can peek at the data in our table with the collect() method, which retrieves all the rows in the table.

films_t.collect()

Pixeltable also provides update() and delete() methods for modifying and removing data from a table; we’ll see examples of them shortly.

Filtering and Selecting Data

Often you want to select only certain rows and/or certain columns in a table. You can do this with the where() and select() methods.

films_t.where(films_t.revenue >= 2000.0).collect()

films_t.select(films_t.film_name, films_t.year).collect()

Note the expressions that appear inside the calls to where() and select(), such as films_t.year. These are column references that point to specific columns within a table. In place of films_t.year, you can also use dictionary syntax and type films_t['year'], which means exactly the same thing but is sometimes more convenient.

films_t.select(films_t['film_name'], films_t['year']).collect()

In addition to selecting columns directly, you can use column references inside various kinds of expressions. For example, our revenue numbers are given in millions of dollars. Let’s say we wanted to select revenue in thousands of dollars instead; we could do that as follows:

films_t.select(films_t.film_name, films_t.revenue * 1000).collect()

Note that since we selected an abstract expression rather than a specific column, Pixeltable gave it the generic name col_1. You can assign it a more informative name with Python keyword syntax:

films_t.select(films_t.film_name, revenue_thousands=films_t.revenue * 1000).collect()

Tables are Persistent

This is a good time to mention a few key differences between Pixeltable tables and other familiar datastructures, such as Python dicts or Pandas dataframes. First, Pixeltable is persistent. Unlike in-memory Python libraries such as Pandas, Pixeltable is a database. When you reset a notebook kernel or start a new Python session, you’ll have access to all the data you’ve stored previously in Pixeltable. Let’s demonstrate this by using the IPython %reset -f command to clear out all our notebook variables, so that films_t is no longer defined.

%reset -f
films_t.collect()  # Throws an exception now

NameError: name ‘films_t’ is not defined
[0;31m---------------------------------------------------------------------------[0m
[0;31mNameError[0m                                 Traceback (most recent call last)
Cell [0;32mIn[11], line 2[0m
[1;32m      1[0m get_ipython()[38;5;241m.[39mrun_line_magic([38;5;124m’[39m[38;5;124mreset[39m[38;5;124m’[39m, [38;5;124m’[39m[38;5;124m-f[39m[38;5;124m’[39m)
[0;32m----> 2[0m [43mfilms_t[49m[38;5;241m.[39mcollect()  [38;5;66;03m# Throws an exception now[39;00m[0;31mNameError[0m: name ‘films_t’ is not defined

The films_t variable (along with all other variables in our Python session) has been cleared out - but that’s ok, because it wasn’t the source of record for our data. The films_t variable is just a reference to the underlying database table. We can recover it with the get_table command, referencing the films table by name.

import pixeltable as pxt

films_t = pxt.get_table('fundamentals.films')
films_t.collect()

You can always get a list of existing tables with the Pixeltable pxt.ls() command. Let’s use it to see the contents of the fundamentals directory.

pxt.ls(path='fundamentals')

Note that if you’re running Pixeltable on colab or kaggle, the database will persist only for as long as your colab/kaggle session remains active. If you’re running it locally or on your own server, then your database will persist indefinitely (until you actively delete it).

Tables are Typed

The second major difference is that Pixeltable is strongly typed. Because Pixeltable is a database, every column has a data type: that’s why we specified String, Int, and Float for the three columns when we created the table. These type specifiers are mandatory when creating tables, and they become part of the table schema. You can always see the table schema with the describe() method.

films_t.describe()

In a notebook, you can also just type films_t to see the schema; its output is identical to films_t.describe().

films_t

In addition to String, Int, and Float, Pixeltable provides several additional data types:

Bool, whose values are True or False;

Array for numerical arrays;

Json, for lists or dicts that correspond to valid JSON structures; and

The media types Image, Video, Audio, and Document.

We’ll see examples of each of these types later in the tutorial.

Besides the column names and types, there’s a third element to the schema, Computed With. We’ll explain what this means in the next section of the tutorial, Computed Columns. All of the methods we’ve discussed so far, such as insert() and get_table(), are documented in the Pixeltable SDK Documentation. The following pages are particularly relevant to this section of the tutorial:

pixeltable package reference
pxt.Table class reference

A Real-World Example: Earthquake Data

Now let’s dive a little deeper into Pixeltable’s data operations. To showcase all the features, it’ll be helpful to have a real-world dataset, rather than our toy dataset with four movies. The dataset we’ll be using consists of Earthquake data drawn from the US Geological Survey: all recorded Earthquakes that occurred within 100 km of Seattle, Washington, between January 1, 2023 and June 30, 2024. The dataset is in CSV format, and we can load it into Pixeltable by using create_table() with the source parameter, which creates a new Pixeltable table from the contents of a CSV file.

eq_t = pxt.create_table(
    'fundamentals.earthquakes',  # Name for the new table
    source='https://raw.githubusercontent.com/pixeltable/pixeltable/release/docs/resources/earthquakes.csv',
    primary_key='id',  # Column 'id' is the primary key
    schema_overrides={'timestamp': pxt.Timestamp}  # Interpret column 3 as a timestamp
)

Created table ‘earthquakes’.
Inserting rows into `earthquakes`: 1823 rows [00:00, 19554.24 rows/s]
Inserted 1823 rows with 0 errors.

In Pixeltable, you can always import external data by giving a URL instead of a local file path. This applies to CSV datasets, media files (such images and video), and other types of content. The URL will often be an http:// URL, but it can also be an s3:// URL referencing an S3 bucket.

Pixeltable’s create_table() function with the source parameter can import data from various formats including CSV, Excel, and Hugging Face datasets. You can also use source to import from a Pandas dataframe. For more details, see the pixeltable.io package reference.

Let’s have a peek at our new dataset. The dataset contains 1823 rows, and we probably don’t want to display them all at once. We can limit our query to fewer rows with the limit() method.

eq_t.limit(5).collect()

A different way of achieving something similar is to use the head() and tail() methods. Pixeltable keeps track of the insertion order of all its data, and head() and tail() will always return the earliest inserted and most recently inserted rows in a table, respectively.

eq_t.head(5)

eq_t.tail(5)

head(n) and limit(n).collect() appear similar in this example. But head() always returns the earliest rows in a table, whereas limit() makes no promises about the ordering of its results (unless you specify an order_by() clause - more on this below).

Let’s also peek at the schema:

eq_t.describe()

Note that while specifying a schema is mandatory when creating a table, it’s not always required when importing data. This is because Pixeltable uses the structure of the imported data to infer the column types, when feasible. You can always override the inferred column types with the schema_overrides parameter of import_csv(). The following examples showcase some common data operations.

eq_t.count()  # Number of rows in the table

# 5 highest-magnitude earthquakes

eq_t.order_by(eq_t.magnitude, asc=False).limit(5).collect()

from datetime import datetime

# 5 highest-magnitude earthquakes in Q3 2023

eq_t.where((eq_t.timestamp >= datetime(2023, 6, 1)) & (eq_t.timestamp < datetime(2023, 10, 1))) \
  .order_by(eq_t.magnitude, asc=False).limit(5).collect()

Note that Pixeltable uses Pandas-like operators for filtering data: the expression

(eq_t.timestamp >= datetime(2023, 6, 1)) & (eq_t.timestamp < datetime(2023, 10, 1))

means both conditions must be true; similarly (say),

(eq_t.timestamp < datetime(2023, 6, 1)) | (eq_t.timestamp >= datetime(2023, 10, 1))

would mean either condition must be true. You can also use the special isin operator to select just those values that appear within a particular list:

# Earthquakes with specific ids

eq_t.where(eq_t.id.isin([123,456,789])).collect()

In addition to basic operators like >= and isin, a Pixeltable where clause can also contain more complex operations. For example, the location column in our dataset is a string that contains a lot of information, but in a relatively unstructured way. Suppose we wanted to see all Earthquakes in the vicinity of Rainier, Washington; one way to do this is with the contains() method:

# All earthquakes in the vicinity of Rainier

eq_t.where(eq_t.location.contains('Rainier')).collect()

Pixeltable also supports various aggregators; here’s an example showcasing two fairly simple ones, max() and min():

# Min and max ids

eq_t.select(min=pxt.functions.min(eq_t.id), max=pxt.functions.max(eq_t.id)).collect()

We’ll discuss the various Pixeltable functions and expressions more thoroughly in the next tutorial section, Computed Columns, and they’re exhaustively documented in the Pixeltable SDK Documentation.

Extracting Data from Tables into Python/Pandas

Sometimes it’s handy to pull out data from a table into a Python object. We’ve actually already done this; the call to collect() returns an in-memory result set, which we can then dereference in various ways. For example:

result = eq_t.limit(5).collect()
result[0]  # Get the first row of the results as a dict

{‘id’: 0,
 ‘magnitude’: 1.15,
 ‘location’: ‘10 km NW of Belfair, Washington’,
 ‘timestamp’: datetime.datetime(2023, 1, 1, 8, 10, 37, 50000, tzinfo=zoneinfo.ZoneInfo(key=‘America/Los_Angeles’)),
 ‘longitude’: -122.93,
 ‘latitude’: 47.51}

result['timestamp']  # Get a list of the `timestamp` field of all the rows that were queried

[datetime.datetime(2023, 1, 1, 8, 10, 37, 50000, tzinfo=zoneinfo.ZoneInfo(key=‘America/Los_Angeles’)),
 datetime.datetime(2023, 1, 2, 1, 2, 43, 950000, tzinfo=zoneinfo.ZoneInfo(key=‘America/Los_Angeles’)),
 datetime.datetime(2023, 1, 2, 12, 5, 1, 420000, tzinfo=zoneinfo.ZoneInfo(key=‘America/Los_Angeles’)),
 datetime.datetime(2023, 1, 2, 12, 45, 14, 220000, tzinfo=zoneinfo.ZoneInfo(key=‘America/Los_Angeles’)),
 datetime.datetime(2023, 1, 2, 13, 19, 27, 200000, tzinfo=zoneinfo.ZoneInfo(key=‘America/Los_Angeles’))]

df = result.to_pandas()  # Convert the result set into a Pandas dataframe
df['magnitude'].describe()

count    5.000000
mean     0.744000
std      0.587988
min      0.200000
25%      0.290000
50%      0.520000
75%      1.150000
max      1.560000
Name: magnitude, dtype: float64

collect() without a preceding limit() returns the entire contents of a query or table. Be careful! For very large tables, this could result in out-of-memory errors. In this example, the 1823 rows in the table fit comfortably into a dataframe.

df = eq_t.collect().to_pandas()
df['magnitude'].describe()

count    1823.000000
mean        0.900378
std         0.625492
min        -0.830000
25%         0.420000
50%         0.850000
75%         1.310000
max         4.300000
Name: magnitude, dtype: float64

Adding Columns

Like other database tables, Pixeltable tables aren’t fixed entities: they’re meant to evolve over time. Suppose we want to add a new column to hold user-specified comments about particular earthquake events. We can do this with the add_column() method:

eq_t.add_column(note=pxt.String)

Added 1823 column values with 0 errors.
1823 rows updated, 1823 values computed.

Here, note is the column name, and pxt.String specifies the type of the new column.

eq_t.add_column(contact_email=pxt.String)

Added 1823 column values with 0 errors.
1823 rows updated, 1823 values computed.

Let’s have a look at the revised schema.

eq_t.describe()

Updating Rows in a Table

Table rows can be modified and deleted with the SQL-like update() and delete() commands.

# Add a comment to records with IDs 123 and 127

(
    eq_t
    .where(eq_t.id.isin([121,123]))
    .update({'note': 'Still investigating.', 'contact_email': '[email protected]'})
)

Inserting rows into `earthquakes`: 2 rows [00:00, 366.84 rows/s]
2 rows updated, 4 values computed.

eq_t.where(eq_t.id >= 120).select(eq_t.id, eq_t.magnitude, eq_t.note, eq_t.contact_email).head(5)

update() can also accept an expression, rather than a constant value. For example, suppose we wanted to shorten the location strings by replacing every occurrence of Washington with WA. One way to do this is with an update() clause, using a Pixeltable expression with the replace() method.

eq_t.update({'location': eq_t.location.replace('Washington', 'WA')})

Inserting rows into `earthquakes`: 1823 rows [00:00, 21494.07 rows/s]
1823 rows updated, 1823 values computed.

eq_t.head(5)

Notice that in all cases, the update() clause takes a Python dictionary, but its values can be either constants such as '[email protected]', or more complex expressions such as eq_t.location.replace('Washington', 'WA'). Also notice that if update() appears without a where() clause, then every row in the table will be updated, as in the preceding example.

Batch Updates

The batch_update() method provides an alternative way to update multiple rows with different values. With a batch_update(), the contents of each row are specified by individual dicts, rather than according to a formula. Here’s a toy example that shows batch_update() in action.

updates = [
    {'id': 500, 'note': 'This is an example note.'},
    {'id': 501, 'note': 'This is a different note.'},
    {'id': 502, 'note': 'A third note, unrelated to the others.'}
]
eq_t.batch_update(updates)

Inserting rows into `earthquakes`: 3 rows [00:00, 984.58 rows/s]
3 rows updated, 3 values computed.

eq_t.where(eq_t.id >= 500).select(eq_t.id, eq_t.magnitude, eq_t.note, eq_t.contact_email).head(5)

Deleting Rows

To delete rows from a table, use the delete() method.

# Delete all rows in 2024

eq_t.where(eq_t.timestamp >= datetime(2024, 1, 1)).delete()

587 rows deleted.

eq_t.count()  # How many are left after deleting?

Don’t forget to specify a where() clause when using delete()! If you run delete() without a where() clause, the entire contents of the table will be deleted.

eq_t.delete()

1236 rows deleted.

eq_t.count()

Table Versioning

Every table in Pixeltable is versioned: some or all of its modification history is preserved. We’ve seen a reference to this already; pxt.ls() will show the most recent version along with each table it lists.

pxt.ls('fundamentals')

To see the version history of a particular table:

eq_t.history()

If you ever make a mistake, you can always call revert() to undo the most recent change to a table and roll back to the previous version. Let’s try it out: we’ll use it to revert the successive delete() calls that we just executed.

eq_t.revert()

eq_t.count()

eq_t.revert()

eq_t.count()

Be aware: calling revert() cannot be undone!

Multimodal Data

In addition to the structured data we’ve been exploring so far in this tutorial, Pixeltable has native support for media types: images, video, audio, and unstructured documents such as pdfs. Media support is one of Pixeltable’s core capabilities, and we’ll have much more to say about it the upcoming Unstructured Data section of this tutorial. For now, we’ll just give one example to show how media data lives side-by-side with structured data in Pixeltable.

# Add a new column of type `Image`
eq_t.add_column(map_image=pxt.Image)
eq_t.describe()

Added 1823 column values with 0 errors.

# Update the row with id == 1002, adding an image to the `map_image` column

eq_t.where(eq_t.id == 1002).update(
    {'map_image': 'https://raw.githubusercontent.com/pixeltable/pixeltable/release/docs/resources/port-townsend-map.jpeg'}
)

Inserting rows into `earthquakes`: 1 rows [00:00, 192.79 rows/s]
1 row updated, 1 value computed.

Note that in Pixeltable, you can always insert images into a table by giving the file path or URL of the image (as a string). It’s not necessary to load the image first; Pixeltable will manage the loading and caching of images in the background. The same applies to other media data such as documents and videos.

Pixeltable will also embed image thumbnails in your notebook when you do a query:

eq_t.where(eq_t.id >= 1000).select(eq_t.id, eq_t.magnitude, eq_t.location, eq_t.map_image).head(5)

Directory Hierarchies

So far we’ve only seen an example of a single directory with a table inside it, but one can also put directories inside other directories, in whatever fashion makes the most sense for a given application.

pxt.create_dir('fundamentals.subdir')
pxt.create_dir('fundamentals.subdir.subsubdir')
pxt.create_table('fundamentals.subdir.subsubdir.my_table', {'my_col': pxt.String})

Created directory ‘fundamentals.subdir’.
Created directory ‘fundamentals.subdir.subsubdir’.
Created table ‘my_table’.

Deleting Columns, Tables, and Directories

drop_column(), drop_table(), and drop_dir() are used to delete columns, tables, and directories, respectively.

# Delete the `contact_email` column

eq_t.drop_column('contact_email')

eq_t.describe()

# Delete the entire table (cannot be reverted!)

pxt.drop_table('fundamentals.earthquakes')

# Delete the entire directory and all its contents, including any nested
# subdirectories (cannot be reverted)

pxt.drop_dir('fundamentals', force=True)

That’s the end of this section of the Fundamentals tutorial! Continue on to the next section: - Computed Columns

Welcome to Pixeltable

Multimodal AI Datastore

Notebooks

Section 1: Tables and Data Operations

Tables

Filtering and Selecting Data

Tables are Persistent

Tables are Typed

A Real-World Example: Earthquake Data

Extracting Data from Tables into Python/Pandas

Adding Columns

Updating Rows in a Table

Batch Updates

Deleting Rows

Table Versioning

Multimodal Data

Directory Hierarchies

Deleting Columns, Tables, and Directories

Welcome to Pixeltable

Multimodal AI Datastore

Notebooks

​Section 1: Tables and Data Operations

​Tables

​Filtering and Selecting Data

​Tables are Persistent

​Tables are Typed

​A Real-World Example: Earthquake Data

​Extracting Data from Tables into Python/Pandas

​Adding Columns

​Updating Rows in a Table

​Batch Updates

​Deleting Rows

​Table Versioning

​Multimodal Data

​Directory Hierarchies

​Deleting Columns, Tables, and Directories

Section 1: Tables and Data Operations

Tables

Filtering and Selecting Data

Tables are Persistent

Tables are Typed

A Real-World Example: Earthquake Data

Extracting Data from Tables into Python/Pandas

Adding Columns

Updating Rows in a Table

Batch Updates

Deleting Rows

Table Versioning

Multimodal Data

Directory Hierarchies

Deleting Columns, Tables, and Directories