sqlAlchemy的sql语句查询

来源：互联网发布：linux sigpipe 编辑：程序博客网时间：2024/05/29 07:53

第一种方式：通过某个条件查询、删除

查询：1、条件查询sequence_query = session.query(ColumnResource.sequence).filter(ColumnResource.columnId == column_id)2、传递dict条件查询：params = {                    "columnId": column_id,                    "resourceId": resource_id,                    "type": type1                }column_resource_result = ColumnResource().all(**params)3、写sql语句查询：

删除：result = session.query(ColumnResource).filter(and_(                            ColumnResource.columnId == column_id,                            ColumnResource.resourceId == resource_id,                            ColumnResource.type == type1                        )).delete(synchronize_session=False)

更新：session.query(Program).filter(Program.contentId == resource_id).update({"shelveStatus": "0"}, synchronize_session=False)

1、条件查询

//这里得到的结果是一个list类型program_ids = session.query(Program).filter(Program.contentId==values["contentId"]).all()//通过获取第一个数据对象，然后直接.属性的方式就可以获取program_id = program_ids[0].id

# -*- coding: utf-8 -*-from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,Stringfrom sqlalchemy.orm import sessionmaker# 创建对象的基类:Base = declarative_base()global engine#建立数据库表def create_all_tables(DB_type,DB_host,DB_port,DB_name,username,password,charset="utf8"):    global engine    if DB_type.upper() == "MYSQL":        DB_URI = "mysql+mysqldb://%s:%s@%s:%s/%s?charset=%s" % (username,password,DB_host,DB_port,DB_name,charset)        engine = create_engine(DB_URI,echo=True)        # 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息        Base.metadata.create_all(engine)#返回数据库会话def loadSession():    Session = sessionmaker(bind=engine)    session = Session()    return sessionclass User(Base):    __tablename__="User"    # 表的结构:    id = Column(Integer,primary_key=True)    userName = Column(String(50),nullable=False,default="Noob")    password = Column(String(50),nullable=False,default="123456")    gender = Column(String(1),nullable=True,default=None)    def __init__(self,id,userName,password,gender = None):        self.id = id        self.userName = userName        self.password = password        self.gender = genderif __name__=="__main__":    #建表    create_all_tables("mysql","localhost",3306,"test","root","123456")    #获取数据库会话    session = loadSession()    #增加    u1 = User(id=1,userName="Rose",password="aaaa",gender="F")    u2 = User(id=2, userName="Joe", password="bbbb",gender="M")    u3 = User(id=3, userName="jack", password="bbbb", gender="M")    u4 = User(id=4, userName="Billy", password="cccc")    session.add(u1)    session.add(u2)    session.add(u3)    session.add(u4)    session.commit()    # 封装好的新增的方法        def my_insert(self, data):        result = {'errorcode': -1}        try :            if data.has_key("createTime") is False and hasattr(self, "createTime"):                setattr(self, "createTime", str(datetime.datetime.utcnow())[:19])                setattr(self, "updateTime", str(datetime.datetime.utcnow())[:19])            for key, value in data.items():                if hasattr(self, key):                    setattr(self, key, value)            self.session.add(self)            self.session.flush()            result = self.to_dict(self.__dict__)            # self.session.commit()            result["errorcode"] = 0        except Exception :            return result        finally:            return result    #删除    pro_movie_res = session.query(ProgramMovie).filter(ProgramMovie.programId == program_id).delete(synchronize_session=False)    #批量删除    poster_res = session.query(Poster).filter(Poster.id.in_(post_ids)).delete(synchronize_session=False)     #更新（如果参数是一个dict，会对应更新相关数据）    session.query(User).filter(User.userName == "jack").update({User.password:"xxxx"})    session.commit()    #查询        #查第一行    session.query(User.id,User.userName,User.password).first()        #查所有行    session.query(User.id, User.userName, User.password).all()        #根据id倒序并取前两行    session.query(User).order_by(User.id.desc()).limit(2)

0 0