使用records来操作mysql

参考：知乎

基础

  1. conda install -y records pymysql

操作

查询

  1. import records
  3. # 获取数据库
  4. db = records.Database('mysql+pymysql://root:tianyunali@soft.tianyunperfect.cn:3306/test')
  5. # 查询
  6. rows = db.query('select * from user')
  7. # 所有对象
  8. print(rows.all())
  9. # 转换为数组和字典
  10. print(rows.all(as_dict=True))
  12. print(rows.as_dict()[0])
  14. print(len(rows))

image.png

DDL

  1. # 创建表
  2. sql_create_table = """CREATE TABLE IF NOT EXISTS lemon_user (
  3.     name varchar(20),
  4.     age int
  5. ) DEFAULT CHARSET=utf8 ;"""
  7. db.query(sql_create_table)

插入单条数据

  1. user = {"name": "yuze5", "age": 20}
  2. db.query('INSERT INTO lemon_user(name,age) values (:name, :age)', **user)

插入多条数据

  1. users = [
  2.     {"name":"yuze", "age": 13},
  3.     {"name":"yuze2", "age": 15},
  4.     {"name":"yuze3", "age": 16}
  5. ]
  6. db.bulk_query('INSERT INTO lemon_user(name,age) values (:name, :age)', users)

事务支持

  1. with db.transaction() as tx:
  2.     user = {"name": "yuze9", "age": 20}
  3.     tx.query('INSERT INTO lemon_user(name,age) values (:name, :age)', **user)
  4.     # 下面是错误的 sql 语句，有错误，则上面的 sql 语句不会成功执行。
  5.     tx.query('sof')

数据导出为json

支持： json, yaml, xls, xlsx, pandas, html 

  1. rows = db.query('SELECT * FROM lemon_user;')
  2. json_rows = rows.export('json')
  3. print(json_rows)

数据导出为excel

  1. rows = db.query('SELECT * FROM lemon_user;')
  2. with open('users.xlsx', 'wb') as f:
  3.     f.write(rows.export('xlsx'))