如何从已经发布到云端的数据库中删除数据？

Question

我在数据库中生成虚拟数据，然后发布到云。 一旦发布，我想从数据库中删除该条目

我知道我需要在publish方法中发送'mid'值，然后调用deleteFromDb（）函数。但是mid总是1，即使我返回ret.mid=index。索引是从数据库检索到的主键

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()

Answer 1

我假设您使用的是sqlite3，因此需要连接并执行delete语句进行删除，然后commit()进行保存的更改。像这样试试

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()