Create, Update, and Delete Data from the Database (Python) - Tutorial

Once you turn a Type into an Entity Type (also known as persistable), you can persist data in a database for later retrieval.

This tutorial explains how to create, update, and delete data from the database using the Python SDK.

Prerequisites

To run this notebook, you need to create your application with a rootPkg of windTurbine or include windTurbine in your list of package dependencies.

Data modeling

Let's assume you've defined a new Type that allows you to keep track of information about wind turbines like their location, power, and manufacturer.

Type

entity type WindTurbine schema name 'WNDT' {
    
    // The location of the wind turbine. 
    location: string
    // Nominal power of the wind turbine (e.g., 150 mega watts).
    power: int
    // The manufacturer of the wind turbine.
    manufacturer: string
}

Create an in-memory instance

Sometimes you just want to create an in-memory instance without persisting it in the database. Here is an example of creating an in-memory instance of the WindTurbine Type:

In [1]:

Python

turbine = c3.WindTurbine(
    location="York",
    power=150,
    manufacturer="Siemens"
)

turbine

Out [1]:

Text

{
  "type" : "WindTurbine",
  "location" : "York",
  "power" : 150,
  "manufacturer" : "Siemens"
}

Create and persist data in the database

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

In [2]:

Python

turbinePersisted = c3.WindTurbine(
    location="Chester",
    power=100,
    manufacturer="Siemens"
).create()

You can read data from a persistable type using Persistable#eval. When calling Persistable#eval from Python you can pass all fields on EvalSpec as keyword arguments. See the C3 AI Type documentation for Persistable (in Python help(c3.Persistable.eval)) for all valid fields for Persistable#eval.

In [3]:

Python

c3.WindTurbine.eval(filter="location=='Chester'")

Out [3]:

ID	location	power	manufacturer
05c1e7d5-1976-475d-bcfa-b93ec21dc5a0	Chester	100	Siemens

In [4]:

Python

## uncomment this line to see the C3 Type documentation for Persistable.eval
# help(c3.Persistable.eval)

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.

In [5]:

Python

# Create wind turbine with a specific ID
turbineWithID = c3.WindTurbine(
    id="specificID",
    location="Southwark",
    power=200,
    manufacturer="Vestas"
).create()

# Or, let .create() generate a unique ID
turbineWithoutID = c3.WindTurbine(
    location="Falmouth",
    power=180,
    manufacturer="Siemens"
).create()

c3.WindTurbine.eval()

Out [5]:

ID	location	power	manufacturer
05c1e7d5-1976-475d-bcfa-b93ec21dc5a0	Chester	100	Siemens
be49372e-aa91-4d80-9c18-35a32c798fe2	Falmouth	180	Siemens
specificID	Southwark	200	Vestas

The second thing happening is that .create() automatically validates your data to make sure it complies with your Type definition.

In this example, the power attribute expects an integer. If you try to create a wind turbine with a different data type for that field, you get an error:

In [6]:

Python

try:
    c3.WindTurbine(power=["one hundred"]).create()
except TypeError as e:
    print(e)
    print("Failed to persist turbine in the database")

Text

must be real number, not list
Failed to persist turbine in the database

Finally, the .create() method also supports customization. By default, the method returns only metadata and the ID field, but you can customize that behavior by specifying an UpsertSpec object.

In [7]:

Python

turbine = c3.WindTurbine(
    id="usingUpsertSpec",
    location="Llanberis",
    power=125,
    manufacturer="Vestas"
).create(c3.UpsertSpec(returnInclude="id, manufacturer"))

turbine

Out [7]:

Text

{
  "type" : "WindTurbine",
  "id" : "usingUpsertSpec",
  "meta" : {
    "fetchInclude" : "[id,manufacturer,version]",
    "fetchType" : "WindTurbine"
  },
  "version" : 1,
  "manufacturer" : "Vestas"
}

Update data in the database

Once you have created an instance in the database, you can update it with Persistable#update.

In [8]:

Python

# instantiate a turbine
turbine = c3.WindTurbine(
    id="toUpdate",
    location="Chelmsford",
    power=175,
    manufacturer="Nordex"
)

# and persist in database
turbine.create()

c3.WindTurbine.eval(ids='toUpdate')

Out [8]:

