类(表) 定义
#!/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()