Python ORM - pymysql&sqlalchemy

Python3主要是面向对象的编码风格，访问数据库也可以使用ORM框架来实现面向对象，本文介绍pymysql和sqlalchemy

安装组件

pip3 install pymysql
pip3 install sqlalchemy

定义数据对象

• user表结构

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `age` int DEFAULT NULL,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`),
  KEY `idx_age` (`age`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• User对象

# coding=utf-8
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    deleted = Column(Integer)

    def __str__(self):
        return "id: {}, name: {}, age: {}, deleted: {}" \
            .format(self.id, self.name, self.age, self.deleted)

操作

创建数据

# 创建记录
tenmao = User(name = 'tenmao', age = 100)
session.add(tenmao)
session.commit()

更新数据

查询数据

• 查询一条数据

# coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from model.user import User

# 数据库配置
db_user = 'maibao'
db_password = 'maibao123'
db_host_port = '9.134.77.51:3306'

# 初始化数据库连接:
engine = create_engine(
    'mysql+pymysql://{db_user}:{db_password}@{db_host_port}/tenmao'.format(db_user=db_user,
                                                                                       db_password=db_password,
                                                                                       db_host_port=db_host_port))
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

# 创建Session:
session = DBSession()

# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
user = session.query(User).filter(User.id==1).one()

# 打印类型和对象的name属性:
print(user)

# 关闭Session:
session.close()

• 查询所有数据

# 查询所有User
users = session.query(User).all()

print("there are {} users".format(len(users)))

• 条件过滤

# 条件查询
users = session.query(User)\
    .filter(User.name == 'timxia')\
    .filter(User.age < 10)\
    .filter(User.name.like('%tim%'))\
    .order_by(User.name.desc(), User.id.asc())\
    .all()

print("there are {} users for given condition".format(len(users)))

常见问题

• sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '<class 'model.user.User'>'
  实体表需要继承declarative_base()

Base = declarative_base()
class User(Base):

• 创建或修改没有生效

因为没有提交，需要调用commit方法session.commit()

参考

• Python操作MySQL -即pymysql/SQLAlchemy用法