Tutorial
========

Installing requirements
-----------------------

For this tutoral, apart from the *Save-to-DB* library itself,
we need *SLQAlchemy* library for working with database. Install both using
this command::

    pip install Save-to-DB SQLAlchemy

First steps
-----------

In this tutorial we are going to save and update stock data into a database. Our
database schema shown on :numref:`tutorial_database_schema`.

.. _tutorial_database_schema:
.. scale_figure:: stdb_tutorial/images/DB-Schema.png
    :width: 560px
    :height: 341px
    :scale-latex: 75%
    :align: center
    :alt: Database schema

    Database schema.

    We have companies that issued stocks (instruments). Companies pay dividends
    on theirs stocks, and the stocks are traded on exchanges under assigned
    ticker codes.

Lets start writing code. Create a new folder called "stdb_tutorial", then
create a file that defines our database connection called "conf.py". We are
going to use SQLite database in this tutorial. The code of the file shown in
:numref:`stdb_tutorial/conf.py`.

.. _stdb_tutorial/conf.py:
.. literalinclude:: stdb_tutorial/conf.py
    :language: python
    :caption: "stdb_tutorial/conf.py" file content.

Add a file that defines our models according to the schema,
see :numref:`stdb_tutorial/models.py`.

.. _stdb_tutorial/models.py:
.. literalinclude:: stdb_tutorial/models.py
    :language: python
    :caption: "stdb_tutorial/models.py" file content.

And finally, before we can move to actually saved data,
a short file with Save-to-DB items in it, :numref:`stdb_tutorial/items.py`.

.. _stdb_tutorial/items.py:
.. literalinclude:: stdb_tutorial/items.py
    :language: python
    :linenos:
    :emphasize-lines: 4
    :caption: "stdb_tutorial/items.py" file content.

Note we have not defined items for all the models. We set
`item_cls_manager.autogenerate` on line 4 to `True`, the missing items will be
auto-generated as needed with default parameters.

Now open a terminal, navigate to "stdb_tutorial" folder and type "python"
command to open python interactive shell. Lets explore one of our items:

.. code-block:: python
    :linenos:
    :emphasize-lines: 2

    >>> import items
    >>> items.InstrumentItem()
    >>> items.InstrumentItem.print_cls_config()
    {'aliases': {},
     'allow_merge_items': False,
     'allow_multi_update': False,
     'autoinject_creators': True,
     'batch_size': None,
     'conversions': {'boolean_false_strings': ('false', 'no', 'off', '0', '-'),
                     'boolean_true_strings': ('true', 'yes', 'on', '1', '+'),
                     'date_formats': '%Y-%m-%d',
                     'datetime_formats': '%Y-%m-%d %H:%M:%S',
                     'decimal_separator': '.',
                     'time_formats': '%H:%M:%S'},
     'creators': [{'company', 'isin_code'}],
     'creators_autoconfig': None,
     'defaults': {},
     'fields': {'isin_code': <ColumnType.STRING: 3>},
     'getters': [{'tickers'}, {'company', 'isin_code'}],
     'getters_autoconfig': True,
     'metadata': {'autogenerated_item_cls': False,
                  'collection_id': None,
                  'setup_completed': True},
     'model_cls': <class 'models.Instrument'>,
     'norewrite_fields': {},
     'nullables': set(),
     'relations': {'company': {'item_cls': <class 'items.CompanyItem'>,
                               'relation_type': <RelationType.MANY_TO_ONE: 3>,
                               'replace_x_to_many': False,
                               'reverse_key': 'instruments'},
                   'dividends': {'item_cls': <class 'items.DividendItem'>,
                                 'relation_type': <RelationType.ONE_TO_MANY: 2>,
                                 'replace_x_to_many': False,
                                 'reverse_key': 'instrument'},
                   'tickers': {'item_cls': <class 'save_to_db.core.item_metaclass.TickerItem'>,
                               'relation_type': <RelationType.ONE_TO_MANY: 2>,
                               'replace_x_to_many': False,
                               'reverse_key': 'instrument'}},
     'update_only_mode': False}

On line 2 we instantiate `InstrumentItem` class to trigger configuration
completion. This also creates missing `TickerItem` class used in relations.
You can read about item configuration in the :ref:`Item` chapter.

Lets load some data into our database, create a new file called
"load_initial.py", see :numref:`stdb_tutorial/load_initial.py`.

.. _stdb_tutorial/load_initial.py:
.. literalinclude:: stdb_tutorial/load_initial.py
    :language: python
    :caption: "stdb_tutorial/load_initial.py" file content.

After running the code, you will have the data in the database.

Now let's take a close look at how the library worked. First it converted
all item fields string values to appropriated data types according to
`conversions` configuration and completed relationships, they are shown on
:numref:`tutorial_items_schema`.

.. _tutorial_items_schema:
.. scale_figure:: stdb_tutorial/images/Items-schema.png
    :width: 506px
    :height: 291px
    :scale-latex: 75%
    :align: center
    :alt: Completed item relations

    Completed item relations.

    We have two companies **C**, each has one instrument **I**, each instrument
    has data about two dividends **D**. Instruments also have tickers **T**
    under which they are being traded on an exchange **E**.

Actual saving and updating is done in groups of the same model classed. Grouped
this way, our items look like this:

.. _tutorial_items_schema_grouped:
.. scale_figure:: stdb_tutorial/images/Items-schema-grouped.png
    :width: 506px
    :height: 166px
    :scale-latex: 75%
    :align: center
    :alt: Completed item relations

    Items grouped by model class.

When persisting out bulk item, Save-to-DB library first put all our items and
all referenced item into separate lists according to model class. The it tried
to get data, fist all companies with one query, then instruments, etc. Since
our database is empty, no data was retrieved. After getting data, it starts to
create new data in the database if possible. In our case the process when like
this:

    #. First the library tried to create new company data in DB, since we
       have enough data (according to "creators" configuration value), library
       issued insert statements for both companies.
    #. Since we already have company models, both instruments models can be
       persisted to the database.
    #. Same logic applies to dividends.
    #. In case of tickers, at this stage we do not have exchange model yet, so
       they are skipped for now.
    #. Exchange model created.
    #. Ticker models created.

Simply saving new data into a database is not difficult, you could use pure
SQLAlchemy with approximately the same amount of code. Now let's run another
code, see :numref:`stdb_tutorial/load_second.py`.

.. _stdb_tutorial/load_second.py:
.. literalinclude:: stdb_tutorial/load_second.py
    :language: python
    :linenos:
    :emphasize-lines: 18
    :caption: "stdb_tutorial/load_second.py" file content.

Note that there is no instrument data here, remember that we added "tickers" as
getters to the instrument item class (tickers have a reference to instruments and
can be got using "exchange" and "ticker_code" values). Save-to-DB library
will not add it automatically as it is a one-to-many relationship, in our
case we assume that it is impossible for a stock that belongs to one company
today, to belong to another company tomorrow.

.. note::
    On line 18 where we use `instrument__tickers` key, we have different
    dividend item each time, meaning that different instrument and ticker item
    will be generated. Items will be merged into one, since `allow_merge_items`
    is set to `True` for the classes.

If you run the code and look into the database, you'll see that the updated and
newly created data is there.