Saving Simulation Results to a Database
=======================================

There are a number of reasons why you might want to save simulation
results to a database:

-  Observing changes in model output over the course of model
   development
-  Cacheing runs of a model to speed later analysis or display,
   especially in large models
-  Creating a traceable record of your work

It’s relatively easy to set up a sequel database and commit runs output
to it. This demo uses sqlite, which creates a database in a local file.

.. code:: ipython3

    import sqlite3
    import numpy as np
    import pysd

Ingredients
-----------

Model
^^^^^

We’ll use the simple teacup model for this experiment, and we’ll ask for
the value at integer times from ``[0..29]``.

.. code:: ipython3

    model = pysd.read_vensim('../../models/Teacup/Teacup.mdl')
    tseries = range(30)

A database
^^^^^^^^^^

In this example, we’ll create a database which will be saved in the
working directory as ``example.db``. We populate its columns with two
columns for storing the parameter values that we’ll change from run to
run, and then a column for each timestamp value we intend to save:

.. code:: ipython3

    conn = sqlite3.connect('example.db')
    c = conn.cursor()
    
    # Create table
    c.execute('''CREATE TABLE simulations
                 (room_temperature real, init_teacup_temperature real, 
                  %s ) '''%', '.join(['t%i real'%i for i in tseries]));

####Parameters We want to save the output of our model when driven with
a variety of parameters. For demonstration, we’ll set these randomly:

.. code:: ipython3

    room_temps = np.random.normal(75, 5, 100)
    init_tea_temps = np.random.normal(175, 15, 100)

The Recipe
----------

We’re now ready to simulate our model with the various parameters. After
execution, we construct a SQL insert querry containing each of the
returned values, and commit it to the database.

.. code:: ipython3

    
    for room_temp, init_tea_temp in zip(room_temps, init_tea_temps):
        output = model.run(params={'room_temperature':room_temp}, 
                           initial_condition=(0,{'teacup_temperature':init_tea_temp}),
                           return_timestamps=tseries)
        
        c.execute("INSERT INTO simulations VALUES (%i,%i,%s)"%
                  (room_temp, init_tea_temp, ', '.join(output['Teacup Temperature'].apply(str))))
    
        conn.commit()


We can see that the result was added properly by fetching a record:

.. code:: ipython3

    c.execute('SELECT * FROM simulations')
    c.fetchone()




.. parsed-literal::

    (73.0,
     161.0,
     161.3004136013599,
     152.93211154727877,
     145.3649295009812,
     138.52217409471785,
     132.33449402260226,
     126.73917716532998,
     121.67951500303411,
     117.10422787460355,
     112.96694525846864,
     109.22573580750158,
     105.84268237493694,
     102.78349772420083,
     100.01717702786915,
     97.51568363383308,
     95.25366491391246,
     93.2081953150439,
     91.35854400886798,
     89.68596478484497,
     88.17350605746613,
     86.80583906198456,
     85.56910249742967,
     84.45076204236265,
     83.43948331956555,
     82.52501702216168,
     81.69809503692045,
     80.95033651195727,
     80.27416291682422,
     79.66272123412638,
     79.10981450421062,
     78.60983901899793)



Finally, we must remember to close our connection to the database:

.. code:: ipython3

    conn.close()

.. code:: ipython3

    #remove the database file when we are finished with it.
    !rm example.db