基于Ubuntu-python3-sqlite3的增删改查代码示例

        一共创建了5个函数：创建连接，增删改查。如果已有数据库，改那个功能有点问题，目前还没想出办法，后面有时间修订。

#!/usr/bin/env python3
# coding: utf-8

'''
Python3 sqlite3 数据库增删改查演示 by shizengfeng
'''
 
import sqlite3

def createSqliteUser():
      conn = sqlite3.connect('mydatabase.db')
      print ("Opened database successfully")
      c = conn.cursor()
      
      c.execute('''CREATE TABLE IF NOT EXISTS COMPANY
            (ID INT PRIMARY  KEY     NOT NULL,
             Name            TEXT    NOT NULL,
             Level           TEXT    NOT NULL,
             Email           TEXT    NOT NULL,
             Phone           TEXT    NOT NULL);''')
      print ("Table created successfully")

      conn.commit()
      conn.close()

      
def addSqliteUser():
      conn = sqlite3.connect('mydatabase.db')
      print ("Opened database successfully")
      c = conn.cursor()
      
      c.execute("INSERT OR IGNORE INTO COMPANY (ID,Name,Level,Email,Phone) 
            VALUES (101, 'Chen Jiajian', 'Administrator', '123@163.com', '12345')");

      c.execute("INSERT OR IGNORE INTO COMPANY (ID,Name,Level,Email,Phone) 
      VALUES (102, 'Shi ZengFeng', 'Engineer', '123@163.com', '67890')");

      c.execute("INSERT OR IGNORE INTO COMPANY (ID,Name,Level,Email,Phone) 
      VALUES (103, 'Qian XiaoLiang', 'Engineer', '123@163.com', '35627')");

      c.execute("INSERT OR IGNORE INTO COMPANY (ID,Name,Level,Email,Phone) 
      VALUES (104, 'Jiang Min', 'Viewer', '123@163.com', '98732')");

      conn.commit()
      conn.close()


def deleteSqliteUser():
      conn = sqlite3.connect('mydatabase.db')
      print ("Opened database successfully")
      c = conn.cursor()
      
      #删除表里面的数据
      # c.execute("DELETE FROM COMPANY WHERe ID=4")
      c.execute("DELETe FROM COMPANY WHERe ID=103")

      conn.commit()
      conn.close()


# def changeSqliteUser():
#       conn = sqlite3.connect('mydatabase.db')
#       print ("Opened database successfully")
#       c = conn.cursor()
      
#       #更改表里面的数据
#       # c.execute("UPDATe COMPANY SET  ID=99  WHERe ID=2")
#       c.execute("UPDATE COMPANY SET  ID=99  WHERe ID=2")

#       conn.commit()
#       conn.close()


def findSqliteUser():
      conn = sqlite3.connect('mydatabase.db')
      print ("Opened database successfully")
      c = conn.cursor()
      
      #查询表里面的数据
      cursor = c.execute("SELECT ID, Name, Level, Email, Phone  from COMPANY")
      for row in cursor:
            print(row)
            # print ("ID = ", row[0])
            # print ("NAME = ", row[1])
            # print ("ADDRESS = ", row[2])
            # print ("SALARY = ", row[3], "n")

      conn.commit()
      conn.close()

createSqliteUser()
addSqliteUser()
deleteSqliteUser()
# # changeSqliteUser()
findSqliteUser()

运行结果如下：