有如下的model,现在想根据create_time 字段更新description字段,要求是先将时间字段转换成年月,然后与id拼接然后与字符串拼接,则整体流程如下
from sqlalchemy import create_engine, Column, Integer, String,Date
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import func
Base = declarative_base()
class TestContact(Base):
__tablename__ = 'test_contact'
id = Column(Integer, primary_key=True)
create_time = Column(Date)
description = Column(String)
engine = create_engine('postgresql://ellis:ellis@192.168.214.134:5432/postgres')
Session = sessionmaker(bind=engine)
session = Session()
# 创建数据库表
def create_table():
Base.metadata.create_all(bind=engine)
# 插入数据
def insert_value():
session.add(TestContact(create_time='2022-10-01',description=''))
session.commit()
# 多列合并进行like查询
def query_by_concat_like():
query = func.concat(func.to_char(TestContact.create_time,'YYYY-MM'),TestContact.id,'haha')
for item in session.query(TestContact).filter(query.ilike('%haha%')):
print(item)
# 时间列format concat 进行update
def update_by_concat():
session.query(TestContact).filter_by(id=1).update({"description":func.concat(func.to_char(TestContact.create_time, 'YYYY-MM'), TestContact.id,'haha')})
session.commit()