PYTHON 数据库 之 sqlite3 有大用

零 SQLite数据库的创建和使用

一、SQLite数据库的创建

1>. 安装SQLite数据库（在Windows上安装SQLite）

SQLite以其零配置而闻名，所以不需要复杂的设置或管理。其本身就只是一个.db文件，哪里需要就拷贝到哪里就好了。
安装按照以下步骤进行：

• 1.打开SQLite官方网站或直接点击下载页面 - http://www.sqlite.org/download.html 并下载预编译的Windows二进制文件。

• 2.下载sqlite-dll的zip文件以sqlite-tools-win32-x86-3170000.zip文件。（-dll文件，32位选择32位的，64位选择64的）
  

• 3.在本地磁盘创建sqlite文件夹，如我的地址为：F:\sqlite

• 4.将下载的zip包在F:\sqlite这个路径下解压，得到如下程序：
  

2>. 创建sqlite数据库文件的两种方法：

• 1、暂时性数据库，程序关闭后数据不保存，一般用于临时数据验证或临时测试，不利于数据库分离。操作步骤如下：
  直接双击运行sqlite3.exe应用程序，得到如下结果：
  

然后数据sqlite相关操作命令可进行临时性的数据库操作。

• 2、创建永久性的数据库文件，只要不人为删除，数据库文件一直保存。操作步骤：
  进入F:\sqlite目录并使用cmd窗口打开sqlite3命令。输入sqlite3，它将如下所示：
  使用 sqlite3 数据库名.db 的方式可以打开数据库文件，如果该数据库文件不存在，则创建一个。如创建一个study.db的数据库文件：输入 sqlite3 study.db
  
  使用 .database 可以查看创建的数据库，或者在F:\sqlite目录下查看新创建的数据库：

然后使用 .quit 命令退出sqlite提示符：

此时数据库创建基本完成，如果想学习使用命令操作数据库的同学，建议去查看入门教程。接下来我会使用sqlite的一种可视化工具sqlite Expert Professianal来进行数据库操作。

二、SQLite数据库的使用

1>. 打开数据库文件

安装好sqlite Expert Professianal 工具后，打开界面，打开study.db数据库文件：File > Open Database：（上述创建数据库的方式也可以使用可视化工具创建，sqlite数据库没有用户名和密码）

2>. 创建数据表

选中数据库名，点击右键>new tanle :

或者直接点击菜单栏新建表：

然后输入table name ，如果不想使用数据表自带的RowID,勾选without RowID
（注意如果使用自增长主键，不能选）。

切换到columns标签，点击add输入表格列：

然后同样的操作创建其他的列。
最后创建主键，选择primary key标签,选择你要作为主键的列，如果要自增长，一并勾选上（注意作为主键并且自增长，类型需要设置为Integer类型）排序方式可不选：

最后点击applay。Role表就创建成功啦。

3>.编写sql语句并执行
由于使用可视化工具插入数据操作比较复杂，建议使用sql语句进行数据插入。对使用工具插入数据感兴趣的同学，操作步骤如下：

在哪儿写sql语句呢？操作如下：

写到这儿，基本使用sqlite数据库应该没什么问题了，如果想要知道该数据库的增删改查语句的语法，可查看入门教程，链接：https://www.yiibai.com/sqlite/quick-start.html

作者：涩涩de柠檬茶
来源：CSDN
原文：https://blog.csdn.net/weixin_42380257/article/details/81360237
版权声明：本文为博主原创文章，转载请附上博文链接！

一 使用sqlite3

使用sqlite3模块的connect方法来创建/打开数据库，需要指定数据库路径，不存在则创建一个新的数据库

导入模块

import sqlite3

建立连接

con = sqlite3.connect('E:\\2018\\spider\\first.db')

不仅可以在硬盘中创建数据库文件，还可以在内存中创建

con = sqlite3.connect(':memory')  

不过既然在内存中创建貌似就么有必要用数据库了，莫非是装X功能？
好吧，给跪了！

connect方法返回con对象，即是数据库链接对象，它提供了以下方法：
.cursor() 方法来创建一个游标对象
.commit() 方法来处理事务提交
.rollback() 方法来处理事务回滚
.close() 方法来关闭一个数据库连接

1、游标对象的使用

对数据库的查询需要使用到游标对象，首先通过cursor()创建一个游标对象：

cur = con.cursor()

