def by_user_and_name(cls, user, name):
""" Get a permission object based on name and user """
return DBSession().query(cls).filter(
and_(cls.name == unicode(name[:cls.NAME_LENGTH]),
cls.user_id == user.id)).first()
def get_next_code(self):
session = DBSession()
q = session.query(func.max(self.model.code))
return q.first()[0]
def dbsession(self):
if self._dbsession is None:
self._dbsession = DBSession()
return self._dbsession
def addreadcoupons(username):
auth_info = get_auth_info()
# get method to get the page for different kinds of users
if request.method == 'GET':
try:
page = int(request.args.get('page'))
except Exception:
page = 1
if page <= 0:
page = 1
#check if username exists
db = DBSession()
user = db.query(User).filter(User.username == username).first()
if not user:
response = jsonify(errMsg="username does not exist")
response.status_code = 401
DBSession.remove()
return response
# usernames different
if auth_info['username'] != username:
# the asked username is a customer
if user.kind == 0:
response = jsonify(
errMsg=
"usernames different and the asked username is a customer")
response.status_code = 401
DBSession.remove()
return response
# the asked username is a saler
else:
# return the left 20 coupons of that saler
data, status_code = returnsalercoupons(db, username, page)
response = jsonify(data=data)
response.status_code = status_code
DBSession.remove()
return response
else:
# usernames identical
if auth_info['kind'] == 1:
# saler
# return the left 20 coupons of that saler
data, status_code = returnsalercoupons(db, username, page)
response = jsonify(data=data)
response.status_code = status_code
DBSession.remove()
return response
else:
# customer
# return the acquired coupons for that customer
data, status_code = returncustomercoupons(db, username)
response = jsonify(data=data)
response.status_code = status_code
DBSession.remove()
return response
# post method to add coupons
try:
coupon_info = json.loads(request.data.decode('utf8'))
except Exception:
response = jsonify(errMsg='wrong coupon information')
response.status_code = 400
return response
coupon_name = coupon_info.get('name')
amount = coupon_info.get('amount')
description = coupon_info.get('description')
stock = coupon_info.get('stock')
# check legality of inputs
# only salers could create coupons
if auth_info['kind'] != 1:
response = jsonify(errMsg='Customers can not add a coupon')
response.status_code = 400
return response
if coupon_name == None or amount == None or stock == None:
response = jsonify(errMsg='fields not complete')
response.status_code = 400
return response
try:
amount = int(amount)
stock = int(stock)
except ValueError:
response = jsonify(errMsg='amount or stock must be numbers')
response.status_code = 400
return response
# add coupon to the database
db = DBSession()
coupon_info = Coupon(username=auth_info['username'],
coupon_name=coupon_name,
amount=amount,
left=amount,
stock=stock,
description=description)
try:
db.add(coupon_info)
db.commit()
except Exception:
response = jsonify(errMsg="coupons already exists")
response.status_code = 400
DBSession.remove()
return response
response = jsonify(msg="coupon added successfully")
response.status_code = 201
DBSession.remove()
return response
def __init__(self):
self.session = DBSession()
def crawl_question(url, cookie, scheduler):
#crawl question page
#like host: http://www.zhihu.com/question/123456
print 'start to question from url: ', url
question_res = requests.get(url, cookies=cookie)
with open('pages/question' + re.search(r'\d+', url).group() + '.html',
'wb') as question_file:
question_file.write(question_res.content)
session = DBSession()
if question_res.status_code == 200:
question_dom = BeautifulSoup(question_res.content)
#find more question
if question_dom.find("div", id="zh-question-related-questions"):
all_related_ques = question_dom.find(
"div", id="zh-question-related-questions").find_all(
"a", class_="question_link")
for ques in all_related_ques:
'''
new_url = ques.get('href') if ques.get('href').startswith('http') \
else 'http://www.zhihu.com/' + ques.get('href')
'''
if ques.get('href').startswith('http'):
new_url = ques.get('href')
elif ques.get('href').startswith('/'):
new_url = 'http://www.zhihu.com' + ques.get('href')
else:
new_url = 'http://www.zhihu.com/' + ques.get('href')
scheduler.add(new_url)
#crawl data
q_id = re.search(r'\d+', url).group()
q_title = question_dom.find(
"div",
id="zh-question-title").find('h2').contents[0].encode('utf-8')
q_detail = ''
if question_dom.find('div', id='zh-question-detail').find('textarea'):
q_detail = question_dom.find('div', id='zh-question-detail').find(
'textarea').get_text().encode('utf-8')
else:
q_detail = question_dom.find(
'div', id='zh-question-detail').get_text().encode('utf-8')
q_author = 'default'
question = Question(q_id=q_id,
title=q_title,
author=q_author,
content=q_detail)
#crawl answer data
#a_id author votes content last_modify
answer_list = question_dom.find_all("div", class_="zm-item-answer")
for answer in answer_list:
a_id = answer.get('data-aid')
author_h3 = answer.find("h3", class_="zm-item-answer-author-wrap")
if author_h3.find_all('a') and len(author_h3.find_all('a')) > 1:
author = author_h3.find_all('a')[1].text.encode('utf-8')
else:
#匿名用户
author = author_h3.text.encode('utf-8')
votes_div = answer.find("div", class_="zm-votebar")
try:
votes_span = votes_div.find_all("span")
except:
print votes_div
sys.exit(1)
votes = votes_span[1].text if len(votes_span) > 1 else 0
'''
content = answer.find("div",
class_="zm-editable-content").get_text().encode('utf-8')
'''
content_div = answer.find("div", class_='zm-editable-content')
if content_div is None:
content = "None"
else:
content = content_div.get_text().encode('utf-8')
#last modify date
try:
last_modify_date = answer.find(
"a", class_='answer-date-link').text.split()[1]
if last_modify_date.index(':') != -1:
last_modify_date = datetime.now().strftime("%Y-%m-%d")
except:
last_modify_date = "2015-06-17"
last_modify = datetime.strptime(last_modify_date, "%Y-%m-%d")
answer_info = Answer(a_id=a_id,
author=author,
votes=votes,
content=content,
last_modify=last_modify)
session.add(answer_info)
question.answers.append(answer_info)
session.add(question)
session.commit()
session.close()
#process_question(question)
else:
print "Error: ", str(question_res.status_code)
def is_unique(self, data):
session = DBSession()
ref_check = session.query(Data).filter(
and_(Data.msg_id == data['msg_id'],
Data.dtype == data['dtype'])).count()
return ref_check == 0
# coding: utf-8 from __future__ import unicode_literals from models import DBSession, ZHArticle from sqlalchemy import func session = DBSession() print session.query(func.count(ZHArticle.id)).scalar()
def by_id(cls, guid):
"""
Returns a the object with id of guid, can accept a string of UUID class
"""
return DBSession().query(cls).filter_by(_id=guid).first()
def prepare(self):
self.db = DBSession()
def all(cls):
""" Returns a list of all objects in the database """
return DBSession().query(cls).all()
def get(self, symbol):
DBSession = sessionmaker(bind=self.engine)
session = DBSession()
ticker = session.query(models.Ticker).get(symbol=symbol)
return ticker
def get_many(self, limit=None, offset=None):
DBSession = sessionmaker(bind=self.engine)
session = DBSession()
return session.query(models.Ticker).offset(offset).limit(limit).all()
def get(self, grid):
session = DBSession()
q = session.query(self.model).get(int(grid))
return q
def queryNumById(table_class):
sess = DBSession()
qNum = sess.query(func.count(table_class.id)).scalar()
sess.close()
return qNum
def on_finish(self):
""" Called after a response is sent to the client """
logging.debug(" ------- [ON FINISH] -------")
DBSession().close()
def __init__(self):
self.session = DBSession()
self.pool = redis.Redis(host='raspberrypi', port=6379, db=2)
def export_view(request):
session = DBSession()
ckeck_types = session.query(CheckStatusType).order_by(asc(CheckStatusType.id))
return {'check_types': ckeck_types}
from models import DBSession, init_db, Customer, Account
import datetime
def add():
init_db()
c1 = Customer('Anna', 'Smith', '*****@*****.**',
datetime.datetime(1990, 5, 1))
c2 = Customer('John', 'Brown', '*****@*****.**',
datetime.datetime(1992, 5, 1))
print(c1)
a1 = Account(c1)
a2 = Account(c2)
db = DBSession().get()
db.add(c1)
db.add(c2)
db.add(a1)
db.add(a2)
db.commit()
db = DBSession().get()
customers = db.query(Customer).filter(Customer.lastname == 'Brown').all()
print(customers)
def by_owner(cls, owner, limit=100, offset=0):
return DBSession().query(cls).filter_by(owner_id=owner.id).order_by(
cls.created.desc()).limit(limit).offset(offset)
def write_to_file(
save_path='/media/spartonia/DATA/Company_stuff/Marketing/Letters',
published_days_ago=30,
date_info_collected=str(
(datetime.now().date() - timedelta(days=-1))), # yesterday
price_range=(0, 20000000),
living_area_range=(0, 500),
merge_to_pdf=True):
# TODO: add county selection option
"""
Writes marketing letters.
Parameters
----------
save_path: str
published_days_ago: int
date_info_collected: str 'YYYY-mm-dd'
price_range: (int, int)
living_area_range: (int, int)
merge_to_pdf : bool
"""
reporting_day_save_path = os.path.join(save_path,
str(datetime.now().date()))
if not os.path.exists(reporting_day_save_path):
os.makedirs(reporting_day_save_path)
save_path_reporting_day_collected_day = os.path.join(
reporting_day_save_path, date_info_collected)
if not os.path.exists(save_path_reporting_day_collected_day):
os.mkdir(save_path_reporting_day_collected_day)
date_collected = date_parser.parse(date_info_collected).date()
session = DBSession()
apts = session.query(Apartment).join(Link).filter(
Apartment.processed.isnot(True),
Apartment.owner.isnot(None),
and_(Apartment.price >= price_range[0],
Apartment.price <= price_range[1]),
and_(Apartment.living_area >= living_area_range[0],
Apartment.living_area <= living_area_range[1]),
Apartment.price.isnot(None),
Apartment.postal_code.isnot(None),
Link.timestamp.between(str(date_collected),
str(date_collected + timedelta(days=1))),
Link.date.between(str(datetime.now().date() + timedelta(days=-10)),
str(datetime.now().date())),
)
file_count = 0
for i, apt in enumerate(apts):
coupon_code = generate_coupon_code()
expiry = str(datetime.now().date() + timedelta(days=120))
coupon_value = 500
print(apt.owner)
try:
write_to_one_file(coupon=coupon_code,
coupon_expiry=expiry,
coupon_value=coupon_value,
customer_name=apt.owner,
customer_formal_address=apt.formal_address,
save_path=save_path_reporting_day_collected_day)
log_data = coupon_code + ';' + expiry + ';' + str(coupon_value)
log_coupons(reporting_day_save_path, data=log_data)
file_count += 1
apt.processed = True
try:
session.commit()
except Exception as e:
print(e.message)
session = DBSession()
except Exception as e:
print('Error creating letter for', apt.formal_address, ',',
apt.owner, coupon_code, expiry)
print('Error message:', e.message)
print('Done!')
print(file_count, 'letters created.')
def crawl_plat_data(shuju_date="2020-01-062020-01-12"):
"""
平台成交数据 https://shuju.wdzj.com/platdata-1.html
"""
url = "https://shuju.wdzj.com/plat-data-custom.html"
form_data = {
"type": 0,
"shujuDate": shuju_date
}
response = requests.post(url, data=form_data)
status = response.status_code
if status != 200:
print("crawl failed. (status is not 200)")
raise CrawlFailed('crawl failed')
plats_data = response.json()
for plat_data in plats_data:
plat_id = plat_data.get('platId')
plat_name = plat_data.get('platName')
wdzj_id = plat_data.get('wdzjPlatId')
session = DBSession()
if wdzj_id != 0:
session.execute(
"""
INSERT INTO products
(plat_id, wdzj_id, name)
select
'{plat_id}', '{wdzj_id}', '{plat_name}'
WHERE not EXISTS (SELECT *
FROM products
WHERE plat_id = '{plat_id}');
""".format(
plat_id=plat_id, wdzj_id=wdzj_id, plat_name=plat_name
)
)
new_platdata = PlatData(
plat_id=plat_data.get('platId'),
wdzj_id=plat_data.get('wdzjPlatId'),
plat_name=plat_data.get('platName'),
start_date=plat_data.get('startDate'),
amount=plat_data.get('amount'),
incomeRate=plat_data.get('incomeRate'),
loanPeriod=plat_data.get('loanPeriod'),
netInflowOfThirty=plat_data.get('netInflowOfThirty'),
stayStillOfTotal=plat_data.get('stayStillOfTotal'),
fullloanTime=plat_data.get('fullloanTime'),
regCapital=plat_data.get('regCapital'),
timeOperation=plat_data.get('timeOperation'),
totalLoanNum=plat_data.get('totalLoanNum'),
bidderNum=plat_data.get('bidderNum'),
avgBidMoney=plat_data.get('avgBidMoney'),
top10DueInProportion=plat_data.get('top10DueInProportion'),
borrowerNum=plat_data.get('borrowerNum'),
avgBorrowMoney=plat_data.get('avgBorrowMoney'),
top10StayStillProportion=plat_data.get('top10StayStillProportion'),
developZhishu=plat_data.get('developZhishu'),
background=plat_data.get('background'),
newbackground=plat_data.get('newbackground'),
shuju_date=shuju_date
)
session.add(new_platdata)
session.commit()
session.close()
def fetch_all(cls):
session = DBSession()
actions = session.query(Action).all()
session.close()
actions = [action.to_dict() for action in actions]
return actions
def update_links(
rss_feed_url
):
"""Inserts new announcements into the database.
Notes:
* Publishing date is the date the property is listed for sale. It might be very old,
* We insert all entries in the rss feed to the database. The url field is unique so duplicates are not allowed.
* When querying new entries, keep in mind to query the date based on ( timestamp = 'today' & pubDate =
'close enough' )so only new listed properties are queried.
Parameters
----------
rss_feed_url : str
"""
feed = feedparser.parse(rss_feed_url)
entries = feed['entries']
num_new_links = 0
print('Updating liks database ..')
session = DBSession()
url_rs = session.query(Link.url)
url_list = [url for (url,) in url_rs]
session.close()
session = DBSession()
browser = logged_in_browser()
for cnt, entry in enumerate(entries):
link = entry['link']
published_str = entry['published']
print(cnt, ':', link)
if link in url_list:
print('duplicate url, passing..')
continue
published = date_parser.parse(published_str)
pubDate = datetime.fromordinal(published.toordinal())
new_link = Link(url=link, date=pubDate)
session.add(new_link)
time.sleep(random.choice(range(20, 60))/10)
try:
data = crawl_hemnet_page(new_link.url, browser=browser)
except Exception as e:
print('Error crawling hemnet page.', e.message)
continue
new_apt = Apartment(**data)
new_apt.link = new_link
session.add(new_apt)
try:
session.commit()
num_new_links += 1
except IntegrityError as e:
print(e.message)
print(link)
session.rollback()
except Exception as e:
print(e.message)
session.rollback()
finally:
session = DBSession()
print('Done!')
print('%s new links added.' % num_new_links)
def search(self, text, search_options=['A']):
session = DBSession()
q = session.query(self.model).filter(
self.model.name.ilike(f"%{text}%"))
return q.filter(self.model.flag.in_(search_options))
def acquire_coupons(username, couponname):
# select for update example
auth_info = get_auth_info()
if auth_info['kind'] != 0:
response = jsonify(errMsg='saler could not acquire a coupon')
response.status_code = 204
return response
db = DBSession()
user_kind = rs.get(username)
# user_kind=='2'means it does exist
if not user_kind:
user = db.query(User).filter(User.username == username).first()
if not user:
rs.set(username, 2)
response = jsonify(errMsg="the username must be valid")
response.status_code = 204
DBSession.remove()
return response
if user.kind == 0:
rs.set(username, 0)
response = jsonify(errMsg="the username must be a saler")
response.status_code = 204
DBSession.remove()
return response
rs.set(username, 1)
elif user_kind == '2':
response = jsonify(errMsg="the username must be valid(from redis)")
response.status_code = 204
DBSession.remove()
return response
elif user_kind == '0':
response = jsonify(errMsg="the username must be a saler(from redis)")
response.status_code = 204
DBSession.remove()
return response
key = auth_info['username'] + "_" + couponname
if not rs.get(key):
# get the coupon of a saler
coupon=db.query(Coupon).filter(Coupon.username==username)\
.filter(Coupon.coupon_name == couponname).with_for_update().first()
if coupon and coupon.left > 0:
db.add(
Coupon(username=auth_info['username'],
coupon_name=coupon.coupon_name,
stock=coupon.stock,
description=coupon.description))
coupon.left -= 1
try:
db.commit()
except Exception:
# the user already has the coupon
rs.set(key, 'has')
response = jsonify(errMsg="coupons acquired failed")
response.status_code = 204
DBSession.remove()
return response
# acquire the coupon successfully
rs.set(key, 'has')
response = jsonify(msg="coupon acquired successfully")
response.status_code = 201
DBSession.remove()
return response
# the key is in the redis, it means the customer has the coupon
response = jsonify(errMsg="coupons acquired failed(from redis)")
response.status_code = 204
DBSession.remove()
return response
def initialize(self):
self.db = DBSession()
