SQLite数据库
概念
SQLite是内嵌在Python中的轻量级、基于磁盘文件的数据库管理系统,不需要安装和配置服务器,支持使用SQL语句来访问数据库。
SQLite是一个开源的关系型数据库,具有零配置、自我包含、便于传输等优点。当多个线程同时访问同一个数据库并试图写入数据时,每一时刻只有一个线程可以写入数据。
关系型数据库的数据存放于多个二维表中,在表中,行称为记录(record),列称为字段(field),一个数据库中可以包含多个表。
连接 SQLite
Python 3 默认自带 SQLite模块,因此不需要额外安装。访问和操作SQLite数据时,首先导入sqlite3模块(内置),然后创建一个与数据库关联的Connection对象。例如:
以下Python代码显示了如何连接到一个指定的数据库。 如果数据库不存在,那么它将被创建,最后将返回一个数据库对象。
import sqlite3
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
现在,运行上面的程序在当前代码文件所在目录中创建数据库:py-sqlite.db
,可以根据需要更改路径。并按如下所示执行。 如果数据库成功创建,则会提供以下消息:
Opened database successfully
由于SQLite3并不是可视化呈现的,因此可以使用第三方工具协助管理数据库。如:SQLlit Expert、Navicat Premium软件。
建立数据库连接对象后,用数据库连接对象的execute()方法可执行SQL语句,对数据库及表实现创建、插入、修改、删除和查询操作。SQL语句大小写不敏感,可分行,关键字之间可使用空格。
SQLite数据库连接和访问步骤
SQLite是Python的内置库,用import sqlite3引用后,访问SQLite数据库的步骤如下:
1.用
connect()
创建数据库连接对象conn。2.若要对数据库进行创建新表、对表插入数据、修改及删除数据等操作,使用
conn.execute()
方法,使用conn.commit()
提交。3.若要查询数据,先使用
conn.cursor()
方法创建游标对象cur,再通过cur.execute()
查询,然后调用cur.fetchone()
、cur.fetchmany()
、cur.fetchall()
方法返回查询值。4.最后关闭cur和conn对象。
用connect()函数可建立数据库文件的连接对象,比如conn。若不存在数据库文件则新建数据库(例:d:/test.db)。
创建表
SQLite3表支持以下4种类型:
- 整数型(
INTEGER
):有符号整数,按实际存储大小,自动存储为1、2、3、4、6或8字节,通常不需要指定位数。 - 实数型(
REAL
):浮点数,以8字节指数形式存储。可指总位数和小数位数. - 文本型(
TEXT
):字符串,以数据库编码方式存储(以UTF-8支持汉字)。 BLOB
型:二进制对象数据,通常用来保存图片、视频、XML等数据。
创建表的SQL语句格式:
CREATE TABLE <表>(<字段元组>)
SQL语句大小写不敏感,但为与Python语言相区别,以大写表示。
设计表结构时,作为一种数据完整性约束,可指定某字段是否允许空,若不允许为空,可用NOT NULL
关键字加以限制。在大多数表中,往往会指定一个非空且唯一的字段作为关键主关键字(PRIMARY KEY
,如学号),便于快速检索,通常将表按主关键字建立索引。
以下Python程序将用于在先前创建的数据库(py-sqlite.py
)中创建一个表:
import sqlite3
## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 创建一个表 - company
conn.execute('''CREATE TABLE company
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS TEXT,
SALARY REAL);''')
print ("Table created successfully");
conn.close()
当执行上述程序时,它将在py-sqlite.db
数据库中创建company
表,并显示以下消息:
Opened database successfully
Table created successfully
插入操作
插入数据SQL语句格式:
INSERT INTO <表>(<字段元组>) VALUES (<数据元组>)
以下Python程序显示如何在上述示例中创建的COMPANY
表中插入数据记录:
import sqlite3
## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 插入数据
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, '张三', 27, '北京', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, '李四', 26, '深圳', 35000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, '王五', 23, '上海', 22000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, '赵六', 25, '蚌埠', 45000.00 )");
conn.commit()
print ("Records Insert successfully");
conn.close()
当执行上述程序时,它将在COMPANY
表中插入给定的数据记录,并显示以下结果:
Opened database successfully
Records Insert successfully
通过第三方工具,查看数据结果如下:
SELECT/查询操作
以下Python程序显示如何从上述示例中创建的COMPANY
表中获取并显示数据记录:
import sqlite3
## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
cur=conn.cursor() #通过建立数据库游标对象,准备读写操作
cur.execute('SELECT id, name, address, salary from COMPANY')#对fish1表执行数据查找命令
for i in cur.fetchall():#以一条记录为元组单位返回结果给i
print(i)
print ("Select Operation done successfully.");
cur.close()
conn.close()
执行上述程序时,会产生以下结果:
Opened database successfully
(1, '张三', '北京', 20000.0)
(2, '李四', '深圳', 35000.0)
(3, '王五', '上海', 22000.0)
(4, '赵六', '蚌埠', 45000.0)
Select Operation done successfully.
查询方式二:
import sqlite3
## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print(row)
print ("Select Operation done successfully.");
conn.close()
执行上述程序时,会产生以下结果:
Opened database successfully
(1, '张三', '北京', 20000.0)
(2, '李四', '深圳', 35000.0)
(3, '王五', '上海', 22000.0)
(4, '赵六', '蚌埠', 45000.0)
Select Operation done successfully.
更新操作
更新数据SQL语句格式:
UPDATE <表> SET <字段>=<值>
以下Python代码演示如何使用UPDATE
语句来更新指定记录,然后再从COMPANY
表中获取并显示更新的记录:
import sqlite3
## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 更新数据
conn.execute("UPDATE COMPANY set SALARY = 29999.00 where ID=1")
conn.commit()
print ("Total number of rows updated :", conn.total_changes)
cur=conn.cursor() #通过建立数据库游标对象,准备读写操作
cur.execute('SELECT id, name, address, salary from COMPANY')#对fish1表执行数据查找命令
for i in cur.fetchall():#以一条记录为元组单位返回结果给i
print(i)
print ("Select Operation done successfully.");
conn.close()
执行上述程序时,会产生以下结果:
Opened database successfully
Total number of rows updated : 1
(1, '张三', '北京', 29999.0)
(2, '李四', '深圳', 35000.0)
(3, '王五', '上海', 22000.0)
(4, '赵六', '蚌埠', 45000.0)
Select Operation done successfully.
删除操作
删除数据SQL语句格式:
DELETE FROM <表> WHERE <条件表达式>
以下Python代码演示如何使用DELETE
语句来删除记录,然后从COMPANY
表中获取并显示剩余的记录:
import sqlite3
## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");
## 删除ID值小于等于2的数据
conn.execute("DELETE from COMPANY where ID<=2;")
conn.commit()
print ("Total number of rows updated :", conn.total_changes)
cur=conn.cursor() #通过建立数据库游标对象,准备读写操作
cur.execute('SELECT id, name, address, salary from COMPANY')#对fish1表执行数据查找命令
for i in cur.fetchall():#以一条记录为元组单位返回结果给i
print(i)
print ("Select Operation done successfully.");
conn.close()
执行上面语句后,得到以下结果
Opened database successfully
Total number of rows updated : 2
(3, '王五', '上海', 22000.0)
(4, '赵六', '蚌埠', 45000.0)
Select Operation done successfully.
综合案例
根据如下图所示的数据结构,在D盘根目录下建立一个空数据库test.db。并按如图所示的表结构,创建学生基本情况表base。
1、创建表
根据如下图所示的数据结构,在D盘根目录下建立一个空数据库test.db。并按如图所示的表结构,创建学生基本情况表base。
import sqlite3 #导入模块
conn=sqlite3.connect('d:/test.db') #连接数据库
conn.execute('''CREATE TABLE base
(学号 TEXT(10) PRIMARY KEY NOT NULL,
姓名 TEXT(10) NOT NULL,
性别 TEXT(1) NOT NULL,
专业 TEXT(6),
生源 TEXT(6),
身高 INTEGER,
电话 TEXT(6));''')
2、添加数据
编写程序为例1创建的base表添加新生学号、姓名和性别三项非空数据
import sqlite3 #导入模块
conn=sqlite3.connect('d:/test.db') #连接数据库
while True:
idd=input('请输入新生学号:(输入0退出程序)\n')
if idd=="0":
break
name=input("请输入新生姓名:\n")
gender=input("请输入新生性别:\n")
#格式化构建SQL字符串
SQL='''insert into base
(学号,姓名,性别)
values ('%s','%s','%s')'''%(idd,name,gender)
#插入数据
conn.execute(SQL)
#提交事务
conn.commit()
conn.close()
说明:在格式化构建SQL字符串时应注意:values后面的数据元组应与前面的表达式字段元组顺序一致,且TEXT类型的数据要加单引号定界符。
小猫钓鱼案例
1、建立数据库表结构,添加一条记录
import sqlite3 #导入sqlite3模块
conn=sqlite3.connect('d:/fish.db') #连接数据库
cur=conn.cursor() #通过建立数据库游标对象,准备读写操作
conn.execute('''create table fish1(date text,name text,nums int,price real,Explain text)''')
conn.execute("insert into fish1 values('2022-3-28','黑鱼',10,28.3,'张三')")
conn.commit()
conn.close()
2、查询数据
import sqlite3 #导入sqlite3模块
conn=sqlite3.connect('d:/fish.db') #连接数据库
cur=conn.cursor() #通过建立数据库游标对象,准备读写操作
cur.execute('select * from fish1')#对fish1表执行数据查找命令
for i in cur.fetchall():#以一条记录为元组单位返回结果给i
print(i)
conn.close()
- fetchone()从结果中取一条记录,并将游标指向下一条记录;
- fetchmany()从结果中取多条记录;
- fetchall()从结果中取出全部记录;
- scroll()用于游标滚动;
3、删除数据
import sqlite3 #导入sqlite3模块
conn=sqlite3.connect('d:/fish.db') #连接数据库
cur=conn.cursor() #通过建立数据库游标对象,准备读写操作
conn.execute("insert into fish1 values('2022-3-29','鲤鱼',17,10.3,'李四')")
conn.execute("insert into fish1 values('2022-3-30','鲢鱼',9,9.3,'王五')")
conn.commit()#提交数据保存到磁盘
cur.execute('select * from fish1')
for i in cur.fetchall():
print(i)
cur.execute("delete from fish1 where nums=10")
conn.commit()
cur.execute('select * from fish1')
for i in cur.fetchall():
print(i)
conn.close()
模拟考题
考题1 单选题
下列命令语句能实现在SQLite数据库插入记录数据的命令是?( )
A. create
B. insert
C. select
D. delete
答案:B
解析:insert能实现在SQLite数据库插入记录
考题2 单选题
使用Python语言对SQLite数据库进行操作,实现打开和关闭名为test.db数据库文件。有下列语句命令:
①conn = sqlite3.connect("test.db")
②cur = conn.cursor( )
③import sqlite3
④conn.close( )
⑤cur.close( )
要实现上述功能,上述语句执行的先后顺序正确是?( )
A. ③①②⑤④
B. ③②①⑤④
C. ③①②④⑤
D. ①②⑤④③
答案:A
解析:先导入sqlite3模块,然后建立连接,建立游标,最后关闭
考题3 单选题
打开名为data.db的数据库(SQLite数据库)文件,下列命令能创建名为test数据表的是?( )
A. create table test
B. create test
C. insert into test
D. data.append(test)
答案:A
解析:增加数据表为creat table。
考题4 单选题
执行下列代码,说法错误的是?( )
import sqlite3
DATABASE = 'data.db'
db = sqlite3.connect(DATABASE)
cur = db.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS list(id INTEGER PRIMARY KEY autoincrement, name text)")
db.commit()
cur.execute("SELECT COUNT(*) FROM list")
if cur.fetchall()[0][0] == 0:
cur.execute('INSERT INTO list(id,name) VALUES(1,"lilei")')
db.commit()
A. data.db文件中有一张名为list的数据表
B. list数据表中有两个字段id和name
C. list数据表中没有记录
D. 去掉最后一行的db.commit(),对程序有影响
答案:C
解析:list数据表中有一条记录id=1,name="lilei"。
考题5 单选题
有下列语句命令:
import sqlite3
conn = sqlite3.connect("db/test.db")
cur = conn.cursor( )
cur.execute("SELECT * FROM students")
data = cur.fetchall()
cur.close()
conn.close()
可知当前的数据表名是?( )
A. db
B. test.db
C. students
D. data
答案:C
解析:db为存放数据库文件的文件夹名,cur是游标,test.db是数据文件名,data是存放数据记录的变量。
考题6 单选题
有如下程序段:
import sqlite3
conn= sqlite3.connect('test.db')
cur = conn.cursor()
sql = 'SELECT name,grade FROM Grades WHERE grade > 60'
rs = cur.execute(sql)
for x in rs:
print(x)
cur.close()
conn.close()
程序实现的功能是?( )
A. 查询成绩大于60分的人的名字
B. 查询成绩小于60分的人的名字和成绩
C. 查询并输出成绩小于60分的人的名字和成绩
D. 查询并输出成绩大于60分的人的名字和成绩
答案:D
解析:sql用于查询成绩大于60分的包含名字和成绩两个字段的记录,再遍历记录并打印,因此程序功能是查询并输出成绩大于60分的人的名字和成绩。
考题7 单选题
执行下列代码,说法错误的是?( )
import sqlite3
DATABASE = 'student.db'
db = sqlite3.connect(DATABASE)
cur = db.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS list(id INTEGER PRIMARY KEY autoincrement, name text)")
db.commit()
cur.execute("SELECT COUNT(*) FROM list")
if cur.fetchall()[0][0] == 0:
cur.execute('INSERT INTO list(id,name) VALUES(1,"李明")')
db.commit()
A. 去掉最后一行的db.commit(),对程序有影响
B. list数据表中有两个字段id和name
C. student. db文件中有一张名为list的数据表
D. list数据表中没有记录
答案:D
解析:数据库中有插入1,李明的数据记录,故答案选D
考题8 单选题
下列关于sqlite数据库说法,不正确的是?( )
A. sqlite是一个跨平台的数据库
B. sqlite是非关系型数据库
C. sqlite数据库可以进行基本的增删改查操作
D. Python可以通过加载sqlite模块来操作sqlite
答案:B
解析:sqlite是一个开源的关系型数据库,具有零配置、自我包含、便于传输等优点。
考题9 判断题
SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的UPDATE 查询来更新选定行,否则所有的行都会被更新。( )
答案:正确
解析:SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行,否则所有的行都会被更新
考题10 判断题
语句“conn = sqlite3.connect("test.db")”的功能是若数据库文件"test.db"存在则打开;若数据库文件不存在,则新建一个名为"test.db"的数据库文件。( )
答案:正确
解析:connect()函数:用于创建和连接数据库。若指定的数据库文件存在,则打开;若数据库文件不存在,则新建一个数据库文件。