Example.py

import sqlite3
import datetime
import random
import time
import matplotlib.pylab as plt
from dateutil import parser
from matplotlib import style
style.use('fivethirtyeight')

conn = sqlite3.connect('example database.db')
c = conn.cursor()


def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS stuffToPlot(unix REAL, datestamp TEXT, keyword TEXT, value REAL)")


def data_entry():
    c.execute("INSERT INTO stuffToPlot VALUES(143232132,'2016-01-01', 'Python', 8) ")
    conn.commit()  # commit changes to database
    c.close()
    conn.close()


def dynamic_data_entry():
    unix = time.time()
    print(unix)
    date = str(datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))     # datetime is a combination of date and time objects
    keyword = 'Python'
    value = random.randrange(0,10)
    c.execute("INSERT INTO stuffToPlot (unix, datestamp, keyword, value) VALUES (?, ?, ?, ?)",
              (unix, date, keyword, value))
    conn.commit()


def read_from_db():
    c.execute("SELECT * FROM stuffToPlot WHERE value>3 AND keyword='Python' AND unix > 1550478138")
    c.execute("SELECT keyword, unix FROM stuffToPlot WHERE value>3 AND keyword='Python' AND unix > 1550478138")
    # data = c.fetchall()             # creates a tuple of each row in the table
    for row in c.fetchall():
        print(row)


def graph_data():
    c.execute('SELECT datestamp, value FROM stuffToPlot')
    data = c.fetchall()   # List of tuples containing data for each row

    dates = []
    values = []

    for row in data:
        dates.append(parser.parse(row[0]))
        values.append(row[1])

    plt.plot_date(dates, values, '-')
    plt.show()

# create_table()

# # To enter data into the table use this
# for i in range(10):
#     dynamic_data_entry()
#     time.sleep(1)

def del_and_update():
        c.execute("SELECT * FROM stuffToPlot")
        [print(row) for row in c.fetchall()]

        # c.execute("UPDATE stuffToPlot SET value = 89 WHERE value = 8")
        # conn.commit()
        #
        # c.execute("SELECT * FROM stuffToPlot")
        # [print(row) for row in c.fetchall()]

        c.execute("DELETE FROM stuffToPlot WHERE value = 5")
        conn.commit()
        print(50*'$')
        c.execute("SELECT * FROM stuffToPlot")
        [print(row) for row in c.fetchall()]


# graph_data()
del_and_update()
c.close()
conn.close()