游标对象有以下方法支持数据库操作：
.execute()用来执行sql语句
.executemany()用来执行多条sql语句
.close() 用来关闭游标
.fetchone()用来从结果中取一条记录，并将游标指向下一条记录
.fetchmany()用来从结果中取多条记录。
.fetchall()用来从结果中取出所以记录
.scroll()用于游标滚动

2、创建表

首先使用游标创建一个person表，包含id,name,age等3列，代码如下：

cur.execute('CREATE TABLE person (id integer primary key ,name varchar(20),age integer)')

3、插入数据

向person表中插入两条数据
方法1：直接构造一个插入的sql语句

data = "1,'leon',22"
cur.execute('INSERT INTO person VALUES (%s)'%data)

方法2：使用占位符“?”，规避sql注入

cur.execute('INSERT INTO person VALUES (?,?,?)',(2,'name2',28))

还可以使用executemany()执行多条sql语句，使用executmany()比循环使用excute()执行多条sql语句效率高。

cur.executemany('INSERT INTO person VALUES (?,?,?)',[(3,'name3',19),(4,'name4',26)])

这两种插入数据操作都不会立即生效，需要使用数据库对象con进行提交操作：

con.commit()

4.查询数据

查询刚插入的所有数据

cur.execute('SELECT * FROM person')

要提取查询数据，游标对象提供了fetchall()和fetchone()方法
fetchall()方法获取所有数据，返回一个二维列表。fetchone()方法获取其中一个结果，返回一个元组

res = cur.fetchall()
for line in res:print("循环fetchall的值>>>",line)

要从新取才能再次取数据哦，因为游标已经

cur.execute('SELECT * FROM person')
res = cur.fetchone()
print("取一条数据>>>",res)

5、修改和删除

cur.execute('UPDATE person SET name=? WHERE id=? ',('cat',2))
cur.execute('DELETE FROM person WHERE id=1')
con.commit()

再查询一下修改个删除之后的数据

cur.execute('SELECT * FROM person')
res = cur.fetchall()
for line in res:print("循环删除之后的数据>>>",line)

最后

con.close()  # 关闭数据库链接

输出结果：
循环fetchall的值>>> (1, ‘leon’, 22)
循环fetchall的值>>> (2, ‘name2’, 28)
循环fetchall的值>>> (3, ‘name3’, 19)
循环fetchall的值>>> (4, ‘name4’, 26)
取一条数据>>> (1, ‘leon’, 22)
循环删除之后的数据>>> (2, ‘cat’, 28)
循环删除之后的数据>>> (3, ‘name3’, 19)
循环删除之后的数据>>> (4, ‘name4’, 26)

总结：

实际上创建表操作应该检查表是否存在，可以使用异常处理try…except
更新和修改和删除都应该检查表数据是否存在，否则程序会报错。
python 操作数据库插入语句占位符问题
1、在 Python 中使用 sqlite3 连接数据库，插入语句的展位符为 “？”
cur.execute(“insert into user values(?,?,?)”,(1,name,12))
2、在 Python 中，使用 pymysql 连接 mysql 数据库，插入语句的占位符为 “%s”
cursor.execute(“insert into user values(%s, %s, %s)”,(1,name,100))

作者：瞌睡的猫猫
来源：CSDN
原文：https://blog.csdn.net/sinat_35886587/article/details/80561959
版权声明：本文为博主原创文章，转载请附上博文链接！

二 SQLITE3数据库性能

实际上处理百万级的数据库还不错。不过要看数据库结构的设计了，设计的不好别说百万，就是十万都慢。主要慢在insert语句。select的性能是极高的，如果有特殊性能需要的话，使用内存数据库性能极高。百分之九十九的应用，使用它性能绝没问题。
我一直在处理百万级别的数据库，数据量大小在500MB左右，性能相当好。虽然是单文件数据库，可也没妨碍你把数据分解为多个数据库啊，并且即便是单个数据库文件也没问题。除非你是非常特殊的要求，相信它，绝没问题。

sqlite免费不说，源代码简单，方便直接集成到程序内部，外人根本不知道你用了数据库，稍加改造就可以加密，根本不知道你用的啥，更不用说追踪到你的数据了。由于是直接把源代码集成到自己的程序中，用户程序中就不需要安装什么数据库软件了，多好啊。

SQLITE3数据库限制

