How to plot time series data from SQLite database

Question

I am trying to plot time series data by accessing the SQLite database. I successfully connect the database by coding the below script,however, i couldn't figure out how to plot the time series data from .sqlite file.

import sqlite3
from sqlite3 import Error


def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by the db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn

def main():
    database = r"E:\Data\SINCAL_Models\AusNetData\20180128_PQV\pq.sqlite"

    # create a database connection
    conn = create_connection(database)
    with conn:
        print("Database connected:")

if __name__ == '__main__':
    main()

The database file is shown in attached picture. I am trying to plot the yellow highlighted variables to get the plot like here

Answer 1

Since you are already using python this will be very easy for you :). I presume the table name as it is not clear to me and a db file can have several.

You can use pandas to access the data using your connections.

import pandas as pd
def fetch_table_data_into_df(table_name, conn):
    return pd.read_sql_query("select * from " + table_name, conn)

This will give you a pandas Dataframe- they are very well suited for plotting.

I usually use pylab directly but I think this should also work:

df = fetch_table_data_into_df("NameOfTable", conn)
df.plot("READ_TIME", "VALUE")

Let me know if you need more information :).

Here is the entire code (using yours and mine) as well:

import sqlite3
from sqlite3 import Error
import pandas as pd
import pylab


def fetch_table_data_into_df(table_name, conn):
    return pd.read_sql_query("select * from " + table_name, conn)

def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by the db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)

    return conn

def main():
    database = r"E:\Data\SINCAL_Models\AusNetData\20180128_PQV\pq.sqlite"

    TABLE_NAME = "pq_data"
    # create a database connection
    conn = create_connection(database)
    with conn:
        print("Database connected:")
        df = fetch_table_data_into_df(TABLE_NAME, conn)
        # reading what you want to do, I presumed you want 
        # to plot per measuremnt
        for measurement in df.MEASUREMENT.unique():
            df[df.MEASUREMENT == measurement].plot("READ_TIME", "VALUE")
            pylab.savefig(f"{measurement}.png")
            pylab.clf()

if __name__ == '__main__':
    main()