def add_a_good(rows_, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') old_rows = [] dd = rows_diff_by_seq(seq(old_rows), seq(rows_), old_rows, rows_, incl=['NAME'], ind=['NAME'], rplc=False, fillback=False) rows = db_utils.db_operate_dict(_conn, goods, dd, pk=['ID']) # get good id stmt = sql.select([sqlite_sequence.c.seq]).\ select_from(sqlite_sequence).\ where(sqlite_sequence.c.name == 'GOODS') row_data = None for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) if conn_ is None: _conn.close() return row_data['seq'] if conn_ is None: _conn.close() return goods_dict
def read_rate(CID=None, GID=None, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') if CID == None and GID == None: stmt = sql.select([rate]).\ select_from(rate).\ order_by(rate.c.CID, rate.c.GID) elif CID != None and GID == None: stmt = sql.select([rate]).\ select_from(rate).\ where(rate.c.CID == CID).\ order_by(rate.c.CID, rate.c.GID) elif CID == None and GID != None: stmt = sql.select([rate]).\ select_from(rate).\ where(rate.c.GID == GID).\ order_by(rate.c.CID, rate.c.GID) rets = [] for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) rets.append(row_data) if conn_ is None: _conn.close() return rets
def reset_disable(tbl_, version_=None, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') stmt = tbl_.update().\ values(disable=1).\ where(tbl_.c.version == version_) _conn.execute(stmt) if conn_ is None: _conn.close()
def add_rates(rows_, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') old_rows = read_rate(conn_=_conn) dd = rows_diff_by_seq(seq(old_rows), seq(rows_), old_rows, rows_, incl=['CID','GID', 'RATE', 'REAL'], ind=['CID','GID'], rplc=False, fillback=False) rows = db_utils.db_operate_dict(_conn, rate, dd, pk=['CID','GID']) if conn_ is None: _conn.close() return rows
def get_goods_dict(reverse=None, conn_=None):
_conn = db_utils.getconnection(conn_, db='rs')
goods_list = read_goods(conn_=_conn)
goods_dict = {}
for g in goods_list:
if not reverse:
goods_dict[g['NAME']] = g['ID']
else:
goods_dict[g['ID']] = g['NAME']
return goods_dict
def get_customers_dict(reverse=None, conn_=None):
_conn = db_utils.getconnection(conn_, db='rs')
cus_list = read_customer(conn_=_conn)
cus_dict = {}
for c in cus_list:
if not reverse:
cus_dict[c['NAME']] = c['ID']
else:
cus_dict[c['ID']] = c['NAME']
return cus_dict
def read_goods(conn_=None): _conn = db_utils.getconnection(conn_, db='rs') stmt = sql.select([goods]).\ select_from(goods) rets = [] for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) rets.append(row_data) if conn_ is None: _conn.close() return rets
def max_good_id(conn_=None): _conn = db_utils.getconnection(conn_, db='rs') stmt = sql.select([goods.c.ID]).\ select_from(goods) rets = [] for row in _conn.execute(stmt): rets.append(row[0]) if conn_ is None: _conn.close() return rets
def add_customers(rows_, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') old_rows = read_customer() dd = rows_diff_by_seq(seq(old_rows), seq(rows_), old_rows, rows_, incl=['NAME'], ind=['NAME'], rplc=False, fillback=False) rows = db_utils.db_operate_dict(_conn, customer, dd, pk=['ID']) # get goods dict cus_dict = get_customers_dict(conn_=_conn) if conn_ is None: _conn.close() return cus_dict
def rated_goods(CID_, conn_=None):
_conn = db_utils.getconnection(conn_, db='rs')
stmt = sql.select([rate]).\
select_from(rate).\
where(and_(rate.c.CID == CID_,rate.c.REAL == 1))
rets = {}
for row in _conn.execute(stmt):
rets[row['GID']] = row['RATE']
if conn_ is None:
_conn.close()
return rets
def read_customer(cus_id=None, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') if not cus_id: stmt = sql.select([customer]).\ select_from(customer) else: stmt = sql.select([customer]).\ select_from(customer).\ where(customer.c.ID == cus_id) rets = [] for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) rets.append(row_data) if conn_ is None: _conn.close() return rets
def update_all_pred_rate(rows_, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') stmt = sql.select([rate]).\ select_from(rate).\ where(rate.c.REAL == 0) old_rows = [] for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) old_rows.append(row_data) dd = rows_diff_by_seq(seq(old_rows), seq(rows_), old_rows, rows_, incl=['CID','GID','RATE', 'REAL'], ind=['CID','GID'], rplc=False, fillback=False) rows = db_utils.db_operate_dict(_conn, rate, dd, pk=['CID','GID']) if conn_ is None: _conn.close() return rows
def check_add_raw_data(conn_=None): _conn = db_utils.getconnection(conn_, db='rs') stmt = sql.select([sqlite_sequence.c.seq]).\ select_from(sqlite_sequence).\ where(sqlite_sequence.c.name == 'CUSTOMER') try: for row in _conn.execute(stmt): res = row['seq'] if conn_ is None: _conn.close() if int(res) > 0: return False else: return True except: if conn_ is None: _conn.close() return True
def add_a_customer(rows_, cus_id=None, conn_=None): _conn = db_utils.getconnection(conn_, db='rs') if cus_id: old_rows = read_customer(cus_id=cus_id,conn_=_conn) else: old_rows = [] dd = rows_diff_by_seq(seq(old_rows), seq(rows_), old_rows, rows_, incl=['ID','NAME'], ind=['ID'], rplc=False, fillback=False) rows = db_utils.db_operate_dict(_conn, customer, dd, pk=['ID']) # get cus id stmt = sql.select([sqlite_sequence.c.seq]).\ select_from(sqlite_sequence).\ where(sqlite_sequence.c.name == 'CUSTOMER') row_data = None for row in _conn.execute(stmt): row_data = db_utils.row2dict(row, has_none=True) if conn_ is None: _conn.close() return row_data['seq'] if cus_id == None else cus_id