sqlAlchemy的sql语句查询

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

查询：
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_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from 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 session


class 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 = gender


if __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)