SQLite是一个很快的数据库，但"快"这个词本身是一个主观的和模糊不清的词。坦白地讲，对于有些事情，SQLite比其他数据库做得快，也有些事情比不上其他数据库。利用SQLite提供的配置参数，SQLite是足够快速和高效的。与大多数数据库一样，SQLite使用B-tree做索引，使用B±tree处理表。因此，在对单表进行查询时，平均而言，SQLite与其他数据库一样快（至少不慢于）。简单的SELECT、INSERT和UPDATE语句是相当快速的–几乎与内存（如果是内存数据库）或者磁盘同速。SQLite通常要快于其他数据库，因为它在处理一个事务开始，或者一个查询计划的产生方面开销较小，并且没有调用服务器的网络或认证以及权限协商的开销。它的简单性使它更快。

但是随着查询变大变复杂，查询时间使得网络调用或者事务处理开销相形见绌，SQLite将会与其他数据库一样。这时一些大型的设计复杂的数据库开始发挥作用了。虽然SQLite也能处理复杂的查询，但是它没有精密的优化器或者查询计划器。SQLite知道如何使用索引，但是它没有保存详细的表统计信息。假如执行17路join，SQLite也会连接表并给您结果，并不像您在Oracle或者PostgreSQL中期望的那样，SQLite没有通过计算各种替代查询计划并选择最快的候选计划来尝试判断优化路径。因此，假如您在大型数据集合上运行复杂的查询，SQLite与那些有复杂设计的查询计划器的数据库运行一样快的机会是非常小的。

一些情况下，SQLite可能不如大型数据库快，但大多数的这些情况是预期的。SQLite是为中小规模的应用程序设计的一个嵌入式的数据库，这些限制是设计目的预见到的。许多新用户错误地认为使用SQLite可以代替大型关系数据库。事实是有时可以这样做，有时不行，这完全取决于您准备用SQLite来做什么。

一般情况下，SQLite的局限性主要有以下两方面：

并发。SQLite的锁机制是粗粒度的，它允许多个读，但是一次只允许一个写。写锁会在写期间排他地锁定数据库，其他人在此期间不能访问数据库。SQLite已经采取措施以最小化排它锁所占用的时间。通常来讲，SQLite中的锁只保持几毫秒。但是按照一般经验，如果您的应用程序有很高的写并发（许多连接竞争向同一数据库写），并且是时间关键型，您可能需要其他数据库。需要实际测试您的应用程序才能知道能获得怎样的性能。作者曾见过一个简单的网络应用程序中，SQLite用100个并发连接每秒处理500多个事务。您的事务所修改的记录数以及查询所涉及的复杂性可能有所不同。什么样的并发性是可接受的，这取决于具体的应用程序，只能通过直接测试来判断。总之，对任何数据库都是真理：直到您做了实际测试才能知道应用程序能获得怎样的性能。

网络。虽然SQLite数据库可以通过网络文件系统共享，但是与这种文件系统相关的潜在延时会导致性能受损。更糟的是，网络文件系统实现中的一些缺陷也使得打开和修改远程文件–SQLite或其他–很容易出错。如果文件系统的锁实现不当，可能允许两个客户端同时修改同一个数据库文件，这样必然会导致数据库出错。实际上，并不是因为SQLite的实现导致SQLite不能在网络文件系统上工作。相反，SQLite在底层文件系统和有线协议下工作，但是这些技术有时并不完善。例如，许多NFS使用有缺陷的fcntl()，这意味着锁可能并不像设想的那样工作。一些较新版本的NFS，如Solaris NFSV4就可以工作正常，SQLite需要的锁机制也是相当可靠的。然而，SQLite开发者既没有时间，也没有资源去验证任何给定的网络文件系统在所有的情况下都可以无缺陷工作。

再次申明，绝大部分限制是有意设计的–它们是SQLite设计的结果。例如，支持高度的写并发会带来很大的复杂性，这将使SQLite的简单性无法保持。同样，作为嵌入式数据库，SQLite是有意不支持网络的。这一点并不奇怪。总之，SQLite不能做的都是它所能做的直接结果。它开始的设计就是一款模块化、简单、紧凑的以及易于使用的嵌入式关系数据库，它的基础代码都在使用它的程序员的掌握范围内。从多方面看，它能完成许多其他数据库不能做的事情，例如，运行在电力消耗实际上是一项制约因素的嵌入式环境中。

尽管SQLite的实现已经相当好了，但仍有部分特性未能实现，这些特性有：

完整的触发器支持。SQLite支持几乎所有的标准触发器功能，包括递归触发器和INSTEAD OF触发器。但是对于所有的触发器类型，当受触发器查询影响的每一行做评估时，SQLite需要FOR EACH ROW行为。ANSI SQL92也说明了当前不支持FOR EACH STATEMENT。

