运维学python之爬虫中级篇（七）Sq
前文已经讲过无数据库版本操作（csv，json），今天我们要开始讲有数据库版本的操作，首先就是sqlite3。
1 介绍
SQLite是一个C库，它提供了一个轻量级的基于磁盘的数据库，它不需要单独的服务器进程，并且允许使用SQL查询语言的非标准格式来访问数据库。一些应用程序可以使用SQLite进行内部数据存储。还可以使用SQLite对应用程序进行原型化，然后将代码移植到更大的数据库，如PostgreSQL或Oracle。
2 sqlite3简单使用
sqlite3模块是由Gerhard Häring写的，它提供了与PEP 249所描述的db-api 2.0规范兼容的SQL接口。要使用这个模块，首先必须创建一个表示数据库的连接对象。这里的数据将存储在示例中。db文件:
# -*- coding: utf-8 -*-
import sqlite3
# 创建数据库连接对象，存储在test.db中
conn = sqlite3.connect('test.db')您还可以提供特殊的名称:memory:在RAM中创建一个数据库。 有了连接对象后，就可以创建一个Cursor对象，并调用它的execute()方法来执行SQL命令:
# -*- coding: utf-8 -*-
import sqlite3
conn = sqlite3.connect('test.db')
# 创建游标对象
c = conn.cursor()
# 创建表
c.execute("CREATE TABLE stocks(data TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL)")
# 插入数据
c.execute("INSERT INTO stocks VALUES ('2018-01-14', 'buy', 'rhat', 100, 35.14)")
# 保存数据
conn.commit()
# 关闭连接
conn.close()执行完后会在本地生成一个test.db文件，保存的数据是持久性的，并且在后续的会话中可用（我是用的win10 + pycharm环境），可以下载一个数据库工具navicat premium来查看test.db文件的内容，安装软件网上很好搜，不讲解，我把连接数据库操作截图说明：
通常，您的SQL操作需要使用来自Python变量的值。您不应该使用Python的字符串操作来组装您的查询，因为这样做是不安全的，它使您的程序容易受到SQL注入***。相反，使用DB-api的参数替换。把?作为您想要使用一个值的占位符，然后提供一个元组作为第二个参数，作为光标的execute()方法的第二个参数。(其他数据库模块可能使用不同的占位符，比如%s)例如:
# 不要这么做
# 定义变量
symbol = 'rhat'
# 获取查询结果
dbs = c.execute("SELECT * FROM stocks WHERE symbol='%s'" % symbol)
# 打印出数据
for db in dbs:
print(db)
# 正确做法
t = ('rhat', )
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())
# 插入多条数据
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-03-29', 'BUY', 'ThinkPad', 5000, 55.00),
('2006-03-30', 'SELL', 'IBM', 100, 33.00),
]
c.executemany('INSERT INTO stocks VALUES (?, ?, ?, ?, ?)', purchases)
conn.commit()
conn.close()在执行一个SELECT语句之后检索数据，您可以将光标视为迭代器获取内容，也可以调用游标的fetchone()方法来检索单个匹配的行，或者调用fetchall()来获取所有匹配行的列表。
# 迭代器方式获取内容
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
print(row)
# fetchone和fetchall方式获取内容
c.execute('SELECT * FROM stocks ORDER BY price')
print(c.fetchone())
print(c.fetchall())3 Sqlite3常用模块
名称 | 解释 |
|---|---|
sqlite3.connect(database[, timeout, other arguments]) | 打开一个到SQLite数据库文件数据库的连接。您可以使用“:memory:”打开数据库连接到存储在RAM中的数据库，而不是在磁盘上。当一个数据库被多个连接访问，其中一个进程修改数据库时，SQLite数据库将被锁定，直到事务被提交。超时参数指定连接应该等待多长时间，直到抛出一个异常。超时参数的默认值是5.0(5秒)。 |
conn.Cursor() | 该例程创建一个 cursor，将在 Python 数据库编程中用到。该方法接受一个单一的可选的参数 cursorClass。如果提供了该参数，则它必须是一个扩展自 sqlite3.Cursor 的自定义的 cursor 类。 |
conn.commit() | 提交当前事务。如果不调用这个方法，那么从上次调用commit()之后所做的任何事情都不会从其他数据库连接中可见。 |
conn.rollback() | 这个方法回滚数据库到上一次调用commit()之后的更改。 |
conn.close() | 这将关闭数据库连接。注意，这并不会自动调用commit()。如果您在不调用commit()的情况下关闭数据库连接，那么您的更改将会丢失！ |
conn.execute(sql[, parameters]) | 这是一个非标准的快捷方式，它通过调用游标()方法创建一个游标对象，并使用给定的参数调用游标的execute()方法，并返回游标。 |
conn.executemany(sql[, parameters]) | 同上execute方法，可以同时传入多个参数 |
conn.executescript(sql_script) | 可以直接传入sql脚本，sqlscript可以是str的一个实例。 |
conn.total_changes() | 返回自数据库连接打开以来已修改、插入或删除的数据库行的总数。 |
Cursor.fetchone() | 获取查询结果集的下一行，返回一个单独的序列，或者在没有更多可用数据的情况下返回None。 |
Cursor.fetchmany(size=cursor.arraysize) | 获取查询结果的下一组行，返回一个列表。当没有更多的行可用时，将返回一个空列表。每次调用的行数由size参数指定。如果没有给出，光标的arraysize决定要获取的行数。 |
Cursor.fetchall() | 获取查询结果的所有(剩余)行，返回一个列表。注意，游标的arraysize属性可以影响该操作的性能。当没有行可用时，返回一个空列表。 |
sqlite3.Row | Row实例充当 Connection对象的高度优化的row_factory。它试图在大多数特性中模拟一个元组。它支持列名称和索引、迭代、表示、平等测试和len()的映射访问。如果两个行对象有相同的列，并且它们的成员是相等的，那么它们就比较相等。 |
Row.keys() | 该方法返回一个列名称列表。在查询之后，它是每个元组中的第一个成员。 |
4 示例
连接及游标确定
import sqlite3
# 创建conn实例
conn = sqlite3.connect('test.db')
# 创建游标对象
c = conn.cursor()增
# 创建表
c.execute("CREATE TABLE stocks(data TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL)")
# 插入单条数据
c.execute("INSERT INTO stocks VALUES ('2018-01-14', 'buy', 'rhat', 100, 35.14)")
# 插入多条数据
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-03-29', 'BUY', 'ThinkPad', 5000, 55.00),
('2006-03-30', 'SELL', 'IBM', 100, 33.00),
]
c.executemany('INSERT INTO stocks VALUES (?, ?, ?, ?, ?)', purchases)删
# 删除
c.execute("DELETE from stocks WHERE data='2018-01-14';")改
# 修改数据
c.execute("UPDATE stocks set price = 25000.00 where symbol='ThinkPad'")查
# 迭代器方式获取内容
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
print(row)
# fetchone和fetchall方式获取内容
c.execute('SELECT * FROM stocks ORDER BY price')
print(c.fetchone())
print(c.fetchall())以上就是基本的增删改查操作，记住一定要commit，并且正常close方式关闭，以免造成资源泄露。