使用python将数据添加到sqlite3，然后使用php5在apache2服务器上使用python、php5、sqli

Question

*更新我正试着为我儿子制作一款游戏，它有两个振动传感器，代表棒球的击球和击球。当传感器被击中时，我有一个循环，在击球或球上加上一个勾号。当传感器被击中时，我希望它也能进入我的sqlite数据库。然后我有一个php文件，它读取sqlitedb，并实时显示球和击球。我使用的是php5、sqlite3和apache2服务器。我有服务器设置，python脚本正确检测传感器并更新2个变量，球和击球。我似乎无法将py文件链接到php文件，所以当我导航到basesloaded.php时，它会执行sensor.py。如果我先在空闲状态下运行py脚本，然后导航到php页面，它会正确更新，但不会从php文件中运行py脚本。

#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):
    with sqlite3.connect('basesLoaded.db') as conn;
        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):
    with sqlite3.connect('basesLoaded.db') as conn;
        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():
    GPIO.add_event_detect(ShockPin, GPIO.FALLING, callback=shock, bouncetime=2500)
    GPIO.add_event_detect(KnockPin, GPIO.FALLING, callback=knock, bouncetime=2500) 
if __name__ == '__main__':     # Program start from here
    setup()
    try:
        loop()
    except KeyboardInterrupt: 
        destroy()

以下是我的php脚本，用于检索sqlite数据-

#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>';
?>

Answer 1

正如@Parfait在评论中提到的，您需要提交更新才能使其持久。您的PHP代码没有看到更新，因为它们没有提交。

您可以在代码中调用conn.commit()，但是，确保更新提交（或在出现错误时回滚）的一种简单方法是使用上下文管理器打开数据库，即使用with语句，例如：

def knock(ev=None):
    with sqlite3.connect('basesLoaded.db') as conn:
        conn.execute("UPDATE bl set STRIKE = 1 WHERE ID =1")

现在，当with语句终止时，更新将自动提交，在这种情况下，当函数knock()返回时，或者在发生异常时回滚。

---

更新

正如评论中所讨论的，事件处理似乎存在问题。我怀疑您的代码需要明确地继续运行，否则，一旦主线程终止，整个程序就会终止。你可以这样做：

WAIT_PIN = 14
def register_callbacks():
    GPIO.add_event_detect(ShockPin, GPIO.FALLING, callback=shock, bouncetime=2500)
    GPIO.add_event_detect(KnockPin, GPIO.FALLING, callback=knock, bouncetime=2500) 
def wait():
    GPIO.wait_for_edge(WAIT_PIN, GPIO.BOTH)    # wait for level change
#    # Or wait in a while loop        
#    while True:
#        time.sleep(0.1)
#    # Or wait for keyboard input
#    input('Press <ENTER> to quit: ')
if __name__ == '__main__':
    try:
        setup()
        register_callbacks()
        wait()    # block the main thread
    except KeyboardInterrupt: 
        destroy()  

您需要让这个Python脚本一直运行。它独立于PHP脚本，PHP脚本仅在通过web服务器响应HTTP请求时运行。您可以将Python脚本作为后台进程运行：

$ python sensor.py &

另外，从PHP脚本中删除exec()调用。