sqlalchemy orm

from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session

engine = create_engine("mysql+pymysql://账号:密码@114.116.54.66:3306/db_bi")

Session = sessionmaker(bind=engine)
dbsession = scoped_session(Session)  获取引擎
Base = declarative_base()
md = MetaData(bind=engine)

数据库连接

from datasource.db.engine import Base, md
from sqlalchemy import Table

#表模型
class Bargain(Base):
    __table__ = Table("tb_bargain", md, autoload=True)

建立 表模型

dbsession.query(Bargain).order_by(desc(Bargain.follow_date)).limit(1).all()

查询时间最大的那一条

follow_obj = follow_dbsession.query(Follow, Employee).join(Employee, Follow.EmpID == Employee.FYEmpID).filter(
            Follow.FlagTrashed == False,
            Follow.FlagDeleted == False,
            Follow.FollowID != "",
            Follow.DeleteAt.is_(None),
            Follow.FollowType.in_(
                ['业主', '其他', '去电', '售后拜访', '议价',
                 '扫雷', '收意向', '收意向金', '来电', '空看',
                 '签单', '签合同']),
            Follow.FollowDate > where2_obj.operation_date)

连表查询 

 for i in follow_obj:
            owner_pay_obj = OwnerPay(follow_id=i[0].FollowID, property_id=i[0].PropertyID, employee_id=i[1].EmployeeID,
                                     structure_id=i[0].DeptID,
                                     operation_date=i[0].FollowDate,
                                     data_source="follow_" + str(datetime.datetime.now().year),
                                     update_at=time.strftime("%Y-%m-%d %H:%M:%S"))
            owner_pay_list.append(owner_pay_obj)  #

获取；连表查询 数据  连表时返回数据 第一个是数据是第一张表的，索引取1 是第二章表的数据

 

执行原生sql

cursor = follow_dbsession.execute(sql) 

获取执行结果
result = cursor.fetchall()