MySQL

https://img.shields.io/pypi/v/kpireport-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 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.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.