Python3.9数据库SQLite3学起（5）

四、PyCharm中函数在SQLite数据库的使用

1、以创建一个表为例，学习函数在数据库中的应用

• 提出需求：连接数据库（无则创建），然后创建一个表，如果有，提示已存在。
  • 用DDL创建表在前面已经提到过，这里要加一个判断
    • [下面check( )函数是用来判断的]

  
  # 把查询函数的结果赋给变量judge（值为True或False）
  judge = check("sqlite9ge.db", 'teacher')
  # 如果judge值为True，就不创建了
  if judge == False:
  	    sql = f'''create table scores (
  	    id integer primary key autoincrement,
  	    name TEXT,
  	    job TEXT,
  	    age INTEGER,
  	    class TEXT)'''
  	    # 我的游标，用它的execute属性执行一下
  	    myCursor.execute(sql)
  else:
      print("数据库中已存在此表")	
  

  这些代码，带不少硬伤，勉强看看吧，后面再好好长进。

2、检查函数check()要用到sqlite3中的虚拟系统表

• 在和数据库建立连接的同时，数据库会自动创建一个虚拟系统表sqlite_master，这个表里，动态更新数据库里面各类表的记录，看图。
  

  列名	数据类型	类型说明
  type	TEXT	记录项目的类型，如table(普通)、index(索引)、view(视图)、trigger(触发器)
  name	TEXT	记录项目的名称，如表名、索引名等
  tbl_name	TEXT	记录所从属的表名，如索引所在的表名。对于表来说，该列就是表名本身
  rootpage	INT	记录项目在数据库页中存储的编号。对于视图和触发器，该列值为0或者NULL
  sql	TEXT	记录创建该项目的SQL语句

• 用户创建的表，增加和删除，这个虚拟表中的记录也会跟着增加和删除。看看下图，上图的表，是不是在这里面记录着呢。
  

• 言归正传，看看自创的这个check（）函数，是如何通过该系统表判断的

def check(db_name, tbl_name):
    # 1.创建数据库连接
    conn = sqlite3.connect(db_name)
    # 2.创建游标对象
    innerCursor = conn.cursor()
    # 3.定义一个语句———对系统表sqlite_master进行查询，加了where条件
    sql_check = f"""select tbl_name from sqlite_master where name = '{tbl_name}'"""
    # 4.使用游标执行上面sql语句，得到teacher的表名
    innerCursor.execute(sql_check)
    # 5.用游标的fetchall属性得到list表，剥离出teacher名字来（紧跟两个print看看情况）
    tables = innerCursor.fetchall()
    # print(tables)
    # print(type(tables))
    if tables != []:
        if tbl_name in tables[0][0]:
            return True  # 不可建表
    else:
        return False

	上面代码中，必须两个if语句，不然不是创建出错，就是已有表了，报错，想让它说人话，不容易啊。

3、反复试错，最后完成有则不变，无则创建的过程

		创建的过程反复试错，总结如下。
		（1）函数里面的问题，在系统虚拟表中，如何获取已建表的表名，反复尝试，需要print几次看看，再根据返回的字段，一点点切片出来。
		（2）DDL语句创建一个表，暂时不会把表名变量化，后面应该就会明白了。
		（3）原来在创建表的语句下面，增加了用户输入并插入的功能，计划着学号ID自动生成，但是没有做到，问老师提到，sqlite3数据库里面，实现不了，但是主键不为空，自增约束的功能还是有的
		（4）总的代码，也满满的做了注释，如下。

# 导入sqlite模块
import sqlite3
def check(db_name, table_name):
    # 1.创建数据库连接
    conn = sqlite3.connect(db_name)
    # 2.创建游标对象
    innerCursor = conn.cursor()
    # 3.定义一个语句———对系统表sqlite_master进行查询，加了where条件
    sql_check = f"""select name from sqlite_master where name = '{table_name}'"""
    # 4.使用游标执行上面sql语句，得到teacher的表名
    innerCursor.execute(sql_check)
    # 5.用游标的fetchall属性得到list表，剥离出teacher名字来（紧跟两个print看看情况）
    tables = innerCursor.fetchall()
    # print(tables)
    # print(type(tables))
    if tables != []:
        if table_name in tables[0][0]:
            return True  # 不可建表
    else:
        return False

if __name__ == '__main__':
    # (1)打开或创建数据库
    conn = sqlite3.connect("sqlite9ge.db")
    # (2)获取游标
    myCursor = conn.cursor()
    # 把查询函数的结果赋给变量judge（值为True或False）
    judge = check("sqlite9ge.db", 'teacher')
    # (3)加入判断，执行DDL语句创建数据表
    if judge == False:
        sql = """create table teacher (ID integer primary key autoincrement,name TEXT, job TEXT,age INTEGER,class TEXT)"""
        myCursor.execute(sql)
        print("创建表成功")
    else:
        print("数据库中已存在此表")

    # 上面，primary key是主键的意思，不能修改
    # increment是“定期加薪”的意思，autoincrement自动增加约束

    # (4)关闭游标
    myCursor.close()
    # (5)关闭连接
    conn.close()

五、 SQLite3 数据库的常用操作代码

1、数据库模块导入

import sqlite3

2、创建数据库和连接数据库

conn = sqlite3.connect("数据库名字.db")

3、创建游标对象，来执行sql命令

# 变量 = 数据库变量.cursor()
# 对应如下
myCursor = conn.cursor()

4 、创建表

• 存储数据 {name": xx(字段名 数据类型, 字段名 数据类型, 字段名 数据类型,)}

create table student (
    id integer primary key autoincrement,     
    name TEXT,
    age INTEGER,
    class TEXT)

• TEXT：文本型
• INTEGER：整数型
• REAL：浮点型
• NULL：空值
• BLOB：二进制对象

5、插入数据

# 定义列表,用来存储数据列
data = [列1，列2，列3……]
# 定义sql语句字符串变量
sql = """insert into 表名 values(?, ?, ?……)"""
# 用游标的execute属性运行
myCursor.execute(sql, data)

6、查找数据

select * from 表名
select * from 表名 where 字段名 = 值 order by 字段名[desc]

7、更新数据

update 数据表 set 字段名 = 值 where 条件表达式；

8、查找

游标.fetchall()

9、删除

delete from 数据表；# 所有记录全部删除
delete from 数据表 where 条件表达式；