SQLAlchemy 表定义 & 增删改查

类(表) 定义

#!/usr/local/python3/bin/python3
# -*- coding:utf-8 -*-
from sqlalchemy import Column, String, create_engine, Integer, Table, ForeignKey, and_,Float,BIGINT,DECIMAL,desc
from sqlalchemy.dialects.mysql import DOUBLE
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref

# --------------------------------- 数据库结构定义部分 ------------------------------------------------
# 创建对象的基类:
Base = declarative_base()

# 定义CoinInfo对象（表）:
class CoinInfo(Base):
    # 表的名字:
    __tablename__ = 'CoinInfo'

    # 表的结构:
    id = Column(Integer,primary_key=True,autoincrement=True)
    #base_price = Column(DOUBLE)
    newest_deal_price = Column(DECIMAL(20,2))
    max_coin_price = Column(DECIMAL(20,2))
    continuous_buy_count = Column(Integer)
    hold_coin_count = Column(String(50))
    hold_cash_count = Column(String(50))
    newest_deal_type = Column(String(50))
    newest_deal_time = Column(BIGINT)
    #hold_coin_avg_price = Column(DOUBLE)

    def __str__(self):
        return str(self.newest_deal_price)

连接 及 关闭数据库

def get_DBSession():
    # 数据库连接字符串
    mysql_connect_string = 'mysql+mysqlconnector://%s:%s@%s:%s/%s' % (mysql_user,mysql_passwd,mysql_addr,mysql_port,mysql_DB)

    # 初始化数据库连接:
    engine = create_engine(mysql_connect_string)

    # 创建DBSession类型:
    DBSession = sessionmaker(bind=engine)

    # 表不存在则创建表
    Base.metadata.create_all(engine)

    # 创建session对象:
    session = DBSession()

    # 返回session对象
    return session
    
def close_DBSession(session):
    # 提交即保存到数据库:
    session.commit()
    # 关闭session:
    session.close()

session = get_DBSession()
close_DBSession(session)

查询

查询CoinInfo表的首行内容 .first()，获取某结果集的首行也是一样

coin_info = session.query(CoinInfo).first()

按条件查询DealTable表，按多个and条件"and_()"，并返回结果集的首行 .first()

can_sell_order = session.query(DealTable).filter(and_(DealTable.info_type == huobi_buy_deal_type,DealTable.my_buy_can_sell_price <= newest_price,DealTable.my_buy_can_sell_amount >= 0.0001)).first()

对结果集进行排序 .order_by() 降序 desc() 升序 asc()

can_sell_order = session.query(DealTable).filter(DealTable.my_buy_can_sell_price <= newest_price).order_by(desc(DealTable.my_buy_can_sell_price))

关联查询

delete_alert_relationship = session.query(Alert_List_Table).join(HostName).join(AppType).join(AlertType).join(User).filter(and_(HostName.name == db_host.name, AppType.name == apptype_key, AlertType.name == alerttype_value,User.name == delete_db_user.name)).one()

 

插入

# 新建DealTable表一行记录，并对立面的info_id字段赋值
deal_table = DealTable(info_id=order_id)
# 对新建记录里的其他字段赋值
deal_table.my_deal_status = 1
# 插入到数据库
session.add(deal_table)
# commit数据库
session.commit()

更新

# 查询出要更新的行
coin_info = session.query(CoinInfo).first()
# 更新里面某个字段的值
coin_info.continuous_buy_count = 2
# commit数据库
session.commit()

删除

# 查询出要删除的行
coin_info = session.query(DealTable).filter(DealTable.info_id='1234567')
# 删除行
session.delete(coin_info)
# commit数据库
session.commit()