python sqlite操作

python sqlite操作

1. 连接数据库

# coding=GBK
# 中文注释

import sqlite3

#1-连接数据库     
conn = sqlite3.connect('Analyzer.db')
c = conn.cursor()
print ("Opened database successfully")
   

2. 创建表

c.execute('''CREATE TABLE T_TestResult
       (FID integer PRIMARY KEY     NOT NULL,
       FSampleId      varchar    ,
       FTestTime      datetime     ,
       FKGSModel      integer,
       FGUID         varchar);''')
conn.commit()
print ("Table created successfully");

3. 插入数据库

3.1 简单插入数据库

c.execute('''INSERT INTO T_TestResult 
          (FID,FSampleId,FTestTime,FKGSModel,FGUID) 
          VALUES(NULL,'TEST_001','2020/01/07 08:46:14',2,'20200107_084614_825');
       ''')
conn.commit()
print ("Table insert successfully");

3.2 按照字典插入数据库

# 插入数据库一条数据
# table_name 表名
# map_value 字段：值
# 注：字符串值需要单引号' '，数字需要转为str,值为none的抛弃，值为字符串且为空的抛弃
def insert_value(c,table_name,map_value):
    insert_str = "INSERT INTO "+ table_name + " ("
    for key,value in map_value.items():
        if value == "NULL":
            insert_str += (key + ",")
        elif(type(value) == type("str") and  len(value) != 0 and value.isspace() != True ):
            insert_str += (key + ",")
        elif (value != None and type(value) != type("str")):
            insert_str += (key + ",")
    
    # 去除最后一个","逗号
    insert_str = insert_str[:-1]
    insert_str  += (") VALUES (")
    for key,value in map_value.items():
        if value == "NULL":
            insert_str += (value + ",")
        elif(type(value) == type("str") and  len(value) != 0 and value.isspace() != True ):
            insert_str += ("'" + value + "'" + ",")
        elif (value != None and type(value) != type("str")):
            insert_str += (str(value) + ",")
            
    insert_str = insert_str[:-1]
    insert_str  += (");")
    
    #print(insert_str)
    c.execute(insert_str)
    
insert_value(c,"T_TestResult",map_TestResult)  

4. 查询数据库

4.1 查询数据库

c.execute("SELECT *  from T_TestResult;")
col_name_value_1 = c.fetchall()
for one_value in col_name_value_1:
    print (one_value)
#(1, 'TEST_001', '2020/01/07 08:46:14', 2, '20200107_084614_825')

4.2 查询数据库数据，同时按照字段获取结果

# 查询的两个表，组合成字典
def makeMap(name_list,value_list):
    # 空字典
    map_value = {}
    if (len(name_list) == len(value_list)):
        for index in range(0,len(name_list)):
            print(name_list[index],value_list[index])
            map_value[name_list[index]] = value_list[index]
    return map_value
    
 # 查询表，返回结果字典列表
def query_value(c,table_name):
    c.execute("SELECT *  from " + table_name )
    #获取查询的值列表fetchall 查询全部，返回列表的列表
    col_name_value_1 = c.fetchall()
    #获取查询的字段列表
    col_name_list_1  = [tuple[0] for tuple in c.description]
    #申请空表，表里装字典
    map_value = list() 
    for one_value in col_name_value_1:
        map_value.append(makeMap (col_name_list_1,one_value))
    return map_value
    
list_TestResult = query_value(c,"T_TestResult")

5. 关闭数据库连接

conn.close()