def join():
print "join"
if request.method =='POST':
print "method is post"
nick = request.form["nick"].strip()
emailaddr = request.form["emailaddr"].strip()
password = request.form["password"].strip()
init_db()
targer_user = User.query.filter_by(email=emailaddr).first()
if not (targer_user == None):
flash('Email is invalid or already taken')
return redirect(url_for('join'))
if (targer_user == None):
"""insert"""
new_user = User(emailaddr,password,nick)
db_session.add(new_user)
db_session.commit()
db_session.close()
return redirect(url_for('login'))
else:
print "method is get"
return render_template('signup_form.html')
def setUp(self):
#app = Flask(__name__)
#app.secret_key = os.environ['APP_SECRET_KEY']
app.config.from_object('app.config.Testing')
db_session.close()
db_session.drop_all()
db_session.create_all()
def success():
results = []
qry = db_session.query(Items)
results = qry.all()
# Author:Sandhya Nagarajan
# Date : 06/09/2018
# Notes: <Bugfix>
# the 'results' tuple have the list of all the items in the db
# iterate through the list and add it to a outputFile
# read the file and return the content as a String
# (could have directly dumped it as string instead of writing into file
# wrote into a line to make the output more readable)
# <improvement>
# Its ideal to close the db session
db_session.close()
outputFile = open("outputFromDB.txt", "w")
outputFile.write("The Query from the DB are : ")
for currentItem in results:
outputFile.write("\n" + currentItem.name + " " +
str(currentItem.quantity) + " " +
currentItem.description + " " +
str(currentItem.date_added))
outputFile.close()
outputFile = open("outputFromDB.txt", "r")
outputString = Response(outputFile.read(), mimetype='text/plain')
outputFile.close()
return outputString
def scrap_n_save_data(self, table_dom, issue_id, source_id):
table = pq(table_dom)("tr td")
article_id = str(uuid.uuid4().hex)
r = lambda: random.randint(0, 255)
try:
if table.children("p").text() is not None and table.children(
".main-url").text() is not None and table.children(
".article-headline").attr("href"):
article = Article(
id=article_id,
object_id=article_id,
pre_content=table.children("p").text(),
article_view_content=None,
issue_id=issue_id,
source_id=source_id,
main_url=table.children(".main-url").text(),
url=table.children(".article-headline").attr("href"),
title=table.children(".article-headline").text(),
img=str(
"http://via.placeholder.com/300x300/{0}/{1}?text={2}".
format('#%02X%02X%02X' % (r(), r(), r()), "#FFFFFF",
"A")))
db_session.add(article)
db_session.commit()
except Exception as e:
db_session.rollback()
print(e)
finally:
db_session.close()
def create_records(pin_num):
with open('fixtures/rel_humidity_data.csv') as csvfile:
reader = csv.reader(csvfile)
f = reader.__next__()
delay = dt.now().timestamp() - int(f[0])
record = Record(pin_num, int(f[2]), int(f[0]) + delay)
db_session.add(record)
for row in reader:
record = Record(pin_num, int(row[2]), int(row[0]) + delay)
db_session.add(record)
db_session.commit()
db_session.close()
with open('fixtures/dht11_data.csv') as csvfile:
reader = csv.reader(csvfile)
f = reader.__next__()
delay = dt.now().timestamp() - int(f[0])
record = DHT11Record(int(f[1]), int(f[2]), int(f[0]) + delay)
db_session.add(record)
for row in reader:
r = DHT11Record(int(row[1]), int(row[2]), int(row[0]) + delay)
db_session.add(r)
db_session.commit()
db_session.close()
def register():
#パラメータ取得
agent_cd = request.form['agent_cd']
agent_name = request.form['agent_name']
#パラメータチェック
if not agent_cd or not agent_name:
flash('入力してください。')
return redirect(url_for('login'))
#UserAgent取得
header = request.headers.get('User-Agent')
ua = woothee.parse(header)
category = ua.get('category')
name = ua.get('name')
version = ua.get('version')
os_name = ua.get('os')
vendor = ua.get('vendor')
os_version = ua.get('os_version')
#DB登録処理
user_agent = UserAgent(agent_cd=agent_cd, agent_name=agent_name, category=category, name=name,
version=version, os_name=os_name, vendor=vendor, os_version=os_version)
try:
db_session.add(user_agent)
db_session.commit()
except Exception as e:
db_session.rollback()
flash('既に登録済みです。')
return redirect(url_for('login'))
finally:
db_session.close()
return render_template('register.html')
def updatehoster(self):
"""ipupdatehoster."""
wh = self.ip.getdrandomonewithdefaulthoster()
if wh:
print(wh[0].ip)
ret = self.ip.getwhoisdata(wh[0].ip)
print("++++++++++++++++++++++++")
print(ret)
print(ret['country'])
if ret['hostername'] is "False":
print("HOSTER:TU JEST FALSE: " + str(ret['hostername']))
hoster = "False"
else:
hoster = ret['hostername']
print("HOSTER:TU NIE MA TRUE" + str(ret['hostername']))
hst = Hoster()
ret['hoster'] = hst.add(name=hoster, email='*****@*****.**')
# print("HOSTER ID="+str(ret['hoster']))
wh[0].country = ret['country']
wh[0].hoster = ret['hoster']
wh[0].asn = ret['asn']
print("------- obiekt przed update")
print(wh[0])
db_session.commit()
db_session.close()
print("Robimy update IP")
return render_template('update.html')
def show_entries():
users_query = db_session.query(User)
entries = [dict(user_id=user.user_id, nickname=user.nickname) for user in users_query]
db_session.close()
return render_template('user/show_entries.html', entries=entries)
def add_entries():
u = User(request.form['user_id'], request.form['nickname'])
u.set_password(request.form['password'])
db_session.add(u)
db_session.commit()
db_session.close()
flash('New User was successfully posted')
return redirect(url_for('show_entries'))
def update_entries(user_id):
if request.form['user_id'] == g.User.user_id:
user = db_session.query(User).filter(User.user_id==request.form['user_id']).first()
user.nickname = request.form['nickname']
user.set_password( request.form['password'] )
db_session.commit()
db_session.close()
return redirect(url_for('show_entries'))
flash('Connected to a different user.','error')
return redirect(url_for('show_entries'))
def create(self, email_address: String, first_name: String,
last_name: String, sub: String, height: Integer,
weight: Integer):
new_user = User(email_address=email_address,
first_name=first_name,
last_name=last_name,
sub=sub,
height=height,
weight=weight,
last_login=datetime.now())
db_session.add(new_user)
db_session.commit()
db_session.close()
def add(self, url, urlstatus='new'):
"""add."""
newphishtank = PhishtankModel(
url=url,
urlstatus=urlstatus
)
try:
db_session.add(newphishtank)
db_session.commit()
db_session.close()
return newphishtank
except:
db_session.close()
return False
def delete():
user_id = request.form['user_id']
print "delete ID : %s , %s " % (user_id, g.User.user_id)
if user_id == g.User.user_id:
db_session.query(User).filter(User.user_id==user_id).delete()
db_session.commit()
db_session.close()
flash('Selected User was successfully deleted')
return redirect(url_for('show_entries'))
flash('Connected to a different user.','error')
return redirect(url_for('show_entries'))
def signup():
if request.json is None:
return jsonify({"success": False, "error": "No data given"}), 404
if 'username' in request.json:
username = request.json['username']
else:
return jsonify({"success": False, "error": "No username given"}), 404
if 'password' in request.json:
password = request.json['password']
pw_hash = argon2.generate_password_hash(password)
else:
return jsonify({"success": False, "error": "No password given"}), 404
if 'email' in request.json:
email = request.json['email']
else:
email = None
if 'name' in request.json:
name = request.json['name']
else:
name = None
user = db_session.query(User).filter(User.username == username)
if user.count() == 1:
db_session.flush()
return jsonify({
"success": False,
"error": "User already registered"
}), 418
else:
try:
newUser = User(username, pw_hash, email, name)
db_session.add(newUser)
db_session.flush()
db_session.commit()
return jsonify({"success": True, "data": {"id": newUser.id}}), 200
except Exception as e:
db_session.rollback()
db_session.flush()
print(e)
return jsonify({
"success": False,
"error": "Error inserting user"
}), 403
finally:
db_session.close()
def addSession(token, user_id):
user = db_session.query(User).filter(User.id == user_id, User.status == 1)
if user.count() == 1:
session = Session(user_id, token, '0.0.0.0', '{"browser": "chrome"}')
try:
db_session.add(session)
db_session.commit()
return True
except Exception as e:
db_session.rollback()
db_session.flush()
return False
finally:
db_session.close()
def add(self, name=None, email=None, www=None):
"""add."""
newbrand = BrandModel(
name=name,
email=email,
www=www
)
try:
db_session.add(newbrand)
db_session.commit()
r = newbrand.id
except:
r = False
db_session.close()
return r
def make_new_gye():
if request.method =='GET':
return render_template("make_new_gye.html")
elif request.method =='POST':
init_db()
new_gye = Gye()
new_gye.gname=request.form["gname"].strip()
new_gye.gintro=request.form["gintro"].strip()
new_gye.gfounder=session['email']
new_gye.gmemcnt=0
db_session.add(new_gye)
db_session.commit()
db_session.close()
return render_template("make_new_gye.html")
def snap():
init_db()
all_users = []
user_data = Users.query.filter(Users.saved == False).all()
user_count = db_session.query(Users).count()
print(user_count)
for user in user_data:
name = user.username
# print(name)
all_users.append(name)
numbers_to_balance = int(user_count / 2)
print(numbers_to_balance)
# Select half users at random
ill_fated = random.sample(all_users, numbers_to_balance)
db_session.close()
ban(ill_fated)
def add(self, name=None, www=None, email=None):
"""add."""
hosterexistid = self.gethosterid(name)
if hosterexistid > 0:
print("HOSTER JEST" + str(hosterexistid))
return hosterexistid
else:
newhoster = HosterModel(name=name, www=www, email=email)
#try:
if 1:
db_session.add(newhoster)
db_session.commit()
r = newhoster.id
#except:
r = False
db_session.close()
return r
def success():
results = []
qry = db_session.query(Items)
results = qry.all()
db_session.close()
outputFile = open("outputFromDB.txt", "w")
outputFile.write("The Query from the DB are : ")
for currentItem in results:
outputFile.write("\n" + currentItem.name + " " +
str(currentItem.quantity) + " " +
currentItem.description + " " +
str(currentItem.date_added))
outputFile.close()
outputFile = open("outputFromDB.txt", "r")
outputString = Response(outputFile.read(), mimetype='text/plain')
outputFile.close()
return outputString
def process_queue(self):
print("Processing queue", flush=True)
current_scans = len(self.scans_in_progress)
slots_available = (self.max_scans - current_scans)
if current_scans > 0:
query = db_session.query(Scan).filter(Scan.status == 0).filter(~Scan.id.in_(self.scans_in_progress.keys())).order_by(Scan.created_date).limit(slots_available)
else:
query = db_session.query(Scan).filter(Scan.status == 0).order_by(Scan.created_date).limit(slots_available)
if slots_available > 0:
for scan in query:
print("Scanning scan ID #" + str(scan.id), flush=True)
scan_thread = threading.Thread(target=self.init_scan, args=(scan.id,))
scan_thread.start()
self.scans_in_progress[scan.id] = { 'thread': scan_thread }
db_session.close()
def add(self, name='Temporary', shortname='tmp'):
"""add."""
countryexistid = self.getcountryid(shortname)
if countryexistid > 0:
print("COUNTRY JEST" + str(countryexistid))
return countryexistid
else:
print("NIE MA COUNTRY")
newcountry = CountryModel(name=name, shortname=shortname)
try:
db_session.add(newcountry)
db_session.commit()
r = newcountry.id
except:
r = False
print("Could'n add Country")
db_session.close()
return r
def main():
today = strftime('%Y%m%d')
batch_name = 'Jobs_Data_%s.csv' % today
# Mark all current jobs_data batches inactive
db_session.query(Batch).filter_by(datasource_id=6).update({'is_active': False})
batch = Batch(datasource_id=6, name=batch_name, is_active=True)
db_session.add(batch)
db_session.flush()
db_session.commit()
categories = range(1, 34)
for c in categories:
print "processing category %s" % c
data = get_job_stats(c)
push_to_hadoop(data, batch_name, c)
push_to_sql(data['response']['cities'], batch.batch_id, c)
db_session.close()
def main():
today = strftime('%Y%m%d')
batch_name = 'Jobs_Data_%s.csv' % today
# Mark all current jobs_data batches inactive
db_session.query(Batch).filter_by(datasource_id=6).update(
{'is_active': False})
batch = Batch(datasource_id=6, name=batch_name, is_active=True)
db_session.add(batch)
db_session.flush()
db_session.commit()
categories = range(1, 34)
for c in categories:
print "processing category %s" % c
data = get_job_stats(c)
push_to_hadoop(data, batch_name, c)
push_to_sql(data['response']['cities'], batch.batch_id, c)
db_session.close()
def login():
print "login"
if request.method =='POST':
emailaddr = request.form["emailaddr"].strip()
password = request.form["password"].strip()
init_db()
targer_user = User.query.filter_by(email=emailaddr, pw=password).first()
db_session.close()
print "Query done"
if not (targer_user == None):
session["email"] = targer_user.email
session["nick"] = targer_user.nick
return redirect(url_for('welcome'))
if (targer_user == None):
flash('Incorrect username or password.')
return redirect(url_for('login'),)
else:
print "method is get"
return render_template('login_form.html')
def process_queue(self):
print("Processing queue", flush=True)
current_scans = len(self.scans_in_progress)
slots_available = (self.max_scans - current_scans)
if current_scans > 0:
query = db_session.query(Scan).filter(Scan.status == 0).filter(
~Scan.id.in_(self.scans_in_progress.keys())).order_by(
Scan.created_date).limit(slots_available)
else:
query = db_session.query(Scan).filter(Scan.status == 0).order_by(
Scan.created_date).limit(slots_available)
if slots_available > 0:
for scan in query:
print("Scanning scan ID #" + str(scan.id), flush=True)
scan_thread = threading.Thread(target=self.init_scan,
args=(scan.id, ))
scan_thread.start()
self.scans_in_progress[scan.id] = {'thread': scan_thread}
db_session.close()
def signup():
print "signup"
emailaddr = request.form["emailaddr"].strip()
password = request.form["password"].strip()
data ={'emailaddr':emailaddr, 'password':password}
error = is_fillup(data)
if error:
flash(error)
return redirect(url_for('welcome'))
init_db()
targer_user = User.query.filter_by(email=emailaddr).first()
if not (targer_user == None):
flash('SAME EMAIL ALREADY')
return redirect(url_for('welcome'))
if (targer_user == None):
new_user = User(emailaddr,password)
db_session.add(new_user)
db_session.commit()
db_session.close()
flash('GOOD TO SEE YOU')
return redirect(url_for('welcome'))
def login_entries():
user_id = request.form['user_id']
password = request.form['password']
remember_me = False
if 'remember_me' in request.form:
remember_me = True
user = db_session.query(User).filter(User.user_id==user_id).first()
db_session.close()
if user is None:
flash('Username is invalid' , 'error')
return redirect(url_for('login'))
if not user.check_password(password):
flash('Password is invalid : '+password,'error')
return redirect(url_for('login'))
login_user(user, remember = remember_me)
flash('Logged in successfully')
return redirect(url_for('show_entries'))
def updateip(self, phishid, ipid):
"""updateip."""
phish = PhishingModel.query.filter_by(id=phishid).first()
phish.ip = ipid
db_session.commit()
db_session.close()
def data_parse(soup, url, max_commend_page=3):
rank = soup.select('#topPointTotalNumber')[0].text
title = soup.title.text.split(':')[0]
titleId = str(parse_qs(urlparse(url).query)['titleId'][0])
no = str(parse_qs(urlparse(url).query)['no'][0])
no_title = soup.select('.tit_area .view h3')[0].text
webtoon_cut_db = db_session.query(WebToonCuts).\
filter(WebToonCuts.titleId == titleId).\
filter(WebToonCuts.no == no).all()
print(title + ' ' + no_title)
# 웹툰 n화저장
if not len(webtoon_cut_db):
webtoon_cut = WebToonCuts(titleId, no, title + ' ' + no_title, rank)
try:
db_session.add(webtoon_cut)
db_session.commit()
except:
e = sys.exc_info()[0]
finally:
db_session.close()
comment_url = WC[
'NAVER_URL'] + '/comment/comment.nhn?titleId=' + titleId + '&no=' + no
objectId = titleId + '_' + no
page_count = 1
u = 'http://apis.naver.com/commentBox/cbox/web_naver_list_jsonp.json?ticket=comic&templateId=webtoon&pool=cbox3&_callback=jQuery1113012327623800394427_1489937311100&lang=ko&country=KR&objectId=' + objectId + '&categoryId=&pageSize=15&indexSize=10&groupId=&listType=OBJECT&sort=NEW&_=1489937311112'
while page_count < max_commend_page: # True를 주면 모든 댓글을 수집함
comment_url = make_link(u, page_count)
header = {
"Host":
"apis.naver.com",
"Referer":
"http://comic.naver.com/comment/comment.nhn?titleId=" + titleId +
"&no=" + no,
"Content-Type":
"application/javascript"
}
res = rq.get(comment_url, headers=header)
soup = BeautifulSoup(res.content, 'lxml')
try:
content_text = soup.select('p')[0].text
one = content_text.find('(') + 1
two = content_text.find(');')
content = json.loads(content_text[one:two])
comments = content['result']['commentList']
for comment in comments:
contents = comment['contents']
commentNo = comment['commentNo']
webtoon_commend_db = db_session.query(WebtoonCommends). \
filter(WebtoonCommends.titleId == titleId). \
filter(WebtoonCommends.no == no).\
filter(WebtoonCommends.commentNo == commentNo)\
.all()
print(titleId, no, commentNo, contents)
if not len(webtoon_commend_db) and len(contents) < 230:
try:
webtoon_commend = WebtoonCommends(
titleId, no, commentNo, contents)
db_session.add(webtoon_commend)
db_session.commit()
except:
e = sys.exc_info()[0]
finally:
db_session.close()
if not len(comments):
# 댓글 마지막 페이지
break
else:
page_count += 1
except:
pass
except:
pass
if __name__ == "__main__":
webtoons = get_daylywebtoons()
for webtoon in webtoons:
webtoon_title, title_id, weekday, target_webtoons = get_all_webtoon(
webtoon, False)
webtoon = WebToons(webtoon_title, title_id, weekday)
webtoons_db = db_session.query(WebToons).filter(
WebToons.titleId == title_id).all()
# 웹툰이름 저장
if not len(webtoons_db):
try:
db_session.add(webtoon)
db_session.commit()
except:
e = sys.exc_info()[0]
finally:
db_session.close()
print(webtoon_title)
for webtoon_page in target_webtoons:
res = rq.get(webtoon_page)
webtoon_page_soup = BeautifulSoup(res.content, 'lxml')
data_parse(webtoon_page_soup, webtoon_page)
def main():
init_db()
add_entry("2015-02-06 09:00:05", "test1")
show_tables()
#delete_entry("2015-02-06 09:00:05", "test1")
db_session.close()
def after_return(self, status, retval, task_id, args, kwargs, einfo):
db_session.close()
db_session.remove()
def main():
# insert User
print 'Insert Example\n'
user1 = User('junki', 'Junki Kim', '12345')
user2 = User('aaa', 'aaaa bb', '654321')
query.insertUser(user1)
query.insertUser(user2)
# show table
print "\n\n Show tables\n"
query.show_tables()
# select user
print 'Select Example\n'
query.selectUser(user1)
# delete user
print 'Delete Example\n'
query.deleteUser(user2)
print "\n\n Show tabales\n"
query.show_tables()
# update user
print 'Update Example\n'
updateuser = User('junki', 'Junki Kim', '654321')
query.updateUser(user1, updateuser)
query.show_tables()
# Relationship
print 'Relationship Example\n'
print "Create tmpUser('aaa', 'bbb aaa', '12345678')\n"
tmpUser = User('aaa', 'bbb aaa', '12345678')
print "tmpUser's email address\n"
tmpUser.addresses
print 'Add email address\n'
tmpUser.addresses = [Address(email_address='*****@*****.**'), Address(email_address='*****@*****.**')]
tmpUser.addresses
print 'Insert tmpUser'
query.insertUser(tmpUser)
print 'Select tmpUser'
query.selectUser(tmpUser)
# Join
joinUser1 = db_session.query(User, Address).filter(User.id == Address.user_id).filter(Address.email_address == '*****@*****.**').all()
print joinUser1
print 'SQL JOIN'
joinUser2 = db_session.query(User).join(Address).filter(Address.email_address=='*****@*****.**').all()
print joinUser2
# Sub Query
stmt = db_session.query(Address.user_id, func.count('*').label('address_count')).group_by(Address.user_id).subquery()
for u, count in db_session.query(User, stmt.c.address_count).outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id):
print u, count
# Session Close
db_session.close()
def close_db(error):
from database import db_session
db_session.close()
def get_all_projects():
projects = db_session.query(Project).order_by(Project.name).all()
db_session.close()
result = projects_schema.dump(projects)
return jsonify(result)
def setUp():
os.remove('/tmp/dev.db')
setup('dev')
db_session.close()
def close_db(error): from database import db_session db_session.close()
def load_user(user_id):
#return User.query.get(int(user_id))
users = db_session.query(User).get(user_id)
db_session.close()
print 'load_user Call!!'
return users
def main():
# insert User
print 'Insert Example\n'
user1 = User('junki', 'Junki Kim', '12345')
user2 = User('aaa', 'aaaa bb', '654321')
query.insertUser(user1)
query.insertUser(user2)
# show table
print "\n\n Show tables\n"
query.show_tables()
# select user
print 'Select Example\n'
query.selectUser(user1)
# delete user
print 'Delete Example\n'
query.deleteUser(user2)
print "\n\n Show tabales\n"
query.show_tables()
# update user
print 'Update Example\n'
updateuser = User('junki', 'Junki Kim', '654321')
query.updateUser(user1, updateuser)
query.show_tables()
# Relationship
print 'Relationship Example\n'
print "Create tmpUser('aaa', 'bbb aaa', '12345678')\n"
tmpUser = User('aaa', 'bbb aaa', '12345678')
print "tmpUser's email address\n"
tmpUser.addresses
print 'Add email address\n'
tmpUser.addresses = [
Address(email_address='*****@*****.**'),
Address(email_address='*****@*****.**')
]
tmpUser.addresses
print 'Insert tmpUser'
query.insertUser(tmpUser)
print 'Select tmpUser'
query.selectUser(tmpUser)
# Join
joinUser1 = db_session.query(
User, Address).filter(User.id == Address.user_id).filter(
Address.email_address == '*****@*****.**').all()
print joinUser1
print 'SQL JOIN'
joinUser2 = db_session.query(User).join(Address).filter(
Address.email_address == '*****@*****.**').all()
print joinUser2
# Sub Query
stmt = db_session.query(Address.user_id,
func.count('*').label('address_count')).group_by(
Address.user_id).subquery()
for u, count in db_session.query(User, stmt.c.address_count).outerjoin(
stmt, User.id == stmt.c.user_id).order_by(User.id):
print u, count
# Session Close
db_session.close()
def main():
add_entry("2015-02-06 09:00:05", "test1")
delete_entry("2015-02-06 09:00:05", "test1")
db_session.close()