完整的修改表结构支持。目前只支持RENAME TABLE和ADD COLUMN类型的ALTER TABLE命令。其他类型的ALTER TABLE操作，例如DROP COLUMN、ALTER COLUMN以及ADD CONSTRAINT还未实现。

右外连接与全外连接。左外连接（LEFT OUT JOIN）已经支持，但是右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）还未实现。所有的右外连接在语义上都有相同的左外连接，相反也成立。通过简单逆向表的顺序和修改连接限制，左外连接可以作为右外连接的实现。全外连接可以通过组合左外连接和UNION以及在WHERE子句中进行恰当的NULL过滤实现。

可更新的视图。SQLite的视图使只读的，您不能在视图上执行DELETE、INSERT或者UPDATE语句。但是您可以创建一个启动对视图进行DELETE、INSERT或者UPDATE的触发器，在触发器内完成您需要执行的操作。

窗口功能。ANSI SQL99的新功能之一就是窗口功能。该功能提供结果集的后处理分析，例如排序、平均移动以及超前和滞后计算等。SQLite目前支持ANSI SQL92的一部分，因此，它不支持像RANK()、ROW_NUMBER()等。

授权和撤销。由于SQLite能读写普通的磁盘文件，因此，唯一可以应用的访问权限就是所在操作系统的普通文件的访问权限。授权（GRANT）和撤销（REVOKE）命令一般是在高端系统中使用的，这些系统中有多个用户，不同用户对数据库中的数据有不同的访问级别。SQLite模型中，应用程序是主用户，能够访问整个数据库。这种模型中的访问明确定义为应用程序级–具体地说，就是应用程序可以访问数据库文件。

三 如何创建成内存数据库和临时数据库(即非持久化)

1、内存数据库：

在SQLite中，数据库通常是存储在磁盘文件中的。然而在有些情况下，我们可以让数据库始终驻留在内存中。最常用的一种方式是在调用sqlite3_open()的时候，数据库文件名参数传递":memory:"，如：
rc = sqlite3_open(":memory:", &db);
在调用完以上函数后，不会有任何磁盘文件被生成，取而代之的是，一个新的数据库在纯内存中被成功创建了。由于没有持久化，该数据库在当前数据库连接被关闭后就会立刻消失。需要注意的是，尽管多个数据库连接都可以通过上面的方法创建内存数据库，然而它们却是不同的数据库，相互之间没有任何关系。事实上，我们也可以通过Attach命令将内存数据库像其他普通数据库一样，附加到当前的连接中，如：
ATTACH DATABASE ‘:memory:’ AS aux1;

2、临时数据库：

在调用sqlite3_open()函数或执行ATTACH命令时，如果数据库文件参数传的是空字符串，那么一个新的临时文件将被创建作为临时数据库的底层文件，如：
rc = sqlite3_open("", &db);
或
ATTACH DATABASE ‘’ AS aux2;
和内存数据库非常相似，两个数据库连接创建的临时数据库也是各自独立的，在连接关闭后，临时数据库将自动消失，其底层文件也将被自动删除。
尽管磁盘文件被创建用于存储临时数据库中的数据信息，但是实际上临时数据库也会和内存数据库一样通常驻留在内存中，唯一不同的是，当临时数据库中数据量过大时，SQLite为了保证有更多的内存可用于其它操作，因此会将临时数据库中的部分数据写到磁盘文件中，而内存数据库则始终会将数据存放在内存中。

较完整代码

SQLite数据库是一款非常小巧的嵌入式开源数据库软件，也就是说
没有独立的维护进程，所有的维护都来自于程序本身。
在python中，使用sqlite3创建数据库的连接，当我们指定的数据库文件不存在的时候
连接对象会自动创建数据库文件；如果数据库文件已经存在，则连接对象不会再创建
数据库文件，而是直接打开该数据库文件。
连接对象可以是硬盘上面的数据库文件，也可以是建立在内存中的，在内存中的数据库
执行完任何操作后，都不需要提交事务的(commit)

创建在硬盘上面： conn = sqlite3.connect('c:\\test\\test.db')
创建在内存上面： conn = sqlite3.connect('"memory:')下面我们一硬盘上面创建数据库文件为例来具体说明：
conn = sqlite3.connect('c:\\test\\hongten.db')
其中conn对象是数据库链接对象，而对于数据库链接对象来说，具有以下操作：commit()            --事务提交rollback()          --事务回滚close()             --关闭一个数据库链接cursor()            --创建一个游标cu = conn.cursor()
这样我们就创建了一个游标对象：cu
在sqlite3中，所有sql语句的执行都要在游标对象的参与下完成
对于游标对象cu，具有以下具体操作：execute()           --执行一条sql语句executemany()       --执行多条sql语句close()             --游标关闭fetchone()          --从结果中取出一条记录fetchmany()         --从结果中取出多条记录fetchall()          --从结果中取出所有记录scroll()            --游标滚动

