MySQL¶
pip install kpireport-mysql
The MySQL plugin provides a Datasource that enables execution of SQL queries against an existing MySQL database.
Show/hide example configuration YAML
---
title: Top-of-funnel report
datasources:
db:
plugin: mysql
args:
host: mysql
user: kpireport
passwd: kpireport_pass
views:
# Show a time series of user signups over the report period.
signups_over_time:
plugin: plot
title: Total sign-ups
description: |
The running total of user accounts.
args:
datasource: db
query: |
select created_at,
count(*) over (order by created_at)
+ (select count(*) from kpireport.signups where created_at < {from})
as total_signups
from kpireport.signups
where created_at >= {from} and created_at < {to}
query_args:
parse_dates: ['created_at']
new_signups:
plugin: plot
title: Daily new sign-ups
description: |
How many new users signed up on each day.
cols: 4
args:
kind: bar
datasource: db
query: |
select date_format(created_at, '%%Y-%%m-%%d') as day, count(*) as daily_total
from kpireport.signups
where created_at >= {from} and created_at < {to}
group by day(created_at)
query_args:
# Because we're roughly grouping by day, and not a full date time,
# automatic parsing of dates doesn't quite work, so we need to give
# a bit of help to say which columns should be treated as dates.
parse_dates:
day: '%Y-%m-%d'
time_column: day
# Additionally, show a stat indicating how much the total signups have
# increased (or decreased) in comparison to the previous report period.
signups_change:
plugin: single_stat
title: Week total
cols: 2
args:
datasource: db
query: |
select count(*) from kpireport.signups
where created_at >= {from} and created_at < {to}
comparison_query: |
select count(*) from kpireport.signups
where created_at >= date_sub({from}, {interval})
and created_at < {from}
comparison_type: percent
# comparison_type: raw
new_signups_table:
plugin: table
args:
datasource: db
query: |
select date_format(created_at, '%%Y-%%m-%%d') as Day, count(*) as 'Daily total'
from kpireport.signups
where created_at >= {from} and created_at < {to}
group by day(created_at)
API¶
- class kpireport_mysql.datasource.MySQLDatasource(report, **kwargs)¶
Bases:
kpireport.datasource.Datasource
Provides an interface for running queries agains a MySQL database.
- kwargs¶
any keyword arguments are passed through to
pymysql.connect()
- query(sql: str, **kwargs) → pandas.core.frame.DataFrame¶
Execute a query SQL string.
Some special tokens can be included in the SQL query. They will be replaced securely and escaped with the built-in parameter substition capabilities of the MySQL client.
{from}
: the start date of the Report{to}
: the end date of the Report{interval}
: an interval string set to the Report interval, i.e., how many days is the Report window. This is useful when doing date substitution, e.g.; Also include previous interval WHERE time > DATE_SUB({from}, {interval})
Note
By default, no automatic date parsing will occur. To ensure that your timeseries data is properly parsed as a date, use the
parse_dates
kwarg supported bypandas.read_sql()
, e.g.,self.datasources.query('my_db', 'select time, value from table', parse_dates=['time'])
- Parameters
sql (str) – the SQL query to execute
kwargs – keyword arguments passed to
pandas.read_sql()
- Returns
a table with any rows returned by the query.
Columns selected in the query will be columns in the output table.
- Return type
Changelog¶
0.0.2¶
Bug Fixes¶
The MySQL connector library is now PyMySQL, which is a pure Python implementation and does not require additional libraries on the host.
0.0.1¶
Prelude¶
Initial commit.
New Features¶
Initial commit.