C3 AI Documentation Home

Fetch and Filter Data from the Database Using Python

Once you turn a Type into an Entity Type (also known as persistable), you can persist data in a database for later retrieval. Learn how to create, update, and delete data from the database.

This article covers how to fetch data from the database, using the Python SDK. Learn how to fetch data using the JavaScript SDK.

Data modeling

Let's assume you've defined a Type that allows keeping track of information about smart bulbs like their wattage and lumens:

Type
entity type SmartBulb schema name "SMRT_BLB" {
    bulbType: !string enum('LED', 'INCAN', 'CFL')
    manufacturer: string
    wattage: decimal
    lumens: decimal

    lumensPerWatt: decimal stored calc "lumens / wattage"
}

Also, let's also assume you've added some smart bulbs to the database:

Python
# Create in-memory data about bulbs
bulbs = [
    { "id": "oBx8wE", "bulbType": "LED", "manufacturer": "Philips", "wattage": 17, "lumens": 1200 },
    {                 "bulbType": "LED", "manufacturer": "Philips", "wattage": 14, "lumens": 1150 },
    {                 "bulbType": "CFL", "manufacturer": "Philips", "wattage": 23, "lumens": 1370 },
    {                 "bulbType": "CFL", "manufacturer": "Philips", "wattage": 23, "lumens": 1430 },
    {                 "bulbType": "LED", "manufacturer": "GE"     , "wattage": 11, "lumens": 1100 },
    {                 "bulbType": "LED", "manufacturer": "GE"     , "wattage": 10, "lumens": 1000 }
]

# Insert multiple smart bulbs in batch in the database. Smart bulbs without pre-assigned
# ID will get an automatically generated one
c3.SmartBulb.createBatch(bulbs)

Find a single instance by ID

If you know the unique ID of an instance, you can fetch it from the database using Persistable#get:

Python
bulb = SmartBulb.forId("oBx8wE")
# c3.SmartBulb(id='oBx8wE',meta=c3.Meta(...), version=1, bulbType='LED', ...)

# Update a field of this smart bulb, in-memory
bulb.manufacturer = bulb.manufacturer.upper()

Find all instances that match a criteria

Another common use-case is to find all instances that match a certain criteria. Sometimes you want to just display that list to the user, other times you want to do some data manipulation, but the first step is to fetch data from the database.

You can achieve this with the Persistable#fetch method:

Python
# LED is a string literal, so it needs to have quotes
efficientLedBulbs = c3.SmartBulb.fetch(spec={"filter":"bulbType == 'LED' && lumensPerWatt > 80"})

# The return value is an object with the results, and some metadata about the result set
print(f'There are {efficientLedBulbs.count} efficient LED bulbs')
# There are 3 efficient LED bulbs

# And the actual results are accessible in the .objs field
list(efficientLedIds)
# ['G4lnkc', 'plMFdy', 'q8Syds',]

With the filter parameter, you can filter data using comparison operators, as well as expression engine functions. See FetchFilterSpec#filter for more information.

Besides filter criteria, you can also specify which fields to include, how to sort the results, and other settings for your data fetch:

Python
c3.SmartBulb.fetch(spec={
    # Filter criteria
    "filter": "bulbType == 'LED' && lumensPerWatt > 80",
    # Which fields to return
    "include": "[id, wattage]",
    # How to sort results
    "order": "wattage, descending(id)",
    # Maximum results to return
    "limit": 10
})

As you can tell the filter criteria can get quite complex. Notice how when filtering for LED smart bulbs, you need to quote the string LED. To prevent mistakes, and keep things organized, use the Filter Type:

Python
# Similar query, but this time using the Filter type to prevent mistakes
c3.SmartBulb.fetch({
    "filter": c3.Filter().eq("bulbType", "LED").and_().gt("lumensPerWatt", 80),
    "include": "[id, wattage]",
    "order": "wattage, descending(id)",
    "limit": 10
})

The keyword and is reserved in Python, that's why you use the and_() method.

Check if any instances match criteria

Sometimes you just want to check if there's any instance that matches a given criteria without actually fetching the instances from the database. Use Persistable#exists to get a boolean, or Persistable#fetchCount to get how many instances match the filtering criteria:

Python
# Check if there's at least one smart bulb that complies with the creteria
c3.SmartBulb.exists(spec={"filter": c3.Filter().eq("bulbType", "LED").and_().gt("lumensPerWatt", 80)})
# True

# Or count how many comply with the criteria
c3.SmartBulb.fetchCount(spec={"filter": c3.Filter().eq("bulbType", "LED").and_().gt("lumensPerWatt", 80)})
# 3

It's best practice to use these methods when you don't need the actual results, since they are faster and require less memory to run.

Fetch and transform data

The .fetch() method allows you to query data and filter by relevant criteria, but it doesn't offer a way to transform the results. To transform data, you have to iterate through the results and process them with your own logic.

If you need to query and transform the data, use the Persistable#evaluate method:

Python
efficientLedBulbs = c3.SmartBulb.evaluate({
    # Filter criteria, optional
    "filter": "bulbType == 'LED' && lumensPerWatt > 80",
    # For each instance that matches the criteria, return
    # ID of the instance
    # manufacturer name in upper case
    # lumensPerWatt, rounded to the nearest integer
    "projection": "id, upperCase(manufacturer), round(lumensPerWatt)"
})

The .evaluate() method not only allows you to fetch and filter persisted data, it also allows transforming data using expression engine functions.

Aggregate data

The real power of .evaluate() starts showing when you aggregate data and compute results with the aggregate data:

Python
manufacturerSummary = c3.SmartBulb.evaluate({
    # Filter criteria
    "filter": "bulbType == 'LED' && lumensPerWatt > 80",
    # Aggregate data by manufacturer
    "group": "manufacturer",
    # Return manufacturer and aggregate data about it
    "projection": "manufacturer, min(lumens), max(wattage), avg(lumensPerWatt)",
    # Filter out all manufacturers with lower average efficiency
    "having": "avg(lumensPerWatt) > 85"
})

# The return value is an object with the results, and some metadata about the result set
print(f'There are {manufacturerSummary.count} manufacturers with efficient LED bulbs')
# There are 1 manufacturers with efficient LED bulbs

# And the actual results are accessible in the .tuples field
len(manufacturerSummary.tuples)
# 1

See also

Was this page helpful?