完整代码

#python sqlite#Author : Hongten
#MailTo : hongtenzone@foxmail.com
#QQ     : 648719819
#Blog   : http://www.cnblogs.com/hongten
#Create : 2013-08-09
#Version: 1.0#DB-API 2.0 interface for SQLite databasesimport sqlite3
import os
'''SQLite数据库是一款非常小巧的嵌入式开源数据库软件，也就是说
没有独立的维护进程，所有的维护都来自于程序本身。
在python中，使用sqlite3创建数据库的连接，当我们指定的数据库文件不存在的时候
连接对象会自动创建数据库文件；如果数据库文件已经存在，则连接对象不会再创建
数据库文件，而是直接打开该数据库文件。连接对象可以是硬盘上面的数据库文件，也可以是建立在内存中的，在内存中的数据库执行完任何操作后，都不需要提交事务的(commit)创建在硬盘上面： conn = sqlite3.connect('c:\\test\\test.db')创建在内存上面： conn = sqlite3.connect('"memory:')下面我们一硬盘上面创建数据库文件为例来具体说明：conn = sqlite3.connect('c:\\test\\hongten.db')其中conn对象是数据库链接对象，而对于数据库链接对象来说，具有以下操作：commit()            --事务提交rollback()          --事务回滚close()             --关闭一个数据库链接cursor()            --创建一个游标cu = conn.cursor()这样我们就创建了一个游标对象：cu在sqlite3中，所有sql语句的执行都要在游标对象的参与下完成对于游标对象cu，具有以下具体操作：execute()           --执行一条sql语句executemany()       --执行多条sql语句close()             --游标关闭fetchone()          --从结果中取出一条记录fetchmany()         --从结果中取出多条记录fetchall()          --从结果中取出所有记录scroll()            --游标滚动'''#global var
#数据库文件绝句路径
DB_FILE_PATH = ''
#表名称
TABLE_NAME = ''
#是否打印sql
SHOW_SQL = Truedef get_conn(path):'''获取到数据库的连接对象，参数为数据库文件的绝对路径如果传递的参数是存在，并且是文件，那么就返回硬盘上面改路径下的数据库文件的连接对象；否则，返回内存中的数据接连接对象'''conn = sqlite3.connect(path)if os.path.exists(path) and os.path.isfile(path):print('硬盘上面:[{}]'.format(path))return connelse:conn = Noneprint('内存上面:[:memory:]')return sqlite3.connect(':memory:')def get_cursor(conn):'''该方法是获取数据库的游标对象，参数为数据库的连接对象如果数据库的连接对象不为None，则返回数据库连接对象所创建的游标对象；否则返回一个游标对象，该对象是内存中数据库连接对象所创建的游标对象'''if conn is not None:return conn.cursor()else:return get_conn('').cursor()###############################################################
####            创建|删除表操作     START
###############################################################
def drop_table(conn, table):'''如果表存在,则删除表，如果表中存在数据的时候，使用该方法的时候要慎用！'''if table is not None and table != '':sql = 'DROP TABLE IF EXISTS ' + tableif SHOW_SQL:print('执行sql:[{}]'.format(sql))cu = get_cursor(conn)cu.execute(sql)conn.commit()print('删除数据库表[{}]成功!'.format(table))close_all(conn, cu)else:print('the [{}] is empty or equal None!'.format(sql))def create_table(conn, sql):'''创建数据库表：student'''if sql is not None and sql != '':cu = get_cursor(conn)if SHOW_SQL:print('执行sql:[{}]'.format(sql))cu.execute(sql)conn.commit()print('创建数据库表[student]成功!')close_all(conn, cu)else:print('the [{}] is empty or equal None!'.format(sql))###############################################################
####            创建|删除表操作     END
###############################################################def close_all(conn, cu):'''关闭数据库游标对象和数据库连接对象'''try:if cu is not None:cu.close()finally:if cu is not None:cu.close()###############################################################
####            数据库操作CRUD     START
###############################################################def save(conn, sql, data):'''插入数据'''if sql is not None and sql != '':if data is not None:cu = get_cursor(conn)for d in data:if SHOW_SQL:print('执行sql:[{}],参数:[{}]'.format(sql, d))cu.execute(sql, d)conn.commit()close_all(conn, cu)else:print('the [{}] is empty or equal None!'.format(sql))def fetchall(conn, sql):'''查询所有数据'''if sql is not None and sql != '':cu = get_cursor(conn)if SHOW_SQL:print('执行sql:[{}]'.format(sql))cu.execute(sql)r = cu.fetchall()if len(r) > 0:for e in range(len(r)):print(r[e])else:print('the [{}] is empty or equal None!'.format(sql)) def fetchone(conn, sql, data):'''查询一条数据'''if sql is not None and sql != '':if data is not None:#Do this insteadd = (data,) cu = get_cursor(conn)if SHOW_SQL:print('执行sql:[{}],参数:[{}]'.format(sql, data))cu.execute(sql, d)r = cu.fetchall()if len(r) > 0:for e in range(len(r)):print(r[e])else:print('the [{}] equal None!'.format(data))else:print('the [{}] is empty or equal None!'.format(sql))def update(conn, sql, data):'''更新数据'''if sql is not None and sql != '':if data is not None:cu = get_cursor(conn)for d in data:if SHOW_SQL:print('执行sql:[{}],参数:[{}]'.format(sql, d))cu.execute(sql, d)conn.commit()close_all(conn, cu)else:print('the [{}] is empty or equal None!'.format(sql))def delete(conn, sql, data):'''删除数据'''if sql is not None and sql != '':if data is not None:cu = get_cursor(conn)for d in data:if SHOW_SQL:print('执行sql:[{}],参数:[{}]'.format(sql, d))cu.execute(sql, d)conn.commit()close_all(conn, cu)else:print('the [{}] is empty or equal None!'.format(sql))
###############################################################
####            数据库操作CRUD     END
##############################################################################################################################
####            测试操作     START
###############################################################
def drop_table_test():'''删除数据库表测试'''print('删除数据库表测试...')conn = get_conn(DB_FILE_PATH)drop_table(conn, TABLE_NAME)def create_table_test():'''创建数据库表测试'''print('创建数据库表测试...')create_table_sql = '''CREATE TABLE `student` (`id` int(11) NOT NULL,`name` varchar(20) NOT NULL,`gender` varchar(4) DEFAULT NULL,`age` int(11) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`phone` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`))'''conn = get_conn(DB_FILE_PATH)create_table(conn, create_table_sql)def save_test():'''保存数据测试...'''print('保存数据测试...')save_sql = '''INSERT INTO student values (?, ?, ?, ?, ?, ?)'''data = [(1, 'Hongten', '男', 20, '广东省广州市', '13423****62'),(2, 'Tom', '男', 22, '美国旧金山', '15423****63'),(3, 'Jake', '女', 18, '广东省广州市', '18823****87'),(4, 'Cate', '女', 21, '广东省广州市', '14323****32')]conn = get_conn(DB_FILE_PATH)save(conn, save_sql, data)def fetchall_test():'''查询所有数据...'''print('查询所有数据...')fetchall_sql = '''SELECT * FROM student'''conn = get_conn(DB_FILE_PATH)fetchall(conn, fetchall_sql)def fetchone_test():'''查询一条数据...'''print('查询一条数据...')fetchone_sql = 'SELECT * FROM student WHERE ID = ? 'data = 1conn = get_conn(DB_FILE_PATH)fetchone(conn, fetchone_sql, data)def update_test():'''更新数据...'''print('更新数据...')update_sql = 'UPDATE student SET name = ? WHERE ID = ? 'data = [('HongtenAA', 1),('HongtenBB', 2),('HongtenCC', 3),('HongtenDD', 4)]conn = get_conn(DB_FILE_PATH)update(conn, update_sql, data)def delete_test():'''删除数据...'''print('删除数据...')delete_sql = 'DELETE FROM student WHERE NAME = ? AND ID = ? 'data = [('HongtenAA', 1),('HongtenCC', 3)]conn = get_conn(DB_FILE_PATH)delete(conn, delete_sql, data)###############################################################
####            测试操作     END
###############################################################def init():'''初始化方法'''#数据库文件绝句路径global DB_FILE_PATHDB_FILE_PATH = 'c:\\test\\hongten.db'#数据库表名称global TABLE_NAMETABLE_NAME = 'student'#是否打印sqlglobal SHOW_SQLSHOW_SQL = Trueprint('show_sql : {}'.format(SHOW_SQL))#如果存在数据库表，则删除表drop_table_test()#创建数据库表studentcreate_table_test()#向数据库表中插入数据save_test()def main():init()fetchall_test()print('#' * 50)fetchone_test()print('#' * 50)update_test()fetchall_test()print('#' * 50)delete_test()fetchall_test()if __name__ == '__main__':main()