ID	location	power	manufacturer
toUpdate	Chelmsford	175	Nordex

In [9]:

Python

# instantiate a new turbine with the same ID
turbineUpdated = c3.WindTurbine(
    id="toUpdate",
    power=35,
    manufacturer="GE"
)

# update the database
turbineUpdated.update()

c3.WindTurbine.eval(ids='toUpdate')

Out[9]:

ID	power	manufacturer
toUpdate	35	GE

Note that the updated WindTurbine did not specify a location, so it was updated to None in the database.

If the instance doesn't exist yet in the database, you'll get an error.

In [10]:

Python

# instantiate a new turbine with the same ID
turbineFailedUpdate = c3.WindTurbine(
    id="idNotInDatabase",
    power=100,
    manufacturer="Siemens"
)

# try to update the database
try:
    turbineFailedUpdate.update()
except RuntimeError as e:
    print(e)

In [11]:

Text

Write failed: Cannot update nonexistent object 'idNotInDatabase' for type 'WindTurbine'

Error calling /api/8/WindTurbine/update with [{"type":"WindTurbine","id":"idNotInDatabase","power":100,"manufacturer":"Siemens"}]

Objects are immutable by default: once created, you cannot change the value of a field.

In [11]:

Python

turbine = c3.WindTurbine(
    location="Chelmsford",
    power=175,
    manufacturer="Nordex"
)

# attempt to change a field
try:
    turbine.location = "Bristol"
except AttributeError as e:
    print(e)

Text

Cannot set member attribute 'location' on type 'WindTurbine'.

However, you can make a copy of an object with modified fields using withField or withFields.

In [12]:

Python

# instatiate a turbine
turbine = c3.WindTurbine(
    id="withFieldsUpdate",
    location="Chelmsford",
    power=175,
    manufacturer="Nordex"
)

# persist in database
turbine.create()

# create a copy with modified fields
turbineUpdated = turbine.withFields({
    'location': 'Bristol',
    'manufacturer': 'GE'
})

# update the database
turbineUpdated.update()

c3.WindTurbine.eval(ids='withFieldsUpdate')

Out [12]:

ID	location	power	manufacturer
withFieldsUpdate	Bristol	175	GE

Create or update data in the database

If an instance does not yet exist in the database, .update() throws an error. To update an existing instance or create a new instance if one does not exist, use Persistable#upsert.

In [13]:

Python

# In-memory instance, doesn't exist in the database yet
turbineUpserted = c3.WindTurbine(
    id="toUpsert",
    location="Wolverhampton",
    power=95,
    manufacturer="Nordex"
)

# Since there isn't any wind turbine with that ID
# in the database yet, this one is inserted
turbineUpserted = turbineUpserted.upsert()

c3.WindTurbine.eval(ids=turbineUpserted.id)

Out [13]:

ID	location	power	manufacturer
toUpsert	Wolverhampton	95	Nordex

In [14]:

Python

turbine = c3.WindTurbine(
    id="toUpsert",
    location="Sundridge",
    manufacturer=None,
#     power=135 # power is not set
)

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

c3.WindTurbine.eval(ids="toUpsert")

Out [14]:

ID	location
toUpsert	Sundridge

The .update and .upsert methods update the database with the latest value for all the 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

For instances in which the values of attributes in the data change, use Persistable#merge to ensure that only the values that changed and are defined (that is, not undefined or null are persisted to the database).

In [15]:

Python

# instantiate a turbine
turbineWithPower = c3.WindTurbine(
    id="toMerge", 
    power=225,
    location="Alnwick"
)

# Since turbine 'toMerge' doesn't exist yet,
# .merge() persists it to the database
turbineWithPower.merge()

c3.WindTurbine.eval(ids="toMerge")

Out [15]:

ID	location	power
toMerge	Alnwick	225

In [16]:

Python

# instantiate a turbine with the same id
turbineWithManufacturer = c3.WindTurbine(
    id="toMerge",
    manufacturer="GE",
    location="Aberystwyth"
)

# Turbine 'toMerge' now exists, 
# so .merge() updates it with the new information.
turbineWithManufacturer.merge()

c3.WindTurbine.eval(ids="toMerge")

Out [16]:

ID	location	power	manufacturer
toMerge	Aberystwyth	225	GE

