How can I store sensor readings from raspi to my database?

Question

This is my beam sensor code:

#!/usr/bin/python

import RPi.GPIO as GPIO
import time

GPIO.setmode(GPIO.BOARD)

GPIO.setup(12, GPIO.IN) #Right level-1

GPIO.setup(16, GPIO.IN) #Right level-2

GPIO.setup(18, GPIO.IN) #Right level-3

while True:

    if (GPIO.input(12) == 1 or GPIO.input(16) == 1 and GPIO.input(18) == 0):  
        print("lying down")
        time.sleep(1)
    elif (GPIO.input(12) == 1 or GPIO.input(16) == 1 and GPIO.input(18) == 1):
        print("Sitting/Crawling")
        time.sleep(1)
    elif (GPIO.input(12) == 0 or GPIO.input(16) == 0 and GPIO.input(18) == 1):
        print("Almost out")
        time.sleep(1)
    else:
        print("Out of the crib")
        time.sleep(1)

This is my code for my database:

#!/usr/bin/env python

import MySQLdb


db = MySQLdb.connect("localhost", "root", "raspberry", "cribdb")
curs=db.cursor()

curs.execute ("""INSERT INTO tbstatus values(NOT NULL, 'STATUSHERE')""")

db.commit()

number_of_rows= curs.execute("SELECT * FROM tbstatus")

if (number_of_rows <= 5):
   print(number_of_rows)

else:

    curs.execute("""DELETE FROM tbstatus order by id LIMIT 1""")
    db.commit()
    print("\n Record Deleted successfully ")

How can I get the status printed by the sensors which is (LYING DOWN, SITTING/CRAWLING, STANDING, etc) and put it here

(curs.execute ("""INSERT INTO tbstatus values(NOT NULL, 'STATUSHERE')""")) 

to store in my database.

Answer 1

Unless I'm misunderstanding things, you can simply merge these two scripts, something like

#!/usr/bin/python

import RPi.GPIO as GPIO
import time
import MySQLdb

db = MySQLdb.connect("localhost", "root", "raspberry", "cribdb")

GPIO.setmode(GPIO.BOARD)

GPIO.setup(12, GPIO.IN)  # Right level-1
GPIO.setup(16, GPIO.IN)  # Right level-2
GPIO.setup(18, GPIO.IN)  # Right level-3

last_status = None

while True:
    input_12 = GPIO.input(12)
    input_16 = GPIO.input(16)
    input_18 = GPIO.input(18)
    if input_12 == 1 or input_16 == 1 and input_18 == 0:
        status = "lying down"
    elif input_12 == 1 or input_16 == 1 and input_18 == 1:
        status = "Sitting/Crawling"
    elif input_12 == 0 or input_16 == 0 and input_18 == 1:
        status = "Almost out"
    else:
        status = "Out of the crib"
    time.sleep(1)
    if status != last_status:
        print(status)
        last_status = status
        curs = db.cursor()
        curs.execute(
            """INSERT INTO tbstatus values(NULL, %s)""", (status,)
        )
        db.commit()

As an aside, I would suggest adding a timestamp column of some sort to your tbstatus table, by the way, unless you already have one.

Another improvement might be to only log statuses when they change.

EDIT: I added keeping track of the last status and only logging changes, as requested in the comments.