sqlalchemy 使用
分为 models(表结构) 和 DML语句
在models中主要存储表结构:
需要导入的模块如下
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Text, Integer, FLOAT, String
具体写法:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Text, Integer, FLOAT, String
Base = declarative_base()
class UserInfo(Base):
__tablename__ = "your table name"
id= Column(Integer,primary_key=True,nullable=False,autoincrement=True)
name = Column(String(255))
age = Column(String(255))
sex = Column(String(255))
DML部分:
#首先导入模块
from models import UserInfo
from sqlalchemy import _or
from sqlalchemy.orm import sessionmaker
from sqlalchemy.enqine import create_enqine
#然后创建连接数据库的session
#config是配置文件,存放了连接数据库所需要的配置
enqine = create_enqine(
'mysql+mysqlconnector://{}:{}@{}/{}'.format(config.MYSQL_SERVER_USER, config.MYSQL_SERVER_PWD,
config.MYSQL_SERVER, config.MYSQL_SERVER_DB),
pool_recycle=1800, pool_pre_ping=True
)
DBSession = sessionmaker(bind=engine)
session = DBSession()
#数据查找,or和and用法类似如下
msg_list = session.query(UserInfo).filter(_or(UserInfo.name.like('%刘%'), UserInfo.id==2)).all()
#插入数据
user = UserInfo()
user.name = "张三"
user.age = 18
user.sex = '男'
try:
session.add(user)
session.commit()
except Exception as ex:
session.rollback()
#数据修改
session.query(UserInfo).filter(UserInfo.id==1).update(
{
UserInfo.name = "李四",
UserInfo.age = 19,
UserInfo.sex = "女",
}
)
try:
session.commit()
except Exception as ex:
session.rollback()
#数据删除
the_user = session.query(UserInfo).filter(UserInfo.id==1).first()
session.delete(the_user)
session.flush()