Because the WindTurbine.power attribute was not set in turbineWithManufacturer, the value is not updated in the database.

Delete data from the database

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

In [17]:

Python

# Create a new turbine and persist it into the database
turbine = c3.WindTurbine(
    id="toDelete",
    location="Inverness",
    power=275,
    manufacturer="GE"
).create()


c3.WindTurbine.eval(ids='toDelete')

Out [17]:

ID	location	power	manufacturer
toDelete	Inverness	275	GE

In [18]:

Python

# Remove turbine from the database
turbine.remove()

# there is no longer a turbine with that id
c3.WindTurbine.fetchCount(filter="id=='toDelete'")

Out [18]:

Text

In [19]:

Python

c3.WindTurbine.eval(ids='toDelete')

Out [19]:

Text

––

You can also delete an instance from the database by ID.

In [20]:

Python

# Create a new turbine and persist it in the database
c3.WindTurbine(
    id="toDelete",
    location="Colchester",
    power=140,
    manufacturer="Nordex"
).create()


c3.WindTurbine.eval(ids="toDelete")

Out [20]:

ID	location	power	manufacturer
toDelete	Colchester	140	Nordex

In [21]:

Python

# remove that instance using the same ID
c3.WindTurbine(id='toDelete').remove()

c3.WindTurbine.fetchCount(filter="id=='toDelete'")

Out [21]:

Text

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.

createBatch

In [22]:

Python

# The data for multiple wind turbines
turbines = [
    c3.WindTurbine(id="turbine1", power=70, location="Stanage", manufacturer="Nordex"),
    c3.WindTurbine(id="turbine2", power=55, location="Malham", manufacturer="GE"),
    c3.WindTurbine(id="turbine3", power=95, location="Bowles", manufacturer="Vestas")
]

# Persist multiple turbines in batch in the database
c3.WindTurbine.createBatch(turbines)

c3.WindTurbine.eval(ids=["turbine1","turbine2","turbine3"])

Out [22]:

ID	location	power	manufacturer
turbine1	Stanage	70	Nordex
turbine2	Malham	55	GE
turbine3	Bowles	95	Vestas

updateBatch

In [23]:

Python

# Updated data for multiple wind turbines - with the same IDs
turbines = [
    c3.WindTurbine(id="turbine1", power=170, location="Plymouth"),
    c3.WindTurbine(id="turbine2", power=155, manufacturer="Nordex"),
    c3.WindTurbine(id="turbine3", power=195, location="Portsmouth", manufacturer="Siemens")
]

# Update multiple turbines in batch in the database
c3.WindTurbine.updateBatch(turbines)

c3.WindTurbine.eval(ids=["turbine1","turbine2","turbine3"])

Out [23]:

ID	location	power	manufacturer
turbine1	Plymouth	170	None
turbine2	None	155	Nordex
turbine3	Portsmouth	195	Siemens

upsertBatch

In [24]:

Python

# Updated data for multiple wind turbines - some with the same IDs, some new
turbines = [
    c3.WindTurbine(id="toUpsertBatch", power=270, location="Glasgow", manufacturer="Siemens"),
    c3.WindTurbine(id="turbine2", power=255, manufacturer="GE"),
    c3.WindTurbine(id="turbine3", power=295, location="Gatwick", manufacturer="GE")
]

# Upsert multiple turbines in batch in the database
c3.WindTurbine.upsertBatch(turbines)

c3.WindTurbine.eval(ids=["toUpsertBatch","turbine1","turbine2","turbine3"])

Out [24]:

ID	location	power	manufacturer
toUpsertBatch	Glasgow	270	Siemens
turbine1	Plymouth	170	None
turbine2	None	255	GE
turbine3	Gatwick	295	GE

mergeBatch

In [25]:

Python

# Data to merge for multiple wind turbines - some with the same IDs, some new
turbines = [
    c3.WindTurbine(id="toMergeBatch", power=370, location="Manchester", manufacturer="Siemens"),
    c3.WindTurbine(id="turbine2", power=355),
    c3.WindTurbine(id="turbine3", power=395, location="Moorgate")
]

# Merge multiple turbines in batch in the database
c3.WindTurbine.mergeBatch(turbines)

c3.WindTurbine.eval(ids=["toMergeBatch","turbine1","turbine2","turbine3"])

