Search code examples
pythondatabaseraspberry-pimariadb

Inserting to Mariadb from multiple scripts

So currently I have 1 script inserting sensor data into my weather database every hour using python.

I have now added a second script to add rainfall data into the same table also every hour.

Now the problem: When the 2nd script inserts, all other values get 'zeroed'. As displayed in grafana.

am I overwriting somewhere or, if someone could point me in the right direction

Weather sensors insert statement

 sql=("INSERT INTO WEATHER_MEASUREMENT (AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY) VALUES ({},{},{})".format(temperature,pressure,humidity))
         mycursor.execute(sql)
         weatherdb.commit()

Rainfall sensors insert

sql=("INSERT INTO WEATHER_MEASUREMENT (RAINFALL) VALUES ({})".format(rainfall))
mycursor.execute(sql)
weatherdb.commit()
Solution

  • Tell me if I understand it right: Your table “WEATHER_MEASUREMENT” has 4 columns (apart ID): AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY and RAINFALL. When you add RAINFALL value it creates a new row in your table with other column values at “NULL” and this is the problem? If this is the case, you probably want to update existing row with a query like:

    sql = ("""
          UPDATE _ WEATHER_MEASUREMENT
          SET RAINFALL = "{}"
          WHERE id_of_the_row = {}
          """.format(rainfall, id)
mycursor.execute(sql)

    You will need to find a way to figure out the ID of the row you just created with your Weather sensor insert statement (maybe search for last inserted row if you are sure of timings).

    /!\NOTE/!\ As mentioned in comments by victor-stafusa-bozonacadeia, sql request made with formated string (as provided in my example) is vulnerable to SQL injection. You can safely use it only if variables used for formating are coming from your code and are known to be safe. Do not use string formating if variable content comes from outside, like user input.

    If there is a chance that query content comes from unsure source, one should "sanitize query". The closest way to a raw SQL request that allows to sanitize request that i found is using sqlalchemy module with "text" query and "bind parameters". You can learn more about here : https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.text