python 操作SQLite数据库创建表查询添加删除

创建表：

import sqlite3

# 连接数据库
conn = sqlite3.connect('test.db')

# 创建表
conn.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')

# 关闭数据库连接
conn.close()

查询：

import sqlite3

# 连接数据库
conn = sqlite3.connect('test.db')

# 查询数据
cursor = conn.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.close()

添加：

import sqlite3

# 连接数据库
conn = sqlite3.connect('test.db')

# 添加数据
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00)")

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00)")

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

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

# 提交事务
conn.commit()

# 关闭数据库连接
conn.close()

删除：

import sqlite3

# 连接数据库
conn = sqlite3.connect('test.db')

# 删除数据
conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()

print("Total number of rows deleted :", conn.total_changes)

# 查询数据
cursor = conn.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.close()
``