0
# SQL Tables and Querying
1

2
Panoramix provides a Table model for managing SQL database tables with rich querying capabilities, metric definitions, and metadata management. Tables serve as the primary interface for traditional database analytics.
3

4
## Capabilities
5

6
### Table Management
7

8
Register and configure SQL tables from connected databases with metadata synchronization and column discovery.
9

10
```python { .api }
11
class Table(Model, Queryable, AuditMixin):
12
    """
13
    SQL table model for data visualization and querying.
14
    
15
    Attributes:
16
        id (int): Primary key
17
        table_name (str): Name of the database table
18
        main_datetime_column_id (int): Foreign key to main datetime column
19
        main_datetime_column (TableColumn): Primary date/time column for time-based queries
20
        default_endpoint (str): Default visualization endpoint
21
        database_id (int): Foreign key to Database
22
        database (Database): Reference to parent database
23
    """
24
    
25
    def query(self, groupby, metrics, granularity, from_dttm, to_dttm,
26
             limit_spec=None, filter=None, is_timeseries=True,
27
             timeseries_limit=15, row_limit=None):
28
        """
29
        Execute SQL query with aggregations and filters.
30
        
31
        Args:
32
            groupby (list): List of columns to group by
33
            metrics (list): List of metrics to calculate  
34
            granularity (str): Time granularity for grouping
35
            from_dttm (datetime): Start datetime for time-based queries
36
            to_dttm (datetime): End datetime for time-based queries
37
            limit_spec (dict, optional): Limit specification
38
            filter (list, optional): List of filter conditions
39
            is_timeseries (bool): Whether query is time-based (default True)
40
            timeseries_limit (int): Limit for timeseries results (default 15)
41
            row_limit (int, optional): Maximum number of rows to return
42
            
43
        Returns:
44
            QueryResult: Named tuple with df, query, and duration
45
        """
46
    
47
    def fetch_metadata(self):
48
        """
49
        Synchronize table structure from the database.
50
        
51
        Discovers columns, data types, and other metadata
52
        from the actual database table structure.
53
        """
54
    
55
    @property
56
    def name(self):
57
        """Get the table name."""
58
        return self.table_name
59
    
60
    @property
61
    def table_link(self):
62
        """Get HTML link to the table view."""
63
        url = "/panoramix/table/{}/".format(self.id)
64
        return '<a href="{url}">{self.table_name}</a>'.format(**locals())
65
    
66
    @property
67
    def metrics_combo(self):
68
        """Get list of metric name/verbose name tuples for forms."""
69
        return sorted([
70
            (m.metric_name, m.verbose_name)
71
            for m in self.metrics
72
        ], key=lambda x: x[1])
73
    
74
    def __repr__(self):
75
        """String representation of the table."""
76
        return self.table_name
77
```
78

79
### SQL Metrics Definition
80

81
Define reusable metrics for SQL tables using aggregation functions and custom SQL expressions.
82

83
```python { .api }
84
class SqlMetric(Model, AuditMixin):
85
    """
86
    SQL-based metric definition for tables.
87
    
88
    Attributes:
89
        id (int): Primary key
90
        metric_name (str): Unique metric identifier
91
        verbose_name (str): Human-readable metric name
92
        metric_type (str): Type of metric ('count', 'sum', 'avg', etc.)
93
        expression (str): SQL expression for the metric
94
        description (str): Metric description
95
        table_id (int): Foreign key to Table
96
        table (Table): Reference to parent table
97
    """
98
```
99

100
### Table Columns Management
101

102
Manage table column metadata including data types, filtering options, and grouping capabilities.
103

104
```python { .api }
105
class TableColumn(Model, AuditMixin):
106
    """
107
    SQL table column metadata.
108
    
109
    Attributes:
110
        id (int): Primary key
111
        column_name (str): Column name in the database
112
        is_dttm (bool): Whether column contains datetime data
113
        is_active (bool): Whether column is active for queries
114
        type (str): Column data type
115
        groupby (bool): Whether column can be used for grouping
116
        filterable (bool): Whether column can be filtered
117
        description (str): Column description
118
        table_id (int): Foreign key to Table
119
        table (Table): Reference to parent table
120
    """
121
    
122
    def __repr__(self):
123
        """String representation of the table column."""
124
        return self.column_name
125
```
126

127
## Usage Examples
128

129
### Basic Table Querying
130

131
```python
132
from panoramix.models import Database, Table
133

134
# Set up database and table
135
db = Database.query.filter_by(database_name='sales').first()
136
table = Table.query.filter_by(table_name='orders', database=db).first()
137

138
# Simple aggregation query
139
result = table.query(
140
    groupby=['product_category'],
141
    metrics=['sum__revenue', 'count'],
142
    row_limit=10
143
)
144

145
print(result.df)
146
print(f"Query took {result.duration} seconds")
147
```
148

149
### Time-based Analysis
150

151
```python
152
# Time series query with granularity
153
result = table.query(
154
    groupby=['product_category'],
155
    metrics=['sum__revenue'],
156
    granularity='day',
157
    since='7 days ago',
158
    until='now',
159
    main_datetime_column='order_date'
160
)
161

162
# Access results as pandas DataFrame
163
daily_sales = result.df
164
```
165

166
### Advanced Filtering
167

168
```python
169
# Query with WHERE and HAVING clauses
170
result = table.query(
171
    groupby=['region', 'product_category'],
172
    metrics=['sum__revenue', 'avg__order_value'],
173
    where="order_status = 'completed'",
174
    having="sum__revenue > 10000",
175
    order_desc=True,
176
    limit_metric='sum__revenue',
177
    row_limit=20
178
)
179
```
180

181
### Metadata Management
182

183
```python
184
# Sync table structure from database
185
table.fetch_metadata()
186

187
# Access column information
188
for column in table.columns:
189
    print(f"Column: {column.column_name}, Type: {column.type}")
190
    
191
# Access available metrics
192
for metric in table.metrics:
193
    print(f"Metric: {metric.metric_name}, Expression: {metric.expression}")
194
```
195

196
## Properties and Helpers
197

198
```python { .api }
199
class Table:
200
    @property
201
    def table_link(self):
202
        """HTML link to table visualization view"""
203
    
204
    @property
205
    def metrics_combo(self):
206
        """List of available metrics as form choices"""
207
    
208
    @property
209
    def column_names(self):
210
        """List of all column names"""
211
        
212
    @property
213
    def groupby_column_names(self):
214
        """List of columns available for grouping"""
215
        
216
    @property
217
    def filterable_column_names(self):
218
        """List of columns available for filtering"""
219
```
220

221
SQL tables provide the foundation for traditional database analytics in Panoramix, offering flexible querying, metric definitions, and integration with the visualization framework.