0

Right now i have a simple python solution that's working perfect. it is making a log.txt file and update everytime there is a connection on/off GPIO 12.

But the file is getting longer and longer, and it's time to upgrade to a little database (SQlite3) to make it more easy to read and sort, on the webpage.

It has been some years since i last had my hands on SQL, so i need some help to get this working.

Here is the Python log-file script i want to update to use SQlite

#!/usr/bin/python

import datetime  
import time
import RPi.GPIO as GPIO
GPIO.setwarnings(False)
GPIO.setmode(GPIO.BOARD)
DS = 12
GPIO.setup(DS,GPIO.IN,pull_up_down = GPIO.PUD_UP)

logfile = "/var/www/html/log.txt"

start = GPIO.input(DS)

while True:

while GPIO.input(DS) == start:
  time.sleep(.25)
now = datetime.datetime.now()
start = GPIO.input(DS)
timp = now.strftime("%d %B %Y - %H:%M:%S") + " - " + str(start)
print timp
with open(logfile, "a") as file:
  file.write(timp +chr(10))
Djarnis
  • 1
  • If you want to sort by timestamp, change your date format to use one of the ones understood by [sqlite date and time functions](https://www.sqlite.org/lang_datefunc.html), all of which can be meaningfully ordered, unlike what you're using. – Shawn Dec 05 '18 at 14:45
  • thanks, that's a good idea, still struggling getting it update the database, i am a little rusty at this, so i probably making alot of mistakes ;-) – Djarnis Dec 05 '18 at 16:38

1 Answers1

0
#!/usr/bin/python

import datetime  
import time
import RPi.GPIO as GPIO
import sqlite3

GPIO.setwarnings(False)
GPIO.setmode(GPIO.BOARD)
DS = 12
GPIO.setup(DS,GPIO.IN,pull_up_down = GPIO.PUD_UP)

logfile = "/var/www/html/log.txt"

start = GPIO.input(DS)

while True:

while GPIO.input(DS) == start:
  time.sleep(.25)
now = datetime.datetime.now()
start = GPIO.input(DS)
timp = now.strftime("%d %B %Y - %H:%M:%S") + " - " + str(start)
print timp
conn = sqlite3.connect(Data Source=\mydb.db;Version=3;Password=myPassword;)
c = conn.cursor()
c.execute("INSERT INTO table VALUES('%s')",timp)
conn.commit()
conn.close()
Koxo
  • 507
  • 4
  • 10