python 操作 sqlite 数据库(建表, 增删改查)

import sqlite3


def create(conn):
    """
    创建相应的数据表
    """
    sql_create = '''
    CREATE TABLE `users` (
      `id`  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      `username`  TEXT NOT NULL UNIQUE,
      `password`  TEXT NOT NULL,
      `email` TEXT
    )
    '''
    # 用 execute 执行一条 sql 语句
    conn.execute(sql_create)
    print('创建成功')


def insert(conn, username, password, email):
    """
    插入一行的数据
    """
    sql_insert = '''
    INSERT INTO
      users(username, password, email)
    VALUES
      (?, ?, ?);
    '''
    # 下面写法会被 SQL 注入
    # sql = '''
    # INSERT INTO
    #   users(username, password, email)
    # VALUES
    #   ("{}", "{}", "{}")
    # '''.format('123', '345', 'a.com')
    # conn.execute(sql)
    # 参数拼接要用 ?, execute 中的参数传递必须是一个 tuple 类型
    conn.execute(sql_insert, (username, password, email))
    print('插入数据成功')


def select(conn, usr, pwd):
    """
    根据不同的条件查找数据
    当用户名和密码都正确时, 查询数据
    """
    sql = '''
    SELECT
        id, username, email
    FROM
      users
    WHERE
      username=? and password=?
    '''
    cursor = conn.execute(sql, (usr, pwd))
    print('一条数据', list(cursor))

    # 被 SQL 注入的情况
    # usr = 'yin" or "1"="1'
    # pwd = 'gua'
    # sql = '''
    # SELECT
    #     id, username, email
    # FROM
    #     users
    # WHERE
    #     username="{}" and password="{}"
    # '''.format(usr, pwd)
    # cursor = conn.execute(sql)
    # print('一条数据', list(cursor))


def delete(conn, user_id):
    """
    根据 id 删除对应的那条数据
    """
    sql_delte = '''
    DELETE FROM
      users
    WHERE
      id=?
    '''
    # tuple 只有一个元素的时候必须是这种写法
    conn.execute(sql_delte, (user_id,))


def update(conn, user_id, email):
    """
    更新相应部分的数据
    """
    sql_update = '''
    UPDATE
      `users`
    SET
      `email`=?
    WHERE
      `id`=?
    '''
    conn.execute(sql_update, (email, user_id))


def main():
    # 指定数据库名字并打开, 无此数据库, 自动创建
    db_path = 'web.sqlite'
    conn = sqlite3.connect(db_path)
    print("打开了数据库")

    # create(conn)
    # insert(conn, 'sql_', '123', 'a@qq.com')
    # delete(conn, 1)
    update(conn, 3, '119@qq.com')
    select(conn, 'asdads', 'asd')

    # 必须用 commit 函数提交你的修改
    # 否则你的修改不会被写入数据库
    conn.commit()
    conn.close()

if __name__ == '__main__':
    main()

以上程序中所用到的知识点如下：

• sqlitebrowser

# sqlite 的数据库管理软件 sqlitebrowser

# 下载地址: 点此下载

• 连接数据库

用法: conn = sqlite3.connect(db_path)

# db_path 即为数据库文件路径

# 若无此数据库, 会自动创建

• 执行 sql 语句

用法: conn.execute(sql)

# 括号里即为需执行的 sql 语句

• 参数传递的两种方法

用到 ?, execute 的参数拼接

# 参数拼接要用 ?, execute 中的参数传递必须是一个 tuple 类型

用到 {}, format 的参数拼接

# 用 string.format 拼 sql, 是一个严重的安全漏洞, 会被 SQL 注入

• 错误提示 database is locked

# SQLite 只支持库级锁

# 库级锁意味着同时只能允许一个写操作

# 出现此问题, 应将多余的写操作进程关闭

• 被 SQL 注入的情况

# 见上程序 select 函数注释部分

• 提交数据库修改

用法: conn.commit()

# 必须用 commit 函数提交你的修改, 否则修改不会被写入数据库

• 数据库连接的关闭

用法: conn.close()

# 数据库使用结束后, 需要关闭数据库