1
$\begingroup$

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

enter image description here

$\endgroup$
2
$\begingroup$

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()
$\endgroup$
  • $\begingroup$ @Thank you for useful answer,but still i am unable to plot. The arror is attached here filebin.net/u41uc853h9tq4luc/error.png?t=xyald2rs. Also the SQL data table is also attached here. filebin.net/u41uc853h9tq4luc/SQL_data.png?t=xn0d4yqa $\endgroup$ – Case Msee Nov 15 at 1:17
  • 1
    $\begingroup$ I updated the answer to help with the missing piece $\endgroup$ – El Burro Nov 15 at 10:08
  • $\begingroup$ Thank you for great answer. Let me try the updated code. $\endgroup$ – Case Msee Nov 18 at 0:51
  • $\begingroup$ @EI Burro. This time the code successfully plot the graphs but the y-axis is quite strange. Also the legends for 3 unique variables (P,Q,V) in table, it prints "VALUE" as legend for very graph as shown here filebin.net/c2kujy1w8lgq9n7m. $\endgroup$ – Case Msee Nov 21 at 7:06
  • $\begingroup$ You can try to use pylab.ylim(bottom = YMIN, top = YMAX) amd if you give the .plot the argument label = measurement it should replace it. Also you can control the legend posisiton with pylab.legend() - but i feel this is becoming another question about data visualization ;) $\endgroup$ – El Burro Nov 21 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.