def onPlayBackStopped(self):
try:
xbmc.log('990.ro: Playback Stopped')
if self.tracking:
playedTime = int(self._lastPos)
percent = int((playedTime / self._totalTime) * 100)
pT = convert_seconds(playedTime)
tT = convert_seconds(self._totalTime)
xbmc.log('990.ro: Service: %s played of %s, total = %s%%' %
(pT, tT, str(percent)))
if playedTime == 0 and self._totalTime == 999999:
raise RuntimeError(
'XBMC silently failed to start playback')
elif (percent > 85) and self.video_type:
xbmc.log(
'990.ro: Service: Threshold met. Marking item as watched'
)
sql = 'DELETE FROM bookmarks WHERE video_type=? AND title=? AND season=? AND episode=?'
import db
db = database.connect(db_dir)
cur = db.cursor()
cur.execute(sql,
(self.video_type, unicode(self.title, 'utf-8'),
self.season, self.episode))
db.commit()
db.close()
else:
xbmc.log(
'990.ro: Service: Threshold not met. Saving bookmark')
sql = 'INSERT OR REPLACE INTO bookmarks (video_type, title, season, episode, bookmark) VALUES (?,?,?,?,?)'
db = database.connect(db_dir)
cur = db.cursor()
cur.execute(sql,
(self.video_type, unicode(self.title, 'utf-8'),
self.season, self.episode, playedTime))
db.commit()
db.close()
subtitle = os.path.join(tmp_dir, self.subtitle)
if os.path.exists(subtitle):
os.remove(subtitle)
except:
pass
self.reset()
def decorated(*args, **kwargs): global credentials auth = flask.request.authorization if not auth: return authenticate() credentials.update(auth) try: db.cursor(**credentials) except db.UnauthorizedAccessException, message: return authenticate(message)
def top_cost():
with db.cursor() as c:
last_kill = db.get(c, 'SELECT MAX(kill_id) AS kill_id FROM kills')
kills = db.query(c, '''
SELECT kills.kill_id, cost, solar_system_id, kill_time,
ship_type_id, typeName AS ship_name
FROM kills
JOIN kill_costs ON kill_costs.kill_id = kills.kill_id
JOIN kill_characters ON kill_characters.kill_id = kills.kill_id
JOIN eve.invTypes ON typeID = ship_type_id
WHERE victim = 1 AND kills.kill_id > ?
ORDER BY cost DESC
LIMIT 25
''', last_kill['kill_id'] - 2500)
# joining eve.mapSolarSystems on the initial query causes filesort on large dbs for some reason
# do a manual join
system_ids = set(map(operator.itemgetter('solar_system_id'), kills))
system_rows = db.query(c, '''
SELECT solarSystemID as solar_system_id, solarSystemName AS system_name,
security, class AS wh_class
FROM eve.mapSolarSystems
LEFT JOIN wh_systems ON solarSystemID = wh_systems.id
WHERE solarSystemID IN ({})
'''.format(','.join(map(str, system_ids))))
systems = {}
for system in system_rows:
systems[system['solar_system_id']] = system
for kill in kills:
kill.update(systems[kill['solar_system_id']])
del kill['solar_system_id']
kill['security_status'] = _security_status(kill['security'], kill['wh_class'])
kill['kill_time'] = _format_kill_time(kill['kill_time'])
return kills
def import_csv():
recipient = request.form.get("recipient")
if recipient is None:
raise ValueError("Can't find recipient")
prefix = "import-"
suffix = "@{}".format(config.mailgun_domain)
if not recipient.startswith(prefix) or not recipient.endswith(suffix):
raise ValueError("Unexpected recipient: {}".format(recipient))
import_token = recipient[len(prefix):-len(suffix)]
body = request.form.get("body-plain")
if body is None:
raise ValueError("Can't find body")
words = body.split()
url = next(
(word
for word in words if word.startswith("https://tickets.edfringe.com/")
and word.endswith(".csv")),
None,
)
if url is None:
raise ValueError("Can't find csv link in body")
with db.cursor(config) as cur:
cur.execute("SELECT id FROM users WHERE import_token = %s",
(import_token, ))
row = cur.fetchone()
if row is None:
raise ValueError("Unknown import token: {}".format(import_token))
uid = row[0]
Process(target=import_from_url_from_config,
args=(config, uid, url)).start()
return "OK"
def main():
rs = requests.session()
with db.cursor() as c:
if len(sys.argv) == 2:
kill_id = sys.argv[1]
response = rs.get('http://api.whelp.gg/kill/' + kill_id)
character_id = response.json()['victim']['character_id']
kill_id = int(kill_id)
url = 'https://zkillboard.com/api/losses/characterID/{}/beforeKillID/{}/limit/200'
response = rs.get(url.format(character_id, kill_id + 200))
data = response.json()
print('got {} kills'.format(len(data)))
for kill in data:
if kill['killID'] != kill_id:
continue
if db.queries.insert_kill(c, kill):
print('inserted!')
else:
print('duplicate')
return
groups = db.query(c, 'SELECT groupID FROM eve.invGroups WHERE categoryID = ?', 6)
groups = list(map(operator.itemgetter('groupID'), groups))
last_kill_ids = {}
for i in range(0, len(groups), 10):
query_groups = ','.join(map(str, groups[i:i+10]))
last_kill_ids[query_groups] = None
last_request_time = 0
while True:
for query_group in last_kill_ids:
path = '/api/losses/api-only/groupID/' + query_group
last_kill_id = last_kill_ids[query_group]
if last_kill_id is not None:
path += '/beforeKillID/' + str(last_kill_id)
now = time.time()
if now - last_request_time < 12:
sleep_secs = 12 - (now - last_request_time)
print('sleeping', sleep_secs)
time.sleep(sleep_secs)
last_request_time = time.time()
try:
r = rs.get('https://zkillboard.com' + path)
kills = r.json()
except Exception as e:
print(repr(e))
break
print('inserting', len(kills), 'kills', end='... ')
sys.stdout.flush()
inserted = 0
try:
for kill in kills:
if db.queries.insert_kill(c, kill):
inserted += 1
except TypeError as e:
print(repr(e), kills)
break
db.conn.commit()
print(len(kills) - inserted, 'dupes')
last_kill_id = kills[-1]['killID']
last_kill_ids[query_group] = last_kill_id
def handle_login():
email = request.form.get("email", None)
password = request.form.get("password", None)
if email is None or password is None:
return flask.redirect(flask.url_for("login", error="true",
email=email))
with db.cursor(config) as cur:
cur.execute(
"SELECT id, password_hash FROM users WHERE email = %s AND confirm_email_token is NULL",
(email, ),
)
row = cur.fetchone()
if row is None:
return flask.redirect(
flask.url_for("login", error="true", email=email))
id, password_hash = row
try:
PasswordHasher().verify(password_hash, password)
except VerifyMismatchError:
return flask.redirect(flask.url_for("login", error="true",
email=email))
login_user(User("{}".format(id)), remember=True)
index_url = flask.url_for("index")
target = flask.request.args.get("next", index_url)
safe_target = target if is_safe_url(target) else index_url
return flask.redirect(safe_target)
def search(q):
like_str = '{}%'.format(q)
with db.cursor() as c:
alliances = db.query(c, '''
SELECT alliance_id, alliance_name FROM alliances
WHERE alliance_name LIKE ? LIMIT 25
''', like_str)
corps = db.query(c, '''
SELECT corporation_id, corporation_name FROM corporations
WHERE corporation_name LIKE ? LIMIT 25
''', like_str)
chars = db.query(c, '''
SELECT character_id, character_name FROM characters
WHERE character_name LIKE ? LIMIT 25
''', like_str)
systems = db.query(c, '''
SELECT solarSystemID AS system_id, solarSystemName AS system_name
FROM eve.mapSolarSystems
WHERE solarSystemName LIKE ? LIMIT 5
''', like_str)
ships = db.query(c, '''
SELECT typeID AS ship_id, typeName AS ship_name FROM eve.invTypes
JOIN eve.invGroups ON invTypes.groupID = invGroups.groupID
WHERE typeName LIKE ? AND invGroups.categoryID = 6 LIMIT 5
''', like_str) # 6 == ship
return {
'alliances': alliances,
'corporations': corps,
'characters': chars,
'systems': systems,
'ships': ships,
}
def available_options(self):
with db.cursor()as cur:
cur.execute(
"""SELECT option_id FROM options
WHERE feature_id = %s;""",
(self.feature_id,))
return [Option(x['option_id']) for x in cur.fetchall()]
def setUpdateStatus(self):
"""
Check the ilcu_update_running column, if set, raise error,
else set it, commit, and return
"""
checkRunningQuery = u"""SELECT ilcu_update_running
FROM {status_table}
WHERE ilcu_lang=%(lang)s""".format(
status_table=self.tool_status_table)
setRunningQuery = u"""UPDATE {status_table}
SET ilcu_update_running=1
WHERE ilcu_lang=%(lang)s""".format(
status_table=self.tool_status_table)
with db.cursor(self.tool_db_conn, 'dict') as db_cursor:
db_cursor.execute(checkRunningQuery, {'lang': self.lang})
row = db_cursor.fetchone()
db_cursor.fetchall() # flush cursor
if row['ilcu_update_running']:
raise UpdateRunningError
db_cursor.execute(setRunningQuery, {'lang': self.lang})
if db_cursor.rowcount != 1:
raise UpdateTableError
self.tool_db_conn.commit()
# OK, done.
return ()
def process_item(self, item, spider):
if isinstance(item, GuaziItem):
try:
db = car_source_pool.connection()
cursor = db.cursor()
sql = """INSERT INTO `CAR` (`source_site`,`city_id`,`price`,`new_price`,`discharge`,`miles`,`gear_box`,`title`,`desc_str`,`emission`,`car_city_zh`,`license_date`,`car_src_url`,`car_src_id`,`tel`,`status`,`transfer_num`,`detected`,`personal`,`visit_time`,`publish_date`,`lock_user_id`,`car_finger_hash`)
VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (
item['sourceSite'], 1, item['price'], item['newPrice'],
item['discharge'], item['miles'], item['gearBox'],
item['title'], item['descStr'], item['emission'],
item['carCityZh'], item['licenseDate'], item['carSrcUrl'],
item['carSrcId'], item['tel'], item['status'],
item['transferNum'], item['detected'], item['personal'],
item['visitTime'], item['publishDate'], item['lockUserId'],
item['carFingerHash'])
result = cursor.execute(sql)
car_id = int(cursor.lastrowid)
sql2 = """INSERT INTO `car_img` (`car_id`,`first_img_str`,`img_str`)
values ('%s','%s','%s')""" % (car_id, item['firstImgStr'],
item['imgStr'])
result2 = cursor.execute(sql2)
db.commit()
db.close()
except Exception as e:
log.msg("2", level=log.WARNING)
print e
finally:
pass
return item
def setUpdateStatus(self):
"""
Check the ilcu_update_running column, if set, raise error,
else set it, commit, and return
"""
checkRunningQuery = u"""SELECT ilcu_update_running
FROM {status_table}
WHERE ilcu_lang=%(lang)s""".format(
status_table=self.tool_status_table)
setRunningQuery = u"""UPDATE {status_table}
SET ilcu_update_running=1
WHERE ilcu_lang=%(lang)s""".format(
status_table=self.tool_status_table)
with db.cursor(self.tool_db_conn, 'dict') as db_cursor:
db_cursor.execute(checkRunningQuery, {'lang': self.lang})
row = db_cursor.fetchone()
db_cursor.fetchall() # flush cursor
if row['ilcu_update_running']:
raise UpdateRunningError
db_cursor.execute(setRunningQuery, {'lang': self.lang})
if db_cursor.rowcount != 1:
raise UpdateTableError
self.tool_db_conn.commit()
# OK, done.
return()
def setups():
"""set up classifier if not yet trained"""
if philosophyfilter.is_ready():
return
db.close()
db.connection(db='test_opp')
ham = scraper.Doc(url='http://umsu.de/papers/magnetism2.pdf')
ham.load_from_db()
ham.content = readfile(os.path.join(testdir, 'attitudes.txt'))
ham.update_db()
spam = scraper.Doc(url='http://umsu.de/papers/spam.pdf')
spam.load_from_db()
spam.content = """
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do
eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut
enim ad minim veniam, quis nostrud exercitation ullamco laboris
nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor
in reprehenderit in voluptate velit esse cillum dolore eu
fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est
laborum.
"""
spam.update_db()
cur = db.cursor()
query = "SELECT cat_id FROM cats WHERE label=%s LIMIT 1"
cur.execute(query, ('philosophy',))
cat_id = cur.fetchall()[0]
query = ("INSERT IGNORE INTO docs2cats (doc_id, cat_id, strength, is_training)"
"VALUES (%s, %s, %s, %s)")
cur.execute(query, (ham.doc_id, cat_id, 1, 1))
cur.execute(query, (spam.doc_id, cat_id, 0, 1))
philosophyfilter.update()
def route_table(src_lat, src_lon, dst_lat, dst_lon):
with db.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cursor:
cursor.execute(QUERY_RT, {
'start_lat': src_lat, 'start_lon': src_lon,
'end_lat': dst_lat, 'end_lon': dst_lon
})
return flask.jsonify(cursor.fetchall())
def gather_historic(local_db, wiki_db, start_date, end_date=None, step=5):
'''
Gather historic data over a larger timespan covering a given number
of days at a time. Returns a list of DataPoint named tuples in no
specific order.
:param local_db: Database connection to the local database
:type local_db: MySQLdb.Connection
:param wiki_db: Database connection to the replicated Wikipedia database
:type wiki_db: MySQLdb.Connection
:param start_date: First date to gather data for
:type start_date: datetime.date
:param end_date: Last date to gather data for
:type end_date: datetime.date
:param step: Number of days to gather data for in each iteration
:type step: int
'''
userid_query = '''SELECT as_userid
FROM account_stats
WHERE as_reg_timestamp >= %(start)s
AND as_reg_timestamp < %(end)s'''
cur_date = start_date
delta_days = dt.timedelta(days=step)
datapoints = []
if not end_date:
end_date = dt.date.today()
while cur_date < end_date:
stop_date = cur_date + delta_days
if stop_date > end_date:
stop_date = end_date
logging.info('gathering data from {} to {}'.format(
cur_date, stop_date))
userids = []
with db.cursor(local_db, 'dict') as db_cursor:
db_cursor.execute(userid_query, {
'start': cur_date,
'end': stop_date
})
for row in db_cursor:
userids.append(row['as_userid'])
logging.info('checking activity for {} users'.format(len(userids)))
datapoints.extend(gather_data(wiki_db, userids))
cur_date = stop_date
return (datapoints)
def add_new_tables():
sql_stmts = (
'CREATE TABLE IF NOT EXISTS comment4 (id INT PRIMARY KEY, text TEXT)',
'CREATE TABLE IF NOT EXISTS comment5 (id INT PRIMARY KEY, text TEXT)',
)
cursor = db.cursor()
for sql in sql_stmts:
cursor.execute(sql)
def search(query):
c = db.cursor()
c.execute("SELECT content FROM quotes WHERE content LIKE ?",
('%' + query + '%', ))
results = []
for result in c.fetchall():
results.append(result[0])
return results
def save(self):
table_name = Comment.get_table_name(self.id)
cursor = db.cursor()
cursor.execute(
"INSERT INTO %s(id, text) values (%s, '%s')" % (
table_name, self.id, self.text
)
)
def onPlayBackStopped(self):
try:
xbmc.log('990.ro: Playback Stopped')
if self.tracking:
playedTime = int(self._lastPos)
percent = int((playedTime / self._totalTime) * 100)
pT = convert_seconds(playedTime)
tT = convert_seconds(self._totalTime)
xbmc.log('990.ro: Service: %s played of %s, total = %s%%' % (pT, tT, str(percent)))
if playedTime == 0 and self._totalTime == 999999:
raise RuntimeError('XBMC silently failed to start playback')
elif (percent > 85) and self.video_type:
xbmc.log('990.ro: Service: Threshold met. Marking item as watched')
sql = 'DELETE FROM bookmarks WHERE video_type=? AND title=? AND season=? AND episode=?'
import db
db = database.connect(db_dir)
cur = db.cursor()
cur.execute(sql, (self.video_type, unicode(self.title, 'utf-8'), self.season, self.episode))
db.commit()
db.close()
else:
xbmc.log('990.ro: Service: Threshold not met. Saving bookmark')
sql = 'INSERT OR REPLACE INTO bookmarks (video_type, title, season, episode, bookmark) VALUES (?,?,?,?,?)'
db = database.connect(db_dir)
cur = db.cursor()
cur.execute(sql, (self.video_type, unicode(self.title, 'utf-8'), self.season, self.episode, playedTime))
db.commit()
db.close()
subtitle = os.path.join(tmp_dir, self.subtitle)
if os.path.exists(subtitle):
os.remove(subtitle)
except: pass
self.reset()
def new_feature(description):
with db.cursor() as cur:
cur.execute("""
INSERT INTO features(description)
VALUES (%s)
RETURNING feature_id;""",
(description,))
return cur.fetchone()['feature_id']
def set_interest(config, user_id, show_id, interest):
with cursor(config) as cur:
cur.execute(
"INSERT INTO interests (show_id, user_id, interest) VALUES (%(show_id)s, %(user_id)s, %(interest)s) "
+ "ON CONFLICT ON CONSTRAINT interests_show_id_user_id_key DO " +
"UPDATE SET interest = %(interest)s where interests.show_id = %(show_id)s and interests.user_id = %(user_id)s",
dict(show_id=show_id, user_id=user_id, interest=interest),
)
def delete(self):
table_name = Comment.get_table_name(self.id)
cursor = db.cursor()
cursor.execute(
"DELETE FROM %s where id = %s and text = '%s'" % (
table_name, self.id, self.text
)
)
def count_of_allnet_car_source(): db = wcar_pool.connection() cursor = db.cursor() sql = """SELECT COUNT(*) FROM `car_allnet_source`""" cursor.execute(sql) results = cursor.fetchall() db.close() return results[0][0]
def handle_batch(c): for i in range(50): r = c.fetchone() if r is None: break with db.cursor() as ic: fill_for_kill(ic, r) return i == 49
def getall() -> Tuple[Feed]:
"""
Get all feeds currently in the db
:return: All current feeds
"""
sql = 'SELECT id FROM feeds;'
c = db.cursor()
c.execute(sql)
return tuple(Feed(x['id']) for x in c.fetchall())
def delete(color_id):
try:
cursor = db.cursor()
cursor.execute("DELETE FROM colors WHERE color_id=%s", [color_id])
cursor.close()
db.commit()
except MySQLdb.Error as err:
print(err)
return None
def get_all():
tables = ['comment%s' % i for i in range(NUM_COMMENT_TABLES)]
cursor = db.cursor()
for table in tables:
db_res = cursor.execute(
"SELECT id, text from %s;" % table
)
for dbr in db_res:
yield Comment(dbr[1], id=dbr[0])
def new_id():
tables = ['comment%s' % i for i in range(NUM_COMMENT_TABLES)]
cursor = db.cursor()
db_res = [
cursor.execute("SELECT max(id) from %s" % table).next()
for table in tables
]
max_id = max([rs[0] if rs[0] else 0 for rs in db_res])
return max_id + 1
def __init__(self, feature_id):
with db.cursor() as cur:
cur.execute(
"""SELECT * FROM features
where feature_id = %s;""",
(feature_id,))
results = cur.fetchone()
for k, v in results.items():
setattr(self, k, v)
def add(description, uom=None, component=None, parent_id=None):
with db.cursor() as cur:
cur.execute(
"""
INSERT INTO options(description, uom, component, parent_id)
VALUES(%s, %s, %s, %s)
RETURNING option_id;""",
(description, uom, component, parent_id))
return cur.fetchone()['option_id']
def car_brand_map_insert(car_brand): db = car_type_pool.connection() cursor = db.cursor() sql = """INSERT INTO `car_brand_map`(`273_brand_id`, `273_brand_name`, `168_brand_id`, `168_brand_name`) VALUES (%d, '%s', %d, '%s')""" % (car_brand['273_brand_id'], car_brand['273_brand_name'], car_brand['168_brand_id'], car_brand['168_brand_name']) print sql result = cursor.execute(sql) db.commit() cursor.close() db.close()
def populate_table(table_name, input_filename, batch_size=1000):
'''
Populate the given table with the dataset of article creation statistics.
:param table_name: name of the database table we are populating
:type table_name: str
:param input_filename: path to the input file with data
:type input_filename: str
:param batch_size: number of inserts to make before issuing a commit
:type batch_size: int
'''
insert_query = '''INSERT INTO {table}
(ac_rev_id, ac_timestamp)
VALUES (%s, %s)'''.format(table=table_name)
db_conn = db.connect('tools.labsdb', 's53463__actrial_p',
'~/replica.my.cnf')
if not db_conn:
logging.error('unable to connect to database server')
return ()
with bz2.open(input_filename, 'rt') as infile:
infile.readline() # skip header
i = 0
datapoints = []
with db.cursor(db_conn) as db_cursor:
for line in infile:
(rev_timestamp, rev_id) = line.strip().split('\t')
rev_timestamp = dt.datetime.strptime(rev_timestamp,
'%Y-%m-%d %H:%M:%S.0')
rev_id = int(rev_id)
datapoints.append((rev_id, rev_timestamp))
i += 1
if i % batch_size == 0:
db_cursor.executemany(insert_query, datapoints)
logging.info('inserted {} entries, committing'.format(i))
db_conn.commit()
datapoints = []
# commit any outstanding inserts
db_cursor.executemany(insert_query, datapoints)
logging.info('inserted {} entries, committing'.format(i))
db_conn.commit()
db_conn.close()
logging.info('completed inserting {} entries'.format(i))
# ok, done
return ()
def on_message(self, headers, message):
try:
data = json.loads(message)
with db.cursor() as c:
db.queries.insert_kill(c, data)
self.inserted += 1
if self.inserted % 500 == 0:
log.write('stomp inserted {} kills'.format(self.inserted))
except Exception as e:
log.write('stomp error: {}, {}\n{}'.format(headers, message, traceback.format_exc()))
def remove_interest(config, user_id, show_id):
with cursor(config) as cur:
cur.execute(
"DELETE FROM interests WHERE user_id = %s AND show_id = %s",
(user_id, show_id),
)
cur.execute(
"DELETE FROM performance_interests WHERE user_id = %s AND show_id = %s",
(user_id, show_id),
)
def mark_booked(config, user_id, performance_id):
set_performance_interest(config,
user_id,
performance_id,
interest="Booked")
with cursor(config) as cur:
cur.execute("SELECT show_id FROM performances WHERE id = %s",
(performance_id, ))
show_id = cur.fetchone()[0]
set_interest(config, user_id, show_id, "Booked")
def empty_model_count_of_allnet_car_source(): db = wcar_pool.connection() cursor = db.cursor() sql = """SELECT COUNT(1) FROM `car_allnet_source` WHERE 1 AND `series_id` > 0""" cursor.execute(sql) results = cursor.fetchall() db.close() return results[0][0]
def list():
with db.cursor() as c:
c.execute("SELECT state,code,error,error_description,inserted FROM token ORDER by inserted")
rows = []
for state, code, error, error_description, inserted in c:
if error:
rows.append("%s : ERROR %s (%s) [%s]" % (state, error, error_description, inserted))
else:
rows.append("%s : %s [%s]" % (state, code, inserted))
return text("\n".join(rows))
def main():
with db.cursor() as c:
i = 0
while True:
db.execute(c, 'SELECT kill_id FROM kills ORDER BY kill_id ASC LIMIT 50 OFFSET %s', i)
if not handle_batch(c):
break
i += 50
if i % 1000 == 0:
print('updated for', i, 'kills')
def get_all():
try:
cursor = db.cursor()
cursor.execute("SELECT color_id,name,hex FROM colors")
result = cursor.fetchall()
cursor.close()
return result
except MySQLdb.Error as err:
print(err)
return None
def update_db(self, **kwargs):
"""write **kwargs to db, also update 'last_checked'"""
if self.source_id:
cur = db.cursor()
kwargs['last_checked'] = time.strftime('%Y-%m-%d %H:%M:%S')
query = "UPDATE sources SET urlhash=MD5(url),{} WHERE source_id = %s".format(
",".join(k+"=%s" for k in kwargs.keys()))
cur.execute(query, tuple(kwargs.values()) + (self.source_id,))
debug(3, cur._last_executed)
db.commit()
def get(state):
with db.cursor() as c:
c.execute("SELECT code,error,error_description FROM token WHERE state = %s", (state,))
try:
token, error, error_description = c.fetchone()
if error:
return text("ERROR: %s (%s)" % (error, error_description))
else:
return text("%s" % token)
except TypeError, e:
return text("Key not found", 404)
def import_form():
with db.cursor(config) as cur:
cur.execute("SELECT import_token FROM users WHERE id = %s",
(user_id(), ))
row = cur.fetchone()
if row is None:
raise ValueError("Internal error: couldn't find import token")
import_token = row[0]
import_email = "import-{}@{}".format(import_token,
config.mailgun_domain)
return render_template("import.html", import_email=import_email)
def children(option):
''' recursively adds children to an option '''
with db.cursor() as cur:
cur.execute("""
SELECT * FROM options
WHERE parent_id = %s;""",
(option['option_id'],))
option['children'] = cur.fetchall()
for child in option['children']:
child = children(child)
return option
def car_series_of_brand_id_of_168(brand_id): db = car_type_pool.connection() cursor = db.cursor() sql = """SELECT `168_series_id`, `168_series_name` FROM `car_series_168` WHERE 1 AND `168_brand_id` = %d""" % (brand_id) cursor.execute(sql) results = cursor.fetchall() cursor.close() db.close() return results
def car_brand_of_all(): db = car_type_pool.connection() cursor = db.cursor() sql = """SELECT `id`, `name` FROM `car_brand` WHERE 1 AND `status` = 1""" cursor.execute(sql) results = cursor.fetchall() cursor.close() db.close() return results
def read(conn, asset_id, keys=FIELDS):
"""
Fetch one or more schedules from the database.
Returns a list of dicts or one dict.
"""
schedules = []
mk = mkdict(keys)
with db.cursor(conn) as c:
c.execute(queries.read_schedule(keys), [asset_id])
schedules = [mk(schedule) for schedule in c.fetchall()]
return schedules
def search(query):
try:
cursor = db.cursor()
cursor.execute(
"SELECT color_id,name,hex FROM colors WHERE name LIKE %s",
['%' + query + '%'])
result = cursor.fetchall()
cursor.close()
return result
except MySQLdb.Error as err:
print(err)
return None
def verify(email, token):
with db.cursor(config) as cur:
cur.execute(
"UPDATE users SET confirm_email_token = NULL WHERE email = %s AND confirm_email_token = %s RETURNING id",
(email, token),
)
row = cur.fetchone()
if row is None:
return flask.redirect(
flask.url_for("login", email=email, error="true"))
login_user(User("{}".format(row[0])), remember=True)
return flask.redirect(flask.url_for("index"))
def update(color_id, name, hex):
try:
cursor = db.cursor()
params = (name, hex, color_id)
cursor.execute("UPDATE colors SET name=%s, hex=%s WHERE color_id=%s",
params)
cursor.close()
db.commit()
return 1
except MySQLdb.Error as err:
print(err)
return None
def task(client, config):
yield from client.wait_until_ready()
now = datetime.now().timestamp()
c = db.cursor()
c.execute(
"SELECT target, time, message FROM alerts WHERE time > {}".format(
datetime.now().timestamp()))
for alert in c.fetchall():
yield from asyncio.sleep(alert[1] - datetime.now().timestamp())
yield from client.send_message(
discord.Object(id=config['main_channel']),
alert[0] + ' ' + alert[2])
def add(name, hex):
try:
cursor = db.cursor()
params = (name, hex)
cursor.execute(
"INSERT INTO colors (created_at,name,hex) VALUES (NOW(), %s, %s)",
params)
last_row_id = cursor.lastrowid
cursor.close()
db.commit()
return last_row_id
except MySQLdb.Error as err:
print(err)
return None
def __init__(self, id):
self.id = id
with db.cursor(config) as cur:
cur.execute(
"SELECT start_datetime_utc, end_datetime_utc FROM users WHERE id = %s",
(id, ),
)
row = cur.fetchone()
if row is None:
raise ValueError("Unknown user: {}".format(id))
self.visit_days = [
date for date in self.dates_between(row[0].date(), row[1].date())
]
def get_shared_by_user_ids_and_emails(config, user_id):
with cursor(config) as cur:
cur.execute("SELECT email FROM users WHERE id = %s", (user_id, ))
row = cur.fetchone()
if row is None:
raise ValueError(
"Couldn't find email address for user {}".format(user_id))
user_email = row[0]
cur.execute(
"SELECT users.id, users.email FROM users INNER JOIN shares ON users.id = shares.shared_by WHERE shares.shared_with_email = %s ORDER BY users.email ASC",
(user_email, ),
)
return [(row[0], row[1]) for row in cur.fetchall()]
def get_share_emails(config, user_id):
with cursor(config) as cur:
cur.execute(
"SELECT shared_with_email FROM shares WHERE shared_by = %s ORDER BY shared_with_email ASC",
(user_id, ),
)
shared_with_user = [row[0] for row in cur.fetchall()]
shared_by_ids_and_emails = get_shared_by_user_ids_and_emails(
config, user_id)
shared_by_user = [email for id, email in shared_by_ids_and_emails]
return shared_by_user, shared_with_user
def populate_table(table_name, input_filename, batch_size=1000):
'''
Populate the given table with the dataset of article creation statistics.
:param table_name: name of the database table we are populating
:type table_name: str
:param input_filename: path to the input file with data
:type input_filename: str
:param batch_size: number of inserts to make before issuing a commit
:type batch_size: int
'''
insert_query = '''INSERT INTO {table}
(na_date, na_num_creations)
VALUES (%s, %s)'''.format(table=table_name)
db_conn = db.connect('tools.labsdb', 's53463__actrial_p',
'~/replica.my.cnf')
if not db_conn:
logging.error('unable to connect to database server')
return ()
with open(input_filename, 'r') as infile:
infile.readline() # skip header
i = 0
with db.cursor(db_conn) as db_cursor:
for line in infile:
(col1, col2) = line.strip().split('\t')
db_cursor.execute(insert_query, (col1, col2))
i += 1
if i % batch_size == 0:
logging.info('inserted {} entries, committing'.format(i))
db_conn.commit()
# commit any outstanding inserts
db_conn.commit()
db_conn.close()
logging.info('completed inserting {} entries'.format(i))
# ok, done
return ()
def unset_performance_interest(config,
user_id,
*,
show_id=None,
performance_id=None):
with cursor(config) as cur:
if show_id is not None:
cur.execute(
"DELETE FROM performance_interests WHERE user_id = %s AND show_id = %s",
(user_id, show_id),
)
elif performance_id is not None:
cur.execute(
"DELETE FROM performance_interests WHERE user_id = %s AND performance_id = %s",
(user_id, performance_id),
)
def clearUpdateStatus(self):
"""
We're done updating, clear the bit in the update table.
"""
setRunningQuery = """UPDATE {status_table}
SET ilcu_update_running=0
WHERE ilcu_lang=%(lang)s""".format(
status_table=self.tool_status_table)
with db.cursor(self.tool_db_conn, 'dict') as db_cursor:
db_cursor.execute(setRunningQuery, {'lang': self.lang})
if db_cursor.rowcount != 1:
raise UpdateTableError
self.tool_db_conn.commit()
# OK, done.
return ()
def each_ccs(db):
print('Loading Census Consolidated Subdivisions from DB...',
file=sys.stderr)
c = db.cursor()
c.execute("""
SELECT p.uid, ARRAY_AGG(c.uid)
FROM regions p
INNER JOIN region_parents rp
ON p.id = rp.parent_region_id
INNER JOIN regions c
ON c.id = rp.region_id
WHERE p.type = 'ConsolidatedSubdivision'
AND c.type = 'Subdivision'
GROUP BY p.uid
""")
return (CensusConsolidatedSubdivision(*row) for row in c)
def publish_small_datasets(config, db_conn):
'''
Based on the given configuration of data to write, and the given
database connection, run the queries and export the data that is
labelled as "small".
:param config: data export definitions (list of key/value pair dicts)
:type config: list
:param db_conn: connection to the database
:type db_conn: MySQL.Connection
'''
# for stuff in config...
for table_def in config:
## Only publish small datasets
if not "is_small" in table_def:
continue
print("processing {}".format(table_def['output_file']))
## Open output file,
output_filename = os.path.expanduser(table_def['output_file'])
if re.match(".*\.bz2$", output_filename):
outfile = bz2.open(output_filename, 'wt')
else:
outfile = open(output_filename, 'w')
# write header
outfile.write('\t'.join(table_def['columns']))
outfile.write('\n')
# execute query and write output
with db.cursor(db_conn, 'dict') as db_cursor:
db_cursor.execute(table_def['sql_query'])
for row in db_cursor:
output = []
for col in table_def['columns']:
output.append(str(row[col]))
outfile.write('{}\n'.format('\t'.join(output)))
outfile.close()
## ok, done
return ()
def read(conn, asset_id=None, keys=FIELDS):
"""
Fetch one or more assets from the database.
Returns a list of dicts or one dict.
Assets' is_active field is updated before returning.
"""
assets = []
mk = mkdict(keys)
with db.cursor(conn) as c:
if asset_id is None:
c.execute(queries.read_all(keys))
else:
c.execute(queries.read(keys), [asset_id])
assets = [mk(asset) for asset in c.fetchall()]
[asset.update({'is_active': is_active(asset)}) for asset in assets]
if asset_id and len(assets):
return assets[0]
return assets
def update(self):
"""
Re-download and parse the RSS file, updating local db accordingly.
"""
self._rss = feedparser.parse(self.url,
etag=self.etag,
modified=self.modified)
# HTTP 304 - Not Modified
if self._rss.status is 304:
self.updated = datetime.utcnow()
sql = 'UPDATE feeds SET updated=? WHERE id=?;'
db.connection.execute(sql, (self.updated.timestamp(), self.id))
return
# This will throw if the rss is malformed, but also if the url is junk
# or the url doesn't point to an rss feed, etc.
# TODO: raise Feed custom exception instead
if self._rss.bozo:
raise self._rss.bozo_exception
self.title = self._rss.feed.title
self.description = self._rss.feed.description
self.etag = self._rss.etag if hasattr(self._rss, 'etag') else None
self.modified = self._rss.modified
self.updated = datetime.utcnow()
self._update_episodes()
sql = 'UPDATE feeds SET title=?, description=?, etag=?, modified=?, updated=? WHERE id=?;'
values = (
self.title,
self.description,
self.etag,
self.modified,
self.updated.timestamp(),
self.id,
)
c = db.cursor()
c.execute(sql, values)
db.connection.commit()
