def join_chat(address):
'''Joins the user into a chat'''
required_args = ('token', )
if any(arg not in request.args for arg in required_args):
return abort(401)
token = request.args['token']
query = cursor.execute(
"""
SELECT COUNT(*)
FROM users
WHERE token = ?;
""", (token, ))
if not db.exists(query):
return jsonify(error=401, message="Invalid token")
query = cursor.execute(
"""
SELECT id
FROM users
WHERE token = ?;
""", (token, ))
user_id = query.fetchone()[0]
query = cursor.execute(
"""
SELECT COUNT(*)
FROM chats
WHERE address = ?;
""", (address, ))
if not db.exists(query):
return jsonify(error=404, message="This chat does not exist")
query = cursor.execute(
"""
SELECT id
FROM chats
WHERE address = ?;
""", (address, ))
chat_id = query.fetchone()[0]
query = cursor.execute(
"""
SELECT id
FROM updates
WHERE chat_id = ?
ORDER BY id DESC
LIMIT 1;
""", (chat_id, ))
latest_update_id = query.fetchone()[0]
cursor.execute(
"""
INSERT INTO updates (
id, user_id, chat_id, type, timestamp)
VALUES (?, ?, ?, ?, ?);
""", (latest_update_id + 1, user_id, chat_id, 1, time.time_ns()))
conn.commit()
return jsonify(success=True)
def get_chats_by_id(chat_id):
'''Returns a chat's list of messages'''
try:
chat_id = int(chat_id)
except ValueError:
return abort(401)
required_args = ('token', )
if any(arg not in request.args for arg in required_args):
return abort(401)
token = request.args['token']
query = cursor.execute(
"""
SELECT COUNT(*)
FROM users
WHERE token = ?;
""", (token, ))
if not db.exists(query):
return jsonify(error=401, message="Invalid token")
query = cursor.execute(
"""
SELECT id
FROM users
WHERE token = ?;
""", (token, ))
user_id = query.fetchone()[0]
query = cursor.execute(
"""
SELECT COUNT(*)
FROM updates
WHERE chat_id = ?
AND user_id = ?;
""", (chat_id, user_id))
if not db.exists(query):
return jsonify(error=401,
message="You are unauthorized to view this chat")
query = cursor.execute(
"""
SELECT
updates.id, updates.timestamp, updates.type, updates.body,
users.id, users.name, users.username
FROM updates
INNER JOIN users
ON updates.user_id = users.id
WHERE chat_id = ?
ORDER BY updates.id;
""", (chat_id, ))
updates = query.fetchall()
return jsonify(list(updates))
def run(self):
log.info("[" + self.gateway_id + "] starting mysensors " +
self.gateway_type + " gateway")
# load previously assigned node ids
if db.exists(self.assigned_ids_key):
self.assigned_ids = db.rangebyscore(self.assigned_ids_key,
"-inf",
"+inf",
withscores=False)
errors = 0
while True:
# connect to the configured gateway
if not self.connected:
self.connected = self.connect()
if not self.connected:
# something went wrong while connecting, sleep for a while and then try again
time.sleep(sleep_on_error)
continue
if self.gateway_type == "serial" or self.gateway_type == "ethernet":
# for serial and ethernet manage the loop manually by reading every single message
message = self.read()
if message is None:
# something went wrong while reading the message, increase the error counter
errors = errors + 1
time.sleep(1)
if errors > 10:
# too many consecutive errors, sleep for a while and then try to reconnect
log.error(
"[" + self.gateway_id +
"] Too many errors, will try reconnecting in a while"
)
time.sleep(sleep_on_error)
self.connected = False
# go and read a new message
continue
# parse the message
parsed = self.parse(message)
if parsed is None:
# something went wrong while parsing the message, increase the error counter
errors = errors + 1
time.sleep(1)
if errors > 10:
# too many consecutive errors, sleep for a while and then try to reconnect
log.error(
"[" + self.gateway_id +
"] Too many errors, will try reconnecting in a while"
)
time.sleep(sleep_on_error)
self.connected = False
# go and read a new message
continue
# parsed correctly, reset the error counter
errors = 0
elif self.gateway_type == "mqtt":
# for mqtt the loop is managed automatically with callbacks
self.gateway.loop()
# the loop should never end, if it will, sleep for a while then try to reconnect
time.sleep(sleep_on_error)
self.connected = False
continue
def get_chats():
'''Returns a list of the chats' information a user is in'''
required_args = ('token', )
if any(arg not in request.args for arg in required_args):
return abort(401)
token = request.args['token']
query = cursor.execute(
"""
SELECT COUNT(*)
FROM users
WHERE token = ?;
""", (token, ))
if not db.exists(query):
return jsonify(error=401, message="Invalid token")
query = cursor.execute(
"""
SELECT id
FROM users
WHERE token = ?;
""", (token, ))
user_id = query.fetchone()[0]
query = cursor.execute(
"""
SELECT updates.chat_id, chats.name
FROM updates
INNER JOIN chats
ON updates.chat_id = chats.id
WHERE updates.user_id = ?
GROUP BY chat_id;
""", (user_id, ))
result = [[row[0], row[1]] for row in query.fetchall()]
return jsonify(result)
def load_user(id):
if db.exists(id):
user = User()
user.id = id
user.password = db.get_pass(id)
return user
return None
def signup():
form = RegisterForm()
if form.validate_on_submit():
if not db.exists(form.username.data):
db.insert_into_users_db(form.username.data, form.password.data)
return redirect(url_for('login'))
return render_template('signup.html', form=form)
def expire(sensor):
total = 0
# define which stat to expire for each retention policy
policies = {
"realtime_days": [""],
"recent_days": [":hour:min", ":hour:avg", ":hour:max", "hour:rate"],
"history_days": [":day:min", ":day:avg", ":day:max", ":day:rate"],
}
# for each policy
for policy, stats in policies.iteritems():
# set the retention to the global configuration
retention = conf["sensors"]["retention"][policy]
# if the policy is overridden in the sensor configuration, update the retention
if "retention" in sensor and policy in sensor["retention"]:
retention = sensor["retention"][policy]
# if retention is 0, keep the data forever
if retention == 0: continue
# for each stat to expire
for stat in stats:
key = sensor['db_sensor'] + stat
if db.exists(key):
# if the key exists, delete old data
deleted = db.deletebyscore(
key, "-inf",
utils.now() - retention * conf["constants"]["1_day"])
log.debug("[" + sensor["module_id"] + "][" +
sensor["group_id"] + "][" + sensor["sensor_id"] +
"] expiring from " + key + " " + str(deleted) +
" items")
total = total + deleted
if total > 0:
log.info("[" + sensor["module_id"] + "][" + sensor["group_id"] + "][" +
sensor["sensor_id"] + "] expired " + str(total) + " items")
def login():
user_email = request.headers['user_email']
password = request.headers['password']
# Database verification
# If user is found in the database respond with <`user_status`: `found`> header
# and email_hash
if (user_email and password):
email_hash = exists(user_email, password)
if (email_hash != 101):
if (email_hash):
response = make_response()
response.headers['email_hash'] = email_hash
response.headers['user_status'] = 'found'
return response
# If user is not found in the database respond with <`user_status`: `not_found`> header
else:
response = make_response()
response.headers['user_status'] = 'not_found'
return response
else: #exists() returns database error 101, returns <`user_status`: `Try again later`>
response = make_response()
response.headers['user_status'] = 'Try again later'
return response
else: #Redirect to login page if credentials were missing
return redirect('/', 303)
def user_exists(username):
try:
formattedUsername = format_username(username)
return exists('user','username',formattedUsername)
except Exception as e:
logging.error(traceback.format_exc())
print("User existence check failed")
def grab(ircsock, channel, owner, index):
""" Take and assign a ticket to the messanger """
if db.exists(index):
db.grab(owner, index)
sendmsg(ircsock, channel, "Grabbed ticket: " + index +
", go fix it!!!")
else:
sendmsg(ircsock, channel + "No such ticket...")
def grab(ircsock, channel, owner, index):
""" Take and assign a ticket to the messanger """
if db.exists(index):
db.grab(owner, index)
sendmsg(ircsock, channel,
"Grabbed ticket: " + index + ", go fix it!!!")
else:
sendmsg(ircsock, channel + "No such ticket...")
def test_add_score():
session = db.get_session(SESSION)
db.create_user(session, 'kevin')
db.create_user(session, 'kevin', 22)
session = db.get_session()
user = db.exists(session, 'kevin')
assert_equal(22, user.score)
def test_add_score():
session = db.get_session(SESSION)
db.create_user(session, 'kevin')
db.create_user(session, 'kevin', 22)
session = db.get_session()
user = db.exists(session, 'kevin')
assert_equal(22, user.score)
def update_news():
news = get_news('https://habrahabr.ru/all/top50', n_pages=1)
for n in news:
(is_exists, ), = s.query(exists().where(News.id == n.get('id')))
if not is_exists:
s.add(News(**n))
s.commit()
redirect('/news')
def login():
form = LoginForm()
if form.validate_on_submit():
if db.exists(form.username.data):
pswd = db.get_pass(form.username.data)
if form.password.data == pswd:
user = User()
user.id = form.username.data
user.password = pswd
login_user(user)
return redirect(url_for('dashboard'))
return '<h1>Invalid username or password</h1>'
return render_template('login.html', form=form)
def get(username, session):
""" Return a list of who this person follows"""
text = _fetch(username)
if not text:
return
friends = _get_friends(text)
score = _get_score(text)
user = db.create_user(session, username, score)
for index, friend in enumerate(friends):
friend_record = db.exists(session, friend)
if not friend_record:
friend_record = db.create_user(session, friend)
_queue(friend)
_store(session, user.id, friend_record.id, index + 1)
def get(username, session):
""" Return a list of who this person follows"""
text = _fetch(username)
if not text:
return
friends = _get_friends(text)
score = _get_score(text)
user = db.create_user(session, username, score)
for index, friend in enumerate(friends):
friend_record = db.exists(session, friend)
if not friend_record:
friend_record = db.create_user(session, friend)
_queue(friend)
_store(session, user.id, friend_record.id, index + 1)
def save(sensor, force=False):
cache_timestamp = 0
# get the raw data from the cache
if db.exists(sensor["db_cache"]):
data = db.range(sensor["db_cache"], withscores=True)
cache_timestamp = data[0][0]
# if too old, refresh it
cache_expire_min = sensor["plugin"][
"cache_expire_min"] if "plugin" in sensor and "cache_expire_min" in sensor[
"plugin"] else conf["sensors"]["cache_expire_min"]
if force or (utils.now() - cache_timestamp
) > cache_expire_min * conf["constants"]["1_minute"]:
# if an exception occurred, skip this sensor
if poll(sensor) is None: return
# get the parsed data
measures = parse(sensor)
# store it into the database
store(sensor, measures)
def signup():
'''Creates a new user via email, username and password'''
required_args = ('name', 'email', 'username', 'password')
if any(arg not in request.args for arg in required_args):
return abort(401)
name = request.args['name']
email = request.args['email']
username = request.args['username'].lower()
password = request.args['password']
query = cursor.execute(
"""
SELECT COUNT(*)
FROM users
WHERE username = ?;
""", (username, ))
if db.exists(query):
return jsonify(error=401,
message="User with this username already exists")
config = configparser.ConfigParser()
config.read('./.settings')
domain = config['settings']['domain'].rstrip('/')
verification_code = util.generate_token()
link = f'{domain}/verify?token={verification_code}'
try:
verify.send_verification_email(email, link)
except httplib2.ServerNotFoundError:
return jsonify(error=404, message="Unable to reach Server")
cursor.execute(
"""
INSERT INTO users (
id, name, email, username, password, verification_code)
VALUES (?, ?, ?, ?, ?, ?);
""", (random.randrange(100_001, 1_000_000), name, email, username,
password, verification_code))
conn.commit()
return jsonify(success=True, message="Verification email sent")
def get_trip_match(start=date(2012, 11, 19), end=None):
delta = timedelta(days=1)
while True:
table_name = f'Trip2PosRap_{start.strftime("%Y%m%d")}'
if not db.exists(table_name):
break
df = db.query(f'SELECT * FROM public."{table_name}"')
print(
f'\n{datetime.now()} - {len(df.index)} rows fetched from {table_name}'
)
df["Timestamp"] = df['LokalTid'].map(
lambda t: int(datetime.combine(start, t).timestamp()))
start += delta
# Skip if number of rows is below threshold
if df.shape[0] < 32:
continue
# Group by TripId
trips = df.groupby(by='TripId', as_index=False)
print(f'{datetime.now()} - table preprocessing complete')
for _, trip in trips:
match = raw_match(trip)
if not match or not [
matching for matching in match['matchings']
if matching['confidence'] > 0.85
]:
continue
yield process_match(match, trip)
if (end != None and end <= start):
break
#! /usr/bin/env python3.2
import sys, os, db, config
if db.exists():
if '-f' in sys.argv:
os.remove(config.paths.db)
else:
raise Exception('The database file already exists, please remove it and try again.')
with db.get_connection(True) as connection:
cursor = connection.cursor()
cursor.executescript('''
create table text_area(
name not null,
primary key (name));
create table gallery_area(
name not null,
primary key (name));
create table text_area_content(
content not null,
version not null,
text_area_name not null,
foreign key (text_area_name) references text_area,
primary key (version, text_area_name));
create table uploaded_image(
id integer primary key autoincrement,
blob not null,
width,
def store(sensor, measures, ifnotexists=False):
# if an exception occurred, skip this sensor
if measures is None: return
# for each returned measure
for measure in measures:
# set the timestamp to now if not already set
if "timestamp" not in measure: measure["timestamp"] = utils.now()
# define the key to store the value
key = sensor["db_group"] + ":" + measure["key"]
# if ifnotexists is set, check if the key exists
if ifnotexists and db.exists(key):
log.debug("[" + sensor["module_id"] + "][" + sensor["group_id"] +
"][" + sensor["sensor_id"] +
"] key already exists, ignoring new value")
return
# delete previous values if needed
realtime_count = conf["sensors"]["retention"]["realtime_count"]
if "retention" in sensor and "realtime_count" in sensor["retention"]:
realtime_count = sensor["retention"]["realtime_count"]
if realtime_count > 0:
db.deletebyrank(key, 0, -realtime_count)
# if only measures with a newer timestamp than the latest can be added, apply the policy
realtime_new_only = conf["sensors"]["retention"]["realtime_new_only"]
if "retention" in sensor and "realtime_new_only" in sensor["retention"]:
realtime_new_only = sensor["retention"]["realtime_new_only"]
if realtime_new_only:
# retrieve the latest measure's timestamp
last = db.range(key, -1, -1)
if len(last) > 0:
last_timestamp = last[0][0]
# if the measure's timestamp is older or the same, skip it
if measure["timestamp"] <= last_timestamp:
log.debug("[" + sensor["module_id"] + "][" +
sensor["group_id"] + "][" + sensor["sensor_id"] +
"] (" +
utils.timestamp2date(measure["timestamp"]) +
") old event, ignoring " + measure["key"] +
": " + str(measure["value"]))
continue
# check if there is already something stored with the same timestamp
old = db.rangebyscore(key, measure["timestamp"], measure["timestamp"])
if len(old) > 0:
if old[0][1] == measure["value"]:
# if the value is also the same, skip it
log.debug("[" + sensor["module_id"] + "][" +
sensor["group_id"] + "][" + sensor["sensor_id"] +
"] (" + utils.timestamp2date(measure["timestamp"]) +
") already in the database, ignoring " +
measure["key"] + ": " + str(measure["value"]))
continue
else:
# same timestamp but different value, remove the old value so to store the new one
db.deletebyscore(key, measure["timestamp"],
measure["timestamp"])
# store the value into the database
log.info("[" + sensor["module_id"] + "][" + sensor["group_id"] + "][" +
sensor["sensor_id"] + "] (" +
utils.timestamp2date(measure["timestamp"]) + ") saving " +
measure["key"] + ": " +
utils.truncate(str(measure["value"])) +
conf["constants"]["formats"][sensor["format"]]["suffix"])
db.set(key, measure["value"], measure["timestamp"])
# re-calculate the derived measures for the hour/day
if "summarize" in sensor:
summarize(sensor, 'hour', utils.hour_start(measure["timestamp"]),
utils.hour_end(measure["timestamp"]))
summarize(sensor, 'day', utils.day_start(measure["timestamp"]),
utils.day_end(measure["timestamp"]))
def main(pages):
global _IMAGE_CNT, _VIDEO_CNT
for i in range(pages):
url = _GRAP_URL + '&page=' + str(i)
html_doc = urllib.urlopen(url).read()
if html_doc:
soup = BeautifulSoup(html_doc)
try:
# ============================================================
# 采集视频
# ============================================================
target_divs = soup.find_all('article', {'class': 'post-video'})
if target_divs and len(target_divs):
for td in target_divs:
# 资源标题
title = td.find('h2')['title']
video_src = td.find('embed')['src'].strip()
# FIXME url
video_id = _get_video_id('http://player.56.com/renrenshare_(.*).swf/1030_.*.swf', video_src)
if not video_id:
continue
# 获取视频信息
video_info = _get_video_info(video_id)
if video_info.get('info', None):
video_img = video_info['info']['bimg']
# 不抓取重复资源
if db.exists(video_src):
continue
resource_info = _deal_resource(video_img)
resource_info['url'] = video_src
resource = db.SpideResource(title, 'video', json.dumps(resource_info), video_src)
db.save(resource)
print '成功爬取视频资源!'
_VIDEO_CNT = _VIDEO_CNT + 1
else:
print 'Fail to get video_info by: %s %s' % (video_src, video_id)
except Exception, err:
print '[VideoError] ' + str(err)
try:
# ============================================================
# 采集图片
# ============================================================
target_divs = soup.find_all('article', {'class': ['post-photo', 'post-article']})
if target_divs and len(target_divs):
for td in target_divs:
post_content = td.find('div', {'class': 'post-content'})
# 资源标题
title = post_content.find('h2')['title']
images = post_content.find_all('img')
# 抓取只有一副图片的资源
if images and len(images) == 1:
image = images[0]
if image.get('data-src', None):
image_src = image['data-src'].strip()
else:
image_src = image['src'].strip()
# 不抓取重复资源
if db.exists(image_src):
continue
resource_info = _deal_resource(image_src)
resource = db.SpideResource(title, 'image', json.dumps(resource_info), image_src)
db.save(resource)
print '成功爬取图片资源!'
_IMAGE_CNT = _IMAGE_CNT + 1
except Exception, err:
print '[ImageError] ' + str(err)
def ismarked(self):
sql = 'select id from msg where id = %d and status = 1' % self.id
if (db.exists(sql)):
return True
else:
return False
def new_chat():
'''Creates a new group chat and adds you in it'''
required_args = ('token', 'name', 'address')
if any(arg not in request.args for arg in required_args):
return abort(401)
token = request.args['token']
query = cursor.execute(
"""
SELECT COUNT(*)
FROM users
WHERE token = ?;
""", (token, ))
if not db.exists(query):
return jsonify(error=401, message="Invalid token")
query = cursor.execute(
"""
SELECT id
FROM users
WHERE token = ?;
""", (token, ))
user_id = query.fetchone()[0]
name = request.args['name']
address = request.args['address'].lower()
query = cursor.execute(
"""
SELECT COUNT(*)
FROM chats
WHERE address = ?;
""", (address, ))
if db.exists(query):
return jsonify(error=401, message="Chat address already in use")
while True:
chat_id = random.randrange(-1_999_999, -1_000_000)
query = cursor.execute(
"""
SELECT COUNT(*)
FROM chats
WHERE id = ?;
""", (chat_id, ))
if not db.exists(query):
break
cursor.execute(
"""
INSERT INTO chats
VALUES (?, ?, ?);
""", (chat_id, name, address))
cursor.execute(
"""
INSERT INTO updates (
id, user_id, chat_id, type, timestamp)
VALUES (?, ?, ?, ?, ?);
""", (1, user_id, chat_id, 1, time.time_ns()))
conn.commit()
return jsonify(id=chat_id)
tables.run()
print('You have now entered passman.\n'\
'The source code can be found at https://github.com/binex-dsk/PyPassMan.\n'\
'Please report any bugs you find there.\n'\
'For available commands, type help.')
while True:
choice = input("passman> ")
if choice == 'help':
print(
'add: add a password\ndelete: delete a password\nedit: edit an entry\nview: view a password\ntips: tips for password management\ninfo: some info on passman\nexit: exit out of the program'
)
elif choice == 'add':
while True:
name = input("What name should this entry have? ")
if db.exists('data', {'name': name}):
print('An entry with that name already exists.')
continue
break
description = input("What should be the description of this entry? ")
while True:
password = None
passchoice = input("Do you want to input your own password? ")
if passchoice == 'yes':
while True:
password = getpass.getpass(prompt="Type your password: "******"Having a short password is a security risk. "\
"The shorter the password, the easier it is to guess. "\
"Please use a length of at least 8 characters.")
continue
def ismarked(self): sql = 'select id from msg where id = %d and status = 1' % self.id if(db.exists(sql)): return True else: return False
import db
db.silent(True)
if not db.exists("./version"):
class setup1(db.db):
setup = {
"major":db.number(),
"middle":db.number(),
"minor":db.number(),
}
name = "version"
table1 = setup1()
table1.new_record(major=0,middle=0,minor=0)
table1.save("version")
table1 = db.load("version")
version = table1.query().get()
print(version)
print("version {}.{}.{}".format(version["major"],version["middle"],version["minor"]))
print("update major [0]")
print("update middle [1]")
print("update minor [2]")
print("reset [3]")
while True:
i = int(input(">>>"))
if i in [0,1,2]:
if i == 0:
version["major"] += 1
elif i == 1:
version["middle"] += 1
def send_message():
'''Sends a message in a chat'''
required_args = ('token', 'chat_id', 'message')
if any(arg not in request.args for arg in required_args):
return abort(401)
token = request.args['token']
query = cursor.execute(
"""
SELECT COUNT(*)
FROM users
WHERE token = ?;
""", (token, ))
if not db.exists(query):
return jsonify(error=401, message="Invalid token")
query = cursor.execute(
"""
SELECT id
FROM users
WHERE token = ?;
""", (token, ))
user_id = query.fetchone()[0]
chat_id = request.args['chat_id']
try:
chat_id = int(chat_id)
except ValueError:
return abort(401)
msg_body = request.args['message']
query = cursor.execute(
"""
SELECT COUNT(*)
FROM updates
WHERE chat_id = ?
AND user_id = ?;
""", (chat_id, user_id))
if not db.exists(query):
return jsonify(error=401,
message="You are unauthorized to view this chat")
query = cursor.execute(
"""
SELECT id
FROM updates
WHERE chat_id = ?
ORDER BY id DESC
LIMIT 1;
""", (chat_id, ))
latest_update_id = query.fetchone()[0]
cursor.execute(
"""
INSERT INTO updates (
id, user_id, chat_id, type, timestamp, body)
VALUES (?, ?, ?, ?, ?, ?)
""",
(latest_update_id + 1, user_id, chat_id, 0, time.time_ns(), msg_body))
conn.commit()
return jsonify(success=True)
for date, amount in result.items():
rate = rates.get(date)
amount_tr += amount
amount_rub += (amount * rate)
msg = f"TOTAL: {amount_tr:.2f} TRY, {amount_rub:.2f} RUB"
context.bot.send_message(chat_id=update.effective_chat.id, text=msg)
def main() -> None:
token = os.environ.get("TG_TOKEN")
updater = Updater(token)
dispatcher = updater.dispatcher
dispatcher.add_handler(CommandHandler("start", start))
dispatcher.add_handler(CommandHandler("help", help))
dispatcher.add_handler(CommandHandler("add", add_expense, pass_args=True))
dispatcher.add_handler(CommandHandler("days", get_expenses_by_days))
dispatcher.add_handler(CommandHandler("total", get_total))
dispatcher.add_handler(CommandHandler("total_rub", get_total_rub))
dispatcher.add_error_handler(error_callback)
updater.start_polling()
logger.info("started bot")
updater.idle()
if __name__ == "__main__":
if not db.exists():
db.init()
main()
def testC(self):
result = db.exists(tests.username)
self.assertTrue(result)