sqlite3 数据库的操作

数据库是程序中常用的部分，sqlite3是puthon自带的数据库程序，使用上比较方便。

sqlite3 操作上需要首先进行连接，由于connect函数无论数据库文件在不存在时会自动创建，为防止创建的文件为空，软件运行时首先判断文件是否存在，不存在则创建数据库的所有信息，也可以在内存中创建数据库，路径采用:memory:即表明在内存中创建之后设置游标cursor，再通过execute对内容进行操作，完成后调用commit写入信息，然后关闭游标和数据库。

主要操作都在execute函数中，按照特定格式发送字符创即可。

创建表

c.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')

数据类型主要是以下几种，其他类型也是用下面几种保存的，可以参照 https://www.runoob.com/sqlite/sqlite-data-types.html

存储类	描述
NULL	值是一个 NULL 值。
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	值是一个 blob 数据，完全根据它的输入存储。

 

INSERT 操作

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")

SELECT 操作

cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

会按照给出的项顺序依次返回项目信息

UPDATE 操作

c.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")

更新项信息

DELETE 操作

c.execute("DELETE from COMPANY where ID=2;")

注意，所有的操作后必须调用commit才能确认写入

完整操作

class LConfigFile:
    """ 配置文件读取/设置  数据库读取/设置 """

    def __init__(self):
        self.sq_path = ''  # 数据库文件路径
        self.device_inf = {'active': False, 'name': '', 'load_ip': '', 'ir_ip': '0.0.0.0', 'ccd_rtsp': 'rtsp://', 'Correction': [0.0]*9}


    def fun_create_sq(self):
        conn = sqlite3.connect(self.sq_path)
        cur = conn.cursor()
        # cur.execute('DROP TABLE DEVICE')
        sql_create = '''CREATE TABLE 'DEVICE'
                  ('ID'            INTEGER,
                   'ACTIVE'        BLOB,
                   'DEVICE_NAME'   TEXT,
                   'LOAD_IP'       TEXT,
                   'IR_IP'         TEXT,
                   'CCD_RTSP'      TEXT,
                   '三点校正1'       REAL,
                   '三点校正2'       REAL,
                   '三点校正3'       REAL,
                   '三点校正4'       REAL,
                   '三点校正5'       REAL,
                   '三点校正6'       REAL,
                   '三点校正7'       REAL,
                   '三点校正8'       REAL,
                   '三点校正9'       REAL);'''
        cur.execute(sql_create)
        for n in range(0, 16):
            sql_text = "INSERT INTO 'DEVICE' VALUES(%d, false, ' ', '0.0.0.0', '0.0.0.0', 'rtsp://', 0, 0, 0, 0, 0, 0, 0, 0, 0)" % n
            cur.execute(sql_text)
        conn.commit()
        cur.close()
        conn.close()

    def fun_read_configfile(self, path):
        self.sq_path = path + 'config.db'
        if not os.path.exists(self.sq_path):  # 如果文件不存在则创建文件
            self.fun_create_sq()
        # 清除字典中的数据
        g_device.clear()
        # 读取数据库
        conn = sqlite3.connect(self.sq_path)
        cur = conn.cursor()
        cur.execute('SELECT * FROM DEVICE')  # 读取全部数据
        s = cur.fetchall()
        for n in range(0, 16):
            self.device_inf["active"] = s[n][1] == 'True'  # 数据有效 由于sqlite3存储的bool类型为字符串，不能直接转换成bool，只能先比较
            self.device_inf["name"] = s[n][2]  # 设备名
            self.device_inf["load_ip"] = s[n][3]  # 本地IP
            self.device_inf["ir_ip"] = s[n][4]  # 红外IP
            self.device_inf["ccd_rtsp"] = s[n][5]  # CCD rtsp
            for m in range(0, 8):
                self.device_inf["Correction"][m] = s[n][6+m]  # 三点校正
            g_device.append(self.device_inf.copy())  # 注意需要拷贝
        cur.close()
        conn.close()

    def fun_write_device_configfile(self, dev):
        conn = sqlite3.connect(self.sq_path)
        cur = conn.cursor()
        cur.execute("UPDATE DEVICE set ACTIVE = '%s' where ID = %d" % (bool(g_device[dev]['active']), dev))
        cur.execute("UPDATE DEVICE set DEVICE_NAME = '%s' where ID = %d" % (g_device[dev]['name'], dev))
        cur.execute("UPDATE DEVICE set LOAD_IP = '%s' where ID = %d" % (g_device[dev]['load_ip'], dev))
        cur.execute("UPDATE DEVICE set IR_IP = '%s' where ID = %d" % (g_device[dev]['ir_ip'], dev))
        cur.execute("UPDATE DEVICE set CCD_RTSP = '%s' where ID = %d" % (g_device[dev]['ccd_rtsp'], dev))
        for n in range(0, 9):
            cur.execute("UPDATE 'DEVICE' set '三点校正%d' = %f where 'ID' = %d" % (n+1, g_device[dev]['Correction'][n], dev))
        conn.commit()
        cur.close()
        conn.close()