输出结果：

当SHOW_SQL = False的时候：

Python 3.3.2 (v3.3.2:d047928ae3f6, May 16 2013, 00:03:43) [MSC v.1600 32 bit (Intel)] on win32
Type "copyright", "credits" or "license()" for more information.
>>> ================================ RESTART ================================
>>> 
show_sql : False
删除数据库表测试...
硬盘上面:[c:\test\hongten.db]
删除数据库表[student]成功!
创建数据库表测试...
硬盘上面:[c:\test\hongten.db]
创建数据库表[student]成功!
保存数据测试...
硬盘上面:[c:\test\hongten.db]
查询所有数据...
硬盘上面:[c:\test\hongten.db]
(1, 'Hongten', '男', 20, '广东省广州市', '13423****62')
(2, 'Tom', '男', 22, '美国旧金山', '15423****63')
(3, 'Jake', '女', 18, '广东省广州市', '18823****87')
(4, 'Cate', '女', 21, '广东省广州市', '14323****32')
##################################################
查询一条数据...
硬盘上面:[c:\test\hongten.db]
(1, 'Hongten', '男', 20, '广东省广州市', '13423****62')
##################################################
更新数据...
硬盘上面:[c:\test\hongten.db]
查询所有数据...
硬盘上面:[c:\test\hongten.db]
(1, 'HongtenAA', '男', 20, '广东省广州市', '13423****62')
(2, 'HongtenBB', '男', 22, '美国旧金山', '15423****63')
(3, 'HongtenCC', '女', 18, '广东省广州市', '18823****87')
(4, 'HongtenDD', '女', 21, '广东省广州市', '14323****32')
##################################################
删除数据...
硬盘上面:[c:\test\hongten.db]
查询所有数据...
硬盘上面:[c:\test\hongten.db]
(2, 'HongtenBB', '男', 22, '美国旧金山', '15423****63')
(4, 'HongtenDD', '女', 21, '广东省广州市', '14323****32')
>>>

