>>> from env_helper import info; info()
页面更新时间： 2024-04-07 15:58:00
运行环境：
Linux发行版本: Debian GNU/Linux 12 (bookworm)
操作系统内核: Linux-6.1.0-18-amd64-x86_64-with-glibc2.36
Python版本: 3.11.2
2.3. 在 Python 中访问 SQLite¶
SQLite3 可使用 sqlite3 模块与 Python 进行集成。sqlite3 模块是由 Gerhard Haring 编写的。 它提供了一个与 PEP 249 描述的 DB-API 2.0 规范兼容的 SQL 接口。 您不需要单独安装该模块，因为 Python 2.5.x 以上版本默认自带了该模块。
为了使用 sqlite3 模块，您首先必须创建一个表示数据库的连接对象。
sqlite3 模块常用的方法：
sqlite3.connect(database [,timeout ,other optional arguments])该 API 打开一个到 SQLite 数据库文件 database 的链接。您可以使用memory来在 RAM 中打开一个到 database 的数据库连接，而不是在磁盘上打开。如果数据库成功打开，则返回一个连接对象。cursor([cursorClass])该例程创建一个 cursor，将在 Python 数据库编程中用到。该方法接受一个单一的可选的参数cursorClass。如果提供了该参数，则它必须是一个扩展自 sqlite3.Cursor 的自定义的cursor类。execute(sql [, optional parameters])该例程执行一个 SQL 语句。该 SQL 语句可以被参数化（即使用占位符代替 SQL 文本）。sqlite3 模块支持两种类型的占位符：问号和命名占位符（命名样式）。commit()该方法提交当前的事务。如果您未调用该方法，那么自您上一次调用commit()以来所做的任何动作对其他数据库连接来说是不可见的。rollback()该方法回滚自上一次调用commit()以来对数据库所做的更改。close()该方法关闭数据库连接。请注意，这不会自动调用commit()。如果您之前未调用commit()方法，就直接关闭数据库连接，您所做的所有更改将全部丢失！fetchone()该方法获取查询结果集中的下一行，返回一个单一的序列，当没有更多可用的数据时，则返回None。fetchall()该例程获取查询结果集中所有（剩余）的行，返回一个列表。当没有可用的行时，则返回一个空的列表。
>>> import os
>>> import sqlite3
2.3.1. 创建数据库¶
用os模块创建一个数据文件，后缀为 .db ，并且用 sqlite3
连接真个数据文件，构建数据库。
>>> db_file = os.path.join(os.getcwd(), 'test.db')
>>> if os.path.exists('test.db'): os.remove('test.db')
>>> conn = sqlite3.connect(db_file)
2.3.2. 创建表¶
该例程创建一个 cursor，将在 Python 数据库编程中用到。该方法接受一个单一的可选的参数 cursorClass。如果提供了该参数，则它必须是一个扩展自 sqlite3.Cursor 的自定义的 cursor 类。
SQLite 的 CREATE TABLE
语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。
>>> cursor = conn.cursor()
>>> cursor.execute('create table company (id int primary key not null ,'
>>> 'person varchar(100) ,'
>>> 'phone varchar(50) ,'
>>> 'email varchar(50));')
<sqlite3.Cursor at 0x7f231d5c3340>
2.3.3. 插入数据¶
SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。
如果要为表中的所有列添加值，您可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。
>>> cursor = conn.cursor()
>>> cursor.execute(r"insert into company values (1,'tom','123456789','124567@outlook.com');")
>>> conn.commit()
2.3.4. 查询数据¶
SQLite 的 SELECT 语句用于从 SQLite
数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。
>>> cursor.execute('select * from company;')
<sqlite3.Cursor at 0x7f231d5c33c0>
>>> cursor.fetchall()
[(1, 'tom', '123456789', '124567@outlook.com')]
2.3.5. 修改数据¶
将已经插入表内的数据进行修改。
SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE
子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。
>>> cursor.execute("update company set phone = 2500000 where ID=1")
>>> conn.commit()
再次查询修改后的值。
>>> cursor.execute('select * from company;')
>>> cursor.fetchall()
[(1, 'tom', '2500000', '124567@outlook.com')]
也可以使用遍历输出。
>>> cursor.execute('select * from company;')
>>> for row in cursor:
>>> print( "id = ", row[0])
>>> print( "name = ", row[1])
>>> print( "phone = ", row[2])
>>> print ("email = ", row[3])
id = 1
name = tom
phone = 2500000
email = 124567@outlook.com
上面分布执行了增加、插入、修改操作，我们接下来执行基本的删除方法。
SQLite 的 DELETE 查询用于删除表中已有的记录。可以使用带有 WHERE
子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。
>>> cursor.execute("DELETE from company where ID=1;")
>>> conn.commit()
>>> cursor.execute('select * from company;')
<sqlite3.Cursor at 0x7f231d5c33c0>
>>> cursor.fetchall()
[]
显然已经将表data内id为1的数据删除了。
2.3.6. 视图（View）¶
视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合。
视图（View）可以包含一个表的所有行或从一个或多个表选定行。视图（View）可以从一个或多个表创建，这取决于要创建视图的 SQLite 查询。、
视图（View）是一种虚表，允许用户实现以下几点：
用户或用户组查找结构数据的方式更自然或直观。
限制数据访问，用户只能看到有限的数据，而不是完整的表。
汇总各种表中的数据，用于生成报告。
SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。
2.3.7. 其他子句¶
limit 用户查询时用于限制由 SELECT 语句返回的数据数量。相当于分页。
>>> cursor.execute("INSERT INTO company (ID,PERSON,PHONE,EMAIL) \
>>> VALUES (1, 'Paul', 2000000,'2000000@qq.com' )")
>>> cursor.execute("INSERT INTO company (ID,PERSON,PHONE,EMAIL) \
>>> VALUES (2, 'Allen',1500000,'1500000@qq.com')")
>>> cursor.execute("INSERT INTO company (ID,PERSON,PHONE,EMAIL) \
>>> VALUES (3, 'Teddy', 2500000,'2500000@qq.com' )")
>>> cursor.execute("INSERT INTO company (ID,PERSON,PHONE,EMAIL)\
>>> VALUES (4, 'Mark', 6500000,'6500000@qq.com' )")
>>> cursor.execute("INSERT INTO company (ID,PERSON,PHONE,EMAIL)\
>>> VALUES (5, 'TOM', 6000,'6000@qq.com' )")
<sqlite3.Cursor at 0x7f231d5c33c0>
>>> cursor.execute("SELECT id,person FROM company LIMIT 3 ;")
>>> cursor.fetchall()
[(1, 'Paul'), (2, 'Allen'), (3, 'Teddy')]
Like 用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。
百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。
>>> cursor.execute("SELECT id,person FROM company WHERE person LIKE '%a_k%' ;")
>>> cursor.fetchall()
[(4, 'Mark')]
Order By 用来基于一个或多个列按升序或降序顺序排列数据。
>>> cursor.execute(" SELECT * FROM company ORDER BY id ASC;")
>>> cursor.fetchall()
[(1, 'Paul', '2000000', '2000000@qq.com'),
(2, 'Allen', '1500000', '1500000@qq.com'),
(3, 'Teddy', '2500000', '2500000@qq.com'),
(4, 'Mark', '6500000', '6500000@qq.com'),
(5, 'TOM', '6000', '6000@qq.com')]
>>> cursor.execute(" SELECT * FROM company ORDER BY phone DESC;")
>>> cursor.fetchall()
[(4, 'Mark', '6500000', '6500000@qq.com'),
(5, 'TOM', '6000', '6000@qq.com'),
(3, 'Teddy', '2500000', '2500000@qq.com'),
(1, 'Paul', '2000000', '2000000@qq.com'),
(2, 'Allen', '1500000', '1500000@qq.com')]
>>> conn.close()
2.3.8. kye-value 用法¶
一个有着20多年历史的用C语言编写的嵌入式SQL数据库似乎不像是并发应用程序的键值存储的最佳解决方案。 而且它不是开箱即用的，但是如果配置正确，它的性能会非常出色。
除此之外，它经过了良好的测试、验证和稳定——这是一个数据库存储有价值数据的好特性。
>>> import sqlite3
>>> class KeyValueStore(dict):
>>> def __init__(self, filename=None):
>>> self.conn = sqlite3.connect(filename)
>>> self.conn.execute("CREATE TABLE IF NOT EXISTS kv (key text unique, value text)")
>>> def close(self):
>>> self.conn.commit()
>>> self.conn.close()
>>> def __len__(self):
>>> rows = self.conn.execute('SELECT COUNT(*) FROM kv').fetchone()[0]
>>> return rows if rows is not None else 0
>>> def iterkeys(self):
>>> c = self.conn.cursor()
>>> for row in self.conn.execute('SELECT key FROM kv'):
>>> yield row[0]
>>> def itervalues(self):
>>> c = self.conn.cursor()
>>> for row in c.execute('SELECT value FROM kv'):
>>> yield row[0]
>>> def iteritems(self):
>>> c = self.conn.cursor()
>>> for row in c.execute('SELECT key, value FROM kv'):
>>> yield row[0], row[1]
>>> def keys(self):
>>> return list(self.iterkeys())
>>> def values(self):
>>> return list(self.itervalues())
>>> def items(self):
>>> return list(self.iteritems())
>>> def __contains__(self, key):
>>> return self.conn.execute('SELECT 1 FROM kv WHERE key = ?', (key,)).fetchone() is not None
>>> def __getitem__(self, key):
>>> item = self.conn.execute('SELECT value FROM kv WHERE key = ?', (key,)).fetchone()
>>> if item is None:
>>> raise KeyError(key)
>>> return item[0]
>>> def __setitem__(self, key, value):
>>> self.conn.execute('REPLACE INTO kv (key, value) VALUES (?,?)', (key, value))
>>> def __delitem__(self, key):
>>> if key not in self:
>>> raise KeyError(key)
>>> self.conn.execute('DELETE FROM kv WHERE key = ?', (key,))
>>> def __iter__(self):
>>> return self.iterkeys()
>>> kv = KeyValueStore('test.db')
>>> print(len(kv))
>>> kv['hello1'] = 'you1'
>>> kv['hello2'] = 'you2'
>>> kv['hello3'] = 'you3'
>>> print(kv['hello1'])
>>> print(len(kv))
>>> del kv['hello1']
>>> print(len(kv))
>>> print('hello1' in kv)
>>> print('hello3' in kv)
>>> kv['hello3'] = 'newvalue'
>>> print(kv['hello3'])
>>> print(kv.keys())
>>> print(kv.values())
>>> print(kv.items())
>>> for k in kv:
>>> print(k, kv[k])
>>> kv.close()
0
you1
3
2
False
True
newvalue
['hello2', 'hello3']
['you2', 'newvalue']
[('hello2', 'you2'), ('hello3', 'newvalue')]
hello2 you2
hello3 newvalue
>>> conn = sqlite3.connect('xx_kvdb.db')
>>> conn.execute("CREATE TABLE IF NOT EXISTS kv (key text unique, value text)")
>>> conn.execute('SELECT COUNT(*) FROM kv').fetchone()[0]
0
>>> uu = conn.execute('SELECT key FROM kv')
>>> for x in uu:
>>> print(x)
>>> conn.execute('REPLACE INTO kv (key, value) VALUES (?,?)', ('key', 'value'))
<sqlite3.Cursor at 0x7f231d78d9c0>