简单 Python 快乐之旅之:Python 基础语法之 sqlite3 专题

Defonds 2020-11-13 04:49:32
Python 简单 之旅 快乐



SQLite 是一种基于磁盘的轻量级的数据库,它不像 MySQL 那样需要一个独立的服务器,它的数据是直接持久存储,它的数据库其实就是一个文件。
正如其他任意关系型数据库那样,我们将在本文中掌握 Python 对 sqlite3 的数据库连接对象的创建、在数据库中创建表、向表中插入记录、基于从句查询表中数据、基于从句更新数据、删除部分或整表数据等相关技能。

1. 创建连接对象

Python 内置了 sqlite3,并提供 sqlite3 库。因此我们不需要安装任意东西、直接使用即可。
要创建一个 sqlite 的连接对象,可以使用 sqlite3.connect() 函数。该函数语法如下:

conn = sqlite3.connect('dbname.db')

其中,connect() 函数读取了作为数据库名的一个字符串并返回一个 sqlite3.Connection 类对象。
如果该数据库已存在,它只会返回一个 Connection 对象,否则的话将会创建该数据库并返回一个该新建数据库的 Connection 对象。

1.1. 使用 sqlite3 创建一个 Connection 对象的例子

本例中,我们将创建一个连接到 sqlite 名为 mysqlite.db 数据库的 Connection 对象。

import sqlite3
conn = sqlite3.connect('mysqlite.db')

在使用 sqlite3 的任意函数之前你需要先导入 sqlite3 库。
当然,你也可以在内存 (RAM) 中创建一个数据库,只需要在创建 Connection 对象的时候将 :memory: 作为参数传给 sqlite3.connect() 即可。

import sqlite3
conn = sqlite3.connect(':memory:')

要在你所创建的 sqlite 数据库上进行操作的话,你还得给 Connection 对象创建一个游标。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

2. 在 sqlite3 数据库创建表

你可以在 sqlite3 数据库中创建一个或多个表。
在本节中,我们将会了解到如何使用 Python 在 sqlite3 数据库中新建一张表。
使用 Python sqlie3 创建表需要遵循以下步骤:

  1. 创建一个连接到 sqlite3 数据库的 Connection 对象。
  2. 一个 Connection 对象的游标。
  3. 将 CREATE 查询传给 sqlite3.execute() 来新建表。

2.1. 使用 sqlite3 新建表

在本示例中,我们将创建一个名为 mysqlite.db 的数据库,并在其中新建一张名为 students 的表。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE students (rollno real, name text, class real)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
建表的例子.png
执行完该程序以后,一个名为 students 的新表将会在数据库 mysqlite.db 中创建。但是如果你再执行该程序,你将会得到以下错误结果:
再次执行建表语句.jpg
为了不再受到表是否已存在的打扰,我们可以参考下小节例子,只有在表不存在的情况下才会创建新表。

2.2. 只有在表不存在的情况下才会创建新表

在查询语句中,我们可以定义为只有当该表不存在的情况下才会创建新表。你可以在查询语句中的表名之前使用 IF NOT EXISTS 来创建新表。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# Create the table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
只有在表不存在的情况下才会创建新表.jpg
可以看到,虽然我们在执行该程序前已经创建过表 students,但是并没有受到前面表已存在错误的打扰。

2.3. 小结

在本节中,我们了解到了如何在 sqlite3 数据库中新建一张表。此外,我们还了解了如何在只有表不存在的情况下才新建表的相关知识。

3. 查看表是否已存在

在对表执行查询语句之前,你可以先检查一下该表是否已存在于 sqlite3 数据库。
要检查某张表是否已存在于 sqlite3 数据库,你可以从表 sqlite_master 中查询是否已有和你的表名匹配的表名。
相关语法如下:

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

其中,table_name 需要替换为你要查询的表名。
在该查询的结果中你可以检查是否已有一些行存在。如果有一行结果的话,那么该表已存在。

3.1. 检查表是否已在存在于 sqlite3

在上一节的示例中我们已经新建了一张名为 students 的表。现在,我们将会用程序检查该表是否已存在。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# get the count of the tables with the name
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name='students' ''')
# if the count is 1, then table exists
if c.fetchone()[0]==1:
print('Table students exists')
else:
print('Table students not exists')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
检查表是否已在存在于 sqlite3.png

3.2. 检查表是否已存在于 sqlite3 (不存在的场景)

本实例中我们将检查一个反面场景,也就是名为 students1 的表不存在于 sqlite3 数据库时的场景。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# get the count of tables with the name
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' and name='students1' ''')
# if the count is 1, then exists
if c.fetchone()[0]==1:
print('Table students1 exists')
else:
print('Table students1 does not exists')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
检查表是否已存在于 sqlite3 (不存在的场景).png