当SHOW_SQL = True的时候：

Python 3.3.2 (v3.3.2:d047928ae3f6, May 16 2013, 00:03:43) [MSC v.1600 32 bit (Intel)] on win32
Type "copyright", "credits" or "license()" for more information.
>>> ================================ RESTART ================================
>>> 
show_sql : True
删除数据库表测试...
硬盘上面:[c:\test\hongten.db]
执行sql:[DROP TABLE IF EXISTS student]
删除数据库表[student]成功!
创建数据库表测试...
硬盘上面:[c:\test\hongten.db]
执行sql:[CREATE TABLE `student` (`id` int(11) NOT NULL,`name` varchar(20) NOT NULL,`gender` varchar(4) DEFAULT NULL,`age` int(11) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`phone` varchar(20) DEFAULT NULL,PRIMARY KEY (`id`))]
创建数据库表[student]成功!
保存数据测试...
硬盘上面:[c:\test\hongten.db]
执行sql:[INSERT INTO student values (?, ?, ?, ?, ?, ?)],参数:[(1, 'Hongten', '男', 20, '广东省广州市', '13423****62')]
执行sql:[INSERT INTO student values (?, ?, ?, ?, ?, ?)],参数:[(2, 'Tom', '男', 22, '美国旧金山', '15423****63')]
执行sql:[INSERT INTO student values (?, ?, ?, ?, ?, ?)],参数:[(3, 'Jake', '女', 18, '广东省广州市', '18823****87')]
执行sql:[INSERT INTO student values (?, ?, ?, ?, ?, ?)],参数:[(4, 'Cate', '女', 21, '广东省广州市', '14323****32')]
查询所有数据...
硬盘上面:[c:\test\hongten.db]
执行sql:[SELECT * FROM student]
(1, 'Hongten', '男', 20, '广东省广州市', '13423****62')
(2, 'Tom', '男', 22, '美国旧金山', '15423****63')
(3, 'Jake', '女', 18, '广东省广州市', '18823****87')
(4, 'Cate', '女', 21, '广东省广州市', '14323****32')
##################################################
查询一条数据...
硬盘上面:[c:\test\hongten.db]
执行sql:[SELECT * FROM student WHERE ID = ? ],参数:[1]
(1, 'Hongten', '男', 20, '广东省广州市', '13423****62')
##################################################
更新数据...
硬盘上面:[c:\test\hongten.db]
执行sql:[UPDATE student SET name = ? WHERE ID = ? ],参数:[('HongtenAA', 1)]
执行sql:[UPDATE student SET name = ? WHERE ID = ? ],参数:[('HongtenBB', 2)]
执行sql:[UPDATE student SET name = ? WHERE ID = ? ],参数:[('HongtenCC', 3)]
执行sql:[UPDATE student SET name = ? WHERE ID = ? ],参数:[('HongtenDD', 4)]
查询所有数据...
硬盘上面:[c:\test\hongten.db]
执行sql:[SELECT * FROM student]
(1, 'HongtenAA', '男', 20, '广东省广州市', '13423****62')
(2, 'HongtenBB', '男', 22, '美国旧金山', '15423****63')
(3, 'HongtenCC', '女', 18, '广东省广州市', '18823****87')
(4, 'HongtenDD', '女', 21, '广东省广州市', '14323****32')
##################################################
删除数据...
硬盘上面:[c:\test\hongten.db]
执行sql:[DELETE FROM student WHERE NAME = ? AND ID = ? ],参数:[('HongtenAA', 1)]
执行sql:[DELETE FROM student WHERE NAME = ? AND ID = ? ],参数:[('HongtenCC', 3)]
查询所有数据...
硬盘上面:[c:\test\hongten.db]
执行sql:[SELECT * FROM student]
(2, 'HongtenBB', '男', 22, '美国旧金山', '15423****63')
(4, 'HongtenDD', '女', 21, '广东省广州市', '14323****32')
>>>

