def get_books_by_user():
result = dict()
query = "Select idusers from users"
user_ids = [user_id for user_id in cmsql.query(query, lambda x: x[0])]
select_book_query = "Select idbooks from order_view where user_id=%s"
for uid in user_ids:
result[uid] = list()
books = cmsql.query(select_book_query, lambda x: x[0], [uid])
for book in books:
result[uid].append(book)
return result
def get_all_book(self, page=0, size=39):
"""
根据当前的页码和每页的条数，获取接下来的数据条数
:param page: 当前页码，从0开始，0表示第一页
:param size: 每页的数据条数，默认是40，即每页显示40条
:return: 图书列表，是否还有下一页图书信息
"""
counts = cmsql.query("Select count(1) from books", lambda x: int(x[0]))
for c in counts:
count = c
query = "Select idbooks, Author, bookname, image_url, unitprice, publisher, publishedOn, discount from books limit %s offset %s;"
# 计算开始的条数
start = page * size
books = cmsql.query(query, self.build_book_item, [size, start])
return books, start + size < count
def login(user_name, password):
current_user_id = None
query = "Select idusers from users where user_name=%s and password=%s"
user_count = cmsql.query(query, lambda cnt: int(cnt[0]), [user_name, password])
for c in user_count:
current_user_id = c
return current_user_id
def get_orders_by_user(user_id):
"""
根据用户编号查询对应的订单信息
:param user_id: 用户编号
:return: 订单列表
"""
sql = "Select idorders from orders where user_id=%s"
orders = cmsql.query(sql, lambda x: x[0], [user_id])
detail_query = "Select bookname,unitprice,image_url from order_view where order_id=%s"
result = dict()
for order in orders:
result[order] = list()
details = cmsql.query(detail_query, lambda x: (x[0], x[1], x[2]), [order])
for detail in details:
result[order].append(detail)
return result
def get_recommend_books(self, books):
result = []
query = "Select idbooks, Author, bookname, image_url, unitprice, publisher, publishedOn, discount from books where idbooks=%s"
for book_id in books:
books = cmsql.query(query, self.build_book_item, [book_id])
for book in books:
result.append(book)
return result
def get_carts_by_user(user_id):
"""
获取指定用户的购物车信息
:param user_id:用户编号
:return:
"""
query = "select idbooks,bookname, image_url, unitprice, idcarts From books inner join shoppingcarts on shoppingcarts.book_id=books.idbooks where shoppingcarts.user_id=%s"
books = cmsql.query(query, lambda cur: (cur[0], cur[1], cur[2], cur[3], cur[4]), [user_id])
return books
# -*- coding:utf-8 -*-
import comm_sql as cmm
class Book(object):
def __init__(self, bookname, author):
self.bookname = bookname
self.author = author
def __str__(self):
return "%s:%s" % (self.bookname, self.author)
sql = "Select bookname, author from books where author=%s or author=%s;"
books = cmm.query(sql, lambda item: Book(item[0], item[1]), ['余华', '高铭'])
for book in books:
print(book)