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 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 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 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 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()
