mardi 3 mai 2016

Using python to add data to sqlite3, then php5 to retrieve data on apache2 server dynamically using python, php5, sqlite3

I am try to make a game for my son, it has 2 vibration sensors that represent Balls and Strikes (for baseball). When a sensor is hit I have a loop that adds a tick to either the strike or the ball. When a sensor is hit I want it also to enter into my sqlite db. then I have a php file that reads the sqlite db and displays the balls and strikes as they happen in realtime. I am using php5, sqlite3 and apache2 server. I have the server setup, the python script detects the sensors correctly and creates 2 variables, balls and strikes to. I have the php script linked to the py script and can see a simple "print 'welcome' when I run the php file. My problem is it doesnt seem like either 1. Its not updating the sqlite db or 2. its not retrieving the data correctly in php. Here is my python script for the sensors -

#sensor.py file
#!/usr/bin/env python
import RPi.GPIO as GPIO
import time
import sqlite3

KnockPin = 12 
ShockPin = 13 

BtnPin = 15

inning = 1
runs = 0
strikes = 0
balls = 0


print ("Welcome to Bases Loaded!")


def setstr():
    global strikes
    strikes = 0
    global balls
    balls = 0




def setup():
    GPIO.setmode(GPIO.BOARD)         
    GPIO.setup(KnockPin, GPIO.IN, pull_up_down=GPIO.PUD_UP)
    GPIO.setup(ShockPin, GPIO.IN, pull_up_down=GPIO.PUD_UP)
    GPIO.setup(BtnPin, GPIO.IN, pull_up_down=GPIO.PUD_UP)   



def knock(ev=None):

    conn = sqlite3.connect('basesLoaded.db');

    global strikes


    strikes += 1

    if strikes ==0:

        pass
    elif strikes == 1:


        conn.execute("UPDATE bl set STRIKE = 1 WHERE ID =1");
        print ("Strike 1 :", conn.total_changes);

    elif strikes == 2:


        conn.execute("UPDATE bl set STRIKE = 2 WHERE ID=1");
        print ("Strike 2 :", conn.total_changes);

    elif strikes == 3:

        conn.execute("UPDATE bl set STRIKE = 0 WHERE ID=1");
        print ("Strike 3 Your Out :", conn.total_changes);

        global inning
        inning +=1

        conn.execute("UPDATE bl set INNING = inning WHERE ID=1");
        setstr()
        print ("Inning Number :", inning);

def shock(ev=None):
    conn = sqlite3.connect('basesLoaded.db');
    print ("Opened database successfully");
    global balls


    balls += 1

    if balls ==0:
        pass
    elif balls == 1:

        conn.execute("UPDATE bl set BALL = 1 WHERE ID=1");
        print ("Ball 1 :", conn.total_changes);


    elif balls == 2:

        conn.execute("UPDATE bl set BALL = 2 WHERE ID=1");
        print ("Ball 2 :", conn.total_changes); 

    elif balls == 3:

        conn.execute("UPDATE bl set BALL = 3 WHERE ID=1");
        print ("Ball 3 :", conn.total_changes);

    elif balls == 4:

        global runs
        runs += 1
        conn.execute("UPDATE bl set BALL = 0 WHERE ID=1");
        conn.execute("UPDATE bl set RUN = 'runs' WHERE ID=1");
        print ("Run Scored! score is :", runs); 
        setstr()


def loop():


    if GPIO.add_event_detect(ShockPin, GPIO.FALLING, callback=shock, bouncetime=2500):
        print "Balls"


    elif GPIO.add_event_detect(KnockPin, GPIO.FALLING, callback=knock, bouncetime=2500): # wait for falling
         while True:
              pass

if __name__ == '__main__':     # Program start from here
    setup()
    try:
        loop()

    except KeyboardInterrupt:  # When 'Ctrl+C' is pressed, the child program destroy() will be  executed.
        destroy()

As you can see I have the "update bl" script in the IF ELIF statements that is supposed to update the db row #1 as I want to only have 1 row that gets updated.

Here is my php script to retrieve the sqlite data -

#basesLoaded.php file
<?php
$try = exec('python sensor.py');
print_r ($try);
$db = new SQLite3('basesLoaded.db');

echo '<br>';
echo '<br>';
$strikes = ($db->querySingle('SELECT STRIKE FROM bl WHERE ID = 1'));
$balls = ($db->querySingle('SELECT BALL FROM bl WHERE ID = 1'));
$outs = ($db->querySingle('SELECT OUT FROM bl WHERE ID = 1'));
$runs = ($db->querySingle('SELECT RUN FROM bl WHERE ID = 1'));
$inning = ($db->querySingle('SELECT INNING FROM bl WHERE ID = 1'));


echo 'Strikes: ' . $strikes . '<br>';
echo 'Balls: ' . $balls . '<br>';
echo 'Outs: ' . $outs . '<br>';
echo 'Runs: ' . $runs . '<br>';
echo 'Inning: ' . $inning . '<br>';
?>

And here is my script to create the db and create first row -

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('basesLoaded.db')
print ("Opened database successfully");

conn.execute('''CREATE TABLE bl
   (ID INT AUTO_INCREMENT   ,
   INNING         INT   ,
   RUN            INT   ,
   OUT            INT   ,
   STRIKE         INT    ,
   BALL           INT   );''')
print ("Table created successfully");

conn.execute("INSERT INTO bl (ID,INNING,RUN,OUT,STRIKE,BALL) \
  VALUES (1,1,1,1,1,1)");

conn.commit()
print ("Records Created Successfully");
conn.close()

When I run basesLoaded.php I get strike:1, ball:1 etc.. But when it wont update when a sensor gets knocked.. Any ideas? Thank you!

Also when I run the sensor.py script in idle it works great and says that a row is updated whenever a sensor is hit, but thats the only indication that the db is actually getting updated as the values in php do not change.. I would like this to change in the php file dynamically or in realtime if possible..either using ajax or something like that..

Aucun commentaire:

Enregistrer un commentaire