def get_last_entry(sport):
"""
Gets the last date entered into the database.
:return:
"""
cursor = DB()
if sport == 'football':
q = "SELECT MAX(week) AS date from rguru_stats"
elif sport == 'baseball':
q = "SELECT MAX(date) AS date from rguru_hitters"
elif sport == 'basketball':
q = "SELECT MAX(date) AS date from rguru_stats"
last_date = cursor.query(q)[0][0]
# If no entries in DB, get first date of season.
if last_date is None:
if sport == 'baseball':
last_date = datetime.date(2015, 4, 5)
elif sport == 'football':
last_date = 1
elif sport == 'basketball':
last_date = datetime.date(2014,10,28)
cursor.finish()
return last_date
def main():
db = DB('myun.db')
with open('last_num.txt') as f:
num = int(f.read())
account = '*****@*****.**' % (num/100+1)
#account = '*****@*****.**'
print 'Start from num:%s, use:%s' % (num, account)
while True:
if num % 100 == 0:
xiaohao.logout()
account = '*****@*****.**' % (num/100+1)
xiaohao = SendMsgHandler(account, 'woshixiaohao')
unames = db.get_uns(num)
print 'unames:%s' % unames.decode('utf-8','ignore').encode('gb2312','ignore')
while True:
result = xiaohao.send(unames)
if result:
print 'Successful:%s,%s' % (num, unames.decode('utf-8','ignore').encode('gb2312','ignore'))
break
with open('last_num.txt','w') as f:
f.write(str(num))
num += 5
def get_urls_soundcloud(url):
ImageUtils.debug("soundcloud: getting %s" % url)
from DB import DB
from json import loads
db = DB()
(client_id, secret_id) = db.get_credentials("soundcloud")
url = "http://api.soundcloud.com/resolve.json?url=%s&client_id=%s" % (url, client_id)
r = ImageUtils.httpy.get(url)
json = None
try:
json = loads(r)
if "download_url" in json:
download = json["download_url"]
if "?" in download:
download += "&"
else:
download += "?"
download += "client_id=%s" % client_id
return ("audio", None, [download])
except Exception, e:
from traceback import format_exc
print format_exc()
raise Exception("unable to parse json: %s" % str(e))
def write_lines(stat_line, sport):
"""
Takes a list of stat lines and inserts them into the database.
:param player_list:
List of player stats.
:param sport:
String of sport, e.g. 'baseball'
:return:
"""
cursor = DB()
player_id = stat_line[0]
date = stat_line[2]
q = []
if sport == 'baseball':
if stat_line[3] != 'P':
q.append(["DELETE FROM rguru_hitters WHERE id = %s AND date = %s", (player_id, date)])
q.append(["INSERT INTO rguru_hitters VALUES({})".format(','.join(['%s']*len(stat_line))), stat_line])
else:
q.append(["DELETE FROM rguru_pitchers WHERE id = %s AND date = %s", (player_id, date)])
q.append(["INSERT INTO rguru_pitchers VALUES({})".format(','.join(['%s']*len(stat_line))), stat_line])
elif sport == 'football':
q.append(["DELETE FROM rguru_stats WHERE id = %s AND week = %s", (player_id, date)])
q.append(["INSERT INTO rguru_stats VALUES({})".format(','.join(['%s']*len(stat_line))), stat_line])
elif sport == 'basketball':
q.append(["DELETE FROM rguru_stats WHERE id = %s AND date = %s", (player_id, date)])
q.append(["INSERT INTO rguru_stats VALUES({})".format(','.join(['%s']*len(stat_line))), stat_line])
for query in q:
cursor.query(*query)
cursor.finish()
def add_all_to_db():
"""Get a list of all symbols in the database, and fetch new data."""
# Connect to the database
db = DB(FILE)
# Get symbols from the ticker table
query = db.sql_query('SELECT Distinct symbol from %s;' % db.TABLE_SYM)
symbols = []
for row in query:
symbols.append(row[0])
# store all errors encountered to pass back up the chain
errors = []
for sym in symbols:
try:
# today's date:
dt = datetime.date.today()
date = str(dt.year) + '-' + str(dt.month) + '-' + str(dt.day)
# Check to see if the data is already there
query = db.sql_query('SELECT date from %s WHERE date=? AND symbol=?;' % db.TABLE, (date,sym,))
if len(query.fetchall()) == 0:
#print 'does not exist!'
add_data_db(sym, db)
db.db.commit()
except Exception as e:
errors.append(e)
return errors
def type_fch(self, request, reqid, protocol):
ip = protocol.transport.getPeer().host
def callb(res):
protocol.sendResponse(request, reqid)
self.parent.field_updated(res, request["Field"])
DB.get_local_devid_from_remote(ip, request["DevId"]).addCallback(callb)
def get_token(client_id, client_secret, code):
'''
This function should get any type of token
since the code is unique and should only
return the type of token that was created
in create_[...]_token
'''
db = DB()
try:
if db.contains(code):
token = db.get(code)
client = get_client(token.client)
if (not token.expire or token.expire + \
token.created > time()) and \
client.id == client_id and \
client.secret == client_secret:
return token
else:
logging.warn('get_token: Did not authenticate')
else:
logging.warn(''.join(['get_token: code ', str(code), ' is not in database']))
except Exception, e:
logging.error(''.join(['get_token(',
str(client_id),
',',
str(client_secret),
',',
str(code),
'): ',
str(e)]))
def get_access_token(token_str):
db = DB()
try:
if db.contains(token_str):
token = deepcopy(db.get(token_str))
if isinstance(token, AccessToken) and not token.expire or token.expire + token.created > time():
return token
else:
logging.warn(
"".join(
[
"get_access_token: Token ",
str(token.code),
" has expired for client ",
str(token.client.id),
" and user ",
str(token.user.id),
]
)
)
else:
logging.warn("".join(["get_access_token: token ", str(token_str), " is not in database"]))
except Exception, e:
logging.error("".join(["get_access_token(", str(token_str), "): ", str(e)]))
def getTestData(self):
data = np.array([])
yTrain = np.array([])
db = DB()
WINDOW_SIZE = 1000 # so luong item muon fetch
WINDOW_INDEX = 0
while True:
start = WINDOW_SIZE * WINDOW_INDEX + 1
stop = WINDOW_SIZE * (WINDOW_INDEX + 1)
# things = query.slice(start, stop).all()
query = "select id, cate_id, word_2 as content from site_content_3 order by id limit " + str(start) + ", " + str(WINDOW_SIZE)
print query
cursor = db.cursor()
cursor.execute(query)
rows = cursor.fetchall()
if rows == None or len(rows) == 0:
print("Total results: 0")
break
else:
print("Total results: " + str(len(rows)))
for row in rows:
content = row['content']
cateId = int(row['cate_id'])
data = np.append(data, content)
yTrain = np.append(yTrain, cateId)
WINDOW_INDEX += 1
return data, yTrain
def handlePushButton2(self, query, message):
if self.noneSelectedError(self.table.selectedItems()): return
reply = QMessageBox.question(self, 'Message', message, QMessageBox.Yes|QMessageBox.No,QMessageBox.No)
inClause = ",".join(["'"+self.table.item(x.row(),1).text()+"'" for x in self.table.selectedIndexes()])
res = 1 if reply==QMessageBox.Yes else 0
DB.query_("Update imei set {0}={1} where imei in ({2})".format(query,res,inClause))
self.populateTable()
def get_token(client_id, client_secret, code):
"""
This function should get any type of token
since the code is unique and should only
return the type of token that was created
in create_[...]_token
"""
db = DB()
try:
if db.contains(code):
token = db.get(code)
client = get_client(token.client)
if (
(not token.expire or token.expire + token.created > time())
and client.id == client_id
and client.secret == client_secret
):
return token
else:
logging.warn("get_token: Did not authenticate")
else:
logging.warn("".join(["get_token: code ", str(code), " is not in database"]))
except Exception, e:
logging.error("".join(["get_token(", str(client_id), ",", str(client_secret), ",", str(code), "): ", str(e)]))
class RedditMinner:
def __init__(self):
self.run = True
self.db = DB(celeryconfig.DATABASE)
self.logger = logging.getLogger('4 Chan Minner')
self.logger.info('Starting 4Chan manner')
def start(self):
while self.run:
try:
user_agent = ("Reddit Mining Feeder Lancaster 1.0 by /u/danjamker "
"github.com/danjamker/Reddit/")
r = praw.Reddit(user_agent=user_agent)
while self.run:
all_comments = r.get_comments('all')
for comment in all_comments:
tmp = Tools.serilize(comment.submission)
self.logger.info(tmp["id"])
self.db.insert_stream_thread(tmp)
mineReddit.delay(tmp["id"])
except Exception as e:
self.logger.error("{0} : Unexpected error GetAllComment.py-start: {1}".format(datetime.now().strftime("%c"), e.args))
time.sleep(60)
def stop(self):
self.run = False
def classifier():
nb = MultinomialNB(alpha=0)
nb.fit(DOC_TRAIN, CLASS_TRAIN)
db = DB()
query = 'select cate_id, tf, url, content from site_content_3'
cursor = db.cursor()
logger.info(query)
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
currentCateId = row['cate_id']
print 'rowID => ', row['cate_id'];
url = row['url']
tf = row['tf']
content = row['content']
termFrequencyDict = {}
# continue
try:
termFrequencyDict = json.loads(tf)
except:
print 'error => ', url
continue
testItem = np.array([])
for word in termFrequencyDict:
tf = termFrequencyDict[word]
if WORDS.has_key(word):
testItem = np.append([tf])
else:
testItem = np.append([0])
print "CURRENT CATE ", currentCateId
print "NEW ", nb.predict(testItem)
def update_main_from_file_senegal(self):
try:
query = open(self.sql_main ,'r').read()
abo_for_query =','.join(self.num_abo_list)
query = query.replace( '#', '(' + abo_for_query + ')')
print '\n'
print query
print '\n'
while True:
try:
db = DB()
except:
time.sleep(10)
else:
break
db.excute_many_for_senegal(
query, self.map
)
except:
import sys
print '='*40, 'ERROR', "="*40
print sys.exc_info()
else:
# send an email
while True:
try:
body ="""
Bonjour,</br>
Les fichiers suivants on etes traites comme des fichiers a charger.</br>
Les clients y figurant sont en production </br>
%s</br>
Pour un volume de (%s) lignes recues </br>
Ce volume est reparti comme suit</br>
%s</br>
Cordialement</br>
Service Informatique</br>
"""%("</br>".join(self.map.keys() or []),
len(self.num_abo_list),
self.main_fichier
)
to = [ '*****@*****.**']
"""
to = ['*****@*****.**',
'*****@*****.**']
"""
subject="[PCCI] Chargement fichier du %s """%self.TODAY
send.send(att_path = None , body =body,
to = to , subject =subject)
except:
# Outlook a des preoblemes mais nous devons absolument
# envoyer l'email puisque le chargement a deja eu lieu
print 'Exception into send Message'
traceback.print_exc()
time.sleep(30)
else:
# L'email est partie tout va bien
break
def add_email(self, email, classs):
con = DB.connect()
table = self._table
cur = con.cursor()
cur.execute("INSERT INTO %s VALUES ("+"'"+email+"','"+classs+"'")
DB.close(con)
def get_associations(user):
db = DB()
try:
key = 'client_association_' + str(user.id)
if db.contains(key):
return deepcopy(db.get(key))
except Exception, e:
logging.error('get_associations: ' + str(e))
def add_chi_square(self, chi_square, email_id, token):
con = DB.connect()
table = self._table
cur = con.cursor()
cur.execute("INSERT INTO %s VALUES (%ld, %s, %f)" % (table, email_id, token, chi_square))
DB.close(con)
def createUser(self, username, numTasksDaily, workloadDaily):
db = DB()
# Instancia objeto user
user = User(username, numTasksDaily, workloadDaily)
self.userList.append(user)
# Insere usuario no BD
db.insertUserSettings(username, numTasksDaily, workloadDaily)
def type_wel(self, request, reqid, protocol):
def callb(res, ip):
protocol.sendResponse({"Name": self.config.name}, reqid)
if ip in self.connecting_servers:
self.connecting_servers[ip].callback(res)
ip = protocol.transport.getPeer().host
port = protocol.transport.getPeer().port
self.peers[ip] = protocol
DB.update_devices(ip, port, request["Name"], request["Devices"]).addCallback(callb, ip)
def deleteUser(self):
db = DB()
# Deletar User no Banco de Dados...
db.deleteUserSettings()
# Remove da lista de User
if len(self.userList) != 0:
self.userList.pop(0)
def get_client(client_id):
db = DB()
try:
if db.contains(client_id):
client = db.get(client_id)
return deepcopy(client)
except Exception, e:
logging.error(''.join(['get_client', str(e)]))
def listSubTask(self):
db = DB()
subTaskList = []
subTasks = db.selectSubTask(self.__idTask)
for i in range(0, len(subTasks)):
s = SubTask(subTasks[i][0], subTasks[i][2], subTasks[i][3])
s.set_idSubTask(subTasks[i][1])
subTaskList.append(s)
return subTaskList
def get_users(sortby='username', orderby='asc', start=0, count=20):
if sortby not in ['username', 'created', 'updated']:
sortby = 'username'
if orderby not in ['asc', 'desc']:
orderby = 'asc'
query = '''
select
id, users.username, users.created, users.updated
from users
order by %s %s
limit %d
offset %d
''' % (sortby, orderby, count, start)
db = DB()
cur = db.conn.cursor()
execur = cur.execute(query)
results = execur.fetchall()
users = []
for (userid, username, created, updated) in results:
images = []
query = '''
select
path, width, height, size, thumb, type
from images
where
images.userid = ?
limit 4
'''
execur = cur.execute(query, [userid])
image_results = execur.fetchall()
for (path, width, height, size, thumb, imagetype) in image_results:
images.append({
'path' : path,
'width' : width,
'height' : height,
'size' : size,
'thumb' : thumb,
'type' : imagetype
})
post_count = db.count('posts', 'userid = ?', [userid])
image_count = db.count('images', 'userid = ? and (type = \'image\' or type = \'album\')', [userid])
video_count = db.count('images', 'userid = ? and type = \'video\'', [userid])
users.append( {
'user' : username,
'created' : created,
'updated' : updated,
'images' : images,
'post_n' : post_count,
'image_n' : image_count,
'video_n' : video_count
})
cur.close()
return {
'users' : users
}
class CLI:
def __init__(self, db_file):
self.db = DB(db_file)
self.cli_re = re.compile(r'^\s*(?P<cmd>\S+)\s*(?P<nick>\S+)?\s*(?P<name>.+)?$')
def help(self):
print """
new [nick [name]] Register new member with optional nick and name (will be prompted if not supplied)
list Show all members that are currently in the database
quit Exit the program
help Show this
"""
def start(self):
while True:
try:
inp = raw_input('[ Verdande ]$ ')
match = self.cli_re.match(inp)
if match:
cmd = match.group('cmd')
if cmd == 'new':
nick = match.group('nick') if match.group('nick') else raw_input('> Nick: ')
name = match.group('name') if match.group('name') else raw_input('> Full Name: ')
if not self.db.add_member(nick, name):
print "Member %s allready exist." % (nick)
else:
print "Successfully registered %s." % (nick)
elif cmd == 'list' or cmd == 'show':
print self.db
elif cmd == 'del':
if raw_input('are you root? ') == 'I always run as root!':
print "ok"
nick = match.group('nick') if match.group('nick') else raw_input('> Nick: ')
self.db.del_member(nick)
else:
print "No you're not!"
elif cmd == 'quit':
answ = raw_input('Are you sure you want to quit [y/N]? ')
if answ == 'y' or answ == 'Y':
break
else:
self.help()
except EOFError:
answ = raw_input('\nAre you sure you want to quit [y/N]? ')
if answ == 'y' or answ == 'Y':
break
self.db.close()
def getCustomPathJSON(self,requestAsJson):
try: # enter the curr loc from json here
if requestAsJson.get('startLocation'):
latidude = requestAsJson['startLocation']['latitude']
longitude = requestAsJson['startLocation']['longitude']
toReturn = DB.getCustomPath(latidude, longitude ,requestAsJson['endPlace'])
else:
toReturn = DB.getCustomPath(40.249145, -111.649238,requestAsJson['endPlace'])
except Exception, e:
toReturn = Parser.error(str(e))
def get_count_not_term_on_class(self, term, classs):
con = DB.connect()
cur = con.cursor()
cur.execute("SELECT * FROM %s WHERE term != %s AND class = %s" % (self._table, term, classs))
count = cur.rowcount
DB.close(con)
return count
def get_alive_createpending_num(fi_id):
"""Return the number of the Campaigns of the account which are alive or
create_pending.
"""
db = DB()
query_tuple = ("SELECT COUNT(*) AS COUNT FROM Campaigns WHERE (LOCAL_STATUS = 2 OR LOCAL_STATUS = 3) AND FI_ID=%s",fi_id)
cur = db.execute(query_tuple)
if cur.rowcount == 0:
raise Exception('TwitterCampaign', 'No such user %d'%fi_id)
return cur.fetchone()['COUNT']
def get_count_email(self):
con = DB.connect()
cur = con.cursor()
cur.execute("SELECT * FROM %s" % self._table)
count = cur.rowcount
DB.close(con)
return count
def delete_user(user, blacklist, reason, admin):
from shutil import rmtree
from os import path as ospath
from DB import DB
db = DB()
count = db.count('albums', 'author = ?', [user])
if count == 0:
raise Exception('user %s does not have any albums' % user)
response = ''
for (rowid, host, album, path, views) in db.select('rowid, host, name, path, views', 'albums', 'author = ?', [user]):
response += 'album %s (%d views) was ' % (path, views)
if blacklist:
try:
db.insert('blacklist', (host, album, reason, admin))
response += 'blacklisted and '
except Exception, e:
response += 'not blacklisted (%s) and ' % str(e)
try:
db.delete('medias', 'album_id = ?', [rowid])
db.delete('urls', 'album_id = ?', [rowid])
db.delete('albums', 'rowid = ?', [rowid])
rmtree(ospath.join('rips', path))
response += 'deleted '
except Exception, e:
response += 'not deleted (%s) ' % str(e)
def update_association(user_id, client_id, refresh_token_str):
client = get_client(client_id)
user = get_user(user_id)
logging.warn('update_associations 1: ' + str(refresh_token_str))
refresh_token = get_token(client_id, client.secret, refresh_token_str)
#always check to see if it is confidential or not.
#it shouldn't be if it's using update_association, but you never know
#and it's good to have a log message to possible alert the admin that
#this is going on.
if client.type.lower() != 'confidential':
raise ConfidentailError('Client ' + client_id + \
' is not a confidentail client')
db = DB()
try:
key = 'client_association_' + str(user.id)
if db.contains(key):
association = db.get(key)
if client.id in association.clients:
logging.warn('update_associations 2: ' + str(association.clients[client.id]))
old_refresh = get_token(client.id, client.secret, association.clients[client.id])
delete_token(old_refresh.access_token)
delete_token(old_refresh.code)
association.clients[client.id] = refresh_token.code
logging.warn('update_associations 3: ' + str(refresh_token.code) + ', ' + str(association.clients[client.id]))
db.update(key, association)
db.commit()
#except Exception, e:
# logging.error('update_associations: ' + str(e))
# db.abort()
finally:
db.close()
return False
def test_q7():
db = DB(DB_NAME)
assert db.query(q7)[0][0] == 'SEA'
assert db.query(q7)[0][1] == 0.280799158489005
def test_q8():
db = DB(DB_NAME)
assert db.query(q8)[0][0] == 2
