使用 Records 访问多种数据库

https://pypi.org/project/records/#description
https://github.com/kennethreitz/records

records库可以快速的查询数据库，比操作游标cursor对象要好使，还支持导出为具体格式。

数据库支持包括 RedShift、PostgreSql、MySQL、SQLite、Oracle 和 MS-SQL(不包含驱动程序)。
安装 records：pip install records。

Records 是基于 SQLAlchemy 实现的，所以数据库链接方式请参考 SQLAlchemy。

1）SQLite 数据库连接串：

• sqlite:///:memory: (or, sqlite://)
• sqlite:///relative/path/to/file.db
• sqlite:absolute/path/to/file.db

例：db = records.Database(‘sqlite:///users.db’)

2）Oracle 数据库连接示例：
先安装 cx_Oracle

• oracle://root:1234@ORCL

3）MySQL 数据库连接串示例：

• mysql://root:12345@localhost/mydb?charset=utf8

4）PostgreSQL 数据库连接串示例：

• postgresql://postgres:1234@localhost/mydb

5）SQL Server 数据库连接示例：
首先安装 pymssql

• mssql+pymssql://sa:12345@localhost:1433/mydb

注意：
有些数据库连接方式因第三方模块不同，连接方式可能有多种，上述只是展示其中某一种连接方式，更详细的连接方式请参考 SQLAlchemy。

1、以 MySQL 使用为例

安装：pip install records mysqlclient

1.1 基本用法

示例1：

import records

db = records.Database('mysql://root:12345@localhost/mydb?charset=utf8')
rows = db.query('select * from a_user')

print(rows)         # <RecordCollection size=9 pending=False>

# 转换成普通的字典列表集
dt = rows.as_dict()
print(dt)   # [{'id': 1, 'cid': 100, 'uname': 'tom1'}, {'id': 2, 'cid': 100, 'uname': 'cat1'}]


# 获取一行
print(rows[0])      # <Record {"id": 1, "cid": 100, "uname": "tom1"}>

# 遍历
for r in rows:
    # 多种获取方式
    print(r.id, r['uname'], r[2])
    # 1 tom1 tom1
    # 2 cat1 cat1
    # ......

# 返回所有行列表
all_rows = rows.all()
print(all_rows)     # [<Record {"id": 1, "cid": 100, "uname": "tom1"}>, <Record {"id": 2, "cid": 100, "uname": "cat1"}>]

# 以字典形式返回所有行
all_rows = rows.all(as_dict=True)
print(all_rows)     # [{'id': 1, 'cid': 100, 'uname': 'tom1'}, {'id': 2, 'cid': 100, 'uname': 'cat1'}]

# 以 OrderedDict 形式返回所有行列表
all_rows = rows.all(as_ordereddict=True)
print(all_rows)     # [OrderedDict([('id', 1), ('cid', 100), ('uname', 'tom1')]), OrderedDict([('id', 2), ('cid', 100), ('uname', 'cat1')])]

# 获取第一行
one_row = rows.first()
print(one_row)      # <Record {"id": 1, "cid": 100, "uname": "tom1"}>

# 以字典形式返回第一行
one_row = rows.first(as_dict=True)
print(one_row)      # {'id': 1, 'cid': 100, 'uname': 'tom1'}

# 以 OrderedDict 形式返回第一行
one_row = rows.first(as_ordereddict=True)
print(one_row)      # OrderedDict([('id', 1), ('cid', 100), ('uname', 'tom1')])

Records 支持安全参数化：

# Records 支持安全参数化，使用 :variable 定义变量，使用字典传入参数值
params = {'id':1}
rows = db.query('select * from a_user where id = :id',**params)

1.2 数据导出功能

Records 还具有完整的 Tablib 集成功能，允许将结果导出到 CSV、XLS、JSON、HTML Tables, YAML、Pandas DataFrames。非常适合与朋友共享数据或生成报告。

1. 以 dataset 方式导出

dataset = rows.dataset
print(dataset)

结果：
id|cid|uname 
--|---|------
1 |100|tom1  
2 |100|cat1  

2. 以 csv 方式导出：结果以逗号分隔

csv = rows.export('csv')
print(csv)

结果：
id,cid,uname
1,100,tom1
2,100,cat1

3. 以 yaml 方式导出

yaml = rows.export('yaml')
print(yaml)

结果：
- cid: 100
  id: 1
  uname: tom1
- cid: 100
  id: 2
  uname: cat1

4. 以 JSON 方式导出：js对象表示法

jn = rows.export('json')
print(jn)

结果：
[{"id": 1, "cid": 100, "uname": "tom1"}, {"id": 2, "cid": 100, "uname": "cat1"}]

5. 以 xls/xlsx 方式导出

xls = rows.export('xls')
print(xls)  # b'\xd0\xcf\x11\xe0\xa1\xb1.......'

with open('E://report.xls', 'wb') as f:
    f.write(rows.export('xls'))

查看 report.xls 文件，内容如下：

5. 以Pandas DataFrame 方式导出
首先安装好 pandas 模块。

rows = db.query('select * from a_user')
df = rows.export('df')
print(df)

结果：
     id   cid   uname
0     1   100    tom1
1     2   100    cat1
2     3   100  kitty1

重要：
Tablib的所有其他特性也可用，因此您可以对结果排序、添加/删除列/行、删除重复项、转置表、添加分隔符、按列切片数据等等。
有关更多细节，请参阅 Tablib 文档。

1.3 批量操作

Database.bulk_query() 和 Database.bulk_query_file()

db = records.Database('mysql://root:123456@localhost/mydb?charset=utf8')
params = [
    {'id': 900, 'cid': 9000, 'uname': 'AAA'},
    {'id': 901, 'cid': 9001, 'uname': 'BBB'}
]
sql = '''
insert into a_user(id,cid,uname) values (:id,:cid,:uname)
'''
db.bulk_query(sql, params)

1.4 事务支持

t = Database.transaction(); t.commit()