Python: SQLAlchemy 增、删、改、查

目录

一、完整代码

1.1 代码

1.2 运行结果

二、增删改查

2.1 增加一行记录

2.2 修改一行记录

2.3 查询一行记录

2.4 删除一行记录

一、完整代码

1.1 代码

import uuid
import datetime
import sqlalchemy
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class TDog(Base):
    __tablename__ = "dog"

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
    name = sqlalchemy.Column(sqlalchemy.String(256), nullable=True)
    uuid = sqlalchemy.Column(sqlalchemy.String(64), unique=True)
    addtime = sqlalchemy.Column(sqlalchemy.DateTime())
    data = sqlalchemy.Column(sqlalchemy.Text(), default='{}')

    def __init__(self, name, uuid, data='{}', addtime=None):
        self.name = name
        self.uuid = uuid
        self.data = data
        self.addtime = addtime if None != addtime else datetime.datetime.now()


url = 'postgresql://dbuser:dbpassword@127.0.0.1:5432/zoodb'
engine = sqlalchemy.create_engine(url)
metadata = sqlalchemy.schema.MetaData(bind=engine)
Base.metadata.create_all(engine) # 创建表结构

Session = orm.sessionmaker(bind=engine)


def admq():
    sess = Session()

    # 增加一只狗
    uuidstr = str(uuid.uuid4())
    adog = TDog("doge", uuidstr)
    sess.add(adog)
    sess.commit()

    # 根据 uuid 字段，查询这只狗
    qdog = sess.query(TDog).filter(TDog.uuid == uuidstr).first()
    print("查询结果：", qdog.name, qdog.uuid, qdog.addtime)

    # 根据 uuid 字段修改这只狗
    data = {"name": "newdoge", "addtime": datetime.datetime.now()}
    sess.query(TDog).filter(TDog.uuid == uuidstr).update(data)
    sess.commit()
    # 查询一下修改后的结果
    qdog = sess.query(TDog).filter(TDog.uuid == uuidstr).first()
    print("修改后的查询结果：", qdog.name, qdog.uuid, qdog.addtime)

    # 根据 uuid 字段删除这只狗
    sess.query(TDog).filter(TDog.uuid == uuidstr).delete()
    sess.commit()
    # 查询一下修改后的结果
    qdog = sess.query(TDog).filter(TDog.uuid == uuidstr).first()
    if None == qdog:
        print("删除后：dog with uuid [{0}] is not exists".format(uuidstr))
    else:
        print("删除后：", qdog.name, qdog.uuid, qdog.addtime)

if "__main__" ==__name__:
    admq()

1.2 运行结果

查询结果： doge 52a23dd0-ea46-4681-b163-c7cf260edfb8 2021-11-24 16:16:10.699270
修改后的查询结果： newdoge 52a23dd0-ea46-4681-b163-c7cf260edfb8 2021-11-24 16:16:10.713411
删除后：dog with uuid [52a23dd0-ea46-4681-b163-c7cf260edfb8] is not exists

二、增删改查

2.1 增加一行记录

# 增加一只狗
uuidstr = str(uuid.uuid4())
adog = TDog("doge", uuidstr)
sess.add(adog)
sess.commit()

2.2 修改一行记录

# 根据 uuid 字段修改这只狗
data = {"name": "newdoge", "addtime": datetime.datetime.now()}
sess.query(TDog).filter(TDog.uuid == uuidstr).update(data)
sess.commit()

2.3 查询一行记录

# 根据 uuid 字段，查询这只狗
qdog = sess.query(TDog).filter(TDog.uuid == uuidstr).first()
print("查询结果：", qdog.name, qdog.uuid, qdog.addtime)

或者：

# 根据 uuid 字段，查询这只狗
query = orm.Query(TDog).filter(TDog.uuid == uuidstr)
qdog = query.with_session(sess).first()

2.4 删除一行记录

# 根据 uuid 字段删除这只狗
sess.query(TDog).filter(TDog.uuid == uuidstr).delete()
sess.commit()