def on_delete(self, req, resq, id):
customer = db.query(Customer).filter(Customer.id == id).first()
if customer is None:
raise falcon.HTTPNotFound()
db.delete(customer)
db.commit()
db.close()
def download():
db.connect()
aufmacher = Aufmacher.select()
aufmacher_length = len(aufmacher)
for index, auf in enumerate(aufmacher):
path_to_file = auf.unique_id.replace("http://xml.zeit.de/", "")
xml_file_path = (Path("xml") / path_to_file).with_suffix(".xml")
if xml_file_path.is_file():
continue
print("{}/{}".format(index, aufmacher_length), xml_file_path)
folder = xml_file_path.parent
Path(folder).mkdir(parents=True, exist_ok=True)
article_content = download_article(auf.unique_id)
if article_content:
print("writing", xml_file_path)
with open(xml_file_path, "w") as xml_file:
xml_file.write(article_content)
else:
print("error!")
db.close()
def db_init():
db.connect()
try:
db.create_tables([Movie])
print('Creating tables...')
except OperationalError:
pass
db.close()
def on_get(self, req, resp, id):
customer = db.query(Customer).filter(Customer.id == id).first()
if customer is None:
raise falcon.HTTPNotFound()
customer = dict(id=customer.id,
name=customer.name,
dob=customer.dob.strftime('%Y-%m-%d'))
resp.body = json.dumps(customer)
db.close()
def on_get(self, req, resp):
customers = db.query(Customer)\
.order_by(Customer.id.desc())\
.all()
resp.status = falcon.HTTP_200
customers = [
dict(id=row.id, name=row.name, dob=row.dob.strftime('%Y-%m-%d'))
for row in customers
]
resp.body = json.dumps(customers)
db.close()
def clean_up():
db.connect()
aufmachers = Aufmacher\
.select(Aufmacher, TweetJob)\
.join(TweetJob, JOIN.LEFT_OUTER, on=(Aufmacher.id == TweetJob.aufmacher).alias('tweetjob'))\
.order_by(Aufmacher.created_at.desc())
for aufmacher in aufmachers:
if not aufmacher.tweetjob.id:
print(model_to_dict(aufmacher.tweetjob))
aufmacher.tweetjob.save()
db.close()
def on_put(self, req, resp, id):
body = req.media
if not body:
raise falcon.HTTPBadRequest('Empty request body',
'A valid JSON document is required.')
customer = db.query(Customer).filter(Customer.id == id).first()
if customer is None:
raise falcon.HTTPNotFound()
customer.name = body['name']
customer.dob = body['dob']
if len(db.dirty) > 0:
db.commit()
new_customer = dict(id=customer.id,
name=customer.name,
dob=customer.dob.strftime('%Y-%d-%m'))
resp.body = json.dumps(new_customer)
db.close()
def scrape():
r = requests.get(DOWNLOAD_URL)
soup = BeautifulSoup(r.text, "html.parser")
teaser = soup.select(".main article")
if len(teaser):
teaser = teaser[0]
else:
return
unique_id = teaser["data-unique-id"].strip().replace("https", "http")
db.connect()
db.create_tables([Image, Author, Aufmacher, TweetJob], safe=True)
possible_duplicate = Aufmacher.select().where(Aufmacher.unique_id == unique_id)
if not len(possible_duplicate):
get_article_data(unique_id)
db.close()
def write_to_db():
#爬取50页的电影数据，可自行修改页数
for j in range (0, 50):
movie=parse_movie(j)
for i in range(0,len(movie)):
id=str(uuid.uuid1()).replace('-','')
# SQL 插入语句
sql = "INSERT INTO movie_actor(id,directors,title, rate, casts,cover) \
VALUES ('%s','%s', '%s', '%s' , '%s','%s')" % \
(id,movie[i]["directors"], movie[i]["title"], movie[i]["rate"], movie[i]["casts"],movie[i]["cover"])
try:
# 执行sql语句
cursor.execute (sql)
# 提交到数据库执行
db.commit ()
print "-------插入%s成功------------" % str ((i+1)+j*20)
except:
# 发生错误时回滚
print "-------插入%s失败-------------" % str (i)
db.rollback ()
# 关闭数据库连接
db.close ()
def stats():
db.connect()
aufmacher_count = Aufmacher.select().count()
author_count = Author.select().count()
image_count = Image.select().count()
print("Stats:")
print("{:>5} Aufmacher".format(aufmacher_count))
print("{:>5} authors".format(author_count))
print("{:>5} images".format(image_count))
print("\nLatest:")
latest_aufmacher = Aufmacher.select().order_by(Aufmacher.created_at.desc())
latest_aufmacher_string = """
since {created_at}
{supertitle}: {title}
{subtitle}
by {author_name}
""".format(**model_to_dict(latest_aufmacher[0]),
author_name=latest_aufmacher[0].author.name)
print(latest_aufmacher_string.strip())
db.close()
db.cur.callproc('get_club_member', (self.uuid,))
data = db.cur.fetchall()
result['status'] = 'ok'
result['data'] = list(data)
return result
def turn_cards_to_user(self, **kwargs):
"""给用户转卡 转让俱乐部库存给用户
user: user id , num:充值数量
"""
result = {}
try:
user_id = kwargs.get('user')
num = kwargs.get("num")
#
except Exception as e:
result['status'] = 'failed'
result['msg'] = e.message
finally:
return result
if __name__ == '__main__':
data = {"name": '--我的俱乐部231', "chair_uuid": 18263, "game_types": '1'}
club = GameClub(**data)
print club.__dict__
db.close()
def write_sql (dbs,host,user,passwd,db,tables):
db = MySQLdb.connect(host, user, passwd, db)
yesterday = (date.today() - timedelta(1)).strftime('%Y-%m-%d')
for table in tables :
cursor = db.cursor()
#check if ts or timestamp is used for storing the timestamp in the table
sql = "DESC "+table
try:
cursor.execute(sql)
except:
print sql + " error in executing"
continue
timestamp_in_table_flag = 0
for row in cursor:
column_name = row[0]
if column_name == 'timestamp':
fieldname = 'timestamp'
column_type = row[1]
print column_type
timestamp_in_table_flag = 1
break
elif column_name == 'ts':
fieldname = 'ts'
column_type = row[1]
timestamp_in_table_flag = 1
break
if timestamp_in_table_flag == 0:
continue
if column_type == 'timestamp':
sql = "SELECT * FROM " + table+ " where "+ fieldname + " like "+ "'"+ yesterday+"%" + "'"
elif 'int' in column_type:
sql = "SELECT * FROM " + table+ " where from_unixtime("+ fieldname + ") like "+ "'"+ yesterday+"%" + "'"
file_writer = open("./"+dbs+"/"+table+".sql", "a+")
try:
cursor.execute(sql)
except:
print "Error in Executing: "+sql
continue
for row in cursor:
row_value="("
for r in range(len(row)):
if r==len(row)-1:
row_value=row_value+"'"+str(row[r])+"'"
else:
row_value=row_value+"'"+str(row[r])+"',"
row_value=row_value+")"
print>>file_writer,row_value
file_writer.close()
db.close()
