python sqlite 操作

python sqlite 操作

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import sqlite3
import threading

class ImgDatabase():

    lock = threading.Lock() #申请一把锁

    def __init__(self):
        print('__init__')
        self.db = sqlite3.connect("D:/copyimg.db")
        try:
            cu = self.db.cursor()
            cu.execute("create table system (name varchar(10) UNIQUE, userimg text NULL, debugimg text NULL, extra text NULL, done integer)")
            self.db.commit()
        except:
            # print('table system has exist')
            pass

    def __del__(self):
        print('__del__')
        self.db.close()

    def addData(self, name, useAdress, debugAdress):
        self.lock.acquire()  #加锁
        print('addData ', [name, useAdress, debugAdress, ''])
        try:
            self.db.execute("insert into system values (?,?,?,?,?)", [name, useAdress, debugAdress, None, 0])
            self.db.commit()
        except:   
            print('table name has exist in the table')
        self.lock.release() #释放锁

    def updateData(self, data):
        self.lock.acquire()  #加锁
        excec_str = "update system set done=1 where name = " + '\'' + data + '\''
        print('excec_str:', excec_str)
        try:
            cu = self.db.cursor() 
            cu.execute(excec_str)
            self.db.commit()
        except:   
            print('updateData failed')
        self.lock.release() #释放锁

    def deleteData(self, data):
        self.lock.acquire()  #加锁
        excec_str = "delete from system where name = " + '\'' + data + '\''
        print('excec_str:', excec_str)
        try:
            cu = self.db.cursor() 
            cu.execute(excec_str)
            self.db.commit()
        except:   
            print('deleteData failed')
        self.lock.release() #释放锁

    def getData(self):
        cu = self.db.cursor() 
        cu.execute("select * from system")
        images = cu.fetchall()
        return images


if __name__ == '__main__':
    test=ImgDatabase()
    test.addData('hello3', '127.8.4', '233.45.67')
    images = test.getData()
    for image in images:
        if image[4] == 0:
            print(image)
            test.updateData(image[0])
        else:
            print('soon delete', image)
            test.deleteData(image[0])