python模块分析之sqlite3数据库
SQLite作为一种应用广泛的文件式关系型数据库，python操作sqlite主要有两种方式，原生SQL语句和ORM映射工具。
SQLAlchemy连接SQLITE
SQLAlchemy是一款优秀的python连接关系型数据库的ORM工具，支持SQLite，MYSQL，ORICLE等多种关系型数据库，具体使用：
参见：http://docs.jinkan.org/docs/flask/patterns/sqlalchemy.html
这里主要记录一下SQLAlchemy连接SQLITE的配置方法：
from sqlalchemy import create_engine
# 在Unix/Mac
engine = create_engine('sqlite:////tmp/test.db', convert_unicode=True) # /tmp/test.db为数据库文件的绝对路径
engine = create_engine('sqlite:///tmp/test.db', convert_unicode=True) # tmp/test.db为数据库文件的相对路径
# 在Windows
engine = create_engine('sqlite:///C:\\path\\to\\foo.db') # C:\\path\\to\\foo.db为绝对路径，需要转义
engine = create_engine(r'sqlite:///C:\path\to\foo.db') # 在Windows 中使用原始字符串
# 使用内存作为数据库容器
engine = create_engine('sqlite://')
engine = create_engine('sqlite:///:memory:')
py-sqlite3连接SQLITE
python内部已经自带了连接sqlite的模块和sqlite数据库引擎，不需要安装。
创建连接
import sqlite3
conn = sqlite3.connect('/tmp/test.db') # 连接指定位置的数据库文件，俄国不存在将被创建
conn.close() # 关闭连接
# 由于关闭连接连接这个操作很频繁并且必要，提供了基于上下文管理的功能
with sqlite3.connect('/tmp/test.db') as conn:
"do something.."
执行sql语句
# 我们通过获取游标来执行语句
with sqlite3.connect('/tmp/test.db') as conn:
cu = conn.cursor()
cu.execute('select * from users WHERE username=xiao') # 执行SQL语句
cu.close() # 关闭游标
conn.commit() # 提交
cu.execute('select * from users WHERE username=xiao') # 执行一条SQL语句
cu.executemany("insert into test values(?, ?, ?)", [(),(),()]) # 执行多条SQL语句
-
注意execute函数的第二个参数必须是元组，否则报错。
-
注意不要采用拼接字符串的方式写SQL语句，否则容易遭到注入攻击。
x = 'xiao'
cu.execute('select * from users WHERE username=%s'%x) # 这种方式不可取
# 使用？作为占位符
cu.execute('select * from users WHERE username=?',('xiao',))
- 获取查询结果
ls = cu.execute('select * from users WHERE username=?',('xiao',)) # 返回一个迭代器
ls.fetchone() # 一条记录的元组
ls.fetchall() # 一个元组列表，查询后最好取一次，因为迭代器不可逆
ls.fetchmany(size) # 指定获取的数目，返回一个列表，无参数默认获取所有
- 常用的方法
conn.commit():事务提交
conn.rollback():事务回滚
conn.close():关闭一个数据库连接
conn.cursor():创建一个游标
典型代码结构
# 插入一条数据
import sqlite3
from sqlite3 import Connection
from contextlib import contextmanager
from decorator import decorator
# 使用上下文管理器管理游标
@contextmanager
def get_cursor(conn:Connection):
cu = conn.cursor()
try:
yield cu
finally:
cu.close()
with sqlite3.connect(ADDRESS_SQL_LOCAL) as conn:
with get_cursor(conn) as cu:
try:
cu.execute('insert into users(username,sex,age,tele) VALUES (?,?,?,?)',
(kwargs.get('username'),
kwargs.get('sex', 'M'),
kwargs.get('age', 20),
kwargs.get('tele', '')))
conn.commit()
return 1
except Exception as e:
conn.rollback() # 存在异常需要回滚数据
raise Exception('insert the data:{}'.format(e))
