python sqlite3 基本操作

创建数据库

import sqlite3

# 连接数据库(如果不存在则创建)
conn = sqlite3.connect('test.db')
print("Opened database successfully")

# 创建游标
c = conn.cursor()

# SQL语句
sql = """
        CREATE TABLE STUDENTS(
            ID INT PRIMARY KEY NOT NULL,
            NAME TEXT NOT NULL,
            AGE INT NOT NULL,
            ADDRESS CHAR(50)
        );
    """
# 创建表
c.execute(sql)

print("Table created successfully")

# 关闭游标
c.close()

# 提交事物
conn.commit()

# 关闭连接
conn.close()

插入数据

import sqlite3

# 连接数据库(如果不存在则创建)
conn = sqlite3.connect('test.db')
print("Opened database successfully")

# 创建游标
c = conn.cursor()

# 插入数据
c.execute("INSERT INTO STUDENTS (ID,NAME,AGE,ADDRESS) VALUES (0, 'Paul', 32, 'California')")
c.execute("INSERT INTO STUDENTS (ID,NAME,AGE,ADDRESS) VALUES (0, 'Allen', 25, 'Texas')")
c.execute("INSERT INTO STUDENTS (ID,NAME,AGE,ADDRESS) VALUES (0, 'Teddy', 23, 'Norway')")
c.execute("INSERT INTO STUDENTS (ID,NAME,AGE,ADDRESS) VALUES (0, 'Mark', 25, 'Rich-Mond')")

print("Records created successfully")

# 关闭游标
c.close()

# 提交事物
conn.commit()

# 关闭连接
conn.close()

删除数据

import sqlite3

# 连接数据库(如果不存在则创建)
conn = sqlite3.connect('test.db')
print("Opened database successfully")

# 创建游标
c = conn.cursor()

# 删除数据
c.execute("DELETE FROM STUDENTS WHERE ID=2;")

# 关闭游标
c.close()

# 提交事物
conn.commit()

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

print("Operation done successfully")

# 关闭连接
conn.close()

修改数据

import sqlite3

# 连接数据库(如果不存在则创建)
conn = sqlite3.connect('test.db')
print("Opened database successfully")

# 创建游标
c = conn.cursor()

# 修改数据
c.execute("UPDATE STUDENTS SET age = 25 WHERE ID=1")

# 关闭游标
c.close()

# 提交事物
conn.commit()

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

print("Operation done successfully")

# 关闭连接
conn.close()

查找数据

import sqlite3

# 连接数据库(如果不存在则创建)
conn = sqlite3.connect('test.db')
print("Opened database successfully")

# 创建游标
c = conn.cursor()

# 查找数据
cursor = c.execute("SELECT id, name, age, address FROM STUDENTS")
for i in cursor:
    print('id:', i[0])
    print('name:', i[1])
    print('age:', i[2])

# 关闭游标
c.close()

print("Operation done successfully")

# 关闭连接
conn.close()