python sqlite操作
- 连接数据库
# coding=GBK
# 中文注释
import sqlite3
#1-连接数据库
conn = sqlite3.connect('Analyzer.db')
c = conn.cursor()
print ("Opened database successfully")
- 创建表
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.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.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")
- 关闭数据库连接
conn.close()