.. DO NOT EDIT.
.. THIS FILE WAS AUTOMATICALLY GENERATED BY SPHINX-GALLERY.
.. TO MAKE CHANGES, EDIT THE SOURCE PYTHON FILE:
.. "auto/integrations/flytekit_plugins/sql/sqlite3_integration.py"
.. LINE NUMBERS ARE GIVEN BELOW.

.. only:: html

    .. note::
        :class: sphx-glr-download-link-note

        Click :ref:`here <sphx_glr_download_auto_integrations_flytekit_plugins_sql_sqlite3_integration.py>`
        to download the full example code

.. rst-class:: sphx-glr-example-title

.. _sphx_glr_auto_integrations_flytekit_plugins_sql_sqlite3_integration.py:


.. _integrations_sql_sqlite3:

Sqlite3
#######

The following example shows how you can write SQLite3 queries using the SQLite3Task, which is bundled as part of the
core flytekit. Since SQL Queries are portable across workflows and Flyte installations (as long as the data exists),
this task will always run with a pre-built container, specifically the `flytekit container <https://github.com/flyteorg/flytekit/blob/v0.19.0/Dockerfile.py38>`__
itself. Therefore, users are not required to build a container for SQLite3. You can simply implement the task - register and
execute it immediately.

In some cases local execution is not possible - e.g. Snowflake. But for SQLlite3 local execution is also supported.

.. GENERATED FROM PYTHON SOURCE LINES 15-19

.. code-block:: default

    import pandas
    from flytekit import kwtypes, task, workflow
    from flytekit.extras.sqlite3.task import SQLite3Config, SQLite3Task


.. GENERATED FROM PYTHON SOURCE LINES 20-22

SQLite3 queries in flyte produce a Schema output.
https://www.sqlitetutorial.net/sqlite-sample-database/

.. GENERATED FROM PYTHON SOURCE LINES 22-26

.. code-block:: default

    from flytekit.types.schema import FlyteSchema

    EXAMPLE_DB = "https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip"


.. GENERATED FROM PYTHON SOURCE LINES 27-29

the task is declared as a regular task. Alternatively it can be declared within a workflow just at the point of using
it (example later)

.. GENERATED FROM PYTHON SOURCE LINES 29-38

.. code-block:: default

    sql_task = SQLite3Task(
        name="cookbook.sqlite3.sample",
        query_template="select TrackId, Name from tracks limit {{.inputs.limit}}",
        inputs=kwtypes(limit=int),
        output_schema_type=FlyteSchema[kwtypes(TrackId=int, Name=str)],
        task_config=SQLite3Config(uri=EXAMPLE_DB, compressed=True),
    )



.. GENERATED FROM PYTHON SOURCE LINES 39-40

As described elsewhere FlyteSchemas can be easily be received as pandas Dataframe and Flyte will autoconvert them

.. GENERATED FROM PYTHON SOURCE LINES 40-45

.. code-block:: default

    @task
    def print_and_count_columns(df: pandas.DataFrame) -> int:
        return len(df[df.columns[0]])



.. GENERATED FROM PYTHON SOURCE LINES 46-47

The task can be used normally in the workflow, passing the declared inputs

.. GENERATED FROM PYTHON SOURCE LINES 47-52

.. code-block:: default

    @workflow
    def wf() -> int:
        return print_and_count_columns(df=sql_task(limit=100))



.. GENERATED FROM PYTHON SOURCE LINES 53-54

It can also be executed locally.

.. GENERATED FROM PYTHON SOURCE LINES 54-59

.. code-block:: default

    if __name__ == "__main__":
        print(f"Running {__file__} main...")
        print(f"Running main {wf()}")



.. GENERATED FROM PYTHON SOURCE LINES 60-61

As mentioned earlier it is possible to also write the SQL Task inline as follows

.. GENERATED FROM PYTHON SOURCE LINES 61-73

.. code-block:: default

    @workflow
    def query_wf() -> int:
        df = SQLite3Task(
            name="cookbook.sqlite3.sample_inline",
            query_template="select TrackId, Name from tracks limit {{.inputs.limit}}",
            inputs=kwtypes(limit=int),
            output_schema_type=FlyteSchema[kwtypes(TrackId=int, Name=str)],
            task_config=SQLite3Config(uri=EXAMPLE_DB, compressed=True),
        )(limit=100)
        return print_and_count_columns(df=df)



.. GENERATED FROM PYTHON SOURCE LINES 74-75

It can also be executed locally.

.. GENERATED FROM PYTHON SOURCE LINES 75-78

.. code-block:: default

    if __name__ == "__main__":
        print(f"Running {__file__} main...")
        print(f"Running main {query_wf()}")


.. rst-class:: sphx-glr-timing

   **Total running time of the script:** ( 0 minutes  0.000 seconds)


.. _sphx_glr_download_auto_integrations_flytekit_plugins_sql_sqlite3_integration.py:

.. only:: html

  .. container:: sphx-glr-footer sphx-glr-footer-example


    .. container:: sphx-glr-download sphx-glr-download-python

      :download:`Download Python source code: sqlite3_integration.py <sqlite3_integration.py>`

    .. container:: sphx-glr-download sphx-glr-download-jupyter

      :download:`Download Jupyter notebook: sqlite3_integration.ipynb <sqlite3_integration.ipynb>`


.. only:: html

 .. rst-class:: sphx-glr-signature

    `Gallery generated by Sphinx-Gallery <https://sphinx-gallery.github.io>`_