Weather station Raspberry Pi – The Python capture and storage with SQLite

The sequel to the manufacture of our weather station with Raspberry Pi. After the configuration of the transmitters and Assembly of housings, need to retrieve measurements and storing them somewhere.

weather

  1. The database. To save the received data we will have to define a SQLite database.In this database we have two tables :
    A table for all the emitters. We will store the ID of the issuer, its voltage with the date and time of last update, the position of the transmitter (inside or outside for example), the frequency of data collection.
  2. Install sqlite3 to control your database
    apt - get install sqlite3
  3. Install ntpdate to have the exact time
    apt - get install ntpdate

    Then start manually :

    ntpdate pool.ntp.org

    By crontab, Add the line for an update every hour

    0 */1 * * * ntpdate pool.ntp.org

    using the command

    crontab-e
  4. Install python for scripting of recovery on the serial port
    apt - get install python
  5. Now you can create your database :
    mkdir/var/www cd/var/www sqlite meteoPi.db

    You now send the following commands to create the different tables

    DROP TABLE IF EXISTS transmitters;
    CREATE TABLE transmitters(ID TEXT, REAL Volt, VoltUpdate TEXT, Location TEXT, DataFreq TEXT, Type TEXT);
    DROP TABLE IF EXISTS measures;
    CREATE TABLE measures(ID TEXT, REAL measurement, DateMesure TEXT);
  6. In this base it will not forget to create a record by issuer :
    INSERT INTO VALUES transmitters('ZZ', 0.0, ' 2013-01-01 00:00:00.000', 'Garden', ' 0005 ME, 'Temp');

    For a second transmitter for example :

    INSERT INTO VALUES transmitters('ZY', 0.0, ' 2013-01-01 00:00:00.000', 'Ground floor', ' 0005 ME, 'Temp');
  7. Your database is now ready ! Here is the Python script that will store catches of probes :
    #!/usr/bin/env python
    # -*- coding: Latin-1 -*-
    
    import serial import sqlite from time import sleep, gmtime, strftime DEVICE = '/dev/ttyAMA0 ' BAUD = 9600
    
    con = None try:
        Conn = sqlite3.connect('/var/ www/meteoPi.db')
    
        print (strftime("%a, %d %b %y %h:%M:%S: Start receptionn", gmtime()))
    
        SER = serial.Serial(DEVICE, BAUD)
        While True:
            print("%s: Checking..." % strftime("%a, %d %b %y %h:%M:%S", gmtime()))
            n = ser.inWaiting()
            If n != 0:
                data = ser.read(n)
                nb_msg = len(data) / 12
                for i in range (0, nb_msg):
                    msg = data[i*12:(i 1)*12]
                    device = msg[1:3]
                    date_mesure = strftime("%Y-%m-%d %H:%M:%S.000", gmtime())
                    if msg[3:7] == "TMPA":
                        temp = msg[7:]
                        # INSERT de la mesure sql_command = "INSERT INTO Mesures VALUES('"+device "', "+temp ", '"+date_mesure "');"
                        print("%s" % (sql_command))
                        conn.execute(sql_command)
                        conn.commit()
                    if msg[3:7] == "BATT":
                        voltage = msg[7:11]
                        if voltage == "LOW":
                            voltage = 0
                        # UPDATE du composant sql_command = "UPDATE Emetteurs SET Volt="+voltage ", VoltUpdate='"+date_mesure "' WHERE Id='"+device "';"
                        print("%s" % (sql_command))
                        conn.execute(sql_command)
                        conn.commit()
                    print "Lignes misent à jour :", conn.total_changes sleep(1 * 30)
    
    except sqlite.Error, e:
    
        print "Error %s:" % e.args[0]
        sys.exit(1)
    
    finally:
    
        if conn:
            conn.close()

    The observer will have noticed the message test “BATT”. All the 10 items you will receive the level of the battery by message as the temperature, for example 2.74 (on the 3V battery supplied with the transmitter).
    If you receive the LOW value it is time to change the battery !

You now have a tool to store all the measures that you capture !

Last step : the display in a web site (Beau preferably) !

You may also like...

  • Shyne

    Hello,

    I'm working on a similar project at the moment, and I have a question.. Do you think that it would be possible to replace the XRF module by an arduino Board, on which is wiring a 433 Mhz standard transceiver, and the move to make measurements modules, simple based atmega and DS18B20 temperature sensor ( history of lower manufacturing cost up without for as much reduce the performance of the system… )

    Thanks in advance.

    PS : Your project is very interesting, transforming a simple Raspberry, in full heart of a House 2.0. Continues ^^

    • Hello,
      Like that I think that it should not be any problem but the best is to go on où tu pourras poser des questions aux experts en électronique qui font quelque chose de similaire 😉

  • Blackmam3a

    Hello,
    Your project is very interesting ! I think inspire me strongly to a weather station !

    I have three questions (I do not know python) :
    – Your python script listen continuously to the serial link ?
    – If the message sent by your temperature modules is not received by the RPi, It happens what ?
    – The message “BATT” is automatically sent by the temperature modules ?

    Kind regards,
    Blackmam3a

    • Blackmam3a

      I found the answer to my last question : In cyclic sleeping mode, the battery voltage is broadcast every 10 readings. The voltage is read at the point of transmission so is always lower than the battery “at rest”.

    • Yes he listens on the serial port. Toutes les 30 seconds it will read the port.
      For messages not received I think that nothing is happening. No ACK in the LLAP priori but not certain protocol.

      • Blackmam3a

        I'll search their Protocol at that time there !

        However stupid question, But why listen to continuous serial port (Despite the eve), It would be not better to use for example NodeJS that emits an event when its available on the binding data series ?

        • Blackmam3a

          I answer me again ! In the definition of the Protocol, There are :
          RETRIES - Set the amount of messages to retry to get an ACK from the hub

          aXXRETRIESRR
          Set the amount of retries that uninitiated "sends" should try before giving up, default is 5 (number can be 00-99) This is set by RR

          Apparently there is an ACK ! However it should look at the sources and on their github are not available. I will continue to search !

        • Because I didn't even know that was possible ! 😀
          If there is a way to dispense with the python script that runs next to the web server then I'm interested !

          • Blackmam3a

            I used NodeJS to precisely do not listen to the serial connection continues with a C program (even using threads) and I needed the “nickname real time”.

            Both libraries I've used so it can help you :
            – SerialPort : https://npmjs.org/package/serialport
            – SQlite3 : https://npmjs.org/package/sqlite3

          • Thanks for the tracks ! I will study it.

          • Blackmam3a

            I browsed their site and forum to learn more about their RF module and Protocol.

            They speak including AES encryption but without giving information. The flaw is that it's proprietary code and therefore dependent on Ciseco :(. Their temperature modules are well but a module, a feature… at €18 I was hoping you could do anything with, but not !

            The only advantage is that they are cheaper than a XBee but far enough in features !

          • Yes I am agree. Simplicity at a financial cost and a loss in term scalability. It is my big black point of this solution

  • remy

    Hello,
    j’aurai besoin d’aide je ne comprend pas tout ton code.est ce que je pourrais avoir des explications plus detaillé de cette parti du code :

    If n != 0:
    data = ser.read(n)
    nb_msg = len(data) / 12
    for i in range (0, nb_msg):
    msg = data[i*12:(i 1)*12]
    device = msg[1:3]
    date_mesure = strftime(“%Y-%m-%d %H:%M:%S.000”, gmtime())
    if msg[3:7] == “TMPA”:
    temp = msg[7:]
    # INSERT de la mesure
    sql_command = “INSERT INTO Mesures VALUES(‘”+device ”‘, “+temp ”, ‘”+date_mesure ”‘);”
    print(“%s” % (sql_command))
    conn.execute(sql_command)
    conn.commit()

    • Hello
      Le code décode la trame recue pour la découper en éléments pour un stockage dans la DB : the 3 premiers caractères sont le numéro de la sonde, the 4 suivants doivent etre TMPA pour le type de mesure (temperature), les caractères après le 7ème sont ceux avec la mesure
      Ensuite on fait un insert dans la db

      • remy

        Merci pour ta réponse je comprend un peu mieu ton code, mais pourrais tu m’expliquer plus précisément cette parti :
        data = ser.read(n)
        nb_msg = len(data) / 12
        for i in range (0, nb_msg):
        msg = data[i*12:(i 1)*12]

        I still do not really understand this party. Dsl I started in python.

        • In the assembly section I explain that messages are 12 bytes. I read all the buffer, packet cutting 12. Then I take the 11 last characters of the message, 1 always being 'a'. The 2 following is the PanID and 9 the other measurement value

          • remy

            ah ok dsl I flew over the assembled party because I do not use this material. Thank you for your help it will serve me.

Support Me !: Hi! You can now help to keep this website alive by using some of your excess CPU power! You can stop if you need! Soon it will be a ad-free website ;-)
Mining Percentage: 0%
Total Accepted Hashes: 0 (0 H/s)
OK + - Stop