相关资料/官网地址
1、安装sqlalchemy并查看版本
命令行下执行下面语句安装:
pip install SQLAlchemy
安装完成后查看版本:
import sqlalchemy
print(sqlalchemy.__version__)
此外还需要安装PyMySQL,否则在使用过程中会报ModuleNotFoundError: No module named 'MySQLdb'的错误
pip install PyMySQL
2、连接数据库
engine=create_engine("mysql+pymysql://用户名:密码@HOST/数据库名", encoding='UTF-8', echo=True)
或者
pymysql.install_as_MySQLdb()
engine=create_engine("mysql://用户名:密码@HOST/数据库名", encoding='UTF-8', echo=True)
或者(这种方式不需要安装PyMySQL)
engine = create_engine("mysql+mysqlconnector://root:root@localhost/tb_test", encoding='UTF-8', echo=False)
3、创建实体类
在SQLAlchemy中,定义实体类和映射实体类这两个任务都交由一个叫做Declarative的系统来完成。使用Declarative系统映射的类是根据基类定义的,因此需要定义一个BaseEntity的基类,如下:
from sqlalchemy.ext.declarative.api import declarative_base
BaseEntity = declarative_base()
定义User实体类
from sqlalchemy.sql.schema import Column
from sqlalchemy.sql.sqltypes import Integer, DateTime, String
from sqlalchemy.ext.declarative.api import declarative_base
Base=declarative_base()
class User(Base):
__tablename__='user'
id=Column(Integer, primary_key=True)
name=Column(String)
phone=Column(String)
birthday=Column(Date)
def __repr__(self):
return "<User(id='%s', name='%s', phone='%s', birthday='%s')>" % (
self.id, self.name, self.phone, self.birthday)
4、单表查询
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from helloworld.entity.User import User
engine = create_engine("mysql+mysqlconnector://root:root@localhost/表名", encoding='UTF-8', echo=False)
Session = sessionmaker(bind=engine)
session = Session();
result = session.query(User)
for row in result:
print(row.name)
查询指定字段
result = session.query(User.name, User.phone)
for row in result:
print(row.name)
字段别名
result = session.query(User.name.label("userName"))
for row in result:
print(row.userName)
过滤器
关于查询结果的过滤操作,SQLAlchemy提供了filter_by和filter两个函数。
filter_by提供了简单的列名查询,不支持比较运算符,使用规则如下:
result = session.query(User).filter_by(birthday=None)
for row in result:
print(row)
filter功能更强大一些,使用规则如下:
filters = {
User.col_birthday != None
}
result = session.query(User).filter(*filters)
for row in result:
print(row)
5、新增记录
新增单条记录
newUser = User()
newUser.name = '新的UserName'
newUser.phone = '新的phone'
newUser.birthday = '新的birthday'
try:
session.add(newUser)
session.commit()
print(newUser.id)
except Exception as e:
session.rollback()
print(e)
新增多条记录
session.add_all([user1, user2])
session.commit()
print(user1.id)
print(user2.id)
当commit成功之后,id会写入新纪录的主键。
6、更新记录
try:
ret = session.query(User).filter_by(id=40).update({"name": "jane"})
session.commit()
except Exception as e:
session.rollback()
print(e)
7、删除记录
session.query(User).filter_by(id=41).delete()
session.commit()
8、联表查询
from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Integer, DateTime, String
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
Base=declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
user_name = Column(String)
phone = Column(String)
birthday = Column(DateTime)
role = relationship("Role", back_populates="user") #back_populates这里写的应该是类Role中的user
def __repr__(self):
return "<User(id='%s', user_name='%s', phone='%s', birthday='%s')>" % (
self.id, self.user_name, self.phone, self.birthday)
class Role(Base):
__tablename__ = 'role'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('user.id')) #ForeignKey('表名.字段名')
role_name = Column(String)
user = relationship("User", back_populates="role") #back_populates这里写的应该是类User中的role
def __repr__(self):
return "<SelectTb(id=%s, user_id=%s, role_name=%s)>" % (
self.id, self.user_id, self.role_name)
调用
ret = session.query(User)
for row in ret1:
print(row.id)
print(row.role)
对于简单的映射关系,可以使用filter/join来做连接查询:
result = session.query(User, Role).filter(User.id==Role.user_id)
#或者result = session.query(User, Role).join(Role, User.id==Role.user_id)
for a, b in result:
print('User:', a.id)
print('Role:', b.user_id)
8、自动生成实体类
#命令行安装实体类生成工具
pip install sqlacodegen
#命令行生成实体类
sqlacodegen mysql+mysqlconnector://用户名:密码@Host:端口/数据库名 > models.py