四 Python SQLite create_function方法：注册自定义函数

数据库连接对象还提供了一个 create_function(name, num_params, func) 方法，该方法用于注册一个自定义函数，接下来程序就可以在 SQL 语句中使用该自定义函数：

create_function 方法包含 3 个参数：
name 参数：指定注册的自定义函数的名字。
num_params：指定自定义函数所需参数的个数。
func：指定自定义函数对应的函数。

下面程序使用 create_function() 方法为 SQL 语句注册一个自定义函数，然后程序就可以在 SQL 语句中使用该自定义函数：

# 导入访问SQLite的模块
import sqlite3# 先定义一个普通函数，准备注册为SQL中的自定义函数
def reverse_ext(st):# 对字符串反转，前后加方括号return '[' + st[::-1] + ']'
# ①、打开或创建数据库
# 也可以使用特殊名：:memory:代表创建内存中的数据库
conn = sqlite3.connect('first.db')
# 调用create_function注册自定义函数：enc
conn.create_function('enc', 1, reverse_ext)
# ②、获取游标
c = conn.cursor()
# ③、在SQL语句中使用enc自定义函数
c.execute('insert into user_tb values(null, ?, enc(?), ?)',('贾宝玉', '123456', 'male'))
conn.commit()
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()

上面程序中，第 12 行代码将 reverse_ext() 函数注册为自定义函数 enc，该函数用于模拟一个简单的加密功能：程序会对字符串反转，并在字符串前后添加方括号。

提示：此时使用的加密功能只是一个简单的模拟，如果需要真正对密码进行加密，则建议使用更高强度的加密算法，比如加盐 MD5 加密。

程序中第 16 行代码在执行的 SQL 语句中使用了 enc 自定义函数，该自定义函数用于对插入的密码进行加密。因此，当使用上面程序插入数据时，程序会自动对插入的密码进行加密；程序会对密码进行反转，并在密码前后添加方括号。

五 Python在SQLite数据库中动态创建数据表的思路与实现

问题描述：

在管理信息系统或者动态网站开发时，离不开数据库的使用。以SQLite数据库为例，系统运行时要求数据库和对应的数据表已存在，一种方案是提前建好数据库和所有表，再一种方案是系统初始化时自动创建数据库或者相应的数据表。本文介绍第二种方法的思路和实现，自动测试数据库中是否存在某个表，如果不存在就创建。对于SQLite数据库来说，关键是系统表sqlite_master，这个表中记录了所有用户表的信息。例如：

参考代码：

运行结果：

作者：Python_小屋
来源：CSDN
原文：https://blog.csdn.net/oh5W6HinUg43JvRhhB/article/details/84949423
版权声明：本文为博主原创文章，转载请附上博文链接！

来自  http://www.taodudu.cc/news/show-5272485.html?action=onClick