Python-数据库sqlite3运用方法记录

import sqlite3
import datetime
import os


'''数据库管理类建立'''
class Db_control():
  # 连接到SQLite数据库
  def connect_db(self,path=None):
    #数据库文件是mrsoft,db,如果文件不存在，会自动在当前目录创建
    path_yuan = os.getcwd()
    if path == None:
      try:
        conn = sqlite3.connect(path_yuan + './config_file/PG_test_information.db')
      except Exception as error:
        print("create_table:%s " % error)
    else:
      conn = sqlite3.connect(path)   
    #创建一个Cursor
    cursor = conn.cursor ()
    return conn,cursor
	
	# 关闭数据库连接
  def close_sq_db(self,conn,cursor):
    cursor.close()
    conn.close()
    
  #创建表格
  def create_table(self,table_name,table_list=None,path=None):
    #连接到SQLite数据库
    #数据库文件是mrsoft,db,如果文件不存在，会自动在当前目录创建
    path_yuan = os.getcwd()
    if path == None:
      try:
        conn = sqlite3.connect(path_yuan + './config_file/PG_test_information.db')
      except Exception as error:
        print("create_table:%s " % error)
    else:
      conn = sqlite3.connect(path)  
    #创建一个Cursor
    cursor = conn.cursor ()

    # 如果该数据库操作不需要返回结果，就直接用conn.execute查询，根据数据库事务隔离级别的不同，可能修改数据库需要conn.commit
    #执行一条SQL语句,创建一个表
    if table_list ==None:
      try:
          cursor.execute("CREATE TABLE {}(id integer PRIMARY KEY, name text,Information text, hireDate text)".format(table_name))
          conn.commit()
      except Exception as error:
          print("创建表错误：%s" % error)
    else:
      #数据库表格式创建/暂时无法做到自动修改格式
      table_list_data = table_list
      if len(table_list_data) == 4:
        try:
            cursor.execute("CREATE TABLE {}(id integer PRIMARY KEY, {} text,{} text, {} text,{} text)".format(table_name,
            table_list_data[0],table_list_data[1],table_list_data[2],table_list_data[3]))
            conn.commit()
        except Exception as error:
            print("创建表错误：%s" % error)
      elif len(table_list_data) == 3:
        try:
            cursor.execute("CREATE TABLE {}(id integer PRIMARY KEY, {} text,{} text, {} text,{} text)".format(table_name,
            table_list_data[0],table_list_data[1],table_list_data[2]))
            conn.commit()
        except Exception as error:
            print("创建表错误：%s" % error)
    #关闭游标
    cursor.close ()
    #关闭Connection
    conn.close ()

  #获取时间
  def get_time(self):
    times = datetime.datetime.now()
    return times
  
  #添加数据
  def add_data(self,table,value,times=None,path=None):
    path_yuan = os.getcwd()
    try:
      if path == None:
        #路径自行选择
        conn = sqlite3.connect(path_yuan + './config_file/PG_test_information.db')
      else:
        conn = sqlite3.connect(path)  
      #创建一个Cursor
      cursor = conn.cursor ()    
      #要在表中插入数据，我们使用 INSERT INTO 语句。
      # cursor.execute('''INSERT INTO PG VALUES("5", 'Johns', 'Manager', '2017-01-04')''')
      # cursor.execute('''INSERT INTO {} VALUES({}, {}, {}, "{}")'''.format(table,value[0],value[1],value[2],times))
      if len(value[0]) == 4:
        cursor.executemany("INSERT INTO {} VALUES(?,?,?,?)".format(table), value)
      elif len(value[0]) == 5:
        cursor.executemany("INSERT INTO {} VALUES(?,?,?,?,?)".format(table), value)    
      conn.commit()
      #关闭游标
      cursor.close ()
      #关闭Connection
      conn.close ()
    except Exception as error:
      error_data = "add_data异常错误: %s" % error
      print(error_data)
      return error_data

  #列出表格
  def sql_find_table(self,path=None): 
    conn,cursorObj = self.connect_db(path)
    cursorObj.execute('SELECT name from sqlite_master where type= "table"')
    table_name = cursorObj.fetchall()
    print(table_name)
    self.close_sq_db(conn,cursorObj)
    if len(table_name) ==1: 
      return table_name[0][0]
    elif len(table_name) >1:
      return table_name 
    
  #更新表格中的数据
  def updata_data(self,table,path,update_data):
    try:
      conn,cursor = self.connect_db(path)
      #更新对应数据中的信息
      cursor.execute('DELETE FROM {}'.format(table)) #删除表格中的所有数据
      for date in update_data:
        if date[0] != "":
          shuju =[]
          dates = tuple(date)
          shuju.append(dates)
          print(shuju)
          cursor.executemany("INSERT INTO {} VALUES(?,?,?,?,?)".format(table), shuju)
        conn.commit()
      self.close_sq_db(conn,cursor)
    except Exception as error:
      print("updata_data: %s" % error)

  #获取所有数据 元组信息
  def get_all_data(self,table,path=None):
    conn,cursor = self.connect_db(path)
    cursor.execute('SELECT * FROM {}'.format(table))
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    self.close_sq_db(conn,cursor)
    return rows

  #查找数据，打印数据
  def find_data(self,table,find_name,data,fuhao= None,path=None):
    conn,cursor = self.connect_db(path)
    if fuhao == None or fuhao == "=" :
      cursor.execute('SELECT * FROM {} WHERE {} = {}'.format(table,find_name,data))
    elif fuhao == ">":
      cursor.execute('SELECT * FROM {} WHERE {} > {}'.format(table,find_name,data))
    elif fuhao == "<":
      cursor.execute('SELECT * FROM {} WHERE {} < {}'.format(table,find_name,data))    
    rows = cursor.fetchall()
    for row in rows:
      print(row)
    self.close_sq_db(conn,cursor)
    return rows

if __name__ == "__main__":
  sq_start = Db_control()
  # 路径自行选择，或者自行创建数据库文件
  sq_start.create_table("test_item_set_value",table_list=["测试1","测试2","测试3","测试4"],
        path="./Test_UI_control/Test_item.db")
 
	# 数据库查询相关数据格式的方法备注:
	shuju = sq_start.find_data("test_item_set_value",find_name="测试项",data="\"测试1\"",fuhao="=",path=path)
	print(shuju)