def add_girl_menu_price(cls, nGirlId, nMenuId, nPrice): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: nRet = cur.execute( ''' INSERT INTO t_girl_menu_price(girl_id, menu_id, price) VALUES (%s, %s, %s) ''', (nGirlId, nMenuId, nPrice)) conn.commit() return nRet except Exception as e: return 0 finally: ThinkPG.get_conn_pool_ex().putconn(conn) # def main(): # print(obj2json(GirlService.get_girls()).encode('utf-8').decode('unicode_escape')) # # if __name__ == '__main__': # main()
def get_deal_count(cls, szDateStart, szDateEnd, nManagerId=0, nGirlId=0): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: szSql = ''' SELECT count(1) as cnt FROM t_deal as a where 1 = 1 and a.create_time >= '{}' and a.create_time <= '{}' '''.format(szDateStart + " 00:00:00", szDateEnd + " 23:59:59") if nManagerId > 0: szSql += " and a.manager_id = {} ".format(nManagerId) if nGirlId > 0: szSql += " and exists (select 1 from t_deal_detail where order_id = a.id and girl_id = {}) ".format( nGirlId) cur.execute(szSql) rows = cur.fetchall() return rows[0]["cnt"] except Exception as e: return 0 finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def get_mans(cls): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: cur.execute("SELECT * from t_man ") rows = cur.fetchall() return rows except Exception as e: return [] finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def random_man(cls, nCount): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: cur.execute("SELECT * from t_man order by random() limit %s", (nCount, )) rows = cur.fetchall() return rows except Exception as e: return [] finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def get_deal(cls, szDateStart, szDateEnd, nManagerId=0, nGirlId=0, nOffset=0, nLimit=10): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: szSql = ''' SELECT b.name as manager_name , c.name as man_name , a.* FROM t_deal as a left join t_manager as b on a.manager_id = b.id left join t_man as c on a.man_id = c.id where 1 = 1 and a.create_time >= '{}' and a.create_time <= '{}' '''.format(szDateStart + " 00:00:00", szDateEnd + " 23:59:59") if nManagerId > 0: szSql += " and a.manager_id = {} ".format(nManagerId) if nGirlId > 0: szSql += " and exists (select 1 from t_deal_detail where order_id = a.id and girl_id = {}) ".format( nGirlId) szSql += " offset {} limit {} ".format(nOffset, nLimit) cur.execute(szSql) rows = cur.fetchall() lstRet = [] for row in rows: row["detail"] = cls.get_deal_detail(row["id"]) lstRet.append(dict(row)) return lstRet except Exception as e: return [] finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def get_menus(cls): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: cur.execute("SELECT * from t_menu ") rows = cur.fetchall() lstRet = [] for dictRow in rows: dictRow["girls"] = cls.get_menu_girls(dictRow["id"]) lstRet.append(dictRow) return lstRet except Exception as e: return [] finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def make_deal_multiple_detail(cls, lstDetails): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: szSql = 'INSERT INTO t_deal_detail(order_id, menu_id, girl_id, price, create_time) VALUES %s ' psycopg2.extras.execute_values(cur, szSql, lstDetails, page_size=len(lstDetails)) conn.commit() except Exception as e: pass finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def get_girls(cls, szBirthyearAfter='1900'): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: szSql = "SELECT * from t_girls WHERE birthday >= '{}-01-01'".format( szBirthyearAfter) cur.execute(szSql) rows = cur.fetchall() lstRet = [] for dictRow in rows: dictRow["menu"] = cls.get_girl_menus(dictRow["id"]) lstRet.append(dictRow) return lstRet except Exception as e: return [] finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def insert_deal(): nStartTime = get_timestamp() conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor() try: cur.execute( ''' insert into public.t_deal select * from csv.t_deal offset %s limit %s ''', (get_offset(), g_nLimit)) conn.commit() incr_offset() g_logger.info("[%d s] %d / %d" % ((get_timestamp() - nStartTime), get_offset(), g_nTotal)) return True except Exception as e: g_logger.info(e) return False finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def make_deal_detail(cls, nOrderId, nMenuId, nGirlId, nPrice, szDealTime=None): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: nRet = cur.execute( ''' INSERT INTO t_deal_detail(order_id, menu_id, girl_id, price, create_time) VALUES (%s, %s, %s, %s, %s) ''', (nOrderId, nMenuId, nGirlId, nPrice, szDealTime)) conn.commit() return nRet except Exception as e: return 0 finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def make_multiple_deal(cls, lstDeals): lstIDRet = [] for dictDeal in lstDeals: dictDeal["id"] = str(uuid.uuid4()) lstIDRet.append({"id": dictDeal["id"]}) conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: szSql = 'INSERT INTO t_deal(id, man_id, manager_id, create_time) VALUES %s ' psycopg2.extras.execute_values(cur, szSql, lstDeals, page_size=len(lstDeals)) conn.commit() return lstIDRet except Exception as e: return None finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def get_girl_menus(cls, nGirlId): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: cur.execute( """ SELECT a.menu_id , b."name" , a.price FROM t_girl_menu_price AS a LEFT JOIN t_menu as b on a.menu_id = b.id where 1 = 1 and a.girl_id = %s """, (nGirlId, )) rows = cur.fetchall() return rows except Exception as e: return [] finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def make_deal(cls, nManId, nManagerId, szDealTime=None): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: szID = str(uuid.uuid4()) nRet = cur.execute( ''' INSERT INTO t_deal(id, man_id, manager_id, create_time) VALUES (%s, %s, %s, %s) ''', (szID, nManId, nManagerId, szDealTime)) conn.commit() # rows = cur.fetchall() # if rows is None or len(rows) <= 0: # return 0 return szID except Exception as e: return 0 finally: ThinkPG.get_conn_pool_ex().putconn(conn)
def get_deal_detail(cls, nDealId): conn = ThinkPG.get_conn_pool_ex().getconn() cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) try: cur.execute( ''' SELECT b.name as menu_name , c.name as girl_name , a.* FROM t_deal_detail as a left join t_menu as b on a.menu_id = b.id left join t_girls as c on a.girl_id = c.id where a.deal_id = %s ''', (nDealId, )) rows = cur.fetchall() return rows except Exception as e: return [] finally: ThinkPG.get_conn_pool_ex().putconn(conn)