python操作SQLite数据库

什么是SQLite数据库

• SQLite是一种嵌入式数据库，它的数据库就是一个文件，且SQLite是遵守ACID的关系数据库管理系统，它包含在一个相对小的C程序库中，与许多其它数据库管理系统不同，SQLite不是一个客户端/服务器结构的数据库引擎，而是被集成在用户程序中的嵌入式关系型数据库；
• SQLite遵守ACID，实现了大多数SQL标准，它使用动态的、弱类型的SQL语法；
• SQLite作为嵌入式数据库，是应用程序，如网页浏览器，在本地/客户端存储数据的常见选择；

SQLite是内嵌在Python中的轻量级、基于磁盘文件袋额数据库管理系统，不需要安装和配置服务，支持使用SQL语句来访问数据库。该数据库使用C语言开发，支持大多数SQL91标准，支持原子的、一致的、独立的和持久的事务，不支持外键限制；通过数据库级的独占性和共享性锁定来实现独立事务，当多个线程同时访问同一个数据库并试图写入数据时，每一时刻只有一个线程可以写入数据。

SQLite支持最大140TB大小的单个数据库，每个数据库完全存储在单个磁盘文件中，以B+树数据结构的形式存储，一个数据库就是一个文件，通过直接复制数据库文件就可以实现数据库的备份。如果需要使用可视化管理工具，可以下载并使用SQLiteManager、SQLite Database Browser 或其他类似工具。

Python sqlite3模块的API

我们还可以来简单了解一下sqlite3模块的API

• sqlite3.connect()：打开SQLite数据库连接，返回一个连接对象；
• connection.cursor()：创建一个 cursor；
• cursor.execute()：执行一个 SQL 语句；
• connection.execute()：通过调用光标（cursor）方法创建了一个中间的光标对象，然后通过给定的参数调用光标的 execute 方法；
• cursor.executemany()：对 seq_of_parameters 中的所有参数或映射执行一个 SQL 命令；
• connection.executemany()：是一个由调用光标（cursor）方法创建的中间的光标对象的快捷方式，然后通过给定的参数调用光标的 executemany 方法；
• cursor.executescript()：一旦接收到脚本，会执行多个 SQL 语句；
• connection.executescript()：是一个由调用光标（cursor）方法创建的中间的光标对象的快捷方式，然后通过给定的参数调用光标的 executescript 方法；
• connection.total_changes()：返回自数据库连接打开以来被修改、插入或删除的数据库总行数；
• connection.commit()：该方法提交当前的事务；
• connection.rollback()：该方法回滚自上一次调用 commit() 以来对数据库所做的更改；
• connection.close()：该方法关闭数据库连接；
• cursor.fetchone()：获取查询结果集中的下一行，返回一个单一的序列，当没有更多可用的数据时，则返回 None；
• cursor.fetchmany()：获取查询结果集中的下一行组，返回一个列表；
• cursor.fetchall()：获取查询结果集中所有（剩余）的行，返回一个列表,当没有可用的行时，则返回一个空的列表；

代码实现

访问和操作SQLite数据时，首先导入sqlite3模块，然后创建一个与数据库关联的Connection对象，例如：

# -*- coding:utf-8 -*-
import sqlite3   #导入模块

'''
连接数据库 connect()方法，可以判断一个数据库文件是否存在，如果不存在就自动创建一个，
如果存在的话，就打开那个数据库。
'''
database=r'C:\Users\WYXCz\Desktop\crawl_data.db'
conn = sqlite3.connect(database)
'''
再创建一个Cusor对象，并且调用Cursor对象的execute()方法来执行SQL语句
创建数据表以及查询、插入、修改或删除数据库中的数据
'''
c = conn.cursor()
#创建表
# c.execute('''CREATE TABLE stocks(date text,trans text,symbol text,gty real,price real)''')
#向表中插入一条数据
# 提交事务   SELECT语句不需要此操作，默认的execute方法的,commit_at_once设为True会隐式调用此方法
c.execute('''insert into stocks(date,trans,symbol,gty,price) values('2016-02-05','BUY','RHAT',100,35.14)''')
# insert 方法2
sql1='''insert into stocks(date,trans,symbol,gty,price) values(?,?,?,?)'''
params=('2016-02-05','BUY','RHAT',100,35.14)
c.execute(sql1,params)
#提交当前事务，保存数据
conn.commit()
#关闭数据库连接
conn.close()

#-----------------查询刚才插入的数据  方法1------------------------
#由于刚才已经关闭了数据库连接，需要重新创建Connection对象和Cursor对象
conn = sqlite3.connect(database)
c = conn.execute('''select * from stocks''')
print(c)   #<sqlite3.Cursor object at 0x00000000007E25E0>
print(list(c))   #[('2016-01-05', 'BUY', 'RHAT', 100.0, 35.14)]

#-----------------查询刚才插入的数据  方法2------------------------
conn = sqlite3.connect(database)
c = conn.cursor()
a = c.execute('select * from stocks')
print(c)   #<sqlite3.Cursor object at 0x00000000007E25E0>
# print('fetchone:',c.fetchone())
# print('fetchall',c.fetchall())
for i in a:
    print(i)    #('2016-01-05', 'BUY', 'RHAT', 100.0, 35.14)

DBUtils+sqlite3

import sqlite3
from DBUtils.PersistentDB import PersistentDB
	
db_path = r'D:/works/python1/test10/resources/stock0.db'
dbpool = PersistentDB(sqlite3, maxusage=2, database=db_path)
db = dbpool.connection()
print (db is not None)
db.close()

sqlite_utils

pip install sqlite-utils

import sqlite_utils
db = sqlite_utils.Database("demo_database.db")
# This line creates a "dogs" table if one does not already exist:
db["dogs"].insert_all([
    {"id": 1, "age": 4, "name": "Cleo"},
    {"id": 2, "age": 2, "name": "Pancakes"}
], pk="id")

sqlite-utils命令行工具

现在，您可以使用CLI实用程序执行以下操作：

$ sqlite-utils tables dogs.db --counts
[{"table": "dogs", "count": 2}]

$ sqlite-utils dogs.db "select * from dogs"
[{"id": 1, "age": 4, "name": "Cleo"},
 {"id": 2, "age": 2, "name": "Pancakes"}]

$ sqlite-utils dogs.db "select * from dogs" --csv
id,age,name
1,4,Cleo
2,2,Pancakes

$ sqlite-utils dogs.db "select * from dogs" --table
  id    age  name
----  -----  --------
   1      4  Cleo
   2      2  Pancakes

Sqlite的多表连接更新

1、set时，要将table2的num2的值赋给table1的num1字段，要select一下table2，并在括号关联起来

update table1
set  num1 = (select num2 from table2 where table2.pid=table1.id)
where...

更新多个字段时：

update table1
set  num1 = (select num2 from table2 where table2.pid=table1.id),
num11 = (select num22 from table2 where table2.pid=table1.id)
where...

2、where时，也一样，比如我就将上面的改一下

update table1
set  num = 99
where table1.id=(select pid from table2 where table2.pid=table1.id)

别人说太慢了，找了一个折中的办法：

1.把连接写成视图

2.导出结果到csv

3.建立一个空表，结构和视图相同

4.把csv导入到空表

5.修整相关的列

datasette

• 安装

pip3 install datasette

• 基本用法

datasette serve path/to/database.db

这将在端口8001上启动Web服务器，访问http://localhost:8001/以访问Web界面。

• 将db文件保存到json中

datasette serve fivethirtyeight.db -m metadata.json