Out [25]:

ID	location	power	manufacturer
toMergeBatch	Manchester	370	Siemens
turbine1	Plymouth	170	None
turbine2	None	355	GE
turbine3	Moorgate	395	GE

mergeAll

In [26]:

Python

# Update the location of every turbine
toMerge = c3.WindTurbine(location="Milton Keynes")

c3.WindTurbine.mergeAll(toMerge)

c3.WindTurbine.eval()

Out [26]:

ID	location	power	manufacturer
05c1e7d5-1976-475d-bcfa-b93ec21dc5a0	Milton Keynes	100	Siemens
be49372e-aa91-4d80-9c18-35a32c798fe2	Milton Keynes	180	Siemens
specificID	Milton Keynes	200	Vestas
toMerge	Milton Keynes	225	GE
toMergeBatch	Milton Keynes	370	Siemens
toUpdate	Milton Keynes	35	GE
toUpsert	Milton Keynes	nan	None
toUpsertBatch	Milton Keynes	270	Siemens
turbine1	Milton Keynes	170	None
turbine2	Milton Keynes	355	GE
turbine3	Milton Keynes	395	GE
usingUpsertSpec	Milton Keynes	125	Vestas
withFieldsUpdate	Milton Keynes	175	GE

A c3.MergeAllSpec adds additional controls to the .mergeAll operation.

In [27]:

Python

# Update the location of every turbine
toMerge = c3.WindTurbine(location="Aberdeen")

# Only change turbines with power <= 200
c3.WindTurbine.mergeAll(
    toMerge,
    spec=c3.MergeAllSpec(filter="power <= 200")
)

c3.WindTurbine.eval()

Out [27]:

ID	location	power	manufacturer
05c1e7d5-1976-475d-bcfa-b93ec21dc5a0	Aberdeen	100	Siemens
be49372e-aa91-4d80-9c18-35a32c798fe2	Aberdeen	180	Siemens
specificID	Aberdeen	200	Vestas
toMerge	Milton Keynes	225	GE
toMergeBatch	Milton Keynes	370	Siemens
toUpdate	Aberdeen	35	GE
toUpsert	Milton Keynes	nan	None
toUpsertBatch	Milton Keynes	270	Siemens
turbine1	Aberdeen	170	None
turbine2	Milton Keynes	355	GE
turbine3	Milton Keynes	395	GE
usingUpsertSpec	Aberdeen	125	Vestas
withFieldsUpdate	Aberdeen	175	GE

removeBatch

In [28]:

Python

# IDs of turbines to delete from database
turbines = [
    {"id": "turbine1"},
    {"id": "turbine2"},
    {"id": "turbine3"},
    {"id": turbinePersisted.id},
    {"id": "specificID"},
    {"id": turbineWithoutID.id},
    {"id": "usingUpsertSpec"},
    {"id": "withFieldsUpdate"},
    {"id": "toUpsert"},
    {"id": "toMerge"},
    {"id": "toUpdate"},
    {"id": "toUpsertBatch"},
    {"id": "toMergeBatch"},
]

# Remove multiple turbines from the database
c3.WindTurbine.removeBatch(turbines)

c3.WindTurbine.eval()

Out [28]:

Text

––

Copy link to this sectionPrerequisites

Copy link to this sectionData modeling

Copy link to this sectionCreate an in-memory instance

Copy link to this sectionCreate and persist data in the database

Copy link to this sectionUpdate data in the database

Copy link to this sectionCreate or update data in the database

Copy link to this sectionUpdate only specific attributes with merge

Copy link to this sectionDelete data from the database

Copy link to this sectionCreate, update, and delete batches of data

Copy link to this sectioncreateBatch

Copy link to this sectionupdateBatch

Copy link to this sectionupsertBatch

Copy link to this sectionmergeBatch

Copy link to this sectionmergeAll

Copy link to this sectionremoveBatch

Copy link to this sectionSee also

Prerequisites

Data modeling

Create an in-memory instance

Create and persist data in the database

Update data in the database

Create or update data in the database

Update only specific attributes with merge

Delete data from the database

Create, update, and delete batches of data

createBatch

updateBatch

upsertBatch

mergeBatch

mergeAll

removeBatch

See also