# SQLAlchemy Connection Pool based Snowflake connector for microservices.

Connection pools of SQLAlchemy are lazy, so for each newly established connection we have a 1 sec penalty.

## Raw SQL usage
Since SQLAlchemy ORM sometimes isn't optimal when generating queries, it would be better to use raw SQL via
`session.execute(sql_template, params_to_bind)`. We suggest to store SQL in plain text files, load them via SQLLoader
class and parametrize with identifiers (db, schema, table name) via BaseValidator class. For all the "bindable" params
we should always use binding. Such approach will help us to avoid SQL injections.


## Environment variables
Required env vars:
```bash
export SNOWFLAKE_ACCOUNT=
export SNOWFLAKE_USER=
export SNOWFLAKE_ROLE=
export SNOWFLAKE_DATABASE=
export SNOWFLAKE_SCHEMA=
export SNOWFLAKE_WAREHOUSE=
```

Specify one of:
```bash
export SNOWFLAKE_PASSWORD=
export SNOWFLAKE_PRIVATE_KEY=
```

## Installation
Add the following lines: 
```
-i https://pypi.theorchard.io/pypi/
snowflake_connector_sqlalchemy
```
to `requirements.txt`. Optionally you can fix the version.

## Usage
The `snowflake_conn` module provides you with `SQLLoader` class, as well as raw get_session method, and ready-to-use execute, fetchone, fetchall methods. An example of usage:
```python
"""Sample Snowflake Modeling / Raw SQL usage."""

from oto import response
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base

from snowflake_connector.snowflake_conn import SQLLoader
from snowflake_connector.snowflake_conn import get_session
from snowflake_connector.snowflake_conn import fetchone

sql_loader = SQLLoader(__file__)

BaseModel = declarative_base()


class SnowflakeSampleModel(BaseModel):
    """Encapsulates Track Publisher data."""

    __tablename__ = 'test_sqlalch'

    tuid = Column(Integer, primary_key=True, nullable=False)
    track_name = Column(String(255), nullable=False)


class SampleSnowflakePersister:
    """Handles high level operations."""

    @classmethod
    def create_sample_row(cls):
        """Creates a sample row in the table.

        An example if ORM usage. NOT RECOMMENDED!
        """
        with get_session() as session:
            sample_row = SnowflakeSampleModel(
                tuid='1', track_name='sample_name')
            session.add(sample_row)

    @classmethod
    def get_row_by_tuid(cls, tuid):
        """Fetch a sample row by tuid.

        An example of raw SQL usage (which is preferrable, because we want
        to avoid generated by ORM non-optimal SQL queries.)
        """
        if not tuid:
            return response.Response(message={})

        sql_template = sql_loader.load_query('get_row_by_uuid')
        try:
            res = fetchone(
                sql_template, params={
                    'db': 'DEV_ENGINEERING',
                    'schema': 'BUVAROV', 
                    'tuid': tuid})
            # wrap result with oto.Response before return
            return res
        except Exception:
            # insert Sentry handling here
            raise
```

Also it's possible to use get_session() method as a context manager, which yields session.

### `SQLLoader`
`SQLLoader` is a convenient helper to store SQL queries as plain text `.sql` files, automatically load them, and access them via the load_query() method. 

*Please add meaningful comment/docstring in the beginning of each .sql file!*

SQLLoader enforces usage of `/queries` directory for `.sql` files.
```
    /queries/sf_count_before_start_period.sql
    snowflake_executor.py  # place sql_loader = SQLLoader(__file__) there 
```
```python
# place this to the snowflake_executor.py file
sql_loader = SQLLoader(__file__)

# get query by its filename
sql_loader.load_query('sf_count_before_start_period')
```