Databases in TouchDesigner

There are many times where we have to deal with large amounts of data in TouchDesigner. Whether you’re working with social media or large data sets provided by clients, you’ve likely experienced how tricky it can be to manage this data in DATs. In this blog post, I’ll show you how you can take advantage of industry standard databases in TouchDesigner.

Getting started

We’ll be primarily interfacing with our database via Python. In this case, we’ll also be using the built-in library inside of Python to create and manage an SQLite database. SQLite is a format of database that is lightweight and works with the same syntax that most SQL databases work with. So if you’re ever done any data science work or worked with SQL databases like MySQL, then you’ll be familiar to a lot of this process. The first thing we’re going to do is open a new project and drop a Text DAT into it and write the following at the top:

import sqlite3

That statement imports the sqlite3 library so we can access and manage our database inside of our Text DAT. The next thing we’re going to do is initiate a connection with our database:

connection = sqlite3.connect('test.db')

What this does is create a Connection object we’ll use to send commands to the database. If the .db file didn’t already exist, this command also creates that new database file for you. In this example, I haven’t provided a path, so it will create it in the same folder as my project file.

Now we need to create a cursor inside of the database. An easy way to think about this is that we just opened a text file but we haven’t clicked inside of it yet, and creating our cursor allows us to send commands to our database:

cursor = connection.cursor()

Since we haven’t created an actual database yet, the best thing to do first is create a table inside of the database. In this example, I’m going to make a simple table named mydata and inside will be 2 columns, one is an integer column named myid and the second is a text column named namedata. We can do that with the following syntax:

create_command = 'CREATE TABLE IF NOT EXISTS mydata(myid INTEGER, namedata TEXT)'

This is a common thing you’re going to see a lot, which is that you’ll create variables in Python and their value will be a string that represents the SQL command you’d like to send to the database. These SQL commands work in any SQL-compatible database, so not just in SQLite. This means there’s a wealth of resources online that can help you build complex SQL commands interactively and test them in simulated databases. You’ll also notice a lot of capital letters in SQL commands, which is a common practice when people are creating SQL commands.

Once we have our command ready, we can tell the cursor object to execute it:

cursor.execute(create_command)

Any type of command we want to send to the database from Python in TouchDesigner will look like this. Step 1 is building your string SQL command and step 2 is executing it. The final element of creating our table is committing our change and closing our connection:

connection.commit()
connection.close()

Committing your changes ensures that they are saved in the actual database file, and just like any other file that we may open/manipulate, we have to close it when we’re finished. Here’s a full recap of our database initialization script that only has to run once the first time we’re going to be using this database:

import sqlite3

# connect to database and create a cursor
connection = sqlite3.connect('test.db')
cursor = connection.cursor()

# create our SQL command as a string
create_command = 'CREATE TABLE IF NOT EXISTS mydata(myid INTEGER, namedata TEXT)'

# execute the create command
cursor.execute(create_command)

# commit changes
connection.commit()

# close connection
connection.close()

If you need to brush up on your Python knowledge, refer back to our tutorial about Python Optimizations & Tips:

Adding data to our databases in TouchDesigner

With our table initialized, we can write some sample data to it. I’ll create another Text DAT that will be used for writing my test data. Luckily, the first three lines of our code are the same as our previous code! We’ll first import sqlite3, create a connection, then create a cursor:

import sqlite3

# connect to database and create a cursor
connection = sqlite3.connect('test.db')
cursor = connection.cursor()

Similarly to before, we need to create a command, and in this case instead of using something like CREATE TABLE, we’re going to use the command INSERT which allows us to insert data into a table. Again, if you need help with SQL command making, your best references will be a web search away! We’re going to create a for loop that will iterate 10,000 times and write the iteration number into the first column and the string hello! into the second column. Like before, when we create our command as a string, we then need to execute it:

for i in range(10000):
	# create our SQL command as a string
	command = "INSERT INTO mydata VALUES (?, ?)"

	# execute the command
	cursor.execute(command, (i, 'hello') )

One thing you’ll notice here is that the SQL commands are generally understandable if you say them outloud. We’re inserting into the mydata table the values of (?, ?). The question marks in this case function as placeholders that we can feed values into when we go to execute the command. You can see in this case when we go to execute our command, the second argument we send with it is a tuple that contains the data that will replace the ? in order.

After that, it’s back to business as usual! We want to commit our changes and close the connection:

# commit changes
connection.commit()

# close connection
connection.close()

I hope you’re starting to see how simple and elegantly you can deal with SQL databases in TouchDesigner! It ends up being a few lines to set things up, making a command/executing it, then committing and closing the connection. Here’s the full script which writes the demo data into our table for reference:

import sqlite3

# connect to database and create a cursor
connection = sqlite3.connect('test.db')
cursor = connection.cursor()


for i in range(10000):
	# create our SQL command as a string
	command = "INSERT INTO mydata VALUES (?, ?)"

	# execute the command
	cursor.execute(command, (i, 'hello') )

# commit changes
connection.commit()

# close connection
connection.close()

Reading data!

The final thing you’ll need to know how to do is read data back from the database. You may have guessed….this is going to look very similar to the last few scripts, except for the command we’re making! So we’ll start with the same setup lines:

import sqlite3

# connect to database and create a cursor
connection = sqlite3.connect('test.db')
cursor = connection.cursor()

Then we’ll need to format a command to access the data. We’ll use a common SQL command called SELECT:

command = "SELECT * FROM mydata WHERE myid BETWEEN ? AND ?"

Similar to before, we can understand the meaning of this just by reading through it! We can see this is selecting all (*) the data from mydata table where the id value is between two variables. Just like we did before, we’ll feed the variables as the second argument of the execute command. So our next few lines will be defining the variable values for the id range selection, as well as running our command:

# execute the command
start_range = 250
end_range = 300
rows = cursor.execute(command, (start_range, end_range)).fetchall()

Here we created two variables for the start and end range of the data we’d like to select. Then we feed that into the cursor.execute() along with the command. Now there’s 2 differences here from before. The first is that after our command is done executing, we actually want the data back so we can use it in TouchDesigner. The first step is adding .fetchall() to the end of the execute command. This will return all of the results of the search to us and thus, we need somewhere to keep it which is why we made a variable called rows and assign it’s value as the execute command. So in simple terms, we made a new variable called rows, told the cursor to execute a search command with the custom values, and then assign all the data we get back as the values of our rows variables.

The final thing from here is to print our the data and perform the normal closing of our connection. In this case, since we didn’t change any of the data, we don’t need to worry about committing:

# print the data or do things with it!
print(rows)

# close connection
connection.close()

That’s it! With these simple types of scripts you can do all the fundamental activities you’ll do with a database! Here’s the reference for the reading script, and below that a sample file you can experiment with:

import sqlite3

# connect to database and create a cursor
connection = sqlite3.connect('test.db')
cursor = connection.cursor()

command = "SELECT * FROM mydata WHERE myid BETWEEN ? AND ?"

# execute the command
start_range = 250
end_range = 300
rows = cursor.execute(command, (start_range, end_range)).fetchall()

# print the data or do things with it!
print(rows)

# close connection
connection.close()

Wrap up: Databases in TouchDesigner

Most industries like interactive tech who deal with large amounts of data always end up using databases, and now you can too! There’s no reason you shouldn’t use an SQLite database in TouchDesigner if you’re dealing with large amount of data. Hopefully this blog post makes it quick and easy to get started with! Enjoy!