Creating Custom Database Tables¶
Building Custom Tables¶
Review the tutorial on python inheritance (from the
How tables are builttutorial in the
Databasesection). This topic is important to understanding when building new tables.
In your new app, open the file
example_app/models.pyand you will see two example tables defined for you. This is all you need to build tables! Note, how we also use
Calculationto add a bunch of columns to in the 2nd table:
class MyCustomTable1(DatabaseTable): custom_column_01 = table_column.IntegerField() custom_column_02 = table_column.FloatField() class MyCustomTable2(Structure, Calculation): input_01 = table_column.FloatField() input_02 = table_column.BooleanField() output_01 = table_column.FloatField(null=True, blank=True) output_02 = table_column.BooleanField(null=True, blank=True)
We only demonstrate
Calculationin the examples shown, but there are many more
base_data_typesyou can utilize. Make sure to review our guides in the
simmate.databasemodule for more information.
In Django (which Simmate uses under the hood), a
DatabaseTableis referred to as a
Model. Therefore, a model and table can be considered the same. As we're using Django, the file name
models.pymust remain as-is. That's where Django searches for your custom database tables.
These examples will build tables with the following columns:
# TABLE 1 # TABLE 2
Note... These tables are not actually in your database yet. You can check your tables in DBeaver to confirm this. Next, we will actually "apply" them to our database.
Applying Changes to the Database¶
example_app/models.pyfile and review the example tables.
Ensure the Simmate configuration includes your new app:
simmate config show --user-only
Add these tables to the database by updating it:
simmate database update
Verify the output of your
updatecommand. You should see that your new app and tables have been added.
Migrations for 'example_app': example_app/migrations/0001_initial.py - Create model MyCustomTable1 - Create model MyCustomTable2
All changes are tracked as "migrations" and stored in an extra folder within your app. Typically, you can just continue to let these files build because migrations are designed to be committed to, and distributed as part of, your codebase. Make sure to read & understand Django's official guides on how to interact with these files.
Check DBeaver and you should see new tables such as
In a separate python script, let's ensure we can view the new tables in your database:
from simmate.database import connect from example_app.models import MyCustomTable1, MyCustomTable2 # both tables should output 0 because we haven't added data yet n1 = MyCustomTable1.objects.count() n2 = MyCustomTable2.objects.count()
You now have your tables in the database!
If you ever change your tables (such as adding or removing a column), you will need to re-update your database. Therefore, whenever you modify the
models.pyfile, make sure to run
simmate database updatefor your changes to take effect in your database.
Adding Data to Your Tables¶
For basic tables, you can set each column manually:
from simmate.database import connect
from example_app.models import MyCustomTable1
new_entry = MyCustomTable1(
Once saved, try viewing your new row in DBeaver!
For more complex tables that include Simmate data types, we automatically populate data using the
from simmate.database import connect
from simmate.toolkit import Structure
from example_app.models import MyCustomTable2
nacl = Structure.from_file("NaCl.cif")
new_entry = MyCustomTable2.from_toolkit(structure=nacl)
Once saved, try viewing your new row in DBeaver. You will see many columns were filled out automatically. Some columns are still empty though (such as those from a
Calculation). We will learn how to populate these tables in the next tutorial on
Adding app workflows
Manually populating your table with data is often unnecessary. Instead, you can connect your database to a workflow, which will automatically fill it with data. We'll cover this in a later step.
Searching Your New Data¶
You can filter results just like any other table. If you need a refresher, refer back to the earlier database tutorial or refer to the Full Guides for more info:
df = MyCustomTable2.objects.filter(nsites__lte=10).to_dataframe()