基于Python的SQLite基础知识学习

作者：JiekeXu

2017年毕业于某本科院校，从事于数据库运维行业，一个热爱Python的DBA

个人公众号：JiekeXu之路

前    言

前一段时间偶然的看到了一个名词SQLite3，大概了解到此为一种轻量型的关系型数据库。官网介绍到SQLite是一个进程内库，它实现了一个自包含的、无服务器的、零配置的事务性SQL数据库引擎（官网：https://www.sqlite.org/）。后来也是偶然的机会，公司使用的数据库存储有问题，无意中看到了三线开发工程师尽然使用SQLite命令加了一块数据盘上去，故此对SQLite的好奇就更进一步了，下面简单学习介绍一下。

SQLite于2000年8月17日发布1.0版本，于2001年9月28日发布2.0.0版本，于2004年6月18日发布3.0.0版本，于2018年11月05日发布了3.25.3,这是写本文时最新的一个版本。

(SQLite历史版本)

SQLite是遵守ACID[原子性、一致性、隔离性和持久性]的关系数据库管理系统，它包含在一个相对小的C程序库中。与许多其它数据库管理系统不同，SQLite不是一个客户端/服务器结构的数据库引擎，而是被集成在用户程序中。

SQLite遵守ACID，实现了大多数SQL标准。它使用动态的、弱类型的SQL语法。它作为嵌入式数据库，是应用程序，如网页浏览器，在本地/客户端存储数据的常见选择。它可能是最广泛部署的数据库引擎，因为它正在被一些流行的浏览器、操作系统、嵌入式系统所使用。同时，它有许多程序设计语言的语言绑定。SQLite是D. Richard Hipp创建的公有领域项目。

为什么要使用SQLite?

• 不需要一个单独的服务器进程或操作的系统（无服务器的）。

• SQLite不需要配置，这意味着不需要安装或管理。

• 一个完整的SQLite数据库是存储在一个单一的跨平台的磁盘文件。

• SQLite是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。

• SQLite是自给自足的，这意味着不需要任何外部的依赖。

• SQLite事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。

• SQLite支持 SQL92（SQL2）标准的大多数查询语言的功能。

• SQLite使用 ANSI-C 编写的，并提供了简单和易于使用的 API。

• SQLite 在 UNIX（Linux, Mac OS-X, Android,iOS）和 Windows（Win32, WinCE,WinRT）中运行。

SQLite的安装

Linux系统和MAC系统均已经集成了SQLite,可直接使用，键入sqlite3即可。如下：

[root@JiekeXu01 ~]# sqlite3

SQLite version 3.6.20

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

使用.help命令获取更多帮助，使用.quit退出使用界面。如果没有上面的结果，就意味着没有安装，便在官网下载相对应位数的源码tar.gz包，解压此包sqlite-autoconf-3250300.tar.gz编译安装即可。命令如下：

$tar xvfz sqlite-autoconf-3250300.tar.gz

$cd sqlite-autoconf-3250300

$./configure --prefix=/usr/local

$make

$make install

编译完后即可使用命令 sqlite3 验证。

在 Windows 上安装 SQLite时，需要下载sqlite-dll-win64-x64-3250300.zip和sqlite-tools-win32-x86-3250300.zip。创建文件夹 D:\sqlite3，并在此文件夹下解压上面两个压缩文件，将得到 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件。添加 D:\sqlite3到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，将显示如下结果：

D:\>sqlite3

SQLite version 3.25.3.0 2018-11-05 19:53:45

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite>

SQLite基础语法

SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号（;）结束。注释是以两个连续的 "-" 字符（ASCII 0x2d）开始，并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准。您也可以使用 C 风格的注释，以 "/*" 开始，并扩展至下一个 "*/" 字符对或直到输入结束，以先到者为准。SQLite的注释可以跨越多行。

SQLite创建数据库：

$sqlite3 DatabaseName.db

SQLite创建表：

CREATE TABLE database_name.table_name(

column1 datatype  PRIMARY KEY(one or more columns),

column2 datatype,

.....

columnN datatype,

)；

创建完之后。便可以使用.tables查看相关的表，使用..schema tablename查看表具体信息。那么删除表操作，和Oracle一样，这里不再演示了，使用DROP TABLE database_name.table_name即可删除表，一旦删除表中信息将无法找回了。

SQLite的增、删、改、查和之前学过的MySQL、Oracle语法一样，均是采用标准SQL格式，这里不再进行说明，简单如下图所示：

好了关于SQLite的基础知识就介绍这么多，想要了解更多，可访问《菜鸟教程》“http://www.runoob.com/sqlite”，下面简单说说关于Python操作SQLite数据库。

Python操作SQLite

网上很多教程都是Python操作MySQL，Redis，Mongodb的一些教程，这里简单说明Python操作SQLite常用的固定语法。Python 2.5x以上版本内置了SQLite3，使用时直接import sqlite3即可，而SQLite3 模块是由 Gerhard Haring 编写的。

