def pwLogin(name, pw):
#链接数据库查询用户资料
db = DBManager(ZERO_DB_NAME)
cu = db.cursor()
sql = "".join(["select * from user where name = '", name, "'"])
cu.execute(sql)
data = cu.fetchone()
cu.close()
if data == None:
rt = dict(code=ZERO_USERNAME_ERROR)
return json.dumps(rt)
if data[3] != pw:
rt = dict(code=ZERO_PASSWORD_ERROR)
return json.dumps(rt)
#计算token
token = ""
if glb.ZeroAccountToken['key'].has_key(name) and \
(datetime.datetime.now() - glb.ZeroAccountToken['time'][name]).days < glb.ZeroAccountTokenTimeout:
token = glb.ZeroAccountToken['key'][name]
#更新token时间
glb.ZeroAccountToken['time'][name] = datetime.datetime.now()
else:
while True:
token = glb.strRandom(32)
if glb.ZeroAccountToken['token'].has_key(token) == False:
break
glb.ZeroAccountToken['key'][name] = token
glb.ZeroAccountToken['time'][name] = datetime.datetime.now()
glb.ZeroAccountToken['token'][token] = name
rt = dict(code=0,name=data[1],nickname=data[2],token=token)
return json.dumps(rt)
def __init__(self):
"""Init main class."""
doScrape = True
self.load_config()
self.init_config()
self.myDB = DBManager(self.config["BASIC"]["DBLocation"])
self.plugin_source = self.plugin_base.make_plugin_source(
searchpath=['./scrapers'])
for plugin_name in self.plugin_source.list_plugins():
plugin = self.plugin_source.load_plugin(plugin_name)
plugin.Scraper().setup(self)
doScrape = "-noscrape" not in sys.argv
doDl = "-nodl" not in sys.argv
if "-add" in sys.argv:
i = sys.argv.index("-add")
newShow = sys.argv[i + 1]
newLang = sys.argv[i + 2]
self.myDB.orderShow(newShow, newLang)
if doScrape:
self.scrape()
self.save_config()
self.myDB.matchEpisodes()
if doDl:
self.myDL = Downloader(self)
self.myDL.downloadQueue()
def send_notification(telegram_id, text):
# check if the user is a registered one
dbm = DBManager()
if dbm.exist_user_by_telegram_id(telegram_id):
logger.info(send_message(text=text, chat_id=telegram_id))
return HTTPResponse(status=200)
else:
return HTTPResponse(status=400)
def __init__(self):
super().__init__()
self.setupUi(self)
self.db_manager = DBManager('questions.db')
self.questions = []
self.load_questions()
self.bind_button_events()
self.selected_question = None
self.item_index = None
def test_aud_target(self, target_id):
db_conn = self.get_conn_conf_by_id(target_id)
# make a connection to database
if db_conn:
if 'envtype' in db_conn.keys():
del db_conn['envtype']
target_db = DBManager(True, True, **db_conn)
target_db.execute_query(sqls4mysql['dblist'])
target_db.close()
def account_check(telegram_id):
# return if user exist + username + role
# e.g. (False, None, None) or (12345, 'Pepet', 'user')
dbm = DBManager()
resultset = dbm.get_user(telegram_id)
logger.info(resultset)
if len(resultset) == 0:
return False, None, None
else:
# its a shame there is no named tuple
return (True, resultset[1], resultset[3])
def __init__(self,
api_key,
path_db,
period_delay=10,
time_delay=5,
num_spots_max=20):
self.client = googlemaps.Client(api_key)
self.db = DBManager(path='./spots_tools.db')
self.period_delay = period_delay
self.time_delay = time_delay
self.num_spots_max = num_spots_max
def __init__(self):
db_path = joinpath(globals.data_dir, globals.db_name)
#if not exists(db_path): create in db manager if not present
# log.error('db file not found')
# raise FBError
self._db = DBManager(db_path)
self._db.connect()
self._types = "(type = 46 OR type = 80 OR type = 128 OR type = 247 OR type = 308 OR type = 60)"
self._fql_limit = globals.max_fb_api_calls
def Mongo_init():
decimal_c = DecimalCodec()
date_c = DateCodec()
type_registry = TypeRegistry([decimal_c, date_c])
codec_options = CodecOptions(type_registry=type_registry)
man = MongoManager(codec_options=codec_options,
user="******",
host="cluster0.xjgrr.mongodb.net",
passwd="GWSgnYU4pu7zs2S",
db="DataTracker")
DBManager.store_data("mongo", man)
def tokenLogin(name, token):
if glb.ZeroAccountToken['key'].has_key(name) and (datetime.datetime.now() - glb.ZeroAccountToken['time'][name]).days < glb.ZeroAccountTokenTimeout and glb.ZeroAccountToken['key'][name] == token:
db = DBManager(ZERO_DB_NAME)
cu = db.cursor()
sql = "".join(["select * from user where name = '", name, "'"])
cu.execute(sql)
data = cu.fetchone()
cu.close()
if data == None:
rt = dict(code=ZERO_USERNAME_ERROR)
return json.dumps(rt)
rt = dict(code=0,name=data[1],nickname=data[2],token=token)
return json.dumps(rt)
else:
rt = dict(code=ZERO_PASSWORD_ERROR)
return json.dumps(rt)
def remove_image(self, column, username):
with DBManager(self.file) as c:
c.execute('''
UPDATE users
SET {} = ?
WHERE username = ?'''.format(column),
('', username))
def store_image_fn(self, column, filename, username):
with DBManager(self.file) as c:
c.execute('''
UPDATE users
SET {} = ?
WHERE username = ?'''.format(column),
(filename, username))
def __init__(self):
self.function_dict = {
self.choose_map_random: "choose_map_random",
1: "no"
}
self.db = DBManager()
self.map_manager = MapManager(self.db)
def carregarDB(self):
db = DBManager(user=self.__cnfg__.get_db_user(),
senha=self.__cnfg__.get_db_passwd(),
end=self.__cnfg__.get_db_url(),
port=self.__cnfg__.get_db_port(),
database=self.__cnfg__.get_db_name())
return db
def login_user(self, username, password):
with DBManager(self.file) as c:
c.execute('''
select username from users
where username = ? and password = ?;
''',
(username, password))
return c.fetchone()
def register_user(self, username, password):
with DBManager(self.file) as c:
c.execute('''
INSERT INTO users (username, password) VALUES (?, ?)
''',
(username, password))
# Verify the user made it into the database
return self.user_exists(username)
def __init__(self):
print _('TV-Maxe Channel Editor is starting...')
self.gui = gtk.Builder()
self.gui.add_from_file('ceditor.glade')
self.gui.connect_signals({
"quit": self.quit,
"deschide": self.deschide, #open
"openFile": self.openFile,
"openURL": self.openURL,
"hideOpenURL": self.hideOpenURL,
"goURL": self.goURL,
"saveList": self.saveList,
"showAddWindow": self.showAddWindow,
"hideAddWindow": self.hideAddWindow,
"addChannel": self.addChannel,
"editChannel": self.editChannel,
"hideChannelEditor": self.hideChannelEditor,
"saveChannel": self.saveChannel,
"deleteChannel": self.deleteChannel,
"uploadImage": self.uploadImage,
"salveaza": self.salveaza,
"addChannelURL": self.addChannelURL,
"addChannelEdit": self.addChannelEdit,
"removeChannelURL": self.removeChannelURL,
"addAudio": self.addAudio,
"addAudioEdit": self.addAudioEdit,
"removeAudio": self.removeAudio,
"saveNew": self.saveNew,
"saveInfo": self.saveNew2,
"hideInfo": self.hideInfo,
"on_entry2_changed": self.on_entry2_changed,
"hideLogoWin": self.hideLogoWin,
"selectIcon": self.selectIcon
})
self.imgur = Imgur()
self.imageFinder = imageFinder(self)
self.db = DBManager(':memory:')
self.db.build()
self.gui.get_object('cellrenderertext12').set_property(
'editable', True)
self.gui.get_object('cellrenderertext10').set_property(
'editable', True)
self.gui.get_object('cellrenderertext8').set_property('editable', True)
self.gui.get_object('cellrenderertext9').set_property('editable', True)
self.icons = {}
def createuser(name, nickname, pw):
#判断用户是否存在
db = DBManager(ZERO_DB_NAME)
cu = db.cursor()
sql = "".join(["select * from user where name = '", name, "'"])
cu.execute(sql)
data = cu.fetchone()
cu.close()
if data != None:
rt = dict(code=ZERO_USERNAME_ERROR)
return json.dumps(rt)
sql = "insert into user(name,nickname,password) VALUES(?,?,?)"
db = DBManager(ZERO_DB_NAME)
cu = db.cursor()
cu.executemany(sql, [(name, nickname, pw)])
cu.close()
rt = dict(code=0,name=name,nickname=nickname)
return json.dumps(rt)
class Controller(object):
def __init__(self):
# vm registries
self.label_vm = []
self.feature_vm = []
self.classify_vm = []
self.logger = MyUtils.getLogger('controller')
#file manager
self.data_manager = StorageManager('test1')
self.db_manager = DBManager()
#engines
# rpc server for now for handling client request
rpc_paths = ('/RPC2',)
# Create server
self.server = SimpleXMLRPCServer(("0.0.0.0", 8888))
self.server.register_introspection_functions()
self.server.register_function(self.addImage, 'addImage')
self.server.serve_forever()
def decode_image_from_string(self,s):
return np.fromstring(s, np.uint8)
def addImage(self, image_name, image, override=False):
# save image to disk first, and then upload to s3
self.logger.debug('received data. override? {}'.format(override))
full_path = './tmp/'+image_name
with open(full_path, 'wb') as f:
f.write(self.decode_image_from_string(image.data))
added = False
if (override or not self.db_manager.hasImage(image_name)):
self.data_manager.uploadFile(image_name,'./tmp', image_name)
# file path, is just its image name in s3
added = self.db_manager.addImage(image_name, override)
# os.remove(full_path)
return added
def get_image_fn(self, column, username):
with DBManager(self.file) as c:
c.execute('''
SELECT {} FROM users
WHERE username = ?;
'''.format(column),
(username,))
try:
return c.fetchone()[0]
except TypeError:
return None
def user_exists(self, username):
with DBManager(self.file) as c:
c.execute('''
SELECT username FROM users
WHERE username = ?
''',
(username,))
if c.fetchone():
return c.fetchone()[0]
else:
return None
def __init__(self):
db_path = joinpath(globals.data_dir, globals.db_name)
#if not exists(db_path): create in db manager if not present
# log.error('db file not found')
# raise FBError
self._db = DBManager(db_path)
self._db.connect()
self._types = "(type = 46 OR type = 80 OR type = 128 OR type = 247 OR type = 308 OR type = 60)"
self._fql_limit = globals.max_fb_api_calls
def create_database(self):
with DBManager(self.file) as c:
c.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT UNIQUE,
password TEXT,
source_image TEXT,
target_image TEXT,
result_image TEXT
);
'''
)
def __init__(self, api_key, size_matrix=10, time_delay=5):
self.client = googlemaps.Client(api_key)
self.db = DBManager(path='./spots_tools.db')
self.size_matrix = size_matrix
self.time_delay = time_delay
# extract id & location from SpotInfo DB
print('Extracting spots\' info from SpotInfo DB...', end=' ')
self.db.run_query('SELECT id, latitude, longitude FROM SpotInfo')
list_rows = self.db.fetch_all()
print('Success!')
# build the objects used for searching
self.num_spots = len(list_rows)
self.list_ids = [row['id'] for row in list_rows]
self.list_lats = [row['latitude'] for row in list_rows]
self.list_lngs = [row['longitude'] for row in list_rows]
# list of (index_min, index_max + 1) for each submatrix
self.list_ranges = [
(i, min(i + self.size_matrix, self.num_spots))
for i in xrange(0, self.num_spots, self.size_matrix)
]
def transfer_products():
mysql = DBManager.retrieve_data("mysql")
mongo = DBManager.retrieve_data("mongo")
# Firstly, get MySQL for conversion.
products_df = mysql.get_dbdata("products")
categories_df = mysql.get_dbdata("categories")
product_sales_df = mysql.get_dbdata("product_sales")
products_df.rename(columns={"id_category": "category"}, inplace=True)
for _, row in categories_df.iterrows():
mask = products_df.loc[:, "category"] == row["id_category"]
products_df.loc[mask, "category"] = row["title"]
products_df.rename(columns={"id_product": "_id"}, inplace=True)
products_data = products_df.to_dict(orient="records")
for p in products_data:
sales = product_sales_df.loc[product_sales_df.loc[:, "id_product"] ==
p["_id"]].copy()
sales.drop(columns=["id_sale", "id_product"], inplace=True)
p["sales"] = sales.to_dict(orient="records")
if p["brand"] is None:
del p["brand"]
# Insert data from MySQL into Mongo.
db_datatracker = mongo.get_database()
coll_products = db_datatracker["products"]
coll_products.drop()
insert_res = coll_products.insert_many(products_data)
if isinstance(insert_res,
InsertManyResult) and (len(insert_res.inserted_ids) > 0):
# Insert Successful
return 1, "Products Successfully Transferred."
else:
return 0, "Error Transferring Products"
def crawl(self, bot):
"""オリジナルユーザの発言をクロール"""
def remove_tab(text):
return text.replace('\t', ' ').replace('\n', ' ')
with DBManager(self._mecab, self._db_file) as db:
api = bot.api
arg = {}
arg['id'] = self._crawl_user
if db.since_id:
arg['since_id'] = db.since_id
new_statuses = []
statuses = tweepy.Cursor(api.user_timeline, **arg).items(3200)
for status in statuses:
#テキストログを作成
columns = [
remove_tab(status.text),
str(status.created_at),
str(status.id)
]
#リプライ先も取得
reply_to = None
if status.in_reply_to_status_id:
try:
reply_to = api.get_status(status.in_reply_to_status_id)
except:
pass
if reply_to:
columns.append(remove_tab(reply_to.text))
columns.append(str(reply_to.created_at))
columns.append(str(reply_to.id))
new_statuses.append('\t'.join(columns))
#DBへ登録
text = db.extract_text(status.text)
logger.debug('%s: %s' % (status.id, status.text))
if reply_to:
logger.debug('>%s: %s' % (reply_to.id, reply_to.text))
db.add_text(text)
db.since_id = str(max(int(db.since_id), int(status.id)))
with open('crawl.tsv', 'a') as f:
for line in reversed(new_statuses):
f.write(line.encode('utf-8') + '\n')
def main():
crawler = Crawler()
db = DBManager(DBType.SQLITE, 'github.db')
rate_limit = crawler.get_rate_limit()
logger.info("Rate Limit: %s Remaining: %s", rate_limit.limit,
rate_limit.remaining)
# repos = crawler.fetch_repositories_with_stars_in_range(10000, 100000)
# logger.info(repos)
repos = crawler.fetch_repositories_with_stars_in_range(100000, 200000)
logger.info(repos)
# Test the DB
for repo in repos:
db_repo = convert_api_repo_to_db(repo)
db.session.add(db_repo)
db.session.commit()
def main():
db = DBManager()
for line in sys.stdin:
line = line.strip()
status = line.split('\t')
text = db.extract_text(status[0])
if text:
db.add_text(text)
print text, '\t', status[1]
if len(status)>=3:
db.since_id = status[2]
sys.stdout.flush()
return
def drop_brands():
mongo = DBManager.retrieve_data("mongo")
db_datatracker = mongo.get_database()
coll_top_products = db_datatracker["top_products"]
data_top_products = coll_top_products.find({})
drop_count = 0
for item in data_top_products:
if drop_count >= 2:
break
res_update = coll_top_products.update_one({"_id": item["_id"]},
{"$unset": {
"brand": ""
}})
if res_update.matched_count >= 1:
drop_count += 1
return 1, "Brands were Successfully Dropped and Stored."
def update_product():
mongo = DBManager.retrieve_data("mongo")
db_datatracker = mongo.get_database()
coll_top_products = db_datatracker["top_products"]
res_update = coll_top_products.find_one_and_update({}, {
"$inc": {
"totalSales": 250
},
"$set": {
"brand": "Shield"
}
})
if res_update is None:
return 0, "No products updated as no products with brands were found."
# coll_products.find_one_and_update(res_update, {"brand": "Orange Juice"})
return 1, "Succesfully update 1 document in the `top_products` collection."
def __init__(self):
# vm registries
self.label_vm = []
self.feature_vm = []
self.classify_vm = []
self.logger = MyUtils.getLogger('controller')
#file manager
self.data_manager = StorageManager('test1')
self.db_manager = DBManager()
#engines
# rpc server for now for handling client request
rpc_paths = ('/RPC2',)
# Create server
self.server = SimpleXMLRPCServer(("0.0.0.0", 8888))
self.server.register_introspection_functions()
self.server.register_function(self.addImage, 'addImage')
self.server.serve_forever()
def create_worst_5_brands():
mongo = DBManager.retrieve_data("mongo")
db_datatracker = mongo.get_database()
coll_products = db_datatracker["products"]
worst5 = coll_products.aggregate([{
"$match": {
"sales.0": {
"$exists": True
},
"brand": {
"$exists": True
}
}
}, {
"$unwind": "$sales"
}, {
"$group": {
"_id": "$brand",
"totalSales": {
"$sum": "$sales.sold"
}
}
}, {
"$sort": {
"totalSales": 1,
"brand": 1
}
}, {
"$limit": 5
}])
print("")
print("The following are the 5 worst brands in the store.")
for level, brand in enumerate(worst5):
print(f"{level+1} -> {brand['_id']}")
return 1, ""
def init(self):
'''
This is actually the real constructor and is run at the start of the forked process.
'''
# Database connection
self.db = DBManager(host=config.DB_HOST,
user=config.DB_USER,
passwd=config.DB_PASSWD,
db=config.DB_SCHEMA,
socket=config.DB_SOCKET,
table="jobs")
# Create folder for scaled images if necessary
if (not os.path.exists(config.SCALED_FOLDER)) :
os.makedirs(config.SCALED_FOLDER)
# Open log file
log_folder = "log"
if (not os.path.exists(log_folder)) :
os.makedirs(log_folder)
self.log_file = open("%s/%s.%d" % (log_folder, socket.gethostname(), self.thread_id), "a")
def BookParse(bookid, pages=None, exclude=None):
""" Takes id of book to parse. Id of book is one from DB,
and should correspond to filename as book12.pdf,
for id of the book in DB is 12. Also function accepts
optional argument "pages", it defines pages to parse, and
optional argument "exclude", to define pages to exclude.
Range format accepted: 1,2,3-8,15
"""
if pages is None:
pages = set()
if exclude is None:
exclude = set()
try:
bookfile = open("data/book" + str(bookid) + ".pdf", "rb")
except FileNotFoundError as e:
exception_msg(lg, e
, level="ERR"
, text="No such book (id=%s) in data dir."\
% str(bookid))
raise
mineparser = PDFParser(bookfile)
document = PDFDocument(mineparser)
if not document.is_extractable:
lg.error("PDF text extraction is not allowed.")
raise PDFTextExtractionNotAllowed
db = DBManager()
for pagenum, page in enumerate(PDFPage.create_pages(document)):
realnum = pagenum + 1
lg.info("Working on page %s (bookid=%s)", str(realnum), str(bookid))
if (len(pages) > 0 and realnum not in pages)\
or realnum in exclude:
lg.info("Page %s (bookid=%s) excluded.", str(realnum), str(bookid))
continue
# Insert page entry to db, no HTML
db.insert_page(bookid, realnum)
lg.info("Recognizing (pagenum=%s) of book (id=%s).", str(realnum),
str(bookid))
pagetype = recognize(bookid, page)
if pagetype == -1:
lg.warning("Can't recognize page (pagenum=%s) in book (id=%s).",
str(realnum), str(bookid))
lg.info("Page %s (bookid=%s) skipped.", str(realnum), str(bookid))
continue
lg.info("Parsing (pagenum=%s) of book (id=%s). Type (pagetype=%s).",
str(realnum), str(bookid), str(pagetype))
try:
data = parse(bookid, page, pagetype)
except Exception as e:
exception_msg(lg, e
, level="WARN"
, text="Errors while parsing."
" Skip (pagenum=%s) of book (id=%s)"\
% (str(realnum), str(bookid)))
continue
else:
lg.info(
"Inserting items to DB."
" (pagenum=%s) of book (id=%s). Type (pagetype=%s).",
str(realnum), str(bookid), str(pagetype))
try:
db.bulk_insert(bookid, data, pnum=realnum)
except Exception as e:
exception_msg(lg,
e,
level="ERR",
text="Errors during inserting data into DB."
" Maybe you should check the parser")
# Update page entry with parsed HTML
lg.info("Parsing to HTML (pagenum=%s) of book (id=%s).", str(realnum),
str(bookid))
try:
html = pdftohtml(page)
except Exception as e:
exception_msg(lg, e
, text="Cannot convert PDF to HTML."
" (pagenum=%s) of book (id=%s)"\
% (str(realnum), str(bookid)))
else:
lg.info(
"Inserting HTML to DB."
" (pagenum=%s) of book (id=%s). Type (pagetype=%s).",
str(realnum), str(bookid), str(pagetype))
db.insert_page(bookid, realnum, data=html)
lg.info(
"Done with page."
" (pagenum=%s) of book (id=%s). Type (pagetype=%s).", str(realnum),
str(bookid), str(pagetype))
pruebas de las librerías de Python y otros conceptos.
'''
import logging
import uvicorn
import os
from fastapi import Body, FastAPI
from fastapi.responses import JSONResponse
from fastapi.encoders import jsonable_encoder
from rich import inspect, print
from dbmanager import DBManager
from recommendator import Tweet, Recommendation, Recommendator
db = DBManager(
host=os.getenv('PB_DB_HOST', 'http://localhost'), # HOST
port=os.getenv('PB_DB_PORT', 2480), # PORT
user=os.getenv('PB_DB_USER', 'admin'), # USER
password=os.getenv('PB_DB_PASS', 'admin') # PASSWORD
)
app = FastAPI()
@app.post("/recommendation", response_class=JSONResponse)
async def getRecommendation(tweet: Tweet = Body(...,
embed=True)) -> Recommendation:
recommendator = Recommendator(db=db, tweet=tweet)
# getRecommendation puede devolver una recomendacion o un error
text, statusCode = recommendator.getRecommendation()
response = JSONResponse(content=jsonable_encoder(text),
status_code=statusCode)
return response
from argparse import ArgumentParser
from fact import FactType
from fact import Fact
from preprocessor import PreProcessor
from textcomparator import TextComparator
from dbmanager import DBManager
import config as cfg
import json
pre_processor = PreProcessor()
text_comp = TextComparator()
db_manager = DBManager()
def parse_args():
parser = ArgumentParser(description="Add a fact or fake fact")
parser.add_argument(
"fact",
choices=['truth', 'fake'],
help=
"\"fact\" if the given text is a true fact, \"fake\" if it is a fake fact"
)
parser.add_argument("-t",
"--text",
dest="text",
help="text to be added to fact database",
metavar="TEXT",
required=True)
parser.add_argument(
"-a",
"--append",
#!/usr/bin/env python2 from dbmanager import DBManager from mqtt import MQTTDatabaseRunner from config import DB_CONNECTION_STRING db_manager = DBManager(DB_CONNECTION_STRING) db_manager.connect() db_runner = MQTTDatabaseRunner(db_manager) db_runner.start(threadded = False) #db_runner.wait()
#!/bin/env python2
from dbmanager import DBManager
dbman = DBManager("dbname=zaehlrohr user=zaehlrohr", "30C3")
print dbman.get_summary('daily','ErsterStock')
print dbman.get_capsules('Baellebad')
def audit_target(self, target_id, snapshot_id='',save_audit=False, envtype='', additional_info=''):
def calculate_max_memory(**mem_param_rec):
try:
max_memory = mem_param_rec['key_buffer_size'] + \
mem_param_rec['query_cache_size'] + \
mem_param_rec['tmp_table_size'] + \
mem_param_rec['innodb_buffer_pool_size'] + \
mem_param_rec['innodb_additional_mem_pool_size'] + \
mem_param_rec['innodb_log_buffer_size'] + \
(mem_param_rec['max_connections'] * (mem_param_rec['sort_buffer_size'] + \
mem_param_rec['read_buffer_size'] + \
mem_param_rec['read_rnd_buffer_size'] + \
mem_param_rec['join_buffer_size'] + \
mem_param_rec['thread_stack'] + \
mem_param_rec['binlog_cache_size']))
return str(max_memory)
except:
return '0'
db_conn = self.get_conn_conf_by_id(target_id)
audit_rec = {}
if db_conn:
# first time when we check 'envtype' is provided or not and trying to get it from target's record
if 'envtype' in db_conn.keys():
if not envtype:
envtype = str(list(db_conn['envtype'])[0])
del db_conn['envtype']
db_conn_4disply = db_conn.copy()
# hide the password and then print
if 'password' in db_conn_4disply.keys():
db_conn_4disply['password'] = '******'
print("\nAuditing ID={1}, connecting to [{0}]...".format(db_conn_4disply, target_id))
try:
target_db = DBManager(True, False, **db_conn)
except Exception as err:
print("For ID={0}, audit failed ...".format(target_id))
print(err)
return
additional_info += 'connected to: {0}'.format(db_conn_4disply['host']) if 'host' in db_conn_4disply.keys() else 'connected to: {0}'.format('localhost')
audit_rec['snapshot_id'] = snapshot_id if snapshot_id else datetime.now().strftime('%S%M%H%d%m%Y%f')
audit_rec['host'] = dict(target_db.execute_query(sqls4mysql['host'], True)[0])['host']
audit_rec['osversion'] = dict(target_db.execute_query(sqls4mysql['osversion'], True)[0])['osversion']
envtype = envtype.upper()
audit_rec['envtype'] = self.get_env_type(audit_rec['host']) if not envtype or envtype not in ('PROD','STG','QA','DEV') else envtype
audit_rec['dbversion'] = dict(target_db.execute_query(sqls4mysql['dbversion'], True)[0])['dbversion']
# checking if INNODB is enabled
innodb = str(target_db.get_global_variable('have_innodb')).upper()
additional_info += ', Have_InnoDB is set to ({0})'.format(innodb)
# calculating MAX memory
memory_params = dict([(row[0][1],long(row[1][1])) for row in target_db.execute_query(sqls4mysql['memory_params'],True)])
audit_rec['max_req_memory'] = calculate_max_memory(**memory_params)
audit_rec['additional_info'] = additional_info
for dblist in target_db.execute_query(sqls4mysql['dblist'], True):
dbname = dict(dblist)['Database']
audit_rec['dbname'] = dbname
audit_rec['dbsize'] = dict(target_db.execute_query(sqls4mysql['dbsize'].format(dbname), True)[0])['dbsize']
audit_rec['free_space'] = dict(target_db.execute_query(sqls4mysql['free_space'].format(dict(dblist)['Database']), True)[0])['free_space']
if save_audit:
cmd = sqls4mysql['insert_aud_rec'].format(audit_rec['snapshot_id'],
audit_rec['host'],
audit_rec['osversion'],
audit_rec['envtype'],
audit_rec['dbversion'],
audit_rec['dbname'],
audit_rec['dbsize'],
audit_rec['free_space'],
audit_rec['max_req_memory'],
audit_rec['additional_info'])
self.execute_dml(cmd, False, message='', additional_fail_msg='', additional_ok_msg='')
else:
print(audit_rec)
target_db.close()
print('OK')
else:
print("Couldn't find record with id={0} ...".format(target_id))
'''
Created on May 2, 2014
@author: luamct
'''
import config
from dbmanager import DBManager
if __name__ == '__main__':
db = DBManager(host=config.DB_HOST,
user=config.DB_USER,
passwd=config.DB_PASSWD,
db=config.DB_SCHEMA,
socket=config.DB_SOCKET)
aes_filter = {
'colors':['dom_colors', 'colorfulness', 'black', 'blue',
'brown', 'gray', 'green', 'orange',
'pink', 'purple', 'red', 'white', 'yellow'],
'contrast': ['Y_contrast'],
'composition':['detail_level', 'bg_area', 'thirds'],
'sharpness':['centrality', 'density',
'sharp00', 'sharp01', 'sharp02',
'sharp10', 'sharp11', 'sharp12',
'sharp20', 'sharp21', 'sharp22'],
'stats':['H_mean', 'Y_mean', 'S_mean',
'H_std', 'Y_std', 'S_std'],
'daubechies':['H_sum', 'S_sum', 'Y_sum'],
'shape' : ['resolution', 'aspect_ratio']
def __init__(self): self.dbman = DBManager(Config.connection_string, Config.eventname)
class PsqlOutput(object): def __init__(self): self.dbman = DBManager(Config.connection_string, Config.eventname) def process_event(self, event): self.dbman.insert_event(event, Config.eventname)
def run():
pygame.init()
random.seed()
display_size = 800, 600
viewport_size = display_size[0], 600
black = 0, 0, 0
red = 255, 70, 70
green = 70, 255, 70
blue = 70, 70, 255
white = 255, 255, 255
l_green = 50, 255, 0
screen = pygame.display.set_mode(display_size)
screen.fill(black)
clock = Clock(readonly=False)
dbman = DBManager()
dbman.import_db("./db")
gfxman = GfxManager()
gfxman.import_gfx(dbman.get(), "./gfx")
stagemanager = StageManager()
stagemanager.import_stages("./stages")
stages = stagemanager.get()
groupmanager = GroupManager()
g_draw = groupmanager.add("draw", "OrderedUpdates")
g_ship = groupmanager.add("ship")
g_enemies = groupmanager.add("enemies")
g_enemy_projectiles = groupmanager.add("enemy_projectiles")
g_player_projectiles = groupmanager.add("player_projectiles")
g_beams = groupmanager.add("beams")
g_explosions = groupmanager.add("explosions")
g_shields = groupmanager.add("shields")
g_bonuses = groupmanager.add("bonuses")
hud = Hud(viewport_size)
g_enemies.add(Obstacle((60, 30)))
g_enemies.add(MovingObstacle((160, 80)))
g_bonuses.add(RechargeBonus((300, 200)))
g_bonuses.add(SuperShieldBonus((500, 300)))
g_bonuses.add(ShieldUpgradeBonus((500, 500)))
g_bonuses.add(ShieldUpgradeBonus((300, 500)))
ship = ref(PlayerShip((175, viewport_size[1] - 60), g_ship))
hud.setup_connections(ship())
back = SpaceBackground(viewport_size)
for stage_name in sorted(stages.keys()):
stage_clock = 0
while True:
for spawn_time in stages[stage_name]["spawn"]:
if spawn_time <= stage_clock:
while stages[stage_name]["spawn"][spawn_time]:
spawn = stages[stage_name]["spawn"][spawn_time].pop()
pos = spawn["x"], spawn["y"]
object_cls = eval(spawn["object_cls_name"])
if spawn["object_base_cls_name"]:
if spawn["object_base_cls_name"] == "Projectile":
if not spawn.has_key("object_params"):
raise ValueError, "Params for projectile '%s' in stage %s \
not set" % (
spawn["object_cls_name"],
stage_name,
)
if not spawn["object_params"].has_key("dir"):
raise ValueError, "Invalid 'dir' for projectile '%s' in \
stage %s" % (
spawn["object_cls_name"],
stage_name,
)
if not spawn["object_params"].has_key("collision_group"):
raise ValueError, "Invalid 'collision_group' for projectile \
'%s' in stage %s" % (
spawn["object_cls_name"],
stage_name,
)
params = spawn["object_params"]
dir = params["dir"]
g_coll = groupmanager.get(params["collision_group"])
object = object_cls(pos, dir, g_coll)
elif spawn["object_base_cls_name"] == "Bonus":
pass
else:
raise ValueError, "Invalid value '%s' for attrubite \
'object_base_cls_name' in stage %s" % (
spawn["object_base_cls_name"],
stage_name,
)
else:
object = object_cls(pos)
if spawn["bonus_cls_name"]:
if isinstance(object, BonusHolder):
object.set_bonus(spawn["bonus_cls_name"], spawn["bonus_params"])
else:
raise ValueError, "Instances of %s can not hold bonuses." % object.__class__.__name__
if spawn["mover_cls_name"]:
mover_cls = eval("mover.%s" % spawn["mover_cls_name"])
m = mover_cls(pos, object.max_speed, spawn["mover_params"])
object.set_mover(m)
for g in spawn["groups"]:
if g == "enemies":
g_enemies.add(object)
elif g == "explosions":
g_explosions.add(object)
elif g == "enemy_projectiles":
g_enemy_projectiles.add(object)
elif g == "player_projectiles":
g_player_projectiles.add(object)
# time management
clock.tick(40)
# clock.tick( float(sys.argv[1]) )
stage_clock += clock.get_time()
for event in pygame.event.get():
if event.type == pygame.QUIT:
sys.exit()
elif event.type == pygame.KEYDOWN:
if event.key == pygame.K_q:
sys.exit()
elif event.key == pygame.K_s:
if ship():
ship().next_weapon()
elif event.key == pygame.K_a:
if ship():
ship().previous_weapon()
elif event.key == pygame.K_x:
if ship():
ship().activate_shield(True)
elif event.type == pygame.KEYUP:
if event.key == pygame.K_UP:
if ship():
ship().fly_up(False)
elif event.key == pygame.K_x:
if ship():
ship().activate_shield(False)
pressed_keys = pygame.key.get_pressed()
if pressed_keys[pygame.K_UP]:
if ship():
ship().fly_up(True)
if pressed_keys[pygame.K_DOWN]:
if ship():
ship().fly_down(viewport_size[1])
if pressed_keys[pygame.K_LEFT]:
if ship():
ship().fly_left()
if pressed_keys[pygame.K_RIGHT]:
if ship():
ship().fly_right(viewport_size[0])
if pressed_keys[pygame.K_z]:
if ship():
ship().shoot()
back.clear(screen, clear_bg)
back.update()
back.draw(screen)
# temp
# if ship():
# ship().damage(1)
#
g_draw.clear(screen, clear_bg)
hud.clear(screen, clear_bg)
g_draw.update()
hud.update()
g_draw.draw(screen)
hud.draw(screen)
pygame.display.flip()
'flora_grass','fauna_cat','fauna_dog','fauna_horse','fauna_fish','fauna_bird',
'fauna_insect','fauna_spider','fauna_amphibianreptile','fauna_rodent','quantity_none',
'quantity_one','quantity_two','quantity_three','quantity_smallgroup','quantity_biggroup',
'age_baby','age_child','age_teenager','age_adult','age_elderly','gender_male',
'gender_female','relation_familyfriends','relation_coworkers','relation_strangers',
'quality_noblur','quality_partialblur','quality_completeblur','quality_motionblur',
'quality_artifacts','style_pictureinpicture','style_circularwarp','style_graycolor',
'style_overlay','view_portrait','view_closeupmacro','view_indoor','view_outdoor',
'setting_citylife','setting_partylife','setting_homelife','setting_sportsrecreation',
'setting_fooddrink','sentiment_happy','sentiment_calm','sentiment_inactive',
'sentiment_melancholic','sentiment_unpleasant','sentiment_scary','sentiment_active',
'sentiment_euphoric','sentiment_funny','transport_cycle','transport_car',
'transport_truckbus','transport_rail','transport_water','transport_air']
db = DBManager(host=config.DB_HOST,
user=config.DB_USER,
passwd=config.DB_PASSWD,
db=config.DB_SCHEMA)
# draw_venn("data/corrects.p", "charts/venn.pdf")
# ids, repins = db.get_repins()
# aes_feats, aes_data = db.get_data_aesthetics(db, aes_filter, ids)
# sem_feats, sem_data = db.get_data_semantics(db, sem_filter, ids)
# soc_feats, soc_data = db.get_data_social(db, ids)
# image_grid(db, "/scratch/images")
# spearman('data/rankcor.txt', 'charts/rankcor.pdf')
# boxplots(soc_data, "soc", soc_feats)
class RouteInfoSearcher(object):
def __init__(self, api_key, size_matrix=10, time_delay=5):
self.client = googlemaps.Client(api_key)
self.db = DBManager(path='./spots_tools.db')
self.size_matrix = size_matrix
self.time_delay = time_delay
# extract id & location from SpotInfo DB
print('Extracting spots\' info from SpotInfo DB...', end=' ')
self.db.run_query('SELECT id, latitude, longitude FROM SpotInfo')
list_rows = self.db.fetch_all()
print('Success!')
# build the objects used for searching
self.num_spots = len(list_rows)
self.list_ids = [row['id'] for row in list_rows]
self.list_lats = [row['latitude'] for row in list_rows]
self.list_lngs = [row['longitude'] for row in list_rows]
# list of (index_min, index_max + 1) for each submatrix
self.list_ranges = [
(i, min(i + self.size_matrix, self.num_spots))
for i in xrange(0, self.num_spots, self.size_matrix)
]
def try_transit(self, info_routes, range_start, range_end):
print('- Trying the transit mode...', end=' ')
input_start = [
'place_id:%s' % self.list_ids[i] for i in xrange(*range_start)
]
input_end = [
'place_id:%s' % self.list_ids[i] for i in xrange(*range_end)
]
response = getattr(self.client, 'distance_matrix')(
origins=input_start,
destinations=input_end,
units='metric',
mode='transit',
transit_routing_preference='less_walking')
num_tries, num_fails = 0, 0
if response['status'] == 'OK':
for i in xrange(*range_start):
row = response['rows'][i - range_start[0]]['elements']
for j in xrange(*range_end):
item = row[j - range_end[0]]
num_tries += 1
if item['status'] == 'OK':
info_routes[i][j] = {
'time': item['duration']['value'],
'distance': item['distance']['value']
}
else:
num_fails += 1
print('%d / %d failed!' % (num_fails, num_tries))
return num_fails == 0
def try_walking(self, info_routes, range_start, range_end):
print('- Trying the walking mode...', end=' ')
num_tries, num_fails = 0, 0
for i in xrange(*range_start):
for j in xrange(*range_end):
if info_routes[i][j] is None:
response = getattr(self.client, 'directions')(
origin='place_id:%s' % self.list_ids[i],
destination='place_id:%s' % self.list_ids[j],
units='metric',
mode='walking')
num_tries += 1
if response and response[0]['legs']:
item = response[0]['legs'][0]
info_routes[i][j] = {
'time': item['duration']['value'],
'distance': item['distance']['value']
}
else:
num_fails += 1
print('%d / %d failed!' % (num_fails, num_tries))
return num_fails == 0
def try_guessing(self, info_routes, range_start, range_end):
print('- Trying to guess using the haversine formula...', end=' ')
num_tries = 0
for i in xrange(*range_start):
for j in xrange(*range_end):
if info_routes[i][j] is None:
distance = calc_distance(self.list_lats[i],
self.list_lngs[i],
self.list_lats[j],
self.list_lngs[j])
num_tries += 1
info_routes[i][j] = {
'time': distance / 1.23, # walking speed: 1.23m/s,
'distance': distance
}
print('Success! (%d)' % num_tries)
def search(self):
info_routes = [[None] * self.num_spots for _ in xrange(self.num_spots)]
try:
for range_start in self.list_ranges:
for range_end in self.list_ranges:
print('Searching (%d ~ %d) X (%d ~ %d):' %
(range_start[0], range_start[1] - 1, range_end[0],
range_end[1] - 1))
# try the transit mode (i.e public transportations)
if self.try_transit(info_routes, range_start, range_end):
continue
# try the walking mode
if self.try_walking(info_routes, range_start, range_end):
continue
# try to guess using the haversine formula
self.try_guessing(info_routes, range_start, range_end)
print('Delaying: %d seconds...' % self.time_delay, end=' ')
time.sleep(self.time_delay)
print('Success!')
except googlemaps.exceptions.Timeout:
print('\nTimeout! We\'ll just guess the remaining routes.')
range_whole = (0, self.num_spots)
self.try_guessing(info_routes, range_whole, range_whole)
# insert the result into RouteInfo DB
print('Inserting the result into RouteInfo DB:')
for i in xrange(self.num_spots):
for j in xrange(self.num_spots):
print('- Route %d -> %d...' % (i, j), end=' ')
self.db.run_query(
'INSERT OR REPLACE INTO RouteInfo ('
' startId, endId, time, distance'
') VALUES (?, ?, ?, ?)', self.list_ids[i],
self.list_ids[j], info_routes[i][j]['time'],
info_routes[i][j]['distance'])
print('Success!')
class ExtractorManager() :
def __init__(self, thread_id, extractors):
'''
Since this is run on the main process, it shouldn't
open connection or file descriptors.
'''
self.thread_id = thread_id
# Features to be extracted
self.extrators = extractors
from warnings import filterwarnings
filterwarnings('ignore')
def init(self):
'''
This is actually the real constructor and is run at the start of the forked process.
'''
# Database connection
self.db = DBManager(host=config.DB_HOST,
user=config.DB_USER,
passwd=config.DB_PASSWD,
db=config.DB_SCHEMA,
socket=config.DB_SOCKET,
table="jobs")
# Create folder for scaled images if necessary
if (not os.path.exists(config.SCALED_FOLDER)) :
os.makedirs(config.SCALED_FOLDER)
# Open log file
log_folder = "log"
if (not os.path.exists(log_folder)) :
os.makedirs(log_folder)
self.log_file = open("%s/%s.%d" % (log_folder, socket.gethostname(), self.thread_id), "a")
def log(self, message, show=False) :
'''
File for multi-threading logging.
'''
print >> self.log_file, message
self.log_file.flush()
if show:
print message
def run(self):
print "Starting %s." % self.thread_id
# Should initiate already in the forked process
self.init()
# Process all users allocated to this thread
while (not utils.inout.stop_file_exists()) :
image_id = None
try :
image_id = self.db.get_next(DBManager.AVAILABLE, self.thread_id)
# Check if there isn't already an scaled version of the image
if (not utils.image.exists_and_valid(config.SCALED_FOLDER, image_id)) :
# Load original sized image
rgb_img = utils.image.load_image(config.IMAGE_FOLDER, image_id)
# Scale it down keeping the aspect ratio
rgb_img = utils.image.scale_down(rgb_img, config.MAX_PIXELS)
# Save a copy on disk
utils.image.save_image(config.SCALED_FOLDER , image_id, rgb_img)
else :
# Load scaled down version of the image
rgb_img = utils.image.load_image(config.SCALED_FOLDER, image_id)
# Process all registered extractors
for extractor in self.extrators :
print extractor.__class__
concepts = extractor.process(rgb_img)
self.db.save_features(extractor.get_table_name(), image_id, concepts)
# Update the pin status to DOWNLOADED
self.db.update_status(image_id, DBManager.COMPLETED)
# Everything went ok if got here
print "%s: OK" % image_id
# Nothing to collect
except NothingToProcessException:
self.log("Nothing to process.", show=True)
break
# Any other exception we log the traceback, update the DB and life goes on.
except Exception:
# Could not even get image id. Must halt.
if image_id is None:
print traceback.format_exc()
break
self.log("%s: ERROR\n%s" % (image_id, traceback.format_exc()), show=True)
self.db.update_status(image_id, DBManager.ERROR)
# Last thing before exiting thread
self.close()
def close(self):
'''Clean up routine'''
self.db.close()
#!/bin/env python2
import json
import os
from datetime import datetime
from dbmanager import DBManager
def print_event(event):
print "%s -> %s %s %s" % (event['origin'], event['destination'], event['velocity'], str(datetime.fromtimestamp(event['time'])))
OLD_PATH = "./old_data"
for filename in os.listdir(OLD_PATH):
filepath = os.path.join(OLD_PATH, filename)
if os.path.isfile(filepath):
dbman = None
print "Reading file %s" % filepath
json_file = open(filepath)
for event in json.load(json_file):
print_event(event)
if dbman == None:
dbman = DBManager("dbname=zaehlrohr user=zaehlrohr", event['event'])
new_event = {
'origin' : event['origin'],
'destination' : event['destination'],
'velocity' : event['velocity'],
'time' : event['time']}
dbman.insert_event(new_event)
class AutoDl(object):
"""Main class."""
scrapers = {}
myDB = ""
config = configparser.ConfigParser()
plugin_base = PluginBase(package='autodl.scraper')
def __init__(self):
"""Init main class."""
doScrape = True
self.load_config()
self.init_config()
self.myDB = DBManager(self.config["BASIC"]["DBLocation"])
self.plugin_source = self.plugin_base.make_plugin_source(
searchpath=['./scrapers'])
for plugin_name in self.plugin_source.list_plugins():
plugin = self.plugin_source.load_plugin(plugin_name)
plugin.Scraper().setup(self)
doScrape = "-noscrape" not in sys.argv
doDl = "-nodl" not in sys.argv
if "-add" in sys.argv:
i = sys.argv.index("-add")
newShow = sys.argv[i + 1]
newLang = sys.argv[i + 2]
self.myDB.orderShow(newShow, newLang)
if doScrape:
self.scrape()
self.save_config()
self.myDB.matchEpisodes()
if doDl:
self.myDL = Downloader(self)
self.myDL.downloadQueue()
def init_config(self):
"""Setup config if nor exist."""
basic_conf = {
"DefaultLang": "de",
"DefaultLocation": "de",
"DBLocation": "./media.db",
"MediaLocation": "./media"
}
network_conf = {
"VPNStart": "windscribe connect {loc}",
"VPNStop": "windscribe disconnect"
}
if "BASIC" not in self.config.sections():
self.config.add_section("BASIC")
if "NETWORK" not in self.config.sections():
self.config.add_section("NETWORK")
for element in basic_conf:
if element not in self.config["BASIC"]:
self.config.set("BASIC", element, basic_conf[element])
for element in network_conf:
if element not in self.config["NETWORK"]:
self.config.set("NETWORK", element, network_conf[element])
def load_config(self):
"""Load config from config.ini."""
if os.path.isfile('config.ini'):
with open('config.ini', 'r') as configfile:
self.config.read_file(configfile)
def save_config(self):
"""Save config in config.ini."""
with open('config.ini', 'w') as configfile:
self.config.write(configfile)
def register_scraper(self, name, scraper):
"""Use to register a scraper plugin."""
self.scrapers[name] = scraper
if "SCRAPER" not in self.config.sections():
self.config.add_section("SCRAPER")
if name not in self.config["SCRAPER"]:
self.config["SCRAPER"][name] = "True"
def addConfigKey(self, section, key, default):
"""Allow scrapers to add config keys."""
if section not in self.config.sections():
self.config.add_section(section)
self.save_config()
if key not in self.config[section]:
self.config.set(section, key, default)
self.save_config()
def scrape(self):
"""Execute all scrapers."""
for name, s in sorted(self.scrapers.items()):
if self.config["SCRAPER"][name] == "True":
print('{}: {}'.format(name, s()))
print('')
class FB():
def __init__(self):
db_path = joinpath(globals.data_dir, globals.db_name)
#if not exists(db_path): create in db manager if not present
# log.error('db file not found')
# raise FBError
self._db = DBManager(db_path)
self._db.connect()
self._types = "(type = 46 OR type = 80 OR type = 128 OR type = 247 OR type = 308 OR type = 60)"
self._fql_limit = globals.max_fb_api_calls
def __del__(self):
self._db.disconnect()
def do_fql(self, query):
try:
fbaccess = FBAccess(self._db)
self._access_token = fbaccess.token
except FBAccessException as fbae:
raise FBError(fbae.message)
if self._fql_limit == 0:
log.error('FQL call limit exceeded')
raise FBError
self._fql_limit -= 1
query_url = 'https://graph.facebook.com/fql?' + urllib.urlencode({'q' : query, 'access_token' : self._access_token})
log.info(query_url)
try:
response = urllib.urlopen(query_url)
log.info(response.getcode())
except Exception as e:
log.error(e.strerror)
raise FBError
try:
data = response.read()
jdata = json.loads(data)
log.info('record count = %d'%len(jdata['data']))
except Exception as e:
log.error(e.strerror)
log.info(data)
raise FBError
return jdata
def get_friends(self):
friends_query = """SELECT uid, first_name, middle_name, last_name, sex, relationship_status, pic,
significant_other_id, age_range, birthday_date, current_location, friend_count,
hometown_location FROM user WHERE uid IN (SELECT uid2 FROM friend WHERE uid1=me()) OR uid=me()"""
jdata = self.do_fql(friends_query)
users = self._db.query("SELECT * FROM user WHERE deleted = 0")
user_ids = [i['id'] for i in users]
for record in jdata['data']:
for k in record:
if (record[k] == ''):
record[k] = None
if (record['friend_count'] != None):
record['friend_count'] = int(record['friend_count'])
if (record['sex'] != None):
record['sex'] = record['sex'][0]
if (record['hometown_location'] != None):
record['hometown_location'] = record['hometown_location']['name']
if (record['current_location'] != None):
record['current_location'] = record['current_location']['name']
if (record['age_range'] != None):
record['age_range'] = str(record['age_range']['min'])
if record['uid'] in user_ids:
user = [u for u in users if u['id'] == record['uid']][0]
update_values = {}
for key in record.keys():
if key in ['uid']:
continue
if record[key] != user[key.encode()]:
update_values[key.encode()] = record[key]
if len(update_values) > 0:
self._db.update('user', update_values, "id = \'%s\'"%record['uid'])
print update_values
print 'u ',; sys.stdout.flush()
#raw_input()
user_ids.remove(record['uid'])
else:
self._db.insert('user', (record['uid'], record['first_name'], record['middle_name'],
record['last_name'], record['sex'], record['relationship_status'], record['pic'],
record['significant_other_id'], record['age_range'], record['birthday_date'],
record['current_location'], record['friend_count'], record['hometown_location'],
0))
log.info('new friend: %s %s'%(record['first_name'], record['last_name']))
for user_id in user_ids:
self._db.update('user', {'deleted': 1}, "id = \'%s\'"%user_id)
user = [u for u in users if u['id'] == user_id][0]
log.info('deleted friend: %s %s'%(user['first_name'], user['last_name']))
self._db.commit()
def get_stream(self, start_time=None, end_time=None, cont=False):
def db_insert(jdata):
for record in jdata['data']:
for k in record:
if (record[k] == ''):
record[k] = None
self._db.insert('stream', (record['post_id'], record['actor_id'], record['created_time'],
record['type'], record['like_info']['like_count'], record['comment_info']['comment_count'],
1 if record['is_popular'] else 0, record['message'], record['share_count'],
record['permalink'], None, None, None, 0, 0, 0)) #last 3: updated(remove), update_likes, update_comments
self._db.commit()
print '.',; sys.stdout.flush()
if not (start_time and end_time):
if cont:
if os.path.exists('./end_time'):
end_time = int(open('./end_time', 'r').read().strip())
else:
end_time = int(time()) #+ int(timedelta(days=-6).total_seconds())
else:
end_time = int(time()) #+ int(timedelta(days=-6).total_seconds())
start_time = end_time + int(timedelta(hours=-12).total_seconds())
stream_query = """SELECT post_id, actor_id, created_time, type, like_info, comment_info,
is_popular, message, share_count, permalink FROM stream WHERE source_id """
source_clause = "IN (SELECT uid2 FROM friend WHERE uid1=me())"
limit_clause = ' LIMIT 500'
time_clause = ' AND created_time > %d AND created_time < %d'%(start_time, end_time)
jdata = self.do_fql(stream_query + source_clause + time_clause)
db_insert(jdata)
source_clause = "= me()"
jdata = self.do_fql(stream_query + source_clause + time_clause)
db_insert(jdata)
if cont: open('end_time', 'w').write(str(start_time))
def update_stream(self):
post_ids_query = "SELECT post_id, like_count, comment_count, share_count FROM stream WHERE created_time > %d AND %s"\
%(int(time()) + timedelta(days=-4).total_seconds(), self._types)
result = self._db.query(post_ids_query)
rc = len(result)
while(rc > 0):
post_ids = ''
for i in range(len(result)-rc, len(result)-rc+20 if rc>20 else len(result)):
post_ids += "'%s',"%result[i]['post_id']
rc -= 20
post_ids = post_ids.rstrip(',')
stream_query = "SELECT post_id, like_info, comment_info, share_count FROM stream WHERE post_id IN (%s)"%(post_ids)
jdata = self.do_fql(stream_query)
def result_find(post_id):
for row in result:
if row['post_id'] == post_id:
return row
for record in jdata['data']:
post_id = record['post_id']
like_count = record['like_info']['like_count']
comment_count = record['comment_info']['comment_count']
share_count = record['share_count']
row = result_find(post_id)
likes_changed = row['like_count'] != like_count
comments_changed = row['comment_count'] != comment_count
if likes_changed or comments_changed:
update_query = """UPDATE stream SET like_count = %d, comment_count = %d, share_count = %d,
update_likes = %d, update_comments = %d WHERE post_id = '%s'"""\
%(like_count, comment_count, share_count, 1 if likes_changed else 0, 1 if comments_changed else 0, post_id)
self._db.query(update_query)
print('.'),; sys.stdout.flush()
self._db.commit()
def get_comments(self):
while(True):
post_ids_query2 = "SELECT post_id FROM stream LIMIT 30"
post_ids_query = """SELECT post_id FROM stream WHERE (post_id NOT IN
(SELECT DISTINCT post_id FROM comment) OR update_comments = 1) AND comment_count > 0 AND
%s LIMIT 20"""%(self._types)
result = self._db.query(post_ids_query)
if len(result) == 0:
return
post_ids = ''
for row in result:
post_ids += "'%s',"%row['post_id']
post_ids = post_ids.rstrip(',')
like_query = "SELECT post_fbid, post_id, fromid FROM comment WHERE post_id IN (%s)"%post_ids #AND user_id IN (SELECT uid2 FROM friend WHERE uid1=me())"%post_ids
jdata = self.do_fql(like_query)
for record in jdata['data']:
self._db.insert('comment', (record['post_fbid'], record['post_id'], record['fromid']))
print '.',; sys.stdout.flush()
self._db.query("UPDATE stream SET update_comments = 0 WHERE post_id in (%s)"%(post_ids))
self._db.commit()
def get_likes(self):
while(True):
post_ids_query = """SELECT post_id FROM stream WHERE (post_id NOT IN
(SELECT DISTINCT post_id FROM like) OR update_likes = 1) AND like_count > 0 AND
%s LIMIT 20"""%(self._types)
result = self._db.query(post_ids_query)
if len(result) == 0:
return
post_ids = ''
for row in result:
post_ids += "'%s',"%row['post_id']
post_ids = post_ids.rstrip(',')
like_query = "SELECT post_id, user_id FROM like WHERE post_id IN (%s)"%post_ids #AND user_id IN (SELECT uid2 FROM friend WHERE uid1=me())"%post_ids
jdata = self.do_fql(like_query)
for record in jdata['data']:
self._db.insert('like', (record['post_id'], record['user_id']))
print '.',; sys.stdout.flush()
self._db.query("UPDATE stream SET update_likes = 0 WHERE post_id in (%s)"%(post_ids))
self._db.commit()
def clean_duplicates(self):
delete_dup_query = None
with open(joinpath(dirname(realpath(__file__)), 'clean_stream.sql'), 'r') as f:
delete_dup_query = f.read()
self._db.executescript(delete_dup_query)
def render_graph(self, start=None, end=None):
drop_temp_tables = "drop table likejoin; drop table result;"
filter_self_likes = True
filter_self_comments = True
time_period_clause = ""
if start and end:
time_period_clause = "AND s.created_time BETWEEN %d AND %d"%(self.get_timestamp(start), self.get_timestamp(end))
likejoin_query = """CREATE TEMP TABLE likejoin AS
SELECT s.source_id AS user1, l.user_id AS user2 FROM stream AS s
JOIN like AS l
ON s.post_id = l.post_id %s WHERE (l.user_id IN (SELECT id FROM user WHERE deleted = 0)
AND s.source_id IN (SELECT id FROM user WHERE deleted = 0)) %s"""\
%("AND s.source_id <> l.user_id" if filter_self_likes else "", time_period_clause)
commentjoin_query = """INSERT INTO likejoin
SELECT s.source_id AS user1, c.user_id AS user2 FROM stream AS s
JOIN comment AS c
ON s.post_id = c.post_id %s WHERE (c.user_id IN (SELECT id FROM user WHERE deleted = 0)
AND s.source_id IN (SELECT id FROM user WHERE deleted = 0)) %s"""\
%("AND s.source_id <> c.user_id" if filter_self_comments else "", time_period_clause)
result_query = """CREATE TEMP TABLE result AS
SELECT min(t1.user1, t1.user2) AS fuser1, max(t1.user1, t1.user2) AS fuser2,
t1.c+coalesce(t2.c, 0) AS count
FROM
(SELECT *, count(user1) AS c FROM likejoin GROUP BY user1, user2) AS t1
LEFT JOIN
(SELECT *, count(user1) AS c FROM likejoin GROUP BY user1, user2) AS t2
ON t1.user2 = t2.user1 AND t1.user1 = t2.user2 AND t1.user1 <> t1.user2
GROUP BY fuser1, fuser2"""
users_query= """SELECT id, first_name, last_name FROM user WHERE id IN
(SELECT fuser1 FROM result UNION SELECT fuser2 FROM result)"""
self._db.query(likejoin_query);
self._db.query(commentjoin_query);
self._db.query(result_query);
min_max_count = self._db.query("SELECT min(count) as min_count, max(count) as max_count FROM result")[0]
g = Graph(min_max_count['min_count'], min_max_count['max_count'])
users = self._db.query(users_query)
for user in users:
g.add_node(str(user['id']), str(user['first_name'] + '.' + user['last_name'][0]))
likes = self._db.query("select * from result;")
for like in likes:
g.add_edge(str(like['fuser1']), str(like['fuser2']), like['count'])
g.render(label='test123')
def render_plot(self, type, count=10, first_name=None, last_name=None):
plots = {}
plots['top_posts'] = PlotInfo(query = """SELECT s.count, u.first_name || '.' || substr(u.last_name, 1, 1) AS name FROM
(SELECT count(*) AS count, source_id FROM stream WHERE %s GROUP BY source_id) AS s JOIN user AS u
ON s.source_id = u.id WHERE u.deleted = 0 ORDER BY s.count DESC LIMIT %d"""%(self._types, count),\
title = 'Top %d Posts'%(count), x = 'name')
plots['top_likes'] = PlotInfo(query = """SELECT s.count, u.first_name || '.' || substr(u.last_name, 1, 1) AS name FROM
(SELECT sum(like_count) AS count, source_id FROM stream WHERE %s GROUP BY source_id) AS s JOIN user AS u
ON s.source_id = u.id WHERE u.deleted = 0 ORDER BY s.count DESC LIMIT %d"""%(self._types, count),\
title = 'Top %d Likes'%(count), x = 'name')
plots['user_posts'] = PlotInfo(query = """select count(post_id) as count , strftime("%%m/%%d", datetime(created_time, 'unixepoch')) as day
from stream where source_id in (select id from user
where first_name = '%s' and last_name = '%s') and %s group by day"""%(first_name, last_name, self._types),\
title = 'Timeline: %s %s'%(first_name, last_name), x = 'day')
result = self._db.query(plots[type].query)
data = ''
count = len(result)
for row in result:
data += '%s %s\n'%(row['count'], row[plots[type].x])
plot_filepath = joinpath(dirname(realpath(__file__)), 'plot.gp')
plot = Popen(["gnuplot", "-e", "title='%s'"%(plots[type].title), "-e",\
"size = '%d, %d'"%(count*60, int(count*60/1.6)), plot_filepath],\
stdin=PIPE)
plot.communicate(data)
def add_job_period(self, start=None, end=None):
if start and end:
start_timestamp = (datetime.strptime(start, '%d%b%Y') - datetime(1970, 1, 1)).total_seconds()
end_timestamp = (datetime.strptime(end, '%d%b%Y') - datetime(1970, 1, 1)).total_seconds()
else:
start_timestamp = self._db.query("SELECT MAX(end_time) FROM job_period")[0][0]
if start_timestamp is not None and start_timestamp > time():
return
if start_timestamp is None:
start_timestamp = int(time())
end_timestamp = start_timestamp + int(timedelta(days=globals.job_period_days).total_seconds())
while start_timestamp < end_timestamp:
self._db.insert('job_period', (start_timestamp, start_timestamp + timedelta(hours=12).total_seconds(), 0))
start_timestamp += timedelta(hours=12).total_seconds()
self._db.commit()
def get_stream_job(self):
time_periods_query = "SELECT * FROM job_period WHERE end_time <= %d AND get_count < 2"%(int(time()) - timezone)
time_periods = self._db.query(time_periods_query)
for tp in time_periods:
self.get_stream(tp['start_time'], tp['end_time'])
self._db.query("UPDATE job_period SET get_count = %d WHERE end_time = %d"%(tp['get_count'] + 1, tp['end_time']))
self._db.commit()
def get_timestamp(self, date_string):
return (datetime.strptime(date_string, '%d%b%Y') - datetime(1970, 1, 1)).total_seconds()
def get_photos(self, start_time=None, end_time=None, cont=False):
def db_insert(jdata):
for record in jdata['data']:
for k in record:
if (record[k] == ''):
record[k] = None
self._db.insert('photo', (record['pid'], record['aid'], record['caption'],
record['comment_info']['comment_count'], record['like_info']['like_count'], record['created'],
record['link'], record['owner'], record['place_id'], record['src_big'], 0, 0))
self._db.commit()
print '.',; sys.stdout.flush()
if not (start_time and end_time):
if cont:
if os.path.exists('./end_time'):
end_time = int(open('./end_time', 'r').read().strip())
else:
end_time = int(time()) #+ int(timedelta(days=-6).total_seconds())
else:
end_time = int(time()) #+ int(timedelta(days=-6).total_seconds())
start_time = end_time + int(timedelta(hours=-12).total_seconds())
stream_query = """SELECT pid, aid, caption, comment_info, like_info, created,
link, owner, place_id, src_big FROM photo WHERE owner """
source_clause = "IN (SELECT uid2 FROM friend WHERE uid1=me())"
limit_clause = ' LIMIT 500'
time_clause = ' AND created > %d AND created < %d'%(start_time, end_time)
jdata = self.do_fql(stream_query + source_clause) # + time_clause)
db_insert(jdata)
source_clause = "= me()"
jdata = self.do_fql(stream_query + source_clause) # + time_clause)
db_insert(jdata)
if cont: open('end_time', 'w').write(str(start_time))