Getting Started with Database SQLIte3 python and Raspberry pi

Adding Database to our Project¶

Objective¶

We want to add time when we turned Lights on to our Database Let us learn how to do that for this, we would be using SQlite3 and make sure to download DB browser on Raspberry pi to open your database file.

In [4]:

%%html
<img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAOAAAADgCAMAAAAt85rTAAAAzFBMVEX////m5+h7msuitdrL1emxs7YrKiwuLC/r7O0pJyr4+PgxMDK1t7p+ntHc3d7u7/Camp03Njg8NzPR2/C7u7s0MTGoqKk3MzBZWVugs9hTXXBgZnVBQEJvcnunu+JEREdMS05wcHKAgILFxcdSV2JYZXyOjpDR0tNJSUxhYWRzjrlzdHeGhoiWnat4hJygoKJ+g49veY5gcpBqgaZ+i6VdYGilrb1thayKmbd2ksC4wdOwucrCzN9+g46OlKGOnr1lbX9kd5hPVmZfZXXCjBVeAAAPs0lEQVR4nO2di1eiTBjGowIcR4FBQKopbsolLbb7ddPq//+fvnlBy0o3kYv6HZ5zdrez6sCvF2HmeYZhZ6dWrVq1atWqVatWrVq1atWqVYyanBRoGm/EqQxe8wKJa657t/KrGWixG1GEBPGnBCRY0TDWpO0EbXqxQ9AUBYm26QzcoQ8augPHDD+pkTUwgu2iDHwbiRiLSIQSBQsORlkKWrFrCkLyVjMOKt7LVeW5DE5ExDUCeakPcKzYWGCQ2N98xmZMGB12tUbGD3K8gzAWLL6U3SpMCttJceU6sNpjjI1C96hQBQSLtpanhaZBMLa5onaoYBmIEC9/KwJBm/lV1BAeFNGObBIkFdFQwWoiMiyoqYiEBbVUpFqiVVRTTUHYwBIqol9YW5GY61RVjhQcFdYWwZsISIT8p9BU8YYCUlQMYSzQDQWkaJh/UNCIMNlMQOw4hIhxPsSGjwh2ow0F1HyLEOSu3g/xHNYZsmPN3MyzqNNqtYYMUSS+l72OsuaKmOH5rJWNBeT5VkuJCCEYmYq3fJ+Z0/wQMTrixC1oZIMBAZH3HcoYsSBGPu/901tqSp4xNJEAcJajtFppExsNmDC24mFkQSXBjMC24/qx0fI8LwCxf1tG7LtRKCIRDkvC3uEbE7ptAJxA8srQMQEzJQUlJtPk5/SFMBoMY/4TbmsAJ5Bsxw0FfLQoMk3bShTaphklHltstNL3fPvctgDOcs7Xgg9UDdiQPJ59ZxzTJuBxphIETE12fPmG5kky62AtBMys6gA5do6LyFdfWpjqq0sd2tRUjMVV2ThAdmkyJxACnAeHCpwGA0niOBnEcZIUeBr/5WxIiDmYPR1uKiDHD0QhOcNbA6W1yJieVZPz2NWPoASTRsN8kOUCBj4V2OkcmX72WKQZ8EMLwRUgHMSrI5YIyCmEXaZQFOcIQ2Qv7XeFLr8iYmmAQcR++8hdoaf8XQ0+AsYoXgmxZZYyXJJMgaCosJZlIxQJjowVEMsBHCI2lCvWNGdHBCHuCoQleDKyRQQnayL0uwIbEzN7AYu3LBqYiEV5Yl8VI2JnqmGLN0vwZCxClwsrsytAJFqesNUaUkqsogENjMvi29nxkK4sRzixPGhcuLNNcZnBqkuW6YYzOsWB3t6g+OtgEwnlFZCVEP/yLYQuupH4HAleKYDFn0A/5eHwnyNFNvKP0oF/5Cf9uxIASZnBuENsbe5ol4312SCTpt6F5fj8pPtaAiAVy8vFNYGdF0MzihxnkMpxItMMyae+DbLKAKSkLEKPNU7JAoWm4/4cJpcDiFqFtjmVj4hF7dgfDl0oXaJBMqlLMabfwR9fzeIBcWASMSo+N/YoEV2NnUWXd5zKARQlmf2qkVPs/A3PZuMJvqn9dpmoBJCTA0cggs0XdUWEsTMRhlyjsSGAHCd7A0TYcJ7Pf1GUYhtmZQ0lmeM2B5AhSkoIloXle6sXkmu5RMBYMA2uAY1uEiDbGzlQbJjaiOjS0yFn2CYTI7EQxZLcmDS5UYAJo2S4NLUNcTQ0Et/6NzKYu2wK8CEs2MMWN6XbREDYJwbZ8h0ipomQgIgJzi/MJQ8CiZMk9gdysMT3dWwRCWlkJFgDRWNwjS+NrQBY8HjwB+AEUpY81sm3iTCTfX0VnmRhAuttKbzHyd/g5gP+Fsa07CoAJ5QMsyEFEL4MXSeyQ4tMQj5i2WbkuImlH4Cd/xPtC+BnFztWIFBz3cG0a8N+cJO+TWyk6WnRptNiwBlQQP2p5IUFZDOAWhLpDqJpHjqTiX6JQ8H4t6OQEqXQrvHvgKuK/Va4QGHF/kCC/I1YUHtWM2UiKCg7PCj7MsO3mVES9r2PfD7zebxCQMbW8AzXFEVAExGyHT9ueb/c8MJ+Hx4MgOkks0PEUbzcHY/iAVnhPCVKT8IodGNtiUvNN3HBJK9iHQ/qtnIdsgUDss5C7IgJG0RUefZsRw4M10KQdlF/deO2SEDoCpnQFRIdo6jhl+wpJpQSDVZkLA6w0eAjRofswu/TaXpDuOiK/iq/taIAZTZEYnSmUZJHF/iMEZnZy1gMYIPz2fgWF3sF+y6YjyjaWRELAZR5djWwyjF2ZgUzSoUo2zFSAGCDc9j1uXy8ZHchysyUNTDAICdfQAkq7raC38SZWHAzvL+JiCLn42O9lUpvOFIy3UcEznbw7x7zL7KIVWZ8M0cawsrSb06MX2/1GspDUmK+uEC8iJY+XzPAkAiKvHIRhZLy73/KIUt/DZsISxFmo7bVEBs8pmWSLFCA0bJvTZxtRSSi2VoFsaHgLKe0otREaNnvRXIdbEgDgWDqB5kZGWAhd0ZmlCwsHdtOne1gKGLWEVI8ORNkQyFiqSjzpeGsgNBbNmCog8nACBoLXaQ5gCQulWWuQpodMHF943QkjsESSXzA3zgbcONZ5fcVu2QlwISRa/lm6oWKcEODFkiNDwPtu9gLzeTGs4oJh3hlQC41w7x4YAvT2x0wNR038S1bmqZ5nsf+5sHhHCazCOjxDUFVHqXNiOgvZHXACaTMBS3FjayPezkWiJDjzo2OzcruLOYFop919HyAU8zU2tYm1nZkhpZFKbXA144GUFXe8/XjTudFJ8gtc7rNhzwbk/Cxs18I4AzoPGs7/W8FH3f2O883gFh6FTVbZIdnZ3+/WMB/wieA+/udW1OHELzEtW8kn30h9L/PsLnqARniE6siRo5WCqMUh4Kukz8p3loAGeLzi6XrGEWFmaKTXfTgbgddvznbn25qPYAMsfP0hzJGcH6LWamp4fkmYnCM7rnzuaF1ASaMjy83OkAie5grIeKSpa8wo6PHX+nWCpgw7j+93JCUkvX6lkr6Z8kg9LeRAGy6dXz22Ol838R6AVPIzuPZn5QSkhjBilzF0LyAa8w7dJsyJwVeK/YHJklCfz1he7l9ngO3EYATyM7zE8O0YXf1yaSG5CZEYtm2CbJtiD5RutBaysVOliZDe9pfwLY5gJ+YjPP27OXv8U1I9YWi9s3x35ez28f0I/9sdJMAv4DCjj8/Pj493TKdnZ3BP7dPT4/Pz5+vL6UsgDiPs7084HzkFT+aCZD4OZztPIB5lAWQCt7qzvZWAFJRW7mGWwFoE8Ff1mTaRkBRcjCh/IrO9lYAygYm2Da4FRi3A5BrcEOBYDz0lvZDtwwQJjT7hJWRDn9M+fyfACbTXJzEKowUj1vC8t06wMT15QfpHFfThTGO/Ot0ye0CTBgbnhJNHF/CRjg8DHAWWNuJt6boWwXIpVMhA2MYTWcsY4GEMLvXjydzcls8bxgxzPN0TJoYv9sFOKVMJm2z8edvzjZdB+CzLiwb0v/mbMMsVY1nBXMH4G2nimDVIjC2tcAn4RoAn4odDzbmeduT/1SI/lQ5Yec4Z/iyvCAftCrnu9VzxGfZAYlZMd+TTqsE7KnEfq7wKO2c6dSuEFAfj7pUP6uKsLN/rKu9g26FgEcnB6Gqm5WcajqdF0K645O9agH3Ti66RL95Wt1DWhJv/4USld6f7FVcwb29k4MjhmjPxD/F03Ue/xKiqhd7TJUDMsTRe5cQ/fh2v4w6djrPL6ZOVHKxd7ImQKjiBVWJrv+MgvLCdR4Tum7vem+yrbUAMsST+zHpdnXd/rMwNckOd3ZM9K7aDS9GHxvKAriT19meAQTGvfsx7QJkeHz2lMO8ThKN2ySFg9qdj05mN5MFMCLDXM72V8CkjqPzd4DsQgL25+zpOUvokL738fbsL7BBK+H4/uDk2zayAHpIWN33nQeYMJ6Mrse9bkoJcdifl9unx8+EZb72nx8hhLoJIWyCz9L3i59wWQF3BkTgczjb8wAnlHuj64sjuzvlTCOym+O/f/+8vLycpWI/vfz5e3x8Y1pEn4KpXdobnwPbgrYzAe6wwbvLrexsLwSc1vLkgHGOezaBPVdncSfqTgSv0d77xfn9CND+1Ww2QFgYjxirmfe/AX5ish0+GN3fX59fXIzH70e9qY6O3sfji/Pz6/vR6CB956/tZQXcCSjGNF7N2V4G8AfrHGVpJDNgsroaEQdaIytjZsBilB1wZ4cPwfZ1eSmjs701gOxATe6zFEy/JS2+9X+bAXeSNXBhLRiRwvq+jSUmbW8b4A4s0exSlMzzFaZ3v8+4aN+Nbc7fOkAQ3OAdosl6HKJA7MTaVpTYSAUrUsCTEi0sELqNgJNaJuuRiB9riU+XJZldnmSrASdqJtO202X5kzVEYP0QP3nGQiDJxlYeohlUA9aANWANWDJg738OSLoHlfOdnC+fD+YHXMOV/iBDfFYAYPe8YsKTXrWAtHtRKeFBT60SUO/31e7RQWWIJyNK1Su1QsDDdl9V6XU1hCcH4y6lp+1qAdt3VFXD+4zG0Up4F11Vfdtt71YLuLu7y4qoWh/hTzl0J6Mxw7OuYHuVA7YfALE7HpVVxpOD6x7Do6+wtTUAAuIlO1C7dHyf1eT8HY7RHTE6tXfVTje2DkCG2L56gzKqR9ejwiAh4biA2qm0fzrd0poAgXH3FRhZIY/OR3s5KSHWuGdwKd1de2Y7awOEMu7e9cMEsmtPEqKsnMknRtcX7xTY2Gnz8vQL3VoBU8iHq34v2TfAfL+4nuRF/0KdZhQHo/vz8Y3VTdno4eXd7ne6tQOmkO3T1/4bVaecatg7Gl8kIdLogGkKBj+P0thp/N6bgsFV5/Dy6qE9B24zACeQ7Ye718vD3pQzYVW7PzXzumq99S+vTncXsW0Q4Cdme/fh7ur1sn/4ZluzsB+iYe/tsH/5enX6kL7/F20S4FfSdO8fHh5OJ3p4mHll2aY2E7BI1YA1YA1YA9aANWANWAPWgDVgDVgW4NIrxOaVh+kaAO/Updf4zasmUl8rJ2z3ll+lObcUrD5UzXepLr/Odn5ZhD5UWsP2qyouv1J6fsmYqHcVErb7asUrQ8sWUfu7FSG2T0MVDSvlYxogiH0qQGw/HKplPZL1n/IIVull2VVsn/YZnlP5swMSxSJW1f6PDKFAut2rN5UIduVrXn/IIOxsE/7MSYqAa7fv+iqrXrQ+PJDnIF1Vrf7cuCQH3cPrIaPD2K/w4rdAMm8iDPMEkkwoN2U7yaksVdWx4K7hsRZzJbcGoqgzyLD/mlCuhJnkNleXhxTgkOWv99D8IclwRAHraVL0evewfO6QvvH06rIPiVQX4IZaVSOjbOI0P0KMspsmYof916u704eZnOW7dh9O7yCASrK2LqwuSwZxjmcCVyLOg7WF4Olz+jQDhKDs7fCw/6HDw7e3cBqqdWHdXAFRx28V9ATJKtSUPD4eOjZJFoKdrgP7KaynD8RF1BzAAxLWf7JcWU1YylfTeCOO4fGfcDMNLBUFz/iV5BJXWa9Vq1atWrVq1apVq1atWrW2Wf8BbgSSz+7LUmUAAAAASUVORK5CYII=" , width=400, height=200>

