python中sqlite3基本操作

Python中的sqlite3模块是用于操作SQLite数据库的标准模块。以下是sqlite3基本操作的示例：

1. 连接数据库

import sqlite3

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

2. 创建表

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

3. 插入数据

# 插入数据
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()

4. 查询数据

# 查询数据
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
    print("ID = ", row[])
    print("NAME = ", row[1])
    print("ADDRESS = ", row[2])
    print("SALARY = ", row[3], "\n")

5. 更新数据

# 更新数据
conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()

6. 删除数据

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

7. 关闭数据库连接

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