python操作流程大概分为以下五步

• 通过sqlite3.open()创建与数据库文件的连接对象connection；

• 通过connection.cursor()创建光标对象cursor；

• 通过cursor.execute()执行SQL语句；

• 通过connection.commit()提交当前的事务，或者通过cursor.fetchall()获得查询结果；

• 通过connection.close()关闭与数据库文件的连接。

建立连接：

[oracle@rhel67 ~]$ python

Python 2.6.6 (r266:84292, May 22 2015, 08:34:51)

[GCC 4.4.7 20120313 (Red Hat 4.4.7-15)] on linux2

Type "help", "copyright", "credits" or "license" for more information.

>>> import sqlite3

>>> conn = sqlite3.connect('testDB.db')

这里已经连接到SQLite数据库，“testDB.db”是前面所创建的数据库，当没有此数据库时也会自动创建一个。连接到数据库以后，按照上边的步骤就需要创建光标对象cursor 。

>>> cursor = conn.cursor()

>>>

接下来就可以使用cursor.execute()直接执行SQL语句了。

建立数据库表：

cursor.execute(‘create table student(id int PRIMARY KEY,name text,age int)’)

注意：python2好像不能使用以下语句建立表，但在python3中是可以的，下图使用的是CentOS6.7自带的python2.6.6，SQLite 版本为3.6.20。

cre_tab=”create table student(id int PRIMARY KEY,name text,age int)”

cursor.execute(cre_tab)

插入数据库表语句

cursor.execute("insert into student(id,name,age) values (1,'zhangsan',22)")

cursor.execute("insert into student values (2,'lisi',24)")

cursor.execute("insert into student values (?,?,?)",(3,"wangwu",25))

conn.commit()   --插入完之后提交

经过提交后使用以下语句查询

cursor.execute(“select * from student”)

>>> cursor.execute("select * from student")

<sqlite3.Cursor object at 0x7f1eb1ed1450>

>>> print (cursor.fetchall())

[(1, u'zhangsan', 22), (2, u'lisi', 24), (3, u'wangwu', 25)]

>>> 

fetchall()返回的是记录数组，可以通过WHERE子句做更细致的选择。

SQLite3更新语句

cursor.execute(“update student set id=0 where age =22 ”)

>>> cursor.execute("update student set id=1 where name ='lisi'")

<sqlite3.Cursor object at 0x7f1eb1ed1450>

>>> conn.commit()

>>> cursor.execute("select * from student")

<sqlite3.Cursor object at 0x7f1eb1ed1450>

>>> cursor.fetchall()

[(0, u'zhangsan', 22), (1, u'lisi', 24), (3, u'wangwu', 25)]

也可以使用以下语句更新，万变不离其宗，都是一样的道理，达到的效果是一样的。

>>> cursor.execute("update student set id = ? where name = ? ",(2,"wangwu"));

<sqlite3.Cursor object at 0x7f1eb1ed1450>

>>> conn.commit()

>>> cursor.execute("select * from student")

<sqlite3.Cursor object at 0x7f1eb1ed1450>

>>> cursor.fetchall()

[(0, u'zhangsan', 22), (1, u'lisi', 24), (2, u'wangwu', 25)]

SQLite3删除语句

cursor.execute("delete from student where age = ? ",(25, ));

cursor.execute("delete from student where name = ? ",("wangwu"));

当使用词语去删除时报参数错误，Python认为传递的字符串是一个元组，导致参数过多报错，传递一个参数时括号里一定要加逗号，不然Python会认为是数字，会报不支持的参数类型错误。而使用标准的语法删除时是没有问题的cursor.execute("delete from student where id = 0 ")；建议使用此语法删除。

关闭连接

>>> cursor.close()

>>> conn.close()

最后不要忘记在完成对数据库的操作以后，千万千万不要忘记关闭连接哦！

写在最后

SQLite是轻量级的关系型数据库，使用简单方便，这里只是简单的了解下增删改查的基础知识，并没深入理解。而你的智能手机上（如iPhone）安装有上百甚至上千个SQLite数据库，如果你现在正在寻找一款能够用于管理科研和临床信息的数据库工具，而且你要求这款工具强大且简单易用，我认为SQLite是你的不二之选。所以还是来看看吧，SQLite你值得拥有！

参考资料：

SQLite官方文档：

https://www.sqlite.org/docs.html

菜鸟教程：

http://www.runoob.com/sqlite/sqlite-python.html

Python的爱好者社区历史文章大合集：

福利：文末扫码立刻关注公众号，“Python爱好者社区”，开始学习Python课程：

关注后在公众号内回复“ 课程 ”即可获取：

小编的转行入职数据科学（数据分析挖掘/机器学习方向）【最新免费】

小编的Python的入门免费视频课程！

小编的Python的快速上手matplotlib可视化库！

崔老师爬虫实战案例免费学习视频。

陈老师数据分析报告扩展制作免费学习视频。

玩转大数据分析！Spark2.X + Python精华实战课程免费学习视频。