Python Dataset | Smartchart开发手册

Introduction #

Python datasets (also called “universal datasets”) allow writing Python code directly in the dataset editor, breaking through SQL query limitations. Enables: cross-data-source joins, Excel reading/processing, API data fetching and storage, calling LLM APIs, and other complex scenarios. The result must be assigned to the ds variable.

Python datasets require a super admin to create a Python connector. Dataset developers can use it directly.

Built-in Functions #

Function	Description
`ds_sql(conn_name, sql)`	Get dataset via connection name and SQL
`ds_df(ds)`	Convert ds dataset to Pandas DataFrame
`ds_list(df)`	Convert DataFrame back to ds dataset
`ds_save(config, content, update=0)`	Write data to database
`ds_gpt(conn_name, prompt, ...)`	Call LLM for results
`ds_get(id, param=None)`	Get another dataset by ID

Use Cases #

When SQL queries can’t meet your needs, process query results further
Need to use Excel data sources, or query data from different systems
Also called universal dataset, can use any Python syntax
In AI/LLM applications, can build powerful agents
You don’t need to be familiar with Python to use it

First create a Python connector. Due to security, only super admins can create them.

Screenshot

Built-in Function Details #

# Variable conventions:
# Use ds for dataset variables, e.g., ds1, ds2
# Use df for pandas objects, e.g., df1, df2

# Get ds dataset via data source connection name and SQL
ds_sql(conn_name, sql)
# Write data to data source
ds_save(config, content, update=0)
# Returns {'status': 200, 'msg': 'success'} on success

# Get LLM results
ds_gpt(conn_name, prompt, stream=False, his='', tool=False, remark={})
# stream: enable streaming, his: history, tool: output tool calls, remark: model parameters
# Returns {'status': 200, 'msg': 'xxxx', 'token': 'xx'} on success
# If tool=True, returns {'tool': 'source_name', 'sql': 'xxxx'} or {'tool': 'agent_name', 'msg': 'xxxx'}
# or {'tool': 'agent_name', 'msg': '', 'param': {'xx': 'xxx'}}

ds_df(ds)     # Convert ds dataset to pandas DataFrame
ds_list(df)   # Convert pandas DataFrame to ds dataset
ds_get(id, param=None)  # Get target ds dataset [rarely used]

Example Usage #

Read Excel data for processing:

import pandas as pd
df = pd.read_excel('file_path', 'sheet_name')
df = df.groupby('province').agg({'count': 'sum'}).reset_index()
ds = ds_list(df)

Generate dictionary-format datasets for multiple charts:

import pandas as pd
df = pd.read_excel('/Users/../smartdemo.xlsx', 'demo')
df0 = df.groupby('c3').agg({'qty': 'sum'}).reset_index()
df1 = df.groupby(['province', 'c3']).agg({'qty': 'sum'}).reset_index()
ds = {'df0': ds_list(df0), 'df1': ds_list(df1)}

Cross-data-source SQL execution:

import pandas as pd
sql_str1 = '''select H1 as heroname, sum(qty) as count from T
/* where H2 = '$H2' */
group by H1 order by sum(qty) desc'''
sql_str2 = 'select heroname, qty as last_month_count from xxx'

ds1 = ds_sql('conn_name1', sql_str1)
df1 = ds_df(ds1)
ds2 = ds_sql('conn_name2', sql_str2)
df2 = ds_df(ds2)
df = pd.merge(df1, df2, how='left', on=['heroname'])
ds = ds_list(df)

Fetch data via API and write to database:

import requests
header = {"Content-Type": "application/json", "x-acs-dingtalk-access-token": 'xxx'}
res = requests.post('https://xxx/api/xx', headers=header, data={'type': ''}).json()
result = res['data']
config = {'table': 'tablename', 'conn': 'conn_name'}
ds = ds_save(config, result)

Note: The final result must be assigned to the ds variable!!

Parameter passing works the same as standard datasets

ds_gpt Function Parameters #

ds_gpt(conn_name, prompt, stream=False, his='', tool=False, remark={})

Parameter	Description
`conn_name`	LLM data source name (e.g., `smtgpt`)
`prompt`	Prompt content
`stream`	Enable streaming response (default False)
`his`	History conversation (string format)
`tool`	Parse tool call JSON (default False)
`remark`	Extra model parameters, e.g., `{"temperature": 0.7}`

Return values:

Normal mode: {'status': 200, 'msg': 'response', 'token': usage}
tool=True: {'tool': 'source_name', 'sql': '...'} or {'tool': 'agent_name', 'msg': '...', 'param': {...}}

Key convention: Python datasets must assign the final result to the ds variable, otherwise no output.