Since SeaTable 2.3, we’ll have an SQL query interface. It is a step forward in data analysis and performance benchmarking. Together with 2.3, we also deployed an SQL Query plugin in the plugins market (Cloud users don’t have to visit the plugins market as they can directly add the plugins in their cloud interface).
SQL in SeaTable
The SQL query in SeaTable is the same as MySQL, so you can directly start using it in SeaTable if you are familiar with MySQL.
Basically, an SQL query in SeaTable looks like this:
SELECT [DISTINCT] fields FROM table_name [WhereClause] [OrderByClause] [GroupByClause] [Limit Option]
The response is a JSON object. Currently, there’re still some limitations, for example, the JOIN method is not yet supported.
For further information, refer to the SQL Reference - SeaTable Scripts Programming Manual.
To query your base’s data, you can use SQL in
- the SQL Query plugin on the web interface, or
- with the SeaTable API - Query with SQL, or
- with the Python SDK.
Query with the SQL Query Plugin
Yes - you can directly use the SQL query language on the web UI. Just open the SQL query plugin and start typing your command. The results are in a table that is very friendly for the eyes:
Query with The REST API
With the SeaTable API, you can query a table, or query the linked records of certain rows.
Let’s say, we have a very simple table with the dates and sales volumn, recording multiple sales activities of each day:
Now, with the SQL query API request, I would like to get the sum of each day’s sales volumn. So I could send the following API request and get the desired results as in the screenshot below:
Query with the Python SDK
The REST API has been packed into the SeaTable Python SDK.
Here are two simple examples:
base.query('select name, price, year from Bill')
or
base.query('select name, sum(price) from Bill group by name')
will return the results, respectively:
[
{'_id': 'PzBiZklNTGiGJS-4c0_VLw', 'name': 'Bob', 'price': 300, 'year': 2019},
{'_id': 'Ep7odyv1QC2vDQR2raMvSA', 'name': 'Bob', 'price': 300, 'year': 2021},
{'_id': 'f1x3X_8uTtSDUe9D60VlYQ', 'name': 'Tom', 'price': 100, 'year': 2019},
{'_id': 'NxeaB5pDRFKOItUs_Ugxug', 'name': 'Tom', 'price': 100, 'year': 2020},
{'_id': 'W0BrjGQpSES9nfSytvXgMA', 'name': 'Tom', 'price': 200, 'year': 2021},
{'_id': 'EvwCWtX3RmKYKHQO9w2kLg', 'name': 'Jane', 'price': 200, 'year': 2020},
{'_id': 'BTiIGSTgR06UhPLhejFctA', 'name': 'Jane', 'price': 200, 'year': 2021}
]
or
[
{'SUM(price)': 600, 'name': 'Bob'},
{'SUM(price)': 400, 'name': 'Tom'},
{'SUM(price)': 400, 'name': 'Jane'}
]
Specialities in SeaTable SQL
The most common use cases in SeaTable involve data collection and analysis. For this reason, we have made our user’s lives easier by making SeaTable SQL support the following special functions:
-
STARTOFWEEK(date, weekStart): returns the first day of the week wheredateis in.weekStartcan be either “Sunday” or “Monday” as per the first day of a week. -
Quarter(date): Returns the quater number (1, 2, 3, or 4) of the date. -
ISODate(date): Returns ISO format string for the date. E.g. “2020-09-08”. -
ISOMonth(date): Returns ISO format string for the month wheredateis in. E.g. “07”.
