Python3 sqlite3 数据库增删改查演示

'''
Python3 sqlite3 数据库增删改查演示 by 郑瑞国
'''

import sqlite3

conn = sqlite3.connect('test6.db')
print ("Opened database successfully")
c = conn.cursor()

try:
    #创建数据库表
    c.execute('''CREATE TABLE COMPANY
           (ID INT PRIMARY KEY     NOT NULL,
           NAME           TEXT    NOT NULL,
           AGE            INT     NOT NULL,
           ADDRESS        CHAR(50),
           SALARY         REAL);''')
    print ("Table created successfully")

    #增加插入数据到表格
    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (1, 'Paul', 32, 'California', 20000.00 )");
        
    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

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

except:
    pass

#删除表里面的数据
c.execute("DELETE FROM COMPANY WHERE ID=4")
c.execute("DELETE FROM COMPANY WHERE ID=3")
#更改表里面的数据
c.execute("UPDATE COMPANY SET  ID=99  WHERE ID=2")
#查询表里面的数据
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")

conn.commit()
conn.close()