def post(self):
data = request.get_json()
user = data.get('user')
message = data.get('message')
currentDateTime = datetime.datetime.now()
connect_database().insert(
"INSERT INTO message (date, user, message) VALUES ('{}', '{}', '{}')"
.format(str(currentDateTime), user, message))
return jsonify({'resp': message})
def get_category():
name = []
value = []
# kết nối database
db = database.connect_database("news")
# group và in số lượng từng thể loại
print('\n SỐ LƯỢNG BÀI VIẾT THEO TỪNG THỂ LOẠI:\n')
for x in db.aggregate([{
'$group': {
'_id': '$category',
'count': {
'$sum': 1
}
}
}]):
print(f"{x['_id']}: {x['count']}")
name.append(x['_id'])
value.append(x['count'])
# vẽ biểu đồ
plt.barh(name, value, color='blue')
plt.title("THỐNG KÊ SỐ LƯỢNG BÀI VIẾT THEO TỪNG THỂ LOẠI")
plt.show()
def find_week_end(cls, time_sheet_id):
day_name_map = {1: 'SATURDAY',
2: 'SUNDAY',
3: 'MONDAY',
4: 'TUESDAY',
5: 'WEDNESDAY',
6: 'THURSDAY',
7: 'FRIDAY'}
session_time = 0
vpn_log = connect_database()
if not vpn_log:
return "Database not connected"
cursor = vpn_log.cursor()
try:
select_weekends = """SELECT weekends FROM vpn_log.time_sheet_weekends where time_sheet_id = %s and deleted_at is NULL"""
cursor.execute(select_weekends, (int(time_sheet_id),))
weekends = cursor.fetchone()
weekends_array = []
if weekends:
for row in weekends:
day_name = day_name_map[row]
weekends_array.append(day_name.lower())
cursor.close()
vpn_log.close()
return weekends_array
except Exception as e:
cursor.close()
vpn_log.close()
print('Can not fetch activity info: ' + str(e))
return {
'error': '1111',
'message': 'Can not fetch. SQL error'
}
def insert_in_report_table(cls, *data):
web_user_id, ldap_user_name, vpn_user_id, session_time_in_hour, idle_time_in_hour, login_date, day_type = data
vpn_log = connect_database()
if not vpn_log:
return "Database not connected"
cursor = vpn_log.cursor()
try:
insert_idlebusy = """INSERT INTO vpn_log.idle_busy_per_day
(
user_id,
ldap_user_name,
vpn_user_id,
busy_in_hour,
idle_in_hour,
date,
day_type)
VALUES
(%s, %s,%s, %s, %s,%s, %s)"""
val = (web_user_id, ldap_user_name, vpn_user_id,
session_time_in_hour, idle_time_in_hour, login_date, day_type)
cursor.execute(insert_idlebusy, val)
vpn_log.commit()
print('Added user idle busy info!')
cursor.close()
vpn_log.close()
except Exception as e:
cursor.close()
vpn_log.close()
print('User idle busy not inserted.Error happened: ' + str(e))
def post(self):
data = request.get_json()
uname = data.get('username')
pwd = data.get('password')
records = connect_database().select(
"SELECT * FROM account WHERE username = '******' AND password = '******'".
format(uname, pwd))
return jsonify({'resp': 'true', 'user': uname})
def setup_database():
conn = db.connect_database('test.db')
#reset db for testing
db.drop_tables(conn)
db.build_database(conn)
return conn
def readId():
with open('session_id.txt', 'r') as f:
lines = f.readlines()
key = lines[0].strip()
encrpted_text = lines[1].strip()
fer = Fernet(key.encode())
session_id = (fer.decrypt(encrpted_text.encode())).decode("utf-8")
result = connect_database()
result[1].execute("select username from register where reg_id=%s",
session_id)
rows = result[1].fetchall()
result[0].commit()
result[0].close()
return rows
def search_news():
search_text = input('Từ khóa: ')
# kết nối database
db = database.connect_database("news")
all_news = []
for x in db.aggregate([{
'$group': {
'_id': '$category',
'count': {
'$sum': 1
}
}
}]):
for doc in db.find({'category': x['_id']}):
text = remove_stopword.remove_stop_word(
doc['title']) + '|' + remove_stopword.remove_stop_word(
doc['quote'])
all_news.append(ViTokenizer.tokenize(text))
vector = TfidfVectorizer()
x = vector.fit_transform(all_news)
search_text = search_text.lower()
te = word_tokenize(search_text, format='text')
te = remove_stopword.remove_stop_word(te)
te = vector.transform([te])
length = str(te).split('\t')
if len(length) > 1:
re = cosine_similarity(x, te)
result = []
for i in range(len(re)):
result.append(re[i][0])
count = 0
for i in np.argsort(result)[-20:][::-1]:
count += 1
print("{}. {}".format(count,
all_news[i].split('|')[0].replace("_", " ")))
else:
print(f'Không tìm thấy bài viết có từ khóa "{search_text}"!')
def sync_access_uuid(cls, uuid):
vpn_log = connect_database()
if not vpn_log:
return "Database not connected"
cursor = vpn_log.cursor()
try:
sync_acccess = """INSERT INTO vpn_log.synced_access_hist
(access_hist_uuid)
VALUES
(%s)"""
val = (str(uuid),)
cursor.execute(sync_acccess, val)
vpn_log.commit()
print('Synced access uuid info!')
cursor.close()
vpn_log.close()
except Exception as e:
cursor.close()
vpn_log.close()
print('Sync uuid failed .Error happened: ' + str(e))
def update_record_in_report_table(cls, *data):
web_user_id, ldap_user_name, vpn_user_id, new_busy, new_idle, date, day_type = data
vpn_log = connect_database()
if not vpn_log:
return "Database not connected"
cursor = vpn_log.cursor()
try:
cursor.execute("""
UPDATE vpn_log.idle_busy_per_day
SET user_id=%s,busy_in_hour=%s,idle_in_hour=%s
WHERE ldap_user_name=%s and date=%s
""", (web_user_id, new_busy, new_idle, ldap_user_name, date))
vpn_log.commit()
print('Updated user idle busy info in users!')
cursor.close()
vpn_log.close()
except Exception as e:
cursor.close()
vpn_log.close()
print('Update failed during idle busy info. Error happened: ' + str(e))
def check_record_exists_in_report(cls, vpn_user_name, date):
vpn_log = connect_database()
if not vpn_log:
return "Database not connected"
cursor = vpn_log.cursor()
try:
select_report = """SELECT busy_in_hour,idle_in_hour FROM vpn_log.idle_busy_per_day where ldap_user_name = %s and date = %s"""
cursor.execute(select_report, (str(vpn_user_name), date))
record = cursor.fetchone()
cursor.close()
vpn_log.close()
return record
except Exception as e:
cursor.close()
vpn_log.close()
print('Can not fetch activity info: ' + str(e))
return {
'error': '1111',
'message': 'Can not fetch. SQL error'
}
def check_if_in_time_sheet(cls, vpn_user_name):
vpn_log = connect_database()
if not vpn_log:
return "Database not connected"
cursor = vpn_log.cursor()
try:
select_date_time = """SELECT start_datetime,end_datetime,id FROM vpn_log.time_sheets where ldap_user_name = %s and deleted_at is null order by id desc limit 1"""
cursor.execute(select_date_time, (str(vpn_user_name),))
date_time_info = cursor.fetchone()
cursor.close()
vpn_log.close()
return date_time_info
except Exception as e:
cursor.close()
vpn_log.close()
print('Can not fetch activity info: ' + str(e))
return {
'error': '1111',
'message': 'Can not fetch. SQL error'
}
def access_hist_data_dict(cls, uuid):
vpn_log = connect_database()
if not vpn_log:
return "Database not connected"
cursor = vpn_log.cursor()
try:
select_access = """SELECT user_id,login_time,logout_time FROM vpn_log.vpn_user_access_history where access_hist_uuid = %s and DATE(login_time) != CURDATE() and (logout_time IS NOT NULL or logout_time != 0)"""
cursor.execute(select_access, (str(uuid),))
access_info = cursor.fetchone()
access_info_dict = None
if access_info:
select_ldap_username = """SELECT user_name FROM vpn_log.vpn_users where user_id = %s"""
cursor.execute(select_ldap_username, (int(access_info[0]),))
user_info = cursor.fetchone()
ldap_user_name = user_info[0]
select_web_id = """SELECT id FROM vpn_log.users where ldap_user_name = %s"""
cursor.execute(select_web_id, (str(ldap_user_name),))
web_user_info = cursor.fetchone()
web_user_id = None
if web_user_info:
web_user_id = web_user_info[0]
access_info_dict = {
'web_user_id': web_user_id,
'ldap_user_name': ldap_user_name,
'vpn_user_id': access_info[0],
'login_time': access_info[1],
'logout_time': access_info[2]
}
cursor.close()
vpn_log.close()
return access_info_dict
except Exception as e:
cursor.close()
vpn_log.close()
print('Can not fetch activity info: ' + str(e))
return {
'error': '1111',
'message': 'Can not fetch. SQL error'
}
def create_cube(cai):
ethdb = connect_database()
db_facts = collection.Collection(ethdb, "%s_facts" % cai['name'])
for cube in cai['cubes']:
print('Generating: %s cube...' % cube['name'])
cube_facts = db_facts.find({"contract": cai['address'], "type": cube['fact_type'], "name": cube['fact_name']})
db_cube = collection.Collection(ethdb, '%s_%s' % (cai['name'], cube['name']))
db_cube.drop()
for fact in cube_facts:
record = dict()
for dimension in cube['dimensions']:
if dimension['field'][:9] == "arguments":
if dimension['field'][10:] in fact['arguments']:
record[dimension['name']] = fact['arguments'][dimension['field'][10:]]['value']
else:
if dimension['field'] in fact:
record[dimension['name']] = fact[dimension['field']]
if 'off_chain_details' in dimension:
if dimension['off_chain_details'] in cai:
if str(record[dimension['name']]) in cai[dimension['off_chain_details']]:
oc_dim_attributes = dict()
oc_details = cai[dimension['off_chain_details']][str(record[dimension['name']])]
for key in oc_details:
oc_dim_attributes['%s %s [OC]' % (dimension['name'], key)] = oc_details[key]
record.update(oc_dim_attributes)
for measure in cube['measures']:
try:
value = eval(measure['value'])
record[measure['name']] = value
except SyntaxError:
pass
if len(record) > 0:
db_cube.insert_one(record)
return
def get_news(url):
db = database.connect_database("news")
soup = get_soup(url)
a = soup.find_all('a', class_='news-item__sapo')
for x in a:
try:
soup_in_link = get_soup('https://dantri.com.vn' + x.attrs['href'])
key = {'_id': get_id(x.attrs['href'])}
value = {
'$set': {
'title': x.attrs['title'],
'link': 'https://dantri.com.vn' + x.attrs['href'],
'quote': x.text,
'image': get_image(soup_in_link),
'category': get_category(soup_in_link),
'content': get_content(soup_in_link)
}
}
database.insert_database(key, value, db)
except:
pass
def unsynced_access_uuids(cls):
vpn_log = connect_database()
if not vpn_log:
return "Database not connected"
cursor = vpn_log.cursor()
try:
unsynced_uuids = """select access_hist_uuid from vpn_user_access_history where access_hist_uuid NOT IN
(select access_hist_uuid from synced_access_hist)"""
cursor.execute(unsynced_uuids)
uuids = cursor.fetchall()
cursor.close()
vpn_log.close()
return uuids
except Exception as e:
cursor.close()
vpn_log.close()
print('Can not fetch unsynced access uuids: ' + str(e))
return {
'error': '1111',
'message': 'Can not fetch. SQL error'
}
def common_word():
# kết nối database
db = database.connect_database("news")
for category in db.aggregate([{'$group': {'_id': '$category'}}]):
my_string = ''
for col in db.find({'category': category['_id']}):
my_string += remove_stopword.remove_stop_word(col['content'] +
col['title'] +
col['quote'])
list_string = [my_string]
vector = CountVectorizer().fit(list_string)
bag_of_words = vector.transform(list_string)
sum_words = bag_of_words.sum(axis=0)
words_freq = [(word, sum_words[0, idx])
for word, idx in vector.vocabulary_.items()]
words_freq = sorted(words_freq, key=lambda x: x[1], reverse=True)
print(
f"\nThể loại: {category['_id']} \n - Từ xuất hiện nhiều nhất: {words_freq[0][0]} \n - Số lần xuất hiện: {words_freq[0][1]}"
)
def parse_contract(contract_address, contract_name):
ethdb = connect_database()
db_contracts = ethdb.contracts
db_transactions = ethdb.transactions
db_facts = collection.Collection(ethdb, "%s_facts" % contract_name)
db_facts.drop()
contract_abi = db_contracts.find({'address': contract_address})[0]
contract_txs = db_transactions.find({'to': contract_address})
for i, tx in enumerate(contract_txs):
if not i % 100:
print('.', end='', flush=True)
if not i % 1000:
print(i)
contract_facts = decode_contract_tx(tx, contract_abi, db_contracts)
for fact in contract_facts:
db_facts.insert_one(fact)
return
def get(self, idms):
records = connect_database().select(
"SELECT * FROM message WHERE id > {}".format(idms))
return jsonify(records)
def connect_database(self):
result = connect_database()
self.con = result[0]
self.cursor = result[1]
def one(ctx, interactive, enable_phantomjs, scripts):
"""
One mode not only means all-in-one, it runs every thing in one process over
tornado.ioloop, for debug purpose
"""
ctx.obj['debug'] = False
g = ctx.obj
g['testing_mode'] = True
if scripts:
from database.local.projectdb import ProjectDB
g['projectdb'] = ProjectDB(scripts)
if g.get('is_taskdb_default'):
g['taskdb'] = connect_database('sqlite+taskdb://')
if g.get('is_resultdb_default'):
g['resultdb'] = None
if enable_phantomjs:
phantomjs_config = g.config.get('phantomjs', {})
phantomjs_obj = ctx.invoke(phantomjs, **phantomjs_config)
if phantomjs_obj:
g.setdefault('phantomjs_proxy', '127.0.0.1:%s' % phantomjs_obj.port)
else:
phantomjs_obj = None
result_worker_config = g.config.get('result_worker', {})
if g.resultdb is None:
result_worker_config.setdefault('result_cls',
'pyspider.result.OneResultWorker')
result_worker_obj = ctx.invoke(result_worker, **result_worker_config)
processor_config = g.config.get('processor', {})
processor_config.setdefault('enable_stdout_capture', False)
processor_obj = ctx.invoke(processor, **processor_config)
fetcher_config = g.config.get('fetcher', {})
fetcher_config.setdefault('xmlrpc', False)
fetcher_obj = ctx.invoke(fetcher, **fetcher_config)
scheduler_config = g.config.get('scheduler', {})
scheduler_config.setdefault('xmlrpc', False)
scheduler_config.setdefault('scheduler_cls',
'pyspider.scheduler.OneScheduler')
scheduler_obj = ctx.invoke(scheduler, **scheduler_config)
scheduler_obj.init_one(ioloop=fetcher_obj.ioloop,
fetcher=fetcher_obj,
processor=processor_obj,
result_worker=result_worker_obj,
interactive=interactive)
if scripts:
for project in g.projectdb.projects:
scheduler_obj.trigger_on_start(project)
try:
scheduler_obj.run()
finally:
scheduler_obj.quit()
if phantomjs_obj:
phantomjs_obj.quit()
def connect_db(ctx, param, value):
if not value:
return
return utils.Get(lambda: connect_database(value))
def cli(ctx, **kwargs):
"""
A powerful spider system in python.
"""
if kwargs['add_sys_path']:
sys.path.append(os.getcwd())
logging.config.fileConfig(kwargs['logging_config'])
# get db from env
for db in ('taskdb', 'projectdb', 'resultdb'):
if kwargs[db] is not None:
continue
if os.environ.get('MYSQL_NAME'):
kwargs[db] = utils.Get(lambda db=db: connect_database(
'sqlalchemy+mysql+%s://%s:%s/%s' % (
db, os.environ['MYSQL_PORT_3306_TCP_ADDR'],
os.environ['MYSQL_PORT_3306_TCP_PORT'], db)))
elif os.environ.get('MONGODB_NAME'):
kwargs[db] = utils.Get(lambda db=db: connect_database(
'mongodb+%s://%s:%s/%s' % (
db, os.environ['MONGODB_PORT_27017_TCP_ADDR'],
os.environ['MONGODB_PORT_27017_TCP_PORT'], db)))
elif ctx.invoked_subcommand == 'bench':
if kwargs['data_path'] == './data':
kwargs['data_path'] += '/bench'
shutil.rmtree(kwargs['data_path'], ignore_errors=True)
os.mkdir(kwargs['data_path'])
if db in ('taskdb', 'resultdb'):
kwargs[db] = utils.Get(lambda db=db: connect_database('sqlite+%s://' % (db)))
else:
kwargs[db] = utils.Get(lambda db=db: connect_database('sqlite+%s:///%s/%s.db' % (
db, kwargs['data_path'], db[:-2])))
else:
if not os.path.exists(kwargs['data_path']):
os.mkdir(kwargs['data_path'])
kwargs[db] = utils.Get(lambda db=db: connect_database('sqlite+%s:///%s/%s.db' % (
db, kwargs['data_path'], db[:-2])))
kwargs['is_%s_default' % db] = True
# create folder for counter.dump
if not os.path.exists(kwargs['data_path']):
os.mkdir(kwargs['data_path'])
# message queue, compatible with old version
if kwargs.get('message_queue'):
pass
elif kwargs.get('amqp_url'):
kwargs['message_queue'] = kwargs['amqp_url']
elif os.environ.get('RABBITMQ_NAME'):
kwargs['message_queue'] = ("amqp://*****:*****@%(RABBITMQ_PORT_5672_TCP_ADDR)s"
":%(RABBITMQ_PORT_5672_TCP_PORT)s/%%2F" % os.environ)
elif kwargs.get('beanstalk'):
kwargs['message_queue'] = "beanstalk://%s/" % kwargs['beanstalk']
for name in ('newtask_queue', 'status_queue', 'scheduler2fetcher',
'fetcher2processor', 'processor2result'):
if kwargs.get('message_queue'):
kwargs[name] = utils.Get(lambda name=name: connect_message_queue(
name, kwargs.get('message_queue'), kwargs['queue_maxsize']))
else:
kwargs[name] = connect_message_queue(name, kwargs.get('message_queue'),
kwargs['queue_maxsize'])
# phantomjs-proxy
if kwargs.get('phantomjs_proxy'):
pass
elif os.environ.get('PHANTOMJS_NAME'):
kwargs['phantomjs_proxy'] = os.environ['PHANTOMJS_PORT_25555_TCP'][len('tcp://'):]
ctx.obj = utils.ObjectDict(ctx.obj or {})
ctx.obj['instances'] = []
ctx.obj.update(kwargs)
if ctx.invoked_subcommand is None and not ctx.obj.get('testing_mode'):
ctx.invoke(all)
return ctx
from datetime import datetime
from database import connect_database
from contract import get_abi
# main loop
#
start_timestamp = datetime.now()
ethdb = connect_database()
db_contracts = ethdb.contracts
db_transactions = ethdb.transactions
contract_txs = db_transactions.find({'contractAddress': {'$ne': None}})
i = 0
for tx in contract_txs:
if not i // 100:
print('.', end='', flush=True)
contract_address = tx['contractAddress']
decoded, functions, events = get_abi(contract_address)
contract = dict(address=contract_address,
creator=tx['from'],
creation_time=tx['timestamp'],
creation_tx=tx['hash'],
decoded=decoded,
functions=functions,
events=events)
db_contracts.insert_one(contract)
def setUpClass(self):
self.resultdb = database.connect_database('mongodb+resultdb://localhost/pyspider_test_resultdb')
if reset == 1:
window.close()
#recursively launch the window again with the new info
run_simple_gui(conn, completedToday)
closed = 1
reset = 0
window.refresh()
if closed == 0:
window.close()
# simplfy and move stuff to other functions
# move main to another file
if __name__ == "__main__":
conn = db.connect_database(DATABASE_PATH)
if len(sys.argv) >= 2 and sys.argv[1] == "full":
layout = create_layout(conn)
window = sg.Window("Roasting Log", layout)
while True:
event, values = window.read()
if event in (sg.WIN_CLOSED, 'Cancel'):
break
# increment
x = re.findall("-increment_\d*-", event)
if len(x) != 0:
def setUpClass(self):
self.taskdb = database.connect_database('mongodb+taskdb://localhost:27017/pyspider_test_taskdb')
def setUpClass(self):
self.projectdb = database.connect_database('mongodb+projectdb://localhost/pyspider_test_projectdb')
def setUpClass(self):
self.taskdb = database.connect_database('mysql+taskdb://localhost/pyspider_test_taskdb')
def setUpClass(self):
self.resultdb = database.connect_database('sqlite+resultdb://')
def setUpClass(self):
self.projectdb = database.connect_database('sqlite+projectdb://')
def setUpClass(self):
self.taskdb = database.connect_database('sqlite+taskdb://')
sumNouns = 0
for noun in total_nouns:
sumNouns += noun.num_occur
# calculate cost per noun in milliseconds
costPerNoun = (elapsedTime * 1000) / sumNouns
costPerNounStr = "Cost per noun: " + str(round(costPerNoun, 3)) + " ms" # used in .csv file
if args.database is True:
# Get server information from user and create a new database in the server
(session_host, session_user, session_password) = database.get_server_info()
db_name = database.create_database(session_host, session_user, session_password)
# Create connection to this new database & store database connection object for further use
connection = database.connect_database(session_host, session_user, session_password, db_name)
# Create tables in database
database.create_tables(connection)
# Insert information into tables
database.insert_documents(docAttributes, connection)
database.insert_sentences(total_sentences, connection)
database.insert_nouns(total_nouns, connection)
database.insert_noun_in_sent(total_nouns, connection)
connection.close()
print("Data has been successfully exported to the database %s", db_name)
else:
# Open csv files to write to
if docInfo.title != None:
