python orm benchmark_Python懒人ORM包dataset教程

Dataset

是一个封装了 sqlalchemy的极简ORM

dataset官方网站

连接数据库import dataset

# connecting to a SQLite database

db = dataset.connect('sqlite:///mydatabase.db')

# connecting to a MySQL database with user and password 使用MySQL-python包方式(Python2 Only)

db = dataset.connect('mysql://user:password@localhost/mydatabase')

#使用 pymysql Python3也可以用

db = dataset.connect('mysql+pymysql://user:password@localhost:port/db')

# connecting to a PostgreSQL database

db = dataset.connect('postgresql://username:password@localhost:5432/mydatabase')

#

# 更多的连接方式可以参考sqlalchemy的文档

# impala demo

db = dataset.connect('impala://127.0.0.1:21050/db')

table = db['demo']

for i in table.find('uidpk>100', _limit=10):

print(i)

'''

result = db.query('SELECT * from db.table')

for row in result:

print(row['uidpk'])

'''

从表获取数据>>> users = db['user'].all()

>>> users

>>> for user in db['user']:

print(user['age'])

OrderedDict([('id', 1), ('country', 'China'), ('name', 'John Doe'), ('age', 47), ('gender', None)])

OrderedDict([('id', 2), ('country', 'France'), ('name', 'Jane Doe'), ('age', 37), ('gender', 'female')])

>>> chinese_users = table.find(country='China')

>>> chinese_users

>>> john = table.find_one(name='John Doe')

>>> john

OrderedDict([('id', 1),

('country', 'China'),

('name', 'John Doe'),

('age', 47),

('gender', None)])

>>> elderly_users = table.find(table.table.columns.age >= 70)

获取非重复数据# Get one user per country

db['user'].distinct('country')

自定义SQLresult = db.query('SELECT country, COUNT(*) c FROM user GROUP BY country')

for row in result:

print(row['country'], row['c'])

插入数据# get a reference to the table 'user'

table = db['user']

# table = db.get_table('user')

# Insert a new record.

table.insert(dict(name='John Doe', age=46, country='China'))

# dataset will create "missing" columns any time you insert a dict with an unknown key

table.insert(dict(name='Jane Doe', age=37, country='France', gender='female'))

# insert many

rows = [dict(name='Dolly')] * 10000

table.insert_many(rows)

更新记录table.update(dict(name='John Doe', age=47), ['name'])

删除记录table.delete(place='Berlin')

导出数据# export all users into a single JSON

result = db['users'].all()

dataset.freeze(result, format='json', filename='users.json')

事务操作

事务操作可以简单的使用上下文管理器来实现,出现异常，将会回滚with dataset.connect() as tx:

tx['user'].insert(dict(name='John Doe', age=46, country='China'))