MySQL

pip install kpireport-sql

The SQL plugin provides a Datasource that enables execution of SQL queries against an existing MySQL or SQLite 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_sql.datasource.SQLDatasource(report: Report, **kwargs)

Bases: Datasource

Provides an interface for running queries agains a SQL database.

driver

which DB driver to use. Possible values are “mysql” and “sqlite”.

Type

str

kwargs

any keyword arguments are passed through to pymysql.connect() (in the case of the MySQL driver) or sqlite3.connect() (for the SQLite driver.)

query(sql: str, **kwargs) → 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 by pandas.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

pandas.DataFrame

Changelog

0.1.0

New Features

• Adds support for SQLite in addition to MySQL. This is controlled via a new driver kwargs, which defaults to “mysql”.

0.0.2

Prelude

Initial commit.

New Features

• Initial commit.

Bug Fixes

• The MySQL connector library is now PyMySQL, which is a pure Python implementation and does not require additional libraries on the host.