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

通过第三方工具,查看数据结果如下:

image-20230705171448305

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。

image-20230705181304118

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()函数:用于创建和连接数据库。若指定的数据库文件存在,则打开;若数据库文件不存在,则新建一个数据库文件。

Copyright © all right reserved,powered by Gitbook该文件修订时间: 2023-10-28 18:20:28