<jupyter_start><jupyter_text>Fugue-sql and Python`Fugue-sql` integrates with Python by creating extensions and applying them in the `%%fsql` cells. This tutorial will show an example of applying a [Fugue Transformer](../transformer.ipynb). Additionally, we'll see the simpler ways that the Python interface and `fugue-sql` interact with each other.<jupyter_code>from fugue_notebook import setup
setup()
import pandas as pd
df = pd.DataFrame({"number": [0,1], "word": ["hello", "world"]})<jupyter_output><empty_output><jupyter_text>[Jinja](https://jinja.palletsprojects.com/) Templating to Insert VariablesBefore going to functions, the simplest way `fugue-sql` integrates with Python is through [Jinja](https://jinja.palletsprojects.com/) templating. `DataFrames` defined previously are automatically accessible by the `DAG`. Variables on the other hand, need to be passed with [Jinja](https://jinja.palletsprojects.com/) templating.<jupyter_code>x=0
%%fsql
SELECT * FROM df WHERE number={{x}}  # see we can use variable x directly
PRINT<jupyter_output><empty_output><jupyter_text>Using [Transformers](../transformer.ipynb)`Fugue` has different [extensions](extensions.ipynb) that allow Python to interact with SQL. `Transformer` is most commonly used because it modifies that data in a dataframe. Below we create a `Transformer` in Python and apply it in the `fugue-sql`. More on `Transformer` syntax can be found here.<jupyter_code>import re
from typing import Iterable, Dict, Any

# schema: *, vowel_count:int, consonant_count:int
def letter_count(df:Iterable[Dict[str,Any]]) -> Iterable[Dict[str,Any]]:
    for row in df:
        row['vowel_count'] = len(re.findall(r'[aeiou]', row['word'], flags=re.IGNORECASE))
        space_count = len(re.findall(r'[ -]', row['word'], flags=re.IGNORECASE))
        row['consonant_count'] = len(row['word']) - row['vowel_count'] - space_count
        yield row
%%fsql
SELECT * 
FROM df 
WHERE number=1
TRANSFORM USING letter_count
PRINT<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12113659022931206, 'fraction_numerical': 0.0019940179461615153, 'mean_word_length': 5.757575757575758, 'pattern_counts': {'":': 2, '<': 13, '<?xml version=': 0, '>': 14, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '194953', 'license': 'Apache-2.0', 'n_tokens_mistral': 651, 'n_tokens_neox': 633, 'n_words': 196, 'path': 'tutorials/fugue_sql/python.ipynb', 'repo_name': 'CRSantiago/tutorials', 'size': '2006'}