Decorators API Reference¶
The sql object provides decorator methods for SQL operations.
sql.dataframe¶
Decorator for SQL queries that return a pandas DataFrame.
Now TaskFlow-compatible: creates a proper task that can be used in dependencies and data passing.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
|
Optional[str]
|
Connection ID for simple queries |
None
|
|
Optional[str]
|
Path to SQL file (relative to sql_files_path) |
None
|
|
Any
|
Variables to pass to Jinja template |
{}
|
sql.query¶
Decorator for SQL queries.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
|
Optional[Table]
|
Table to write results to (optional) |
None
|
|
Optional[str]
|
Connection ID for simple queries without table parameters |
None
|
|
Optional[str]
|
Path to SQL file (relative to sql_files_path) |
None
|
|
bool
|
If True, simulate the operation without writing data |
False
|
|
bool
|
If True, truncate table before writing |
False
|
|
Any
|
Variables to pass to Jinja template. Non-Jinja variables are passed as kwargs to the operator for dynamic task naming. |
{}
|
sql.append¶
Decorator for SQL queries that append data to a destination table.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
|
Table
|
Table to append data to |
required |
|
Optional[str]
|
Connection ID for the source database |
None
|
|
Optional[str]
|
Path to SQL file (relative to sql_files_path) |
None
|
|
bool
|
If True, simulate the operation without writing data |
False
|
|
Any
|
Variables to pass to Jinja template. Non-Jinja variables are passed as kwargs to the operator for dynamic task naming. |
{}
|
sql.replace¶
Decorator for SQL operations that replace table content.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
|
Table
|
Table to replace content in |
required |
|
Optional[str]
|
Connection ID for the source database |
None
|
|
Optional[str]
|
Path to SQL file (relative to sql_files_path) |
None
|
|
str
|
Replace method - 'replace' (default) or 'truncate' |
'replace'
|
|
bool
|
If True, simulate the operation without writing data |
False
|
|
Any
|
Variables to pass to Jinja template. Non-Jinja variables are passed as kwargs to the operator for dynamic task naming. |
{}
|
sql.truncate¶
Decorator for SQL operations that truncate table content and reload.
Truncates the table while preserving table structure and resetting sequences.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
|
Table
|
Table to truncate and reload |
required |
|
Optional[str]
|
Connection ID for the source database |
None
|
|
Optional[str]
|
Path to SQL file (relative to sql_files_path) |
None
|
|
bool
|
If True, simulate the operation without writing data |
False
|
|
Any
|
Variables to pass to Jinja template. Non-Jinja variables are passed as kwargs to the operator for dynamic task naming. |
{}
|
sql.merge¶
Decorator for SQL operations that merge/upsert into tables.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
|
Table
|
Table to merge data into |
required |
|
List[str]
|
Columns to use for conflict resolution (ON clause) |
required |
|
Optional[List[str]]
|
Columns to update on conflict (optional, defaults to all non-conflict columns) |
None
|
|
Optional[str]
|
Connection ID for the source database |
None
|
|
Optional[str]
|
Path to SQL file (relative to sql_files_path) |
None
|
|
bool
|
If True, truncate the table before performing merge |
False
|
|
bool
|
If True, simulate the operation without writing data |
False
|
|
Any
|
Variables to pass to Jinja template. Non-Jinja variables are passed as kwargs to the operator for dynamic task naming. |
{}
|
sql.load_dataframe¶
Decorator for functions that return a DataFrame to be loaded into a table, or for directly loading a provided DataFrame.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
|
Table
|
Table to write DataFrame to |
required |
|
Optional[str]
|
Custom timestamp column name (optional) |
None
|
|
str
|
How to behave if table exists ('append', 'replace', 'truncate', 'fail') |
'append'
|
|
Pre-existing DataFrame to load (optional) |
None
|
|
|
bool
|
If True, simulate the operation without writing data |
False
|
|
Extra keyword arguments for dynamic task naming and mapping |
{}
|
Example 1 - Function that returns DataFrame
@sql.load_dataframe( output_table=Table( conn_id="postgres_conn", table_name="analytics.users" ), if_exists='replace' ) def create_user_summary(): # Your DataFrame creation logic return pd.DataFrame({ 'user_id': [1, 2, 3], 'name': ['Alice', 'Bob', 'Charlie'] })
Example 2 - Direct DataFrame loading
@sql.load_dataframe( output_table=Table( conn_id="postgres_conn", table_name="analytics.users" ), if_exists='replace', dataframe=my_existing_df ) def load_existing_data(): pass # Function body can be empty when dataframe is provided