0

I am generating dummy data in the database and then publishing to cloud. As soon as it is published, I want to delete that entry from the database.

I understand that I need to send the 'mid' value in the publish method then call deleteFromDb() function. But mid is always 1, even though I return ret.mid=index. Index is the primary key retrieved from the database. 

def on_publish(unused_client,unused_userdata,mid):
    print('on_publish')
    deleteFromDb(mid)


def main() :
    TableSchema="""
      create table if not exists heartbeat(
        id integer primary key autoincrement,
        Date_Time text,
        Heartbeat text
      );

      """

    while True:
        conn = sqlite3.connect(dbFile)
        print("Connected to db")
        conn.execute('pragma foreign_keys = on')
        conn.commit()
        curs = conn.cursor()

        print "Writing to db..."
        sqlite3.complete_statement(TableSchema)
        curs.executescript(TableSchema)
        conn.commit()

        rectime=strftime("%Y-%m-%d %H:%M:%S", gmtime())
        res="ON"
        curs.execute("insert into heartbeat (Date_Time, Heartbeat) 
                    values (?,?)",[rectime,res])
        conn.commit()
        print "Inserted Heartbeat Data into Database."
        for row in curs.execute("select * from heartbeat"):
            index=row[0]
            continue
        conn.commit()
        encoded_row=''
        encoded_row=json.dumps(row) #Dumped in the form of str
        print encoded_row

        client = mqtt.Client(client_id=_CLIENT_ID)
        client.username_pw_set (username='unused', password=create_jwt(project_id, ssl_private_key_filepath, ssl_algorithm))    
        client=mqtt.Client()

        client.on_connect = on_connect
        client.on_publish=on_publish
        client.on_message = on_message

        client.tls_set(ca_certs=root_cert_filepath)
        client.connect('mqtt.googleapis.com', 8883,60)

        client.loop_start()
        ret=client.publish(_MQTT_TOPIC,encoded_row,qos=1)
        time.sleep(0.5)
        ret.mid=index
        client.loop_stop()
        #print(ret.mid)

        curs.close()
        conn.close()
hardillb
  • 54,545
  • 11
  • 67
  • 105
Ishita Roy
  • 25
  • 5

1 Answers1

0

I am assuming you are using sqlite3 so you need to connect and execute a delete statement to delete and then commit() to make changes you saved. Try like this

import sqlite3

deleteFromDb(mid):
    con=sqlite3.connect("mydatabase.db") #your db name  
    cursor=con.cursor()
    cursor.execute("DELETE FROM TABLE_NAME WHERE ID = %s" % mid)
    con.commit()
    con.close()
Yugandhar Chaudhari
  • 3,831
  • 3
  • 24
  • 40