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()
