Wireless alarm with a Raspberry Pi with python

Create the database

In order to store alarms we need a table called 'Sensors' (time, kind of sensor and so on...). And the user need to make the configuration of this alarms. So we need 2 other tables, the first one 'AlarmeState' for stored the state of the alarm (switch on or off ) and the second 'Plannings' for the planning's alarm of a week template.

All python's files to build the database :

• After connected to mysql, create the database "ALARME" and user ('XXXX'@'localhost') :

  mysql> create database ALARME;

  mysql> CREATE USER 'yourLogin'@'localhost' IDENTIFIED BY 'yourPassword';

  mysql> GRANT ALL PRIVILEGES ON * . * TO 'yourLogin'@'localhost'

• Install python lib to access mysql :

  sudo apt-get install python-mysqldb

• create tables with python

  - python CreateAlarmeDB.py

   #!/usr/bin/python  
   # -*- coding: utf-8 -*-  
   import MySQLdb as mdb  
   import sys  
   try:  
        con = mdb.connect('localhost', 'yourLogin', 'yourPassword', 'ALARME');  
        with con:  
         cur = con.cursor()  
         cur.execute("DROP TABLE IF EXISTS Sensors")  
         cur.execute("CREATE TABLE Sensors(Id INT PRIMARY KEY AUTO_INCREMENT, \  
        Date DATETIME, Heure TIME,SensorName VARCHAR(25), \  
        SensorValue REAL, \  
        Status INT)")  
   except mdb.Error, e:  
     print "Error %d: %s" % (e.args[0],e.args[1])  
     sys.exit(1)  
   finally:    
     if con:    
       con.close()  
  

  
  

  - python CreatePlanningDB.py

   #!/usr/bin/python  
   # -*- coding: utf-8 -*-  
   import MySQLdb as mdb  
   import sys  
   try:  
        con = mdb.connect('localhost',  'yourLogin', 'yourPassword', 'ALARME');  
        with con:  
         cur = con.cursor()  
         cur.execute("DROP TABLE IF EXISTS Plannings")  
         cur.execute("CREATE TABLE Plannings(Id INT PRIMARY KEY AUTO_INCREMENT, \  
        PlanningName VARCHAR(25), \  
        Semaine VARCHAR(180))")  
   except mdb.Error, e:  
     print "Error %d: %s" % (e.args[0],e.args[1])  
     sys.exit(1)  
   finally:    
     if con:    
       con.close()  
  

  
  

  - python CreateTableState.py

   #!/usr/bin/python  
   # -*- coding: utf-8 -*-  
   import MySQLdb as mdb  
   import sys  
   try:  
        con = mdb.connect('localhost',  'yourLogin', 'yourPassword', 'ALARME');  
        with con:  
         cur = con.cursor()  
         cur.execute("DROP TABLE IF EXISTS AlarmeState")  
         cur.execute("CREATE TABLE AlarmeState(Id INT PRIMARY KEY AUTO_INCREMENT, \  
        Date DATETIME,Status INT)")  
   except mdb.Error, e:  
     print "Error %d: %s" % (e.args[0],e.args[1])  
     sys.exit(1)  
   finally:    
     if con:    
       con.close()  
  

• Insert Data for the first time :

  python /ScriptPython/InsertDataAlarmeState.py

   #!/usr/bin/python  
   # -*- coding: utf-8 -*-  
   import MySQLdb as mdb  
   import sys  
   def Insert(_date,_Status):  
        try:  
             con = mdb.connect('localhost', 'yourLogin', 'yourPassword', 'ALARME');  
             stringSQL = "INSERT INTO AlarmeState(date,Status) VALUES (\'%s\',%d)" % (_date,_Status)  
             print stringSQL  
             with con:  
              cure = con.cursor()  
              cure.execute(stringSQL)  
        except mdb.Error, e:  
          print "Error %d: %s" % (e.args[0],e.args[1])  
          sys.exit(1)  
        finally:    
          if con:    
            con.close()  
   Insert('2015-12-07',1)  
   print "ok alarme on"  
  

  python /ScriptPython/InsertDataPlanningDB.py

   #!/usr/bin/python  
   # -*- coding: utf-8 -*-  
   import MySQLdb as mdb  
   import sys  
   try:  
       con = mdb.connect('localhost', 'yourLogin', 'yourPassword', 'ALARME');  
       with con:  
        cur = con.cursor()  
        cur.execute("INSERT INTO Plannings (PlanningName,Semaine) VALUES  (\'000000000000000000000000\',\'L\');");  
        cur.execute("INSERT INTO Plannings (PlanningName,Semaine) VALUES  (\'000000000000000000000000\',\'M\');");  
        cur.execute("INSERT INTO Plannings (PlanningName,Semaine) VALUES  (\'000000000000000000000000\',\'W\');");  
        cur.execute("INSERT INTO Plannings (PlanningName,Semaine) VALUES  (\'000000000000000000000000\',\'J\');");  
        cur.execute("INSERT INTO Plannings (PlanningName,Semaine) VALUES  (\'000000000000000000000000\',\'V\');");  
        cur.execute("INSERT INTO Plannings (PlanningName,Semaine) VALUES  (\'000000000000000000000000\',\'S\');");  
        cur.execute("INSERT INTO Plannings (PlanningName,Semaine) VALUES  (\'000000000000000000000000\',\'D\');");  
   except mdb.Error, e:  
     print "Error %d: %s" % (e.args[0],e.args[1])  
     sys.exit(1)  
   finally:  
     if con:  
       con.close()  
  

The database is ready to use !