Download Sqlite Browser¶

https://github.com/sqlitebrowser/sqlitebrowser

In [2]:

%%html
<img src="https://windows-cdn.softpedia.com/screenshots/SQLite-Database-Browser_1.png" , width=400, height=200>

CIRCUIT DIAGRAM:¶

In [3]:

%%html

<img src="http://razzpisampler.oreilly.com/images/rpck_0901.png" , width=400, height=200>

we are using BOARD numbering for this experiment Pin number 12¶

In [ ]:

# import the important library
import tkinter as tk
import RPi.GPIO as GPIO
import time
import datetime
import sqlite3


GPIO.setmode(GPIO.BOARD) # to use Raspberry Pi board pin numbers
GPIO.setup(11, GPIO.OUT) # set up GPIO output channel


mainwindow=tk.Tk()
mainwindow.title('Test ')
mainwindow.geometry('640x340')


my_label=tk.Label(mainwindow,text="My First UI",
                  font=("Arial",22), bg= "Green",fg="white")
my_label.grid(row=0,column=0,sticky='NSEW',padx=10,pady=10)

button_on=tk.Button(mainwindow,text="On",bg="black",fg="white",
                    command=lambda :my_on())
button_on.grid(row=1,column=0,sticky='NSEW',padx=10,pady=10)


button_off=tk.Button(mainwindow,text="OFF",bg="black",fg="white",
                     command=lambda:my_off())
button_off.grid(row=1,column=1,columnspa=1,sticky='NSEW',padx=10,pady=10)


def my_on():
    t=datetime.datetime.now()
    my_t = "{}-{}-{}".format(t.hour,t.minute,t.second)
    database_on(my_t)

    print('Led Turn On !!!!! ')
    GPIO.output(11, GPIO.LOW) # set RPi board pin 11 low. Turn off LED.
    time.sleep(1)
    print('Yes you did it !')


def my_off():

    t=datetime.datetime.now()
    my_off = "{}-{}-{}".format(t.hour,t.minute,t.second)
    database_off(my_off)

    print('Led Turned Off !!!!!!  ')
    GPIO.output(11, GPIO.HIGH) # set RPi board pin 11 high. Turn on LED.
    time.sleep(2)
    print('Great Work ! ')

def database_on(on_time):

    conn = sqlite3.connect('led.db')
    cursor = conn.cursor()

    cursor.execute(""" CREATE TABLE IF NOT EXISTS
     ledon
     (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     on_time TEXT)""")

    cursor.execute("""
    INSERT INTO ledon (on_time) 
     VALUES (?)""", (on_time))

    conn.commit()
    cursor.close()
    conn.close()

def database_off(of_time):

    conn = sqlite3.connect('led.db')
    cursor = conn.cursor()

    cursor.execute(""" CREATE TABLE IF NOT EXISTS
     ledoff
     (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     off_time TEXT)""")

    cursor.execute("""
    INSERT INTO ledoff (off_time) 
     VALUES (?)""", (of_time))

    conn.commit()
    cursor.close()
    conn.close()

mainwindow.mainloop()