150-数据库操作和模型-SQLite数据库连接

SQLite数据库连接

SQLite数据库是一个常用的开源、跨平台轻量化本机版数据库,可以作为嵌人式数据库使用。

Python 自带的 sqlite3 可以实现对 SQLite 数据库的查询使用前需要用import sqlite3 语句导人 sglite3。下面介绍 sqlite3 的使用方法。

sqlite3提供了两个基本的类:数据库连接 Connection 和游标Cursor。

用sqlite3的connect(database:str)方法打开一个已经存在的SQLite 数据库或新建一个数据库并返回Connection对象

• 其中databaseName是本机上已经存储的数据库文件或者新建立的数据库文件名称,
• 还可取";memory:"表示在内存中创建临时数据库。

用Connection 对象的cursor()方法获取 Cursor 对象,用Cursor 对象提供的方法可以执行 SQL 指令。

数据库连接Connection 的常用方法

数据库连接Connection的常用方法如表所示主要方法是

• 用cursor()创建并返回Cursor对象;用commit()方法提交对数据库的操作;
• 用rollback()方法放弃自上次调用commit()方法后对数据库的操作。

Connection的方法及参数类型	说明
cursor()	创建并返回 Cursor 对象
commit()	提交当前的事务
interrupt()	停止还未执行的操作
rollback()	放弃对数据库的操作,返回到调用commit()时的状态
close()	关闭数据库
backup(target: Connection)	将数据备份到另外一个数据库中
execute(sql: str[,parameters])	调用cursor()方法获取Cursor对象,并调用Cursor的execute()方法 执行一条SQL命令。parameters是SQL中的占位符的值
executemany(sql: str,sequence of parameters)	调用cursor()方法获取Cursor对象,并调用Cursor的 executemany()方 法执行多条 SQL命令
executescript(sql_script: str)	调用cursor()方法获取Cursor对象,并调用Cursor的 executeseript()方 法执行多条 SQL命令
create_function(name: str,num_ params:int,func:Function)	将func 函数定义成 SQL指令中可以使用的函数,其中 name 是SQL 中使用的函数名,num_params是函数参数的个数
lotal_changes	操作数据后,返回受影响的行的数量

游标Cursor 的常用方法

游标Cursor 用于执行 SQL 命令。Cursor 的常用方法如表所示,主要方法介绍如下

• 执行一条SQL命令

  • 可以用execute(sql:str[;parameters])方法

    • parameters是可选的,用于SQL命令中有占位符的情况,占位符可取"?"。

    • parameters 的取值是序列(如元组、列表)

      execute("insert into tableName values(?,?)",("ABC",2015));
      

    • 也可以在SQL命令中用名称做占位符,这时 parameters 是字典且字典的键是占位符的名称,例如

      execute("select * from tableName where birthday= :year",("year",2015));
      

  • 用executemany(sql:str,sequence of parameters)方法可以重复执行一条 SQL命令,重复执行的次数是参数序列的长度 len(sequence of parameters),例如

    executemany("insert into tableName values(?,?)",(("A",2015),("B",2016)("C",2017))
    # 执行3次,占位符(?,?)依次取("A",2015),("B",2016)("C",2017)
    

• 要执行多条 SQL 命令,需要用executescript(sql_script:str)方法,例如

  executescript("insert into tableName values("A",2015); insert into tableNamevalues("B",2016);insert into tableName values("C",2017);")
  

• 要获取数据表中的数据,可以用execute()方法执行SQL的SELECT命令

  • 也可用etchone()方法获取当前行的下一行对象,如果当前行是最后一行,则返回值是None;
  • 用fetchmany(size=cursor.arraysize)方法可以获取当前行后的多行记录
    • 如果没有给出行的数量,则用cursor.arraysize 属性值确定行数
    • 用fetchall()方法获取所有剩余的行。

Cursor的方法及参数类型	说 明
execute(sql: str[,parameters])	执行一条 SQL命令,parameters 是SQL中的占位符的值
executemany(sql: str,sequence of parameters)	重复执行SQL命令,parameters是SQL中的占位符的值
executescrip1(sql_script: str)	执行多条SQL.命令
fetchone()	获取数据表中的下一行,返回由行数据构成的元组或 None
fetchmany(size=cursor. arraysize)	获取多行数据,参数size是要获取的行数,并返回多行数据构成的 列表
arraysize	设置或获取Letchmany()方法每次默认读取的行数
fetchall()	获取所有剩余的行,并返回多行数据构成的列表
connection	获取 Connection对象
lastrowid	获取用execute()方法执行sql指令INSERT或 REPLACE后,最后 插人的行的ID号,用executemany方法或 executescript()方法插人 行不改变 lastrowid 的值
close()	关闭游标,游标不可再使用

Python 的数据类型与SQLite 的数据类型的相互转换

SQLite 支持的数据类型较少,它的数据类型有NULL、INTEGER、REAL、TEXT和BLOB,在往SQLite 数据库中写入数据和从SQLite数据中读取数据时需要注意 Python和SQLite数据类型之间的转换。

Python 的数据类型与 SQLite 的数据类型的相互转换如表所示。

SQLite的TEXT数据类型转换成 Python 数据类型时,与数据库连接对象Connection的 text_factory 属性有关默认将TEXT数据类型转换成Python的 str 数据型

• 如果设置context_factory=bytes,则TEXT类型将转换成 bytes 类型

Python数据类型	SQLite 数据类型	SQLite 数据类型	Python数据类型
None	NULL	NULL	None
int	INTEGER	INTEGER	int
float	REAL	REAL	float
str	TEXT	TEXT	与Connection对象的属性text factory 有关,默认是str
bytes	BLOB	BLOB	bytes

对SOLite数据库查询的应用实例

下面的程序建立一个数据库 student _score.db,建立一个表格 score,并输人4个学生的考试成绩,然后重新打开数据库,并输出结果。

# -*- coding: UTF-8 -*-
# File date: Hi_2023/3/12 22:03
# File_name: 01-对SOLite数据库查询的应用实例.py
import os
import sqlite3

dbName ="./db/student_score.db"# 数据库保存位置和数据库名称


def create_demo():
    con1 = sqlite3.connect(dbName)# 新建数据库链接
    cur = con1.cursor()# 创建游标

    cur.execute('''create table score(ID INTEGER,NAME TEXT,语文 REAL,数学 REAL)''')# 执行一条 SQL 命令,创建表格
    information =((2001,"张",78,89),(2002,"刘",88,82.5),(2003,"王",78.5,83),(2004,"张",72.5,86))# 学生考试信息
    cur.executemany("INSERT INTO score VALUES(?,?,?,?)",information)# 执行多条 SQL命令
    con1.commit()# 提交事务
    con1.close()# 关闭数据库


def connect_demo():
    con = sqlite3.connect(dbName)# 打开数据库
    concur = con.cursor()# 创建游标
    for row in concur.execute("SELECT * From score"):  # 查询数据表中的内容
        print(row)

    concur.execute("SELECT * From score Where name ='张'")# 重新查询数据表中的内容
    rows = concur.fetchall()# 获取数据表中的所有内容

    for row in rows:
        print(row)
    con.close()# 关闭数据库
    os.remove("./db/student_score.db")


if __name__ == '__main__':
    create_demo()
    connect_demo()