3.3. 检查表是否存在于内存 (RAM)

如果想检查存放于内存 (RAM) 中的表,可以使用 sqlite_temp_master 将上述示例中的 sqlite_master 替换。

3.4. 小结

在本示例中我们了解了检查指定表是否已存在于 sqlite3 数据库的相关知识。

4. 向 sqlite3 表中插入行

你可以向 sqlite3 表中插入一行或多行。
要插入一行数据到 sqlite3 表,执行以下步骤。

  1. 创建一个连接到 sqlite3 数据库的连接。
  2. 拿到该连接的一个游标。
  3. 检查该表是否已存在,如果不存在的话新建之。
  4. 该表已存在的话,使用游标的 execute() 方法,通过将 SQL 插入查询传给该方法插入新行。

你可以通过 cursor.lastrowid() 来检查新行是否插入成功。

4.1. 插入一行到 sqlite3 表

在接下来的例子中,如果表不存在的话我们将新建表,然后使用 INSERT INTO 查询来插入一条记录到该表中去。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
c.execute('''INSERT INTO students VALUES(1, 'Alex', 8)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
插入一行到 sqlite3 表.jpg

4.2. 插入新行到 sqlite3 表并检查该插入是否成功

在大多数场景下,我们需要去核实该 INSERT INTO 查询是否成功插入。
要做到这一点,我们可以检查由 sqlite3 的游标所插入的最后一行记录的 id,如果该 id 不是 0,那么我们就可以确认该插入已经成功了。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
c.execute('''INSERT INTO students VALUES(1, 'Glen', 8)''')
print(c.lastrowid)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
插入新行到 sqlite3 表并检查该插入是否成功
如果输出结果非零,那么证明你的插入确实奏效了。如果你得到了一个零,那么你需要调试你的程序了。

4.3. 小结

本节中,我们了解到了怎样向 sqlite3 表中插入新行。

5. 从 sqlite3 表中查询行

要从 sqlite3 数据库表中查询数据需要遵循以下步骤:

  1. 创建一个连接到 sqlite 数据库的 Connection 对象。
  2. 新建一个该连接的游标。
  3. 将 SELECT FROM 查询传给 sqlite3.execute() 方法并执行。

5.1. 从 sqlite3 表中读取数据

以下示例将读取 sqlite3 数据库表 students 里的所有行。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
c.execute('''SELECT * FROM students;''')
rows = c.fetchall()
for row in rows:
print(row)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
从 sqlite3 表中读取数据.png
可以看到之前插入的测试数据都已打印出来了。当然,还可以修改 SELECT FROM 查询来对数据进行过滤、排序或转换。

5.2. 使用 WHERE 从句从 sqlite3 中读取行

接下来是在 SELECT FROM 查询中使用 WHERE 从句来过滤查询行的示例。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
c.execute('''SELECT * FROM students WHERE name="Alex";''')
rows = c.fetchall()
for row in rows:
print(row)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
使用 WHERE 从句从 sqlite3 中读取行.png

5.3. 小结

本节中我们了解到了如何从一张表中查询记录的相关知识。

6. 插入多行到 sqlite3 表

在第四节中,我们了解到了如何向表中插入单行。本节中我们来了解一下如何使用单个 INSERT INTO 查询向表中插入多行数据。
插入多行记录到一张表的步骤如下:

  1. 准备连接到数据库的 Connection 对象并获取一个游标。
  2. 将待插入的记录存放进一张 list 里。
  3. 将待插入记录的 list 作为参数传给 executemany() 方法并执行。

6.1. 插入多行记录到 sqlite 表

在接下来的示例中,我们将向前面已创建的 students 表中再插入三条数据。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# records or rows in a list
records = [(1, 'Alen', 8),
(2, 'Elliot', 9),
(3, 'Bob', 7)]
# insert multiple records in a single query
c.executemany('INSERT INTO students VALUES(?,?,?);', records)
print('We have inserted', c.rowcount, 'records to the table.')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
插入多行记录到 sqlite 表.jpg
我们打印出 cursor.rowcount 以确认该查询语句成功插入了三条记录。

6.2. 小结

在本节中,我们了解到了如何在单个查询语句中插入多行记录到 sqlite3 表的相关知识。

7. 删除 sqlite3 表中的所有记录

要删除 sqlite3 表中的所有记录,执行 DELETE FROM 查询。详细步骤如下:

  1. 新建一个 sqlite3 的连接。
  2. 得到该连接的游标。
  3. 执行 DELETE FROM table 查询。

7.1. 删除 sqlite3 表中的行

以下示例中,我们将学习到如何使用 DELETE FROM table 查询语句来将 sqlite3 的表中的记录全部删除。

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# delete all rows from table
c.execute('DELETE FROM students;')
print('We have deleted', c.rowcount, 'records from the table.')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

执行和输出:
删除 sqlite3 表中的行.png
没错,我们前边向该表总共插入了 5 条数据,现在全部删除了。

7.2. 小结

在本节中,我们了解了删除 sqlite3 表中的所有记录的相关知识。

参考资料

版权声明
本文为[Defonds]所创,转载请带上原文链接,感谢
https://defonds.blog.csdn.net/article/details/102943410

  1. 利用Python爬虫获取招聘网站职位信息
  2. Using Python crawler to obtain job information of recruitment website
  3. Several highly rated Python libraries arrow, jsonpath, psutil and tenacity are recommended
  4. Python装饰器
  5. Python实现LDAP认证
  6. Python decorator
  7. Implementing LDAP authentication with Python
  8. Vscode configures Python development environment!
  9. In Python, how dare you say you can't log module? ️
  10. 我收藏的有关Python的电子书和资料
  11. python 中 lambda的一些tips
  12. python中字典的一些tips
  13. python 用生成器生成斐波那契数列
  14. python脚本转pyc踩了个坑。。。
  15. My collection of e-books and materials about Python
  16. Some tips of lambda in Python
  17. Some tips of dictionary in Python
  18. Using Python generator to generate Fibonacci sequence
  19. The conversion of Python script to PyC stepped on a pit...
  20. Python游戏开发,pygame模块,Python实现扫雷小游戏
  21. Python game development, pyGame module, python implementation of minesweeping games
  22. Python实用工具,email模块,Python实现邮件远程控制自己电脑
  23. Python utility, email module, python realizes mail remote control of its own computer
  24. 毫无头绪的自学Python,你可能连门槛都摸不到!【最佳学习路线】
  25. Python读取二进制文件代码方法解析
  26. Python字典的实现原理
  27. Without a clue, you may not even touch the threshold【 Best learning route]
  28. Parsing method of Python reading binary file code
  29. Implementation principle of Python dictionary
  30. You must know the function of pandas to parse JSON data - JSON_ normalize()
  31. Python实用案例,私人定制,Python自动化生成爱豆专属2021日历
  32. Python practical case, private customization, python automatic generation of Adu exclusive 2021 calendar
  33. 《Python实例》震惊了,用Python这么简单实现了聊天系统的脏话,广告检测
  34. "Python instance" was shocked and realized the dirty words and advertisement detection of the chat system in Python
  35. Convolutional neural network processing sequence for Python deep learning
  36. Python data structure and algorithm (1) -- enum type enum
  37. 超全大厂算法岗百问百答(推荐系统/机器学习/深度学习/C++/Spark/python)
  38. 【Python进阶】你真的明白NumPy中的ndarray吗?
  39. All questions and answers for algorithm posts of super large factories (recommended system / machine learning / deep learning / C + + / spark / Python)
  40. [advanced Python] do you really understand ndarray in numpy?
  41. 【Python进阶】Python进阶专栏栏主自述:不忘初心,砥砺前行
  42. [advanced Python] Python advanced column main readme: never forget the original intention and forge ahead
  43. python垃圾回收和缓存管理
  44. java调用Python程序
  45. java调用Python程序
  46. Python常用函数有哪些?Python基础入门课程
  47. Python garbage collection and cache management
  48. Java calling Python program
  49. Java calling Python program
  50. What functions are commonly used in Python? Introduction to Python Basics
  51. Python basic knowledge
  52. Anaconda5.2 安装 Python 库(MySQLdb)的方法
  53. Python实现对脑电数据情绪分析
  54. Anaconda 5.2 method of installing Python Library (mysqldb)
  55. Python implements emotion analysis of EEG data
  56. Master some advanced usage of Python in 30 seconds, which makes others envy it
  57. python爬取百度图片并对图片做一系列处理
  58. Python crawls Baidu pictures and does a series of processing on them
  59. python链接mysql数据库
  60. Python link MySQL database