class UserRepository(IUserRepository): def __init__(self): self.db_conn = DbConnection() def fetch_one_by_user_pwd(self, user, pwd): cursor = self.db_conn.connect() sql = """select nid ,username,email,last_login,vip,user_type from UserInfo WHERE username=%s AND pwd=%s""" cursor.execute(sql, (user, pwd)) db_result = cursor.fetchone() self.db_conn.close() if db_result: obj = UserModel(nid=db_result['nid'], username=db_result['username'], email=db_result["email"], last_login=["last_login"], vip_obj=VipModel(db_result["vip"]), user_type_obj=UserTypeModel( db_result['"user_type'])) return obj def fetch_one_by_email_pwd(self, email, pwd): cursor = self.db_conn.connect() sql = """select nid ,username,email,last_login,vip,user_type from UserInfo WHERE email=%s AND pwd=%s""" cursor.execute(sql, (email, pwd)) db_result = cursor.fetchone() self.db_conn.close() if db_result: obj = UserModel(nid=db_result['nid'], username=db_result['username'], email=db_result["email"], last_login=["last_login"], vip_obj=VipModel(db_result["vip"]), user_type_obj=UserTypeModel( db_result['"user_type'])) return obj def get_user_to_select(self): cursor = self.db_conn.connect() sql = """select nid as value,username as text from userinfo""" cursor.execute(sql) db_result = cursor.fetchall() self.db_conn.close() return db_result
class ProvinceRepository(IProvinceRepository): def __init__(self): self.db_conn = DbConnection() def fetch_province_count(self): cursor = self.db_conn.connect() sql = """select count(1) as count from Province""" cursor.execute(sql) db_result = cursor.fetchone() self.db_conn.close() return db_result["count"] def fetch_province(self): cursor = self.db_conn.connect() sql = """select nid,caption from province""" effect_rows = cursor.execute(sql) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_province_by_page(self, start, rows): cursor = self.db_conn.connect() sql = """select nid,caption from province order by nid desc limit %s offset %s""" cursor.execute(sql, (rows, start)) db_result = cursor.fetchall() self.db_conn.close() return db_result def add_province(self, caption): cursor = self.db_conn.connect() sql = """insert into Province (caption) VALUES (%s)""" effect_rows = cursor.execute(sql, (caption, )) self.db_conn.close() return effect_rows def delete_province(self, nid): cursor = self.db_conn.connect() sql = """delete from Province WHERE nid=%s""" effect_rows = cursor.execute(sql, (nid, )) self.db_conn.close() return effect_rows def update_province(self, nid, caption): cursor = self.db_conn.connect() sql = """update Province set caption=%s where nid=%s""" effect_rows = cursor.execute(sql, (caption, nid)) self.db_conn.close() return effect_rows def is_exist(self, caption): cursor = self.db_conn.connect() sql = """select count(1) as count from Province WHERE caption=%s""" cursor.execute(sql, (caption, )) db_result = cursor.fetchone() ret = db_result['count'] self.db_conn.close() return ret
class UserRepository(IUserRepository): def __init__(self): self.db_conn = DbConnection() def fetch_user(self): cursor = self.db_conn.connect() sql = """select nid as value,username as text from userinfo""" cursor.execute(sql) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_one_user_by_name_pwd(self, name, pwd): cursor = self.db_conn.connect() sql = """select nid from userinfo WHERE username=%s and password=%s""" cursor.execute(sql, (name, pwd)) db_result = cursor.fetchone() print(db_result) self.db_conn.close() return db_result
class UserRepository(IUserRepository): def __init__(self): self.db_conn = DbConnection() def fetch_user(self): cursor = self.db_conn.connect() sql = """select nid as value,username as text from userinfo""" cursor.execute(sql) db_result = cursor.fetchall() self.db_conn.close() return db_result def login_user_by_pwd(self,username,passwd): cursor = self.db_conn.connect() sql = """select nid,username,email,last_login,vip,user_type from UserInfo where username=%s and password=%s""" cursor.execute(sql, (username, passwd)) db_result = cursor.fetchone() self.db_conn.close() return db_result
class CategoryRepository(ICategoryRepository): def __init__(self): self.db_conn = DbConnection() def fetch_all_category(self): cursor = self.db_conn.connect() sql = """select subsite.nid as i1, subsite.caption as c1, upper_category.nid as i2, upper_category.caption as c2, category.nid as i3, category.name as c3 from category left JOIN upper_category on category.nid left JOIN subsite ON upper_category.favor_id=subsite.nid""" cursor.execute(sql) db_result = cursor.fetchall() self.db_conn.close() return db_result
class CityRepository(ICityRepository): def __init__(self): self.db_conn = DbConnection() def fetch_city(self): cursor = self.db_conn.connect() sql = """SELECT city.nid, city.caption, city.province_id, province.caption as province FROM city LEFT JOIN province on city.province_id=province.nid""" effect_rows = cursor.execute(sql) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_city_by_province(self, province_id): cursor = self.db_conn.connect() sql = """SELECT nid,caption from city WHERE province_id=%s""" effect_rows = cursor.execute(sql, (province_id, )) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_city_count(self): cursor = self.db_conn.connect() sql = """select count(1) as count from city""" effect_rows = cursor.execute(sql) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def fetch_city_by_page(self, start, rows): cursor = self.db_conn.connect() sql = """select city.nid, city.caption, city.province_id, province.caption as province FROM city LEFT JOIN province on city.province_id=province.nid ORDER BY city.nid desc LIMIT %s OFFSET %s """ effect_rows = cursor.execute(sql, (rows, start)) db_result = cursor.fetchall() self.db_conn.close() return db_result def add_city(self, caption, province_id): cursor = self.db_conn.connect() print(caption, province_id) sql = """insert into city (caption,province_id) VALUES (%s,%s)""" effect_rows = cursor.execute(sql, (caption, province_id)) self.db_conn.close() return effect_rows def update_city(self, nid, caption, province_id): cursor = self.db_conn.connect() sql = """update city set caption=%s,province_id=%s WHERE nid=%s""" effect_rows = cursor.execute(sql, (caption, province_id, nid)) self.db_conn.close() return effect_rows def delete_city(self, nid): cursor = self.db_conn.connect() sql = "delete from city WHERE nid=%s" effect_rows = cursor.execute(sql, (nid, )) self.db_conn.close() return effect_rows def is_exist(self, caption, province_id): cursor = self.db_conn.connect() sql = """select count(1) as count from city WHERE caption=%s AND province_id=%s""" cursor.execute(sql, (caption, province_id)) db_result = cursor.fetchone() self.db_conn.close() return db_result["count"]
class CountryRepository(ICountryRepository): def __init__(self): self.db_conn = DbConnection() def fetch_country_count(self): cursor = self.db_conn.connect() sql = """select count(1) as count from country""" cursor.execute(sql) db_result = cursor.fetchone() self.db_conn.close() return db_result["count"] def fetch_country_by_page(self, start, rows): cursor = self.db_conn.connect() sql = """SELECT country.nid, country.caption, country.city_id, city.caption as city, city.province_id as province_id, province.caption as province FROM country LEFT JOIN city ON country.city_id=city.nid LEFT JOIN province ON city.province_id=province.nid ORDER BY country.nid DESC limit %s offset %s""" cursor.execute(sql, (rows, start)) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_country_by_city(self, city_id): cursor = self.db_conn.connect() sql = """select nid,caption from country WHERE city_id=%s""" cursor.execute(sql, (city_id, )) db_result = cursor.fetchall() self.db_conn.close() return db_result def add_country(self, caption, city_id): cursor = self.db_conn.connect() sql = """insert into country (caption,city_id) VALUES (%s,%s)""" effect_rows = cursor.execute(sql, (caption, city_id)) self.db_conn.close() return effect_rows def update_country(self, nid, caption, city_id): cursor = self.db_conn.connect() sql = """update country set caption=%s,city_id=%s WHERE nid=%s""" effect_rows = cursor.execute(sql, (caption, city_id, nid)) self.db_conn.close() return effect_rows def delete_country(self, nid): cursor = self.db_conn.connect() sql = """delete from country WHERE nid=%s""" effect_rows = cursor.execute(sql, (nid, )) self.db_conn.close() return effect_rows def is_exist(self, caption, city_id): cursor = self.db_conn.connect() sql = """select count(1) as count from country WHERE caption=%s and city_id=%s""" cursor.execute(sql, (caption, city_id)) db_result = cursor.fetchone() self.db_conn.close() return db_result["count"]
class UserRepository(IUserRepository): def __init__(self): self.db_conn = DbConnection() def insert_user(self, firstname, lastname, phone, email): cursor = self.db_conn.connect() sql = """insert into userinfo(firstname,lastname,phone,email) values(%s,%s,%s,%s)""" ret = cursor.execute(sql, (firstname, lastname, phone, email)) print ret self.db_conn.close() def get_user_by_id(self, user_id): cursor = self.db_conn.connect() sql = """select * from userinfo where id = %s """ print type(user_id) cursor.execute(sql, (user_id, )) db_result = cursor.fetchone() self.db_conn.close() return db_result def get_user_list(self): cursor = self.db_conn.connect() sql = """select * from userinfo""" cursor.execute(sql) db_result = cursor.fetchall() self.db_conn.close() return db_result def delete_user_by_id(self, user_id): cursor = self.db_conn.connect() sql = """delete from userinfo where id = %s """ db_result = cursor.execute(sql, (user_id, )) self.db_conn.close() return db_result def update_user_by_id(self, id, firstname, lastname, phone, email): cursor = self.db_conn.connect() sql = """update userinfo set firstname=%s, lastname=%s,phone=%s,email=%s where id = %s """ db_result = cursor.execute(sql, (firstname, lastname, phone, email, id)) self.db_conn.close() return db_result def get_user_list_by_page(self, page, page_size): start = (int(page) - 1) * int(page_size) cursor = self.db_conn.connect() sql = """select * from userinfo limit %s,%s""" print sql print start, page_size cursor.execute(sql, (int(start), int(page_size))) db_result = cursor.fetchall() self.db_conn.close() return db_result def get_user_count(self): cursor = self.db_conn.connect() sql = """select count(*) as count from userinfo""" cursor.execute(sql) db_result = cursor.fetchone() self.db_conn.close() return db_result
class RegionRepository(IRegionRepository): def __init__(self): self.db_conn = DbConnection() def fetch_province(self): cursor = self.db_conn.connect() sql = """select nid,caption from province order by nid desc """ cursor.execute(sql) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_province_by_page(self, start, offset): ret = None cursor = self.db_conn.connect() sql = """select nid,caption from province order by nid desc limit %s offset %s """ cursor.execute(sql, (offset, start)) db_result = cursor.fetchall() self.db_conn.close() return db_result def exist_province(self, caption): cursor = self.db_conn.connect() sql = """select count(1) as count from province where caption=%s """ cursor.execute(sql, (caption, )) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def add_province(self, caption): cursor = self.db_conn.connect() sql = """insert into province (caption) values(%s)""" effect_rows = cursor.execute(sql, (caption, )) self.db_conn.close() return effect_rows def update_province(self, nid, caption): cursor = self.db_conn.connect() sql = """update province set caption=%s where nid=%s """ effect_rows = cursor.execute(sql, ( caption, nid, )) self.db_conn.close() return effect_rows def remove_province(self, nid): cursor = self.db_conn.connect() sql = """delete from province where nid=%s """ effect_rows = cursor.execute(sql, (nid, )) self.db_conn.close() return effect_rows def fetch_province_count(self): cursor = self.db_conn.connect() sql = """select count(1) as count from province """ cursor.execute(sql) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def fetch_city_by_province(self, province_id): cursor = self.db_conn.connect() sql = """select nid, caption from city where province_id=%s order by city.nid desc """ cursor.execute(sql, (province_id, )) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_city_by_page(self, start, offset): cursor = self.db_conn.connect() sql = """select city.nid as nid, city.caption as caption,province.caption as province, city.province_id as province_id from city left join province on city.province_id = province.nid order by city.nid desc limit %s offset %s """ cursor.execute(sql, (offset, start)) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_city_count(self): cursor = self.db_conn.connect() sql = """select count(1) as count from city """ cursor.execute(sql) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def exist_city(self, province_id, caption): cursor = self.db_conn.connect() sql = """select count(1) as count from city where caption=%s and province_id=%s""" cursor.execute(sql, ( caption, province_id, )) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def add_city(self, province_id, caption): cursor = self.db_conn.connect() sql = """insert into city (caption,province_id) values(%s,%s)""" effect_rows = cursor.execute(sql, (caption, province_id)) self.db_conn.close() return effect_rows def remove_city(self, nid): cursor = self.db_conn.connect() sql = """delete from city where nid=%s """ effect_rows = cursor.execute(sql, (nid, )) self.db_conn.close() return effect_rows def update_city(self, nid, province_id, caption): cursor = self.db_conn.connect() sql = """update city set caption=%s,province_id=%s where nid=%s """ effect_rows = cursor.execute(sql, ( caption, province_id, nid, )) self.db_conn.close() return effect_rows def fetch_county_by_page(self, start, offset): cursor = self.db_conn.connect() sql = """select county.nid as nid, county.caption as caption, city.caption as city, county.city_id as city_id, province.caption as province, city.province_id as province_id from county left join city on county.city_id = city.nid left join province on city.province_id = province.nid order by county.nid desc limit %s offset %s """ cursor.execute(sql, (offset, start)) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_county_count(self): cursor = self.db_conn.connect() sql = """select count(1) as count from county """ cursor.execute(sql) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def exist_county(self, city_id, caption): cursor = self.db_conn.connect() sql = """select count(1) as count from county where caption=%s and city_id=%s""" cursor.execute(sql, ( caption, city_id, )) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def add_county(self, city_id, caption): cursor = self.db_conn.connect() sql = """insert into county (caption,city_id) values(%s,%s)""" effect_rows = cursor.execute(sql, (caption, city_id)) self.db_conn.close() return effect_rows def update_county(self, nid, city_id, caption): cursor = self.db_conn.connect() sql = """update county set caption=%s,city_id=%s where nid=%s """ effect_rows = cursor.execute(sql, ( caption, city_id, nid, )) self.db_conn.close() return effect_rows def remove_county(self, nid): cursor = self.db_conn.connect() sql = """delete from county where nid=%s """ effect_rows = cursor.execute(sql, (nid, )) self.db_conn.close() return effect_rows def fetch_county_by_city(self, city_id): cursor = self.db_conn.connect() sql = """select nid, caption from county where city_id=%s order by county.nid desc """ cursor.execute(sql, (city_id, )) db_result = cursor.fetchall() self.db_conn.close() return db_result
class ProductRepository(IProductRepository): def __init__(self): self.db_conn = DbConnection() def fetch_page_by_merchant_id(self, merchant_id, start, rows): cursor = self.db_conn.connect() sql = """select nid,title,img,category_id from product where merchant_id=%s order by nid desc limit %s offset %s""" cursor.execute(sql, ( merchant_id, rows, start, )) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_count_by_merchant_id(self, merchant_id): cursor = self.db_conn.connect() sql = """select count(1) as count from product where merchant_id=%s""" cursor.execute(sql, (merchant_id, )) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def exist_product_by_pid(self, product_id): cursor = self.db_conn.connect() sql = """select count(1) as count from product where nid=%s""" cursor.execute(sql, (product_id, )) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def fetch_product_by_pid(self, merchant_id, product_id): cursor = self.db_conn.connect() sql = """select nid,title from product where nid=%s""" cursor.execute(sql, ( merchant_id, product_id, )) db_result = cursor.fetchone() self.db_conn.close() return db_result def fetch_product_by_id(self, merchant_id, product_id): cursor = self.db_conn.connect() sql = """select nid,title from product where merchant_id=%s and nid=%s""" cursor.execute(sql, ( merchant_id, product_id, )) db_result = cursor.fetchone() self.db_conn.close() return db_result def add_product(self, product_dict, detail_list, img_list): """ 创建商品 :param product_dict: 商品字典 {'title': 'x'} :param detail_list: [{'key': xx, 'value': 'xxx'}] :param img_list: [{'src': 'fa'},{'src': 'fa'}] :return: """ # print(product_dict, detail_list, img_list) product_sql = "insert into product(%s) values(%s)" p_k_list = [] p_v_list = [] for k in product_dict.keys(): p_k_list.append(k) p_v_list.append('%%(%s)s' % k) product_sql = product_sql % ( ','.join(p_k_list), ','.join(p_v_list), ) cursor = self.db_conn.connect() cursor.execute(product_sql, product_dict) product_id = cursor.lastrowid if detail_list: d = map(lambda x: x.update(product_id=product_id), detail_list) list(d) detail_sql = "insert into product_detail(%s) values(%s)" d_k_list = [] d_v_list = [] for k in detail_list[0].keys(): d_k_list.append(k) d_v_list.append('%%(%s)s' % k) detail_sql = detail_sql % ( ','.join(d_k_list), ','.join(d_v_list), ) cursor.executemany(detail_sql, detail_list) if img_list: i = map(lambda x: x.update(product_id=product_id), img_list) list(i) img_sql = "insert into product_img(%s) values(%s)" i_k_list = [] i_v_list = [] for k in img_list[0].keys(): i_k_list.append(k) i_v_list.append('%%(%s)s' % k) img_sql = img_sql % ( ','.join(i_k_list), ','.join(i_v_list), ) print(img_sql, img_list) cursor.executemany(img_sql, img_list) self.db_conn.close() def fetch_price_by_product_id(self, merchant_id, product_id): cursor = self.db_conn.connect() sql = """select price.nid as nid, standard, price, selling_price, product_id from price left join product on price.product_id = product.nid where product.merchant_id=%s and product_id=%s order by nid desc""" cursor.execute(sql, ( merchant_id, product_id, )) db_result = cursor.fetchall() self.db_conn.close() return db_result def add_price(self, price_dict): price_sql = "insert into price(%s) values(%s)" p_k_list = [] p_v_list = [] for k in price_dict.keys(): p_k_list.append(k) p_v_list.append('%%(%s)s' % k) price_sql = price_sql % ( ','.join(p_k_list), ','.join(p_v_list), ) cursor = self.db_conn.connect() cursor.execute(price_sql, price_dict) self.db_conn.close() def update_price(self, nid, price_dict): sql = """update price set %s where nid=%s""" value_list = [] for k, v in price_dict.items(): value_list.append('%s=%%(%s)s' % (k, k)) sql = sql % (','.join(value_list), nid) cursor = self.db_conn.connect() cursor.execute(sql, price_dict) self.db_conn.close() def fetch_product_pv(self, product_id): sql = """select timespan,count(nid) as pv from product_view where product_id=%s group by timespan""" cursor = self.db_conn.connect(cursor=None) cursor.execute(sql, product_id) result = cursor.fetchall() self.db_conn.close() return result def fetch_product_uv(self, product_id): sql = """select timespan, count(1) as uv from (select ip,timespan,count(1) from product_view where product_id=%s group by timespan,ip) as B group by timespan""" cursor = self.db_conn.connect(cursor=None) cursor.execute(sql, product_id) result = cursor.fetchall() self.db_conn.close() return result def add_product_puv(self, product_id, ip, current_date, current_timestamp): sql = 'insert into product_view(product_id,ip,ctime,timespan) values(%s,%s,%s,%s)' cursor = self.db_conn.connect(cursor=None) cursor.execute(sql, ( product_id, ip, current_date, current_timestamp, )) self.db_conn.close() def fetch_super_new_product(self): """ 获取首页新品上市的数据 :return: """ cursor = self.db_conn.connect() sql = """ SELECT price.nid as nid, product.title as title, product.img as img, price.selling_price as selling_price, product.nid as product_id FROM super_product LEFT JOIN price ON super_product.price_id = price.nid LEFT JOIN product ON product.nid = price.product_id where super_product.super_type =1 """ cursor.execute(sql) result = cursor.fetchall() self.db_conn.close() return result def fetch_super_excellent_product(self): """ 获取首页精品推荐数据 :return: """ cursor = self.db_conn.connect() sql = """ SELECT price.nid as nid, product.title as title, product.img as img, price.selling_price as selling_price, product.nid as product_id FROM super_product LEFT JOIN price ON super_product.price_id = price.nid LEFT JOIN product ON product.nid = price.product_id where super_product.super_type =2 """ cursor.execute(sql) result = cursor.fetchall() self.db_conn.close() return result def fetch_limit_price_and_product(self, subsite_caption): cursor = self.db_conn.connect() sql = """ SELECT price.nid as nid, product.title as title, product.img as img, price.selling_price as selling_price, product.nid as product_id FROM price LEFT JOIN product ON product.nid = price.product_id LEFT JOIN category ON product.category_id = category.nid LEFT JOIN upper_category ON category.favor_id = upper_category.nid LEFT JOIN subsite ON upper_category.favor_id = subsite.nid where subsite.caption = %s group by product.nid order by price.nid DESC limit 6 offset 0 """ cursor.execute(sql, (subsite_caption, )) result = cursor.fetchall() self.db_conn.close() return result def fetch_product_and_merchant(self, product_id): sql = """ select product.nid, product.title, product.img, merchant.name, merchant.business_phone, merchant.business_mobile, merchant.qq from product left join merchant on product.merchant_id = merchant.nid where product.nid = %s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id, )) result = cursor.fetchone() self.db_conn.close() return result def fetch_price_list(self, product_id): sql = """ select price.nid as price_id, product.nid as product_id, price.standard, price.price, price.selling_price from price left join product on product.nid = price.product_id where product.nid = %s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id, )) result = cursor.fetchall() self.db_conn.close() return result def fetch_price_detail(self, price_id): sql = """ select price.nid, price.standard, price.price, price.selling_price from price where price.nid = %s """ cursor = self.db_conn.connect() cursor.execute(sql, (price_id, )) result = cursor.fetchone() self.db_conn.close() return result def fetch_image_list(self, product_id): sql = """ select src as img from product_img where product_img.product_id = %s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id, )) result = cursor.fetchall() self.db_conn.close() return result def fetch_detail_list(self, product_id): sql = """ select name, value from product_detail where product_detail.product_id = %s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id, )) result = cursor.fetchall() self.db_conn.close() return result def fetch_comment_list(self, product_id): sql = """ select content, username, comment.ctime from comment left join userinfo on comment.user_id = userinfo.nid where product_id = %s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id, )) result = cursor.fetchall() self.db_conn.close() return result def fetch_comment_count(self, product_id, fine): """ :param product_id: 商品ID :param fine: 1表示满意,2表示不满意 :return: """ sql = """ select count(1) as count from comment where product_id = %s and fine = %s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id, fine)) result = cursor.fetchone() self.db_conn.close() return result['count']
class UserRepository(IUserReository): def __init__(self): self.db_conn = DbConnection() def fetch_one_by_email_pwd(self, email, password): ret = None cursor = self.db_conn.connect() sql = """select nid,username,email,last_login,vip,user_type from userinfo where email=%s and password=%s""" cursor.execute(sql, (email, password)) db_result = cursor.fetchone() self.db_conn.close() # print(type(db_result), db_result) if db_result: ret = User(nid=db_result['nid'], username=db_result['username'], email=db_result['email'], last_login=db_result['last_login'], user_type=UserType(nid=db_result['user_type']), vip_type=VipType(nid=db_result['vip']) ) return ret def fetch_one_by_user_pwd(self, username, password): ret = None cursor = self.db_conn.connect() sql = """select nid,username,email,last_login,vip,user_type from userinfo where username=%s and password=%s""" cursor.execute(sql, (username, password)) db_result = cursor.fetchone() self.db_conn.close() if db_result: ret = User(nid=db_result['nid'], username=db_result['username'], email=db_result['email'], last_login=db_result['last_login'], user_type=UserType(nid=db_result['user_type']), vip_type=VipType(nid=db_result['vip']) ) return ret def update_last_login_by_nid(self, nid, current_date): cursor = self.db_conn.connect() sql = """update userinfo set last_login=%s where nid=%s""" cursor.execute(sql, (current_date, nid)) self.db_conn.close() def fetch_all(self, user_type_nid): ret = None cursor = self.db_conn.connect() sql = """select nid,username,email,last_login,vip,user_type from userinfo where user_type=%s""" cursor.execute(sql, (user_type_nid)) db_result = cursor.fetchall() # 返回列表嵌套字典 self.db_conn.close() # print('db_result', db_result) if db_result: result_all = [] for item in db_result: result_all.append(User(nid=item['nid'], username=item['username'], email=item['email'], last_login=item['last_login'], user_type=UserType( nid=item['user_type']), vip_type=VipType(nid=item['vip']) )) return result_all def fetch_user(self): cursor = self.db_conn.connect() sql = """select nid as value,username as text from userinfo""" cursor.execute(sql) db_result = cursor.fetchall() self.db_conn.close() return db_result
class ProductRepository(IProductRepository): """docstring for ProductRepository""" def __init__(self): self.db_conn = DbConnection() def fetch_page_by_merchant_id(self, merchant_id, start, rows): cursor = self.db_conn.connect() sql = """ SELECT nid, title, img, category_id FROM product WHERE merchant_id =% s ORDER BY nid DESC LIMIT % s OFFSET % s """ cursor.execute(sql, (merchant_id, rows, start,)) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_count_by_merchant_id(self, merchant_id): cursor = self.db_conn.connect() sql = """ SELECT count(1) AS count FROM product WHERE merchant_id =% s """ cursor.execute(sql, (merchant_id,)) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def exist_product_by_pid(self, product_id): cursor = self.db_conn.connect() sql = """ SELECT count(1) AS count FROM product WHERE nid =% s """ cursor.execute(sql, (product_id,)) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def fetch_product_by_pid(self, product_id): cursor = self.db_conn.connect() sql = """ SELECT nid, title FROM product WHERE nid =% s """ cursor.execute(sql, (product_id,)) db_result = cursor.fetchone() self.db_conn.close() return db_result def fetch_product_by_id(self, merchant_id, product_id): cursor = self.db_conn.connect() sql = """ SELECT nid, title FROM product WHERE merchant_id =% s AND nid =% s """ cursor.execute(sql, (merchant_id, product_id,)) db_result = cursor.fetchone() self.db_conn.close() return db_result def add_product(self, product_dict, detail_list, img_list): """ 创建商品 :param product_dict: 商品字典 {'title': 'x'} :param detail_list: [{'key': xx, 'value': 'xxx'}] :param img_list: [{'src': 'fa'},{'src': 'fa'}] :return: """ product_sql = """ INSERT INTO product (% s) VALUES (% s) """ p_k_list = [] p_v_list = [] for k in product_dict.keys(): p_k_list.append(k) p_v_list.append('%%(%s)s' % k) product_sql = product_sql % (','.join(p_k_list), ','.join(p_v_list),) cursor = self.db_conn.connect() cursor.execute(product_sql, (product_dict,)) product_id = cursor.lastrowid() if detail_list: d = map(lambda x: x.update(product_id=product_id), detail_list) list(d) detail_sql = """ INSERT INTO product_detail (% s) VALUES (% s) """ d_k_list = [] d_v_list = [] for k in detail_list[0].keys(): d_k_list.append(k) d_v_list.append('%%(%s)s' % k) detail_sql = detail_sql % (','.join(d_k_list), ','.join(d_v_list),) cursor.executemany(detail_sql, (detail_list,)) if img_list: i = map(lambda x: x.update(product_id=product_id), img_list) list(i) img_sql = """ INSERT INTO product_img (% s) VALUES (% s) """ i_k_list = [] i_v_list = [] for k in img_list[0].keys(): i_k_list.append(k) i_v_list.append('%%(%s)s' % k) img_sql = img_sql % (','.join(i_k_list), ','.join(i_v_list),) cursor.executemany(img_sql, (img_list,)) self.db_conn.close() def fetch_price_by_product_id(self,merchant_id, product_id): cursor = self.db_conn.connect() sql = """ SELECT price.nid AS nid, standard, price, selling_price, product_id FROM price LEFT JOIN product ON price.product_id = product.nid WHERE product.merchant_id =% s AND product_id =% s ORDER BY nid DESC """ cursor.execute(sql, (merchant_id, product_id,)) db_result = cursor.fetchall() self.db_conn.close() return db_result def add_price(self, price_dict): price_sql = """ INSERT INTO price (% s) VALUES (% s) """ p_k_list = [] p_v_list = [] for k in price_dict.keys(): p_k_list.append(k) p_v_list.append('%%(%s)s' % k) price_sql = price_sql % (','.join(p_k_list), ','.join(p_v_list), ) cursor = self.db_conn.connect() cursor.execute(price_sql, price_dict) self.db_conn.close() def update_price(self, nid, price_dict): sql = """ UPDATE price SET % s WHERE nid =% s """ value_list = [] for k, v in price_dict.items(): value_list.append('%s=%%(%s)s' % (k, k)) sql = sql % (','.join(value_list), nid ) cursor = self.db_conn.connect() cursor.execute(sql, price_dict) self.db_conn.close() def fetch_product_pv(self, product_id): sql = """ SELECT timespan, count(nid) AS pv FROM product_view WHERE product_id =% s GROUP BY timespan """ cursor = self.db_conn.connect(cursor=None) cursor.execute(sql, product_id) result = cursor.fetchall() self.db_conn.close() return result def fetch_product_uv(self, product_id): sql = """ SELECT timespan, count(1) AS uv FROM ( SELECT ip, timespan, count(1) FROM product_view WHERE product_id =% s GROUP BY timespan, ip ) AS B GROUP BY timespan """ cursor = self.db_conn.connect(cursor=None) cursor.execute(sql, product_id) result = cursor.fetchall() self.db_conn.close() return result def add_product_puv(self, product_id, ip, current_date, current_timestamp): sql = """ INSERT INTO product_view ( product_id, ip, ctime, timespan ) VALUES (% s ,% s ,% s ,% s) """ cursor = self.db_conn.connect(cursor=None) cursor.execute(sql, (product_id, ip, current_date, current_timestamp,)) self.db_conn.close() def fetch_super_new_product(self): """ 获取首页新品上市的数据 :return: """ cursor = self.db_conn.connect() sql = """ SELECT price.nid as nid, product.title as title, product.img as img, price.selling_price as selling_price, product.nid as product_id FROM super_product LEFT JOIN price ON super_product.price_id = price.nid LEFT JOIN product ON product.nid = price.product_id where super_product.super_type =1 """ cursor.execute(sql) result = cursor.fetchall() self.db_conn.close() return result def fetch_super_excellent_product(self): """ 获取首页精品推荐数据 :return: """ cursor = self.db_conn.connect() sql = """ SELECT price.nid as nid, product.title as title, product.img as img, price.selling_price as selling_price, product.nid as product_id FROM super_product LEFT JOIN price ON super_product.price_id = price.nid LEFT JOIN product ON product.nid = price.product_id where super_product.super_type =2 """ cursor.execute(sql) result = cursor.fetchall() self.db_conn.close() return result def fetch_limit_price_and_product(self, subsite_caption): cursor = self.db_conn.connect() sql = """ SELECT price.nid AS nid, product.title AS title, product.img AS img, price.selling_price AS selling_price, product.nid AS product_id FROM price LEFT JOIN product ON product.nid = price.product_id LEFT JOIN category ON product.category_id = category.nid LEFT JOIN upper_category ON category.favor_id = upper_category.nid LEFT JOIN subsite ON upper_category.favor_id = subsite.nid WHERE subsite.caption = % s ORDER BY price.nid DESC LIMIT 6 OFFSET 0 """ cursor.execute(sql, (subsite_caption,)) result = cursor.fetchall() self.db_conn.close() return result def fetch_product_and_merchant(self, product_id): sql = """ SELECT product.nid, product.title, product.img, merchant.name, merchant.business_phone, merchant.business_mobile, merchant.qq FROM product LEFT JOIN merchant ON product.merchant_id = merchant.nid WHERE product.nid = % s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id,)) result = cursor.fetchone() self.db_conn.close() return result def fetch_price_list(self, product_id): sql = """ SELECT price.nid AS price_id, product.nid AS product_id, price.standard, price.price, price.selling_price FROM price LEFT JOIN product ON product.nid = price.product_id WHERE product.nid = % s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id,)) result = cursor.fetchall() self.db_conn.close() return result def fetch_price_detail(self, price_id): sql = """ SELECT price.nid, price.standard, price.price, price.selling_price FROM price WHERE price.nid = % s """ cursor = self.db_conn.connect() cursor.execute(sql, (price_id,)) result = cursor.fetchone() self.db_conn.close() return result def fetch_image_list(self, product_id): sql = """ SELECT src AS img FROM product_img WHERE product_img.product_id = % s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id,)) result = cursor.fetchall() self.db_conn.close() return result def fetch_detail_list(self, product_id): sql = """ SELECT name, value FROM product_detail WHERE product_detail.product_id = % s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id,)) result = cursor.fetchall() self.db_conn.close() return result def fetch_comment_list(self, product_id): sql = """ SELECT content, username, comment.ctime FROM comment LEFT JOIN userinfo ON comment.user_id = userinfo.nid WHERE product_id = % s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id,)) result = cursor.fetchall() self.db_conn.close() return result def fetch_comment_count(self, product_id, fine): """ :param product_id: 商品ID :param fine: 1表示满意,2表示不满意 :return: """ sql = """ SELECT count(1) AS count FROM comment WHERE product_id = % s AND fine = % s """ cursor = self.db_conn.connect() cursor.execute(sql, (product_id, fine)) result = cursor.fetchone() self.db_conn.close() return result['count']
class ProductRepository(IProductRepository): def __init__(self): self.db_conn = DbConnection() def fetch_count_by_merchant(self, merchant_id): cursor = self.db_conn.connect() sql = """select count(1) as count from product WHERE merchant_id=%s""" effect_rows = cursor.execute(sql, (merchant_id, )) db_result = cursor.fetchone() self.db_conn.close() return db_result["count"] def get_page_by_merchant_id(self, merchant_id, start, rows): cursor = self.db_conn.connect() sql = """select product.nid as nid,title,img,category_id from product where merchant_id=%s order BY nid DESC limit %s offset %s""" effect_row = cursor.execute(sql, (merchant_id, rows, start)) db_result = cursor.fetchall() self.db_conn.close() return db_result def get_product_by_id(self, merchant_id, product_id): cursor = self.db_conn.connect() sql = """select nid,title,img,category_id from product WHERE nid=%s AND merchant_id=%s""" cursor.execute(sql, (product_id, merchant_id)) db_result = cursor.fetchone() self.db_conn.close() return db_result def create_product(self, product_dict, detail_list, img_list): """ 创建商品 :param product_dict:商品字典 :param detail_list: [{'key': xx, 'value': 'xxx'}] :param img_list: [{'src': 'fa'},{'src': 'fa'}] :return: """ product_sql = """insert into product(%s) value(%s)""" p_k_list = [] p_v_list = [] for k in product_dict.keys(): p_k_list.append(k) p_v_list.append("%%(%s)s" % k) product_sql = product_sql % (",".join(p_k_list), ",".join(p_v_list)) cursor = self.db_conn.connect() cursor.execute(product_sql, product_dict) product_id = cursor.lastrowid if detail_list: # 如果商品详细不为空,则添加商品详细 #detail_list追加productID # d=map(lambda x:x.update( {"product_id": product_id}),detail_list) d = [] for i in detail_list: i.update({"product_id": product_id}) d.append(i) #构造sql语句 detail_sql = """insert into product_detail(%s) VALUES (%s)""" d_k_list = [] d_v_list = [] for k in d[0].keys(): d_k_list.append(k) d_v_list.append("%%(%s)s" % k) detail_sql = detail_sql % (",".join(d_k_list), ",".join(d_v_list)) effect_row = cursor.executemany(detail_sql, d) print(effect_row) if img_list: # 如果商品图片不为空,则添加商品图片 # img=map(lambda x:x.update({"product_id": product_id}),img_list) # 往img_list添加产品id img = [] for i in img_list: i.update({"product_id": product_id}) img.append(i) img_sql = """insert into product_img(%s) VALUES (%s)""" i_k_list = [] i_v_list = [] for k in img_list[0].keys(): i_k_list.append(k) i_v_list.append("%%(%s)s" % k) img_sql = img_sql % (",".join(i_k_list), ",".join(i_v_list)) cursor.executemany(img_sql, img) self.db_conn.close() return True def delete_product(self, product_id): cursor = self.db_conn.connect() sql = """delete from product WHERE nid=%s""" effect_rows = cursor.execute(sql, (product_id)) self.db_conn.close() return effect_rows def update_product(self, product_id): pass def get_price_by_product_id(self, merchant_id, product_id): cursor = self.db_conn.connect() sql = """select price.nid as nid, standard, price, selling_price, product_id from price left JOIN product on price.product_id=product.nid WHERE product.merchant_id=%s and product_id=%s order BY nid desc""" cursor.execute(sql, (merchant_id, product_id)) db_result = cursor.fetchall() self.db_conn.close() return db_result def get_product_detail(self, product_id): cursor = self.db_conn.connect() sql = """select name,value from product_detail WHERE product_detail.product_id=%s""" cursor.execute(sql, (product_id, )) db_result = cursor.fetchall() self.db_conn.close() return db_result def create_price(self, price_dict): price_sql = """insert into price(%s)VALUE (%s)""" p_k_list = [] p_v_list = [] for k in price_dict.keys(): p_k_list.append(k) p_v_list.append("%%(%s)s" % k) price_sql = price_sql % (",".join(p_k_list), ",".join(p_v_list)) cursor = self.db_conn.connect() cursor.execute(price_sql, price_dict) self.db_conn.close() def update_price(self, nid, price_dict): sql = """update price set %s WHERE nid=%s""" value_list = [] for k, v in price_dict.items(): value_list.append("%s=%%(%s)s" % (k, k)) sql = sql % (','.join(value_list), nid) cursor = self.db_conn.connect() effect_row = cursor.execute(sql, price_dict) self.db_conn.close() return effect_row def delete_price(self, price_id): cursor = self.db_conn.connect() sql = """delete from price WHERE nid=%s""" effect_row = cursor.execute(sql, (price_id, )) self.db_conn.close() return effect_row def fetch_price_count(self, product_id): cursor = self.db_conn.connect() sql = """select count(1) as count from price WHERE product_id=%s""" cursor.execute(sql, (product_id, )) db_result = cursor.fetchone() return db_result["count"] def get_upv(self, merchant_id, product_id): pass def create_puv(self, product_id, ip): pass def fetch_index_product(self): pass def fetch_product_detail(self, product_id, price_id): pass
class MerchantRepository(IMerchantRepository): def __init__(self): self.db_conn = DbConnection() def fetch_merchant_count(self): cursor = self.db_conn.connect() sql = """select count(1) as count from merchant""" cursor.execute(sql) db_result = cursor.fetchone() self.db_conn.close() return db_result["count"] def fetch_merchant_by_page(self, start, rows): cursor = self.db_conn.connect() sql = """select nid,name,domain from merchant ORDER BY nid DESC limit %s offset %s""" cursor.execute(sql, (rows, start)) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_merchant_detail_by_nid(self, nid): cursor = self.db_conn.connect() sql = """select merchant.nid as nid, domain, business_mobile, qq, backend_mobile, country_id, country.caption as country_caption, user_id, userinfo.username as user_name, name, business_phone, backend_phone, address from merchant LEFT JOIN userinfo ON merchant.user_id=userinfo.nid LEFT JOIN country ON merchant.country_id=country.nid WHERE merchant.nid=%s""" cursor.execute(sql, nid) db_result = cursor.fetchone() self.db_conn.close() return db_result def add_merchant(self, **kwargs): cursor = self.db_conn.connect() sql = """insert into merchant(%s) values(%s)""" key_list = [] value_list = [] for k, v in kwargs.items(): key_list.append(k) value_list.append("%%(%s)s" % k) sql = sql % (','.join(key_list), ",".join(value_list)) effect_rows = cursor.execute(sql, kwargs) self.db_conn.close() return effect_rows def update_merchant(self, nid, **kwargs): cursor = self.db_conn.connect() sql = """update merchant set %s WHERE nid=%s""" value_list = [] print(kwargs) for k, v in kwargs.items(): value_list.append("%s=%%(%s)s" % (k, k)) sql = sql % (",".join(value_list), nid) effect_rows = cursor.execute(sql, kwargs) self.db_conn.close() return effect_rows def delete_merchant(self, nid): cursor = self.db_conn.connect() sql = """delete from merchant where nid=%s""" effect_row = cursor.execute(sql, (nid, )) self.db_conn.close() print(effect_row) return effect_row
class MerchantRepository(IMerchantRepository): """docstring for MerchantRepository""" def __init__(self): self.db_conn = DbConnection() def fetch_merchant_count(self): cursor = self.db_conn.connect() sql = """ SELECT count(1) AS count FROM merchant """ cursor.execute(sql) db_result = cursor.fetchone() self.db_conn.close() return db_result['count'] def fetch_merchant_by_page(self, start, rows): cursor = self.db_conn.connect() sql = """ SELECT nid, name, domain FROM merchant ORDER BY nid DESC LIMIT % s OFFSET % s """ cursor.execute(sql, (rows, start)) db_result = cursor.fetchall() self.db_conn.close() return db_result def fetch_merchant_detail_by_nid(self, nid): cursor = self.db_conn.connect() sql = """ SELECT merchant.nid AS nid, name, domain, business_phone, business_mobile, qq, backend_mobile, backend_phone, address, user_id, county_id, county.caption AS county_caption FROM merchant LEFT JOIN userinfo ON merchant.user_id = userinfo.nid LEFT JOIN county ON merchant.county_id = county.nid WHERE merchant.nid =% s """ cursor.execute(sql, (nid, )) db_result = cursor.fetchone() self.db_conn.close() return db_result def add_merchant(self, **kwargs): cursor = self.db_conn.connect() sql = """ INSERT INTO merchant (% s) VALUES (% s) """ key_list, value_list = [], [] for k, v in kwargs.items(): key_list.append(k) value_list.append('%%(%s)s' % k) sql = sql % (','.join(key_list), ','.join(value_list)) cursor.execute(sql, kwargs) self.db_conn.close() def update_merchant(self, nid, **kwargs): cursor = self.db_conn.connect() sql = """ UPDATE merchant SET % s WHERE nid =% s """ value_list = [] for k, v in kwargs.items(): value_list.append('%s=%%(%s)s' % (k, k)) sql = sql % (','.join(value_list), nid) cursor.execute(sql, kwargs) self.db_conn.close() def detete_merchant(self, nid): cursor = self.db_conn.connect() sql = """ DELETE FROM merchant WHERE nid =% s """ db_result = cursor.execute(sql, (nid, )) self.db_conn.close()