C3 AI Documentation Home

Create, Update, and Delete 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.

This page explains how to create, update, and delete data from the database using the Python SDK. Check the JavaScript SDK version.

Data modeling

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

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

Create an in-memory instance

Sometimes you just want to create an in-memory instance without persisting it in the database. Use the type constructor to create a new in-memory instance:

Python
bulb = c3.SmartBulb(bulbType="LED", manufacturer="Philips")

Create and persist data in the database

Use the Persistable#create method to create a new instance, and persist it in the database:

Python
bulb = c3.SmartBulb(bulbType="LED", manufacturer="Philips").create()

There are two things happening when you invoke the .create() method. The first is that if you don't specify a unique ID, one is automatically created for you:

Python
# Create smart bulb with a specific ID
bulb = c3.SmartBulb(id="jW2dGw", bulbType="LED", manufacturer="Philips").create()

# Or, let .create() generate a unique ID
bulb = c3.SmartBulb(bulbType="LED", manufacturer="Philips").create()
# c3.SmartBulb(id='bb1e23b5-c35c-493e-9434-d883ea4470c1', meta=c3.Meta(...), version=1)

The second thing happening is that .create() automatically validates your data to make sure it complies with your Type definition. In this example the bulbType attribute is mandatory and only accepts a set of valid values.

If you try to create a smart bulb without that field, or with a string that's different from the ones allowed, you get an error:

Python
try:
    c3.SmartBulb().create()
except:
    print("Failed to persist bulb in the database")
# SmartBulb.create: Write failed: Required field SmartBulb.bulbType not set.

Finally, the .create() method also supports customization. By default the method returns all fields that are not null, but you can customize that behavior by specifying an UpsertSpec object:

Python
bulb = c3.SmartBulb(bulbType="LED", manufacturer="Philips").create({"returnInclude": "id"})

bulb.id
# "58062c46-8b0f-4f65-a2fa-b14d6157911f"

type(bulb.manufacturer)
# NoneType

Update data in the database

Once you have created an instance in the database, you can update it with Persistable#update. If the instance doesn't exist yet in the database, you'll get an error:

Python
var bulb = SmartBulb.make({bulbType: "LED", manufacturer: "Philips"}).create();
bulb = bulb.withbulbType("CFL")

// Update the database with the latest values
bulb = bulb.update();

Create or update data in the database

While .update() throws an error if the instance doesn't exist yet in the database, sometimes it's useful to create a new instance in the database if one doesn't exist yet. Use Persistable#upsert to achieve this:

Python
# In-memory instance, doesn't exist in the database yet
bulb = c3.SmartBulb(bulbType="LED", manufacturer="Philips")

# We're not specifying an ID, so a unique one is generated. Then since there isn't
# any smart bulb with that ID in the database yet, this one is inserted
bulb = bulb.upsert()

bulb.bulbType = "CFL"
bulb.manufacturer = None

# Upsert again, but this time a smart bulb with this ID already exists, so we update
# it with the latest values
bulb = bulb.upsert()

Keep in mind that .update() updates the database with the latest value for all attributes of the instance. If some of those attributes are set with a null value, the database is updated with null.

Update only specific attributes with merge

Sometimes you want to persist in the databases changes to attributes only if the attribute was set with a value, and not persist any changes to attributes that are not defined or are set to null.

You can use Persistable#merge to achieve this:

Python
bulbWithType = c3.SmartBulb(id="2x3fMq", bulbType="LED")
bulbWithManufacturer = c3.SmartBulb(id="2x3fMq", manufacturer="Philips")

# Persist bulb 2x3fMq in the database
bulbWithType = bulbWithType.create()

# Create bulb 2x3fMq if it doesn't exist yet, or update it to have information about the manufacturer.
# Since the bulbType attribute is set to null, don't update the value that's in the database
bulbWithAllAttributes = bulbWithManufacturer.merge()

Delete data from the database

Use the Persistable#remove method to delete instances from the database:

Python
# Create new smart bulb and insert it into the database
bulb = c3.SmartBulb(id="YrmW5p", bulbType="LED").create()

# Remove smart bulb YrmW5p from the database
bulb.remove()

Create, update, and delete batches of data

All data manipulation methods have a batch counter-part so you can operate on multiple instances at the same time:

Python
# The data for multiple smart bulbs
bulbs = [
    {"id": "gerXzN", "bulbType": "LED", "manufacturer": "Philips"},
    {"id": "QtY2b1", "bulbType": "LED", "manufacturer": "GE"},
    {"id": "3P6kEf", "bulbType": "LED", "manufacturer": "Sylvania"}
]

# Insert multiple smart bulbs in batch in the database
c3.SmartBulb.createBatch(bulbs)

# Remove all new instances from the database
c3.SmartBulb.removeAll()

Work with fields that share reserved words in Python

Let's say we are working with data where the field names share reserved words in python. For instance, the SmartBulbToFixtureRelation Type has to and from fields that need to be populated. From what we learned above, that would look like:

Python
c3.SmartBulbToFixtureRelation(id = "someId", from = c3.SmartBulb.get('fromId'), to = c3.Fixture.get('toId'))

Since from is a reserved keyword in Python, when we execute the code above, we get the following error:

Python
c3.SmartBulbToFixtureRelation(id = "someId", from = c3.SmartBulb.get('fromId'), to = c3.Fixture.get('toId'))
                                             ^
SyntaxError: invalid syntax

For any variable assignment that has a reserved word, add an underscore at the end of the variable name as shown below:

Python
c3.SmartBulbToFixtureRelation(id = "someId", from_ = c3.SmartBulb.get('fromId'), to = c3.Fixture.get('toId'))

See also

Was this page helpful?