def wifi():
cmds = []
dir = os.getcwd()
cmd = ''
cmdpre = 'cd /root && /root/scripts/action config "'
cmdsuf = '" && cd %s' % (dir)
opt = ''
if 'essid' in form:
essid = form['essid'].value
#cmds.append('cd /root && /root/scripts/action config WIFI_SSID=%s && cd %s'%(essid,dir))
cmd = '%s%sWIFI_SSID=%s' % (cmd, opt, essid)
opt = '&'
currenc = gen.currval('WIFI_SEC').replace('"', '')
if ('newenc' in form):
newenc = form['newenc'].value
if newenc != currenc:
cmd = '%s%sWIFI_SEC=%s' % (cmd, opt, newenc.upper())
opt = '&'
if 'pass' in form:
if 'newenc' in form:
enc = form['newenc'].value
else:
enc = gen.currval('WIFI_SEC')
passph = form['pass'].value
cmd = '%s%sWIFI_PASS=%s' % (cmd, opt, passph)
if cmd != '':
cmd = '%s%s%s' % (cmdpre, cmd, cmdsuf)
sql.insert('cmd', 'cmd', [0, cmd])
else:
print "<p>Nothing to update"
return
exec_cmd('Wireless Settings ')
def loadgeomtopgsql(file, filename, noaaid, orderNumber):
reader = ogr.Open(file)
layer = reader.GetLayer(0)
try:
epsg = layer.GetSpatialRef().GetAuthorityCode("GEOGCS")
except:
print('Cannot find EPSG code')
epsg = 0
if not epsg == 0:
for i in range(layer.GetFeatureCount()):
feature = layer.GetFeature(i)
# print(feature.ExportToJson())
geom = feature.geometry()
SQL = "INSERT INTO {table}(file_name, noaaid, orderNumber, footprint) SELECT '{fn}', {ni}, {on}, ST_GeomFromText('{geom}',{epsg})".format(
table='imagedata',
fn=filename,
ni=noaaid,
on=orderNumber,
geom=str(geom),
epsg=str(epsg))
data = ('', )
try:
sql.insert(SQL, data)
except Exception as e:
raise
else:
pass
finally:
pass
def save_sample_plant(sample_id, plant_id, date):
if add_plant(plant_id): progress('Plant %d added' % plant_id)
if not sql.exists('samples', sample_id):
sample = {
'id': sample_id,
'created': date,
}
if sql.insert('samples', sample):
progress('Sample %d added' % sample['id'])
sample_plants = {
'sample_id': sample_id,
'plant_id': plant_id
}
if sql.insert('sample_plants', sample_plants):
progress('Sample_plants (%d, %d) added' % (sample_id, plant_id))
else:
# check if there is already a link between sample and plant
samples = sql.fetch_all('sample_plants', { 'sample_id': sample_id, 'plant_id': plant_id })
if samples:
for sample in samples:
if sample['plant_id'] == 1:
if sql.update('sample_plants', { 'sample_id': sample_id }, { 'plant_id': plant_id }):
progress('Updated %d sample plant_id from %d to %d' % (sql.lastrowid(), 1, plant_id))
else:
sample_plants = {
'sample_id': sample_id,
'plant_id': plant_id
}
if sql.insert('sample_plants', sample_plants):
progress('Sample_plants (%d, %d) added' % (sample_id, plant_id))
def loadgeomtopgsql(file, filename, noaaid, orderNumber):
reader = ogr.Open(file)
layer = reader.GetLayer(0)
try:
epsg = layer.GetSpatialRef().GetAuthorityCode("GEOGCS")
except:
print('Cannot find EPSG code')
for i in range(layer.GetFeatureCount()):
feature = layer.GetFeature(i)
# print(feature.ExportToJson())
geom = feature.geometry()
SQL = "INSERT INTO {table}(file_name, noaaid, orderNumber, footprint) SELECT '{fn}', {ni}, {on}, ST_GeomFromText('{geom}',{epsg})".format(
table='imagedata',
fn=filename,
ni=noaaid,
on=orderNumber,
geom=str(geom),
epsg=str(epsg)
)
data = ('',)
try:
sql.insert(SQL,data)
except Exception as e:
raise
else:
pass
finally:
pass
# INSERT INTO imagedata(footprint) SELECT ST_GeomFromText('POLYGON ((47.8986402364685 -19.9761359737374,77.2019166143206 -24.5331415521829,75.348830485111 -44.4051468911004,38.8567335982238 -38.6872585624496,47.8986402364685 -19.9761359737374))',4326)
def process_feed(args):
try:
# Attempt to enrich the URL.
res = enrich_url(args)
# Check for any error code.
if not (res.status_code >= 200 and res.status_code < 300):
log.error('Error enriching {}'.format(args[0]))
log.error(res.json())
log.error('Failed on HTTP code {}: {}'.format(res.status_code, args[1]))
return 'error'
# Normalize the document to set correct field types.
res = normalize.main(res.json())
# Add the label field.
res['label'] = args[7]
# Write to file.
write(res, args[2])
# Insert to elasticsearch.
#_ = es.insert(args[5], args[3], args[4], res, args[2])
# Add to dedup database.
sql.insert(args[6], today(), args[0])
return res
except:
return False
def data_file():
try:
dt = {}
with open('www_access_20140823.log') as f:
while True:
line = f.readline().split()
if line:
ip,status = line[0],line[8]
dt[(ip,status)] = dt.get((ip,status),0)+1
else:break
new_dt = {}
for key,val in dt.items():
new_dt.setdefault(val,[])
new_dt[val].append(key)
arr = new_dt.keys()
count = 0
sql.truncate()
while count < 100:
tmp = max(arr)
ip_tmp, status_tmp, count_tmp = new_dt[tmp][0][0],new_dt[tmp][0][1],tmp
sql.insert(ip_tmp,status_tmp,count_tmp)
count +=1
arr.remove(tmp)
except:
return "File not found."
def wifi():
cmds = []
dir = os.getcwd()
cmd = ''
cmdpre = 'cd /root && /root/scripts/action config "'
cmdsuf = '" && cd %s'%(dir)
opt = ''
if 'essid' in form:
essid = form['essid'].value
#cmds.append('cd /root && /root/scripts/action config WIFI_SSID=%s && cd %s'%(essid,dir))
cmd = '%s%sWIFI_SSID=%s'%(cmd,opt,essid)
opt = '&'
currenc = gen.currval('WIFI_SEC').replace('"','')
if ('newenc' in form):
newenc = form['newenc'].value
if newenc != currenc:
cmd='%s%sWIFI_SEC=%s'%(cmd,opt,newenc.upper())
opt = '&'
if 'pass' in form:
if 'newenc' in form:
enc = form['newenc'].value
else:
enc = gen.currval('WIFI_SEC')
passph = form['pass'].value
cmd = '%s%sWIFI_PASS=%s'%(cmd,opt,passph)
if cmd != '':
cmd = '%s%s%s'%(cmdpre,cmd,cmdsuf)
sql.insert('cmd','cmd',[0,cmd])
else:
print "<p>Nothing to update"
return
exec_cmd('Wireless Settings ')
def loadxml(xmlfile, orderNumber):
print('INFO: Loading XML Manifest file', str(xmlfile),
'into table images')
tree = ET.parse(xmlfile)
root = tree.getroot()
comment = root.find('comment').text
total_files = int(root.find('total_files').text)
for lineitem in root.findall('./line_item'):
try:
noaaid = lineitem.get('id')
file_name = lineitem.find('item/file_name').text
file_size = lineitem.find('item/file_size').text
creation_date = lineitem.find('item/creation_date').text
creation_date = datetime.strptime(creation_date,
"%Y-%m-%dT%H:%M:%SZ")
expiration_date = lineitem.find('item/expiration_date').text
expiration_date = datetime.strptime(expiration_date,
"%Y-%m-%dT%H:%M:%SZ")
except:
print('ERROR: Cannot read all values in Manifest',
str(xmlfile))
try:
checksum = lineitem.find('item/checksum').text
except:
print('ERROR: Manifest at', str(xmlfile),
'does not include checksum')
checksum = None
print(
'INFO: Loading data to database table images:\
', noaaid, file_name, file_size, creation_date,
expiration_date, checksum)
SQL = "INSERT INTO images (manifest,file_name,checksum,ordernumber,ordercreated,orderexpiration,status,file_size,noaaid) VALUES (%s,%s,%s,%s,TIMESTAMP %s,TIMESTAMP %s,%s,%s,%s);" # Note: no quotes
data = (os.path.basename(xmlfile), file_name, checksum,
orderNumber, creation_date, expiration_date, 'NEW',
file_size, noaaid)
try:
sql.insert(SQL, data)
print('insert')
except:
print(
'ERROR: Information for this image and order already present?'
)
SQL = "UPDATE orders set notice = %s, manifesttotal = %s WHERE ordernumber = %s"
data = (comment, total_files, orderNumber)
try:
sql.insert(SQL, data)
except:
print('ERROR: Cannot insert in database')
SQL = "SELECT COUNT(ordernumber) FROM images WHERE ordernumber = %s"
data = (orderNumber, )
count = sql.select(SQL, data)
if total_files == count[0][
0]: # get the only element that the query returns
return 1
else:
return 0
def login_refresh():
ctime = int(time.time())
res = sql.read('val','login:%s'%(os.environ['REMOTE_ADDR']))
if res[0][0] == None:
sql.insert('val','login:%s'%(os.environ['REMOTE_ADDR']),ctime)
else:
sql.modify('val','login:%s'%(os.environ['REMOTE_ADDR']),ctime)
return
def login_refresh():
ctime = int(time.time())
res = sql.read('val', 'login:%s' % (os.environ['REMOTE_ADDR']))
if res[0][0] == None:
sql.insert('val', 'login:%s' % (os.environ['REMOTE_ADDR']), ctime)
else:
sql.modify('val', 'login:%s' % (os.environ['REMOTE_ADDR']), ctime)
return
def buy(self, row, place, session_id, user):
print(user)
self.update_btns(session_id)
if self.btns_flg[(row, place)] == None:
sql.insert("update билет set логин_пользователя='" + user +
"' where num=" + str(self.ticket_id[(row, place)]) +
";")
self.update_btns(session_id)
def login_check():
res = sql.read('val','login:%s'%(os.environ['REMOTE_ADDR']))
if res[0][0] == None:
sql.insert('val','login:%s'%(os.environ['REMOTE_ADDR']),0)
res = [(0,0)]
lastmtime = res[0][0]
ctime = int(time.time())
if (ctime - lastmtime) > (5*60):
return False
return True
def login_check():
res = sql.read('val', 'login:%s' % (os.environ['REMOTE_ADDR']))
if res[0][0] == None:
sql.insert('val', 'login:%s' % (os.environ['REMOTE_ADDR']), 0)
res = [(0, 0)]
lastmtime = res[0][0]
ctime = int(time.time())
if (ctime - lastmtime) > (5 * 60):
return False
return True
def add_record(self):
values = []
for line in self.add_values:
values.append(line.text())
try:
sql.insert(self.table, values)
self.add_window.close()
self.change_table(self.table)
except sqlite3.IntegrityError:
self.add_error.setText("<font color='red'>Was errors!</font>")
def add_session(self, film_cb, date_ent, price_ent, ImaxCheck,
ThreeDCheck):
film = film_cb.get()
if film[0] == '{':
film = film[1:]
if film[-1] == '}':
film = film[:-1]
hall = self.hall_lb.get(tk.ANCHOR)
date = date_ent.get()
imax = ImaxCheck.get()
threed = ThreeDCheck.get()
price = price_ent.get()
film_imax, film_threed = sql.query_with_fetchall(
"select imax_flg, 3d_flg from фильм where название='" + film +
"';")[0]
hall_imax, hall_threed = sql.query_with_fetchall(
"select imax_flg, 3d_flg from зал where zal_id=" + str(hall) +
";")[0]
success = True
if imax == 1 and film_imax == 0:
success = False
if threed == 1 and film_threed == 0:
success = False
if imax == 1 and hall_imax == 0:
success = False
if threed == 1 and hall_threed == 0:
success = False
if success == False:
self.success_lbl["text"] = "Ошибка"
return
query = "insert into сеанс(название_фильма, zal_id, дата_сеанса, imax_flg, 3d_flg, цена) values ('"\
+ film + "', " + str(hall) + ", '" + date + "', " + str(imax) + ", " + str(threed) \
+ ", " + str(price) + ");"
print(query)
session_id = sql.insert(query)
query = "select колличество_рядов, колличество_мест_в_ряде from зал where zal_id=" + str(
hall) + ";"
print(query)
num_rows, num_places = sql.query_with_fetchall(query)[0]
for row in range(1, num_rows + 1):
for place in range(1, num_places + 1):
ticket_id = sql.insert(
"insert into билет(seans_id, ряд, место, цена) values (" +
str(session_id) + ", " + str(row) + ", " + str(place) +
", " + str(price) + ");")
if success == True:
self.success_lbl["text"] = "Успех!"
def add(key, long):
if key != '':
ngx.wr(key, key)
return key
else:
x = 0
for i in key:
x = x + ord(i)
res = short_url.encode_url(x)
sql.insert(res, long, 0)
ngx.wr(key, long)
return res
def crawl(root, regex=r'^.*$', level=1, quiet=False):
cursor = db.cursor()
RootPageID = select_or_insert(db, 'Webpage', url=root,
quiet=quiet)[0]['WID']
CrawlID = insert1(db, 'Crawl', rootwid=RootPageID, nLevels=level)[0]['CID']
soup = mine(root, cid=CrawlID, wid=RootPageID, quiet=quiet, regex=regex)
discovered = get_links(soup, root)
discovered = filter(lambda link: re.match(regex, link), discovered)
discovered_wids = set([])
for url in discovered:
curl = clean(url)
discovered_wids.add(
select_or_insert(db, 'Webpage', url=str(curl))[0]['WID'])
discovered_wids = list(discovered_wids)
discovered_wids.sort()
discovered_wids = [str(wid) for wid in discovered_wids]
if len(discovered_wids):
query = 'UPDATE Webpage SET newCID={cid} WHERE wid IN ({wids});'.format(
cid=CrawlID, wids=','.join(discovered_wids))
if not quiet:
print query
cursor.execute(query)
else:
print '-- No links'
insert(db, 'Link', [{
'fromWID': RootPageID,
'toWID': url
} for url in discovered_wids])
while level > 0:
query = 'SELECT wid, url FROM Webpage WHERE newCID = {cid} AND mined=False;'.format(
cid=CrawlID)
if not quiet:
print query
cursor = db.cursor()
cursor.execute(query)
for wid, url in cursor:
# print url
mine(url, cid=CrawlID, wid=wid, quiet=quiet, regex=regex)
# crawl(url,level-1,CrawlID,quiet=quiet)
level -= 1
print '-- Level:', level
query = 'UPDATE Crawl SET endtime="{now}" WHERE cid = {cid};'.format(
cid=CrawlID, now=datetime.datetime.now())
if not quiet:
print query
cursor.execute(query)
db.close()
def save(self, afields = None):
if afields:
fields = afields
else:
fields = self.table.fields
key = self.keyfield
tab = self.table.name
exists = self.id and self.db.fetchval(sql.get_fval(key, tab, key), [self.id])
db = self.db.self()
try:
# UPDATE
if exists:
self.mode = MOD_UPD
q = sql.update(fields, tab, key)
vals = map(lambda field: self.get(field, None), fields) + [self.id]
# INSERT
else:
self.mode = MOD_INS
self.newid(db)
q = sql.insert(fields, tab)
vals = map(lambda field: self.get(field, None), fields)
db.execute(q, vals, commit = True)
finally:
pass
def img_addcrc(db, param, id):
crc = getcrc(param)
q = sql.get_fval('crc', 'mem_image_crc', 'crc')
if not db.fetchval(q, [crc]):
q = sql.insert(['crc', 'id_image'], 'mem_image_crc')
db.execute(q, [crc, id])
async def ow_add(ctx, battle_tag, member : discord.Member = None):
"""Add an Overwatch player to the database (e.g. !ow_add JeffKaplan#420 @JeffKaplan)"""
if member is None:
member = ctx.message.author
log.debug(type(member))
log.debug(discord.Member)
await bot.send_typing(ctx.message.channel)
# print(type(member))
# print(discord.Member)
if type(member) is not discord.Member:
await ctx.send("ERROR: @mention the user instead of just typing it")
return
# See if the battle_tag is already in the db
query = sql.select(overwatch_table, column_names=['BattleTag', 'DiscordName'], condition={'BattleTag':battle_tag})
if len((c.execute(query)).fetchall()) is not 0:
await ctx.send("Account " + battle_tag + " already in list!")
return
sr = await owh.get_sr(battle_tag)
if sr == None:
await ctx.send("Account " + battle_tag + " doesn't exist!")
return
query = sql.insert(overwatch_table, [battle_tag, sr, str(member)])
#query = "INSERT INTO " + overwatch_table + " VALUES('" + battle_tag + "', '" + str(member) + "')"
#print(query)
c.execute(query)
await ctx.send("Added " + battle_tag + " with discord member @" + str(member))
conn.commit()
async def rps(ctx, player_choice):
"""Play rock, paper, scissors against Botboy (eg. !rps r)"""
# See if player is already in database. If not, create their entry.
member = str(ctx.message.author)
query = sql.select(rps_table, condition={"DiscordName":member})
# Store all results from query in a list of tuples
results = c.execute(query).fetchall()
if len(results) == 0:
# Create entry
default_values = [member,0,0,0]
c.execute(sql.insert(rps_table, default_values))
# Result matrix - columns: player's choice / rows: bot's choice
result_matrix = [['draw','user','bot'],['bot','draw','user'],['user','bot','draw']]
choices = ['rock','paper','scissors']
alt_choices = ['r','p','s']
# Bot makes a choice
bot_choice_index = random.randint(0,2)
log.debug("rps - player input: {0}".format(player_choice))
log.debug("rps - bot's random number: {0}".format(bot_choice_index))
# Try getting index from players choice
try:
player_choice_index = choices.index(player_choice)
except:
try:
player_choice_index = alt_choices.index(player_choice)
except:
await ctx.send("ERROR: must enter 'rock' (or r), 'paper' (or p), 'scissors' (or s)")
return
# Determine result from matrix
result = result_matrix[bot_choice_index][player_choice_index]
column_update = {}
if result == "user":
winner = "You win!"
column_update = {"Wins":"Wins+1"}
elif result == "draw":
winner = "It's a draw ¯\_(ツ)_/¯"
column_update = {"Draws":"Draws+1"}
elif result == "bot":
winner = "Better luck next time!"
column_update = {"Losses":"Losses+1"}
# Update database
c.execute(sql.update(rps_table, column_update, {"DiscordName":member}))
# Create response
response = "You chose: {0} \nBotBoy chose: {1} \n{2}".format(choices[player_choice_index], choices[bot_choice_index], winner)
query = sql.select(rps_table, condition={"DiscordName":member})
results = c.execute(query).fetchone()
wins = results[1]
draws = results[2]
losses = results[3]
win_percentage = (wins/(wins+draws+losses))*100
response += "\nWins: " + str(wins) + "\nDraws: " + str(draws) + "\nLosses: " + str(losses) + "\nWin %: " + str(win_percentage) + "%"
# Say it
await ctx.send(response)
conn.commit()
def refresh(targets):
for link in targets:
print(link)
flights = get_posts(link)
for flight in flights:
if flight.get('details')[0] != None:
r = insert("posts", flight)
print(r)
def add_plant(plant_id):
if not sql.exists('plants', plant_id):
ora_sql.set_formatting(False)
plant = ora_sql.get_plant_information(plant_id)
ora_sql.set_formatting(True)
if plant == None: # fcuk! not found in LIMS, add a dummy :/
plant = { 'id': plant_id, 'culture_id': DUMMY_CULTURE_ID, 'subspecies_id': DUMMY_SUBSPECIES_ID } # culture_id 1 is the dummy culture
else:
# prepare the plant
plant = dict( (k.lower(), v) for k,v in plant.iteritems() )
plant['subspecies_id'] = plant['u_subspecies_id']
plant['lineid'] = plant['line_id']
plant['id'] = plant['aliquot_id']
plant['culture_id'] = plant['u_culture']
del(plant['u_subspecies_id'])
del(plant['line_id'])
del(plant['aliquot_id'])
del(plant['u_culture'])
del(plant['location_id'])
# add the culture if not exists
if plant != None:
if not sql.exists('cultures', plant['culture_id']):
ora_sql.set_formatting(False)
culture = ora_sql.get_culture_information(plant['culture_id'])
ora_sql.set_formatting(True)
if culture: # prepare the culture
culture = dict( (k.lower(), v) for k,v in culture.iteritems() )
culture['experiment_id'] = 1
culture['id'] = culture['study_id']
del(culture['study_id'])
else: # prepare dummy
culture = { 'id': DUMMY_CULTURE_ID, 'name': 'placeholder' }
if sql.insert('cultures', culture): progress('Culture %d added' % culture['id'])
# add the subspecies if not exists
if plant['subspecies_id'] == None:
plant['subspecies_id'] = DUMMY_SUBSPECIES_ID
if not sql.exists('subspecies', plant['subspecies_id']):
subspecies = { 'species_id': SPECIES_ID, 'id': plant['subspecies_id'] }
if sql.insert('subspecies', subspecies): progress('Subspecies %d added' % subspecies['id'])
return sql.insert('plants', plant)
def insert(self, data, returning='*'):
if isinstance(data, dict):
d = dict([(k,data[k]) for k in self.fields if k in data])
else:
d = dict([(k,getattr(data,k)) for k in self.fields if hasattr(data,k)])
pk = self.pk
if self.pk_seq and d.get(pk,None) is None:
d[pk] = self.next_id()
return self.db.query_one(sql.insert(self.table, d, returning))
def add_credits(self, film_cb, person_lb, credits_ent):
film = film_cb.get()
person = person_lb.get(tk.ANCHOR)
credits = credits_ent.get()
id = person[0]
query = "insert into кинодиетели_в_фильме(название_фильма, kinodeyatel_id, функция_в_фильме) values ('" + film + "' ,"
query = query + str(id) + ", '" + credits + "');"
credits_id = sql.insert(query)
def privacy():
try:
dur = int(form['dur'].value)
ctime = int(time.time())
ntime = ctime + dur*60
res = sql.read('val','privend')
if res[0][0] == None:
sql.insert('val','privend',ntime)
else:
sql.modify('val','privend',ntime)
cmd = 'pgrep tie && killall tie'
sql.insert('cmd','cmd',[0,cmd])
ret = exec_cmd('Privacy Settings ')
if ret:
print "<p>Private mode enabled for %s minutes"%(dur)
else:
pass
except:
print "<p>Private mode not enabled"
def privacy():
try:
dur = int(form['dur'].value)
ctime = int(time.time())
ntime = ctime + dur * 60
res = sql.read('val', 'privend')
if res[0][0] == None:
sql.insert('val', 'privend', ntime)
else:
sql.modify('val', 'privend', ntime)
cmd = 'pgrep tie && killall tie'
sql.insert('cmd', 'cmd', [0, cmd])
ret = exec_cmd('Privacy Settings ')
if ret:
print "<p>Private mode enabled for %s minutes" % (dur)
else:
pass
except:
print "<p>Private mode not enabled"
def processLgJob():
page = 1
config_file = "../../conf/sys.conf"
dictConfig = sql.readConfig( config_file )
db = sql.connSql( dictConfig )
max_date = get_last_date( db )
url = getUrl("深圳")
is_update = True;
while 1:
if( is_update != True ):
break
if page == 1:
post_data = {'first':'true','pn':'1'}
else:
post_data = {'first':'false','pn': page}
page = page + 1
job_list = getJobList(url,post_data)
time.sleep(5)
#print job_list;
#job_list = readJobs()
if job_list:
job_data = processJobList( job_list )
for index in xrange(len(job_data)):
if( job_data[index]["create_date"] < max_date ):
is_update = False
break
job_sql = getSql( job_data[index] )
#print job_sql
sql.insert( job_sql , db )
else:
break
sql.close(db)
def parse():
path_to_file = "../enwiki-20150515-pages-articles.xml"
context = etree.iterparse(path_to_file, events=("start", "end"))
namespace = ""
dicts = {}
stack = []
for event, element in context:
tagname = etree.QName(element.tag).localname
if event == "start":
if tagname == "page":
dicts = {}
dicts["page"] = {}
dicts["revision"] = {}
stack.append("page")
elif tagname == "revision":
stack.append("revision")
elif tagname == "contributor":
stack.append("contributor")
elif event == "end":
if tagname == "revision":
stack.pop()
elif tagname == "contributor":
stack.pop()
elif tagname == "page":
stack.pop()
# At this point one row each for page and revision
# complete. Send to DB (use UTF-8 and capture errors)
insert(dicts)
elif len(stack) != 0 and stack[-1] in ["page", "revision"]:
# This is where the actual properties get added
# We are only interested in properties who are children of page/revision
if element.text:
dicts[stack[-1]][tagname] = element.text.encode("UTF-8")
else:
dicts[stack[-1]][tagname] = None
# This node is parsed. Clear memory for the next one
# More info here:
# https://www.ibm.com/developerworks/library/x-hiperfparse/
element.clear()
while element.getprevious() is not None:
del element.getparent()[0]
def authorized():
# Checks if the state has been changed from the login and redirects back to the index page
if request.args.get('state') != session.get("state"):
return redirect(
url_for("index",
error="Login failed",
error_description="Login has failed, please try again"))
# Error in the authentication/authorization (such as cancelling approval of allowing permissions) and redirect back with error
if "error" in request.args:
return redirect(url_for("index", **request.args))
# If login was somewhat successfuly
if request.args.get('code'):
# Gets cached data
cache = _load_cache()
# Gets token of user
result = _build_msal_app(
cache=cache).acquire_token_by_authorization_code(
request.args['code'],
scopes=app_config.SCOPE,
redirect_uri=url_for("authorized",
_external=True,
_scheme=protocolScheme))
# Checks if token is invalid and redirects back to index with error
if "error" in result:
return redirect(url_for("index", **request.args))
# Saves token into session and cache
session["user"] = result.get("id_token_claims")
_save_cache(cache)
# Inserts user information into SQL database
sql.insert(
_get_token_from_cache(app_config.SCOPE)['refresh_token'],
session["user"]["preferred_username"])
# Redirects user back to index
return redirect(url_for("index"))
def set(self, key, value, paramno = 1):
field = 'val' + str(paramno)
d = self.__data
if key in d:
q = sql.update([field], 'param', filter = ['name = %s', 'id_prop = %s'])
db.execute(q, [value, key, self.idp], commit = True)
else:
q = sql.insert(['id_prop', 'name', field], 'param')
db.execute(q, [self.idp, key, value], commit = True)
d[key] = [None] * 4
d[key] = list(d[key])
d[key][paramno - 1] = value
def fill_proxypool():
page = get_webpage() # 爬取西刺代理网站，注意频度保持在15分钟以上
# page = test.read('西刺代理.html', 'utf-8') # 测试所用，读取本地网页，避免频繁爬取被封杀
counter = 1
for item in parse_page(page):
print('正在更新：{0:^2} - {1:<16}'.format(counter, item[0]), end='\t')
if sql.insert(item[0], item[1], item[2], item[3], item[4]):
counter += 1
else:
print('重复插入')
sql.commit()
print('\n此次共更新 %d 条代理IP\n' % (counter - 1))
def login():
res = sql.read('val','webpass')
if res[0][1] == None:
sql.insert('val','webpass',gen.default_pass)
res = [(0,gen.default_pass)]
pwd = res[0][1]
if pwd == form['pwd'].value:
gen.login_refresh()
latency = 0
dest = 'main'
msg = ''
else:
latency = 4
dest = 'login'
msg = '<p>Wrong Password! Going back to login page...'
print "Content-Type: text/html"
print
print '<html>\
<body>\
<meta http-equiv="REFRESH" content="%s;url=%s.py">\n\
%s\
</html>\
'%(latency,dest,msg)
def login():
res = sql.read('val', 'webpass')
if res[0][1] == None:
sql.insert('val', 'webpass', gen.default_pass)
res = [(0, gen.default_pass)]
pwd = res[0][1]
if pwd == form['pwd'].value:
gen.login_refresh()
latency = 0
dest = 'main'
msg = ''
else:
latency = 4
dest = 'login'
msg = '<p>Wrong Password! Going back to login page...'
print "Content-Type: text/html"
print
print '<html>\
<body>\
<meta http-equiv="REFRESH" content="%s;url=%s.py">\n\
%s\
</html>\
' % (latency, dest, msg)
def parse_text(text, oid):
words = [stem(word) for word in re.split(r'\s+', text)]
words = filter(lambda word: word, words)
words = enumerate(words)
words = filter(lambda word: word[1] not in default, words)
words = [str(word) for word in words]
words = [(word[0], select_or_insert(db, 'Stem', stem=word[1])[0]['stem'])
for word in words]
words = [{
'stem': word[1],
'oid': oid,
'pos': word[0],
} for word in words]
return insert(db, 'Word', words)
def crawl_url(url, run_headless=False):
while (1):
try:
url = correct_url(url)
chrome_options = Options()
chrome_options.add_argument("--headless")
chrome_options.add_argument('--no-sandbox')
chrome_options.add_argument('--disable-dev-shm-usage')
browser = webdriver.Chrome(chrome_options=chrome_options)
browser.get(url)
#time.sleep(1)
curr_time = str(datetime.datetime.now())
print(str(curr_time))
content = browser.page_source
soup = BeautifulSoup(content)
locations = soup.findAll('tr')
location_infos = []
for location in locations:
infos = location.findAll("td")
if (infos != []):
station, weather, visibility, \
cloud, temp, wind_dir, wind_speed, \
hum, _, air_pressure = infos
# INSERT to db
insert(infos)
browser.quit()
time.sleep(60) # Get data one minute / time
except:
traceback.print_exc()
def img_addcrclist(db, alist):
if not alist:
return
lst = {}
for (param, id) in alist:
lst[getcrc(param)] = id
crclist = lst.keys()
q = sql.get_f('crc', 'mem_image_crc', sql.fin('crc', crclist, True))
crclist = db.fetchvals(q, crclist)
for crc in crclist:
if crc in lst:
del lst[crc]
q = sql.insert(['crc', 'id_image'], 'mem_image_crc')
for (crc, id) in lst.iteritems():
db.execute(q, [crc, id])
def main(argv):
parser = argparse.ArgumentParser(description='')
parser.add_argument('files', nargs='+')
parser.add_argument('--standortid', type=int)
parser.add_argument('--pages', type=int, default=1)
args = parser.parse_args(argv)
for full_fn in args.files:
fn = ntpath.basename(full_fn)
# look up the file id
file_id = sql.fetch('ufiles', fn, id_key='name')
if not file_id:
print "File '%s' not found in DB, skipping" % fn
else:
data = []
headers = []
for page in xrange(args.pages):
data, headers = p_xls.read_xls_data(full_fn, page)
lines = 0 # administration: number of succesfully inserted lines
for row in data:
standortid = -1
if hasattr(row, 'StandortID'):
standortid = getattr(row, 'StandortID')
elif args.standortid != None:
standortid = args.standortid
else:
sys.stderr.write('No StandortID found!')
exit()
rs = sql.fetch_all('temps', {
'datum': getattr(row, 'Datum'),
'location_id': standortid
})
if rs != False:# and len(rs) == 1:
for i in xrange(len(rs)):
if (sql.insert('ufiletemps', {
'ufile_id': file_id['id'],
'temp_id': rs[i]['id']
})):
lines += 1
else:
print "%d,%d" % (file_id['id'], rs[i]['id'])
print "Inserted %d/%d of page %d" % (lines, len(data), page)
sql.commit() # after each file
return None
def register_process(user, identity):
# user password 加密
user.password = sha256_crypt.hash(user.password)
try:
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
key = ["id"]
condition = {"username": user.username}
query = sql.select(identity,key,condition,0)
if cursor.execute(query):
return "该用户名已经存在", False
key = ["id"]
condition = {"number": user.number}
query = sql.select(identity, key, condition, 0)
if cursor.execute(query):
return "该手机号已经存在", False
# 生成SQL语句
query = sql.insert(identity, utility.class_2_dict(user))
try:
# 使用execute方法执行SQL语句
cursor.execute(query)
# 提交操作到db
db.commit()
except:
# 操作失败回滚
db.rollback()
db.close()
return "注册失败", False
# 关闭数据库连接
db.close()
return "注册成功", True
except:
return "Unable to connect to DB", False
def enter_spec(name, commodityId, detail, url):
try:
# 打开数据库连接
db = pymysql.connect("localhost", "root", "wujiahao.", "flaskTest", charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
company_name = vendor_name2company_name(name)
# 查询条件
condition = dict()
condition['company'] = company_name
condition['commodity_id'] = commodityId
# 生成SQL语句
query = sql.delete("provide", condition)
# 使用execute方法执行SQL语句
try:
cursor.execute(query)
db.commit()
for obj in detail:
condition = dict()
condition["spec"] = obj["spec"]
condition["price"] = obj["price"]
condition["commodity_id"] = commodityId
condition["pdf_path"] = url
condition["company"] = company_name
query = sql.insert("provide", condition)
cursor.execute(query)
db.commit()
except:
db.rollback()
return False, "录入商品支数和价格失败", "null"
db.close()
return True, "录入商品支数和价格成功", "null"
except:
return False, "无法连接数据库" , "null"
def get_proxies():
curTime = TIME(UnixTime=TIME().NowUnix) # 获得当前时间
sql.start()
if sql.insert(ip='000.000.0.000',
port='8888',
addr='爬取时间',
time=curTime.normal()):
fill_proxypool() # 第一次爬取代理网站
else:
time_info = sql.get(ip='000.000.0.000')[6]
lastTime = TIME(NormalTime=time_info)
diffTime = (curTime.unix() - lastTime.unix()) / 60 # 相差多少分钟
# 距上次爬取超过30分钟，则更新代理池
if diffTime > 30:
print('代理池需要更新，请稍后...')
fill_proxypool()
sql.update('000.000.0.000', 'TIME', curTime.normal())
else:
print('代理池无需更新')
proxy = select_proxy()
sql.close()
return proxy
#!/usr/bin/env python
import cgi
import cgitb
import gen
import sql
import os
cgitb.enable()
res = sql.read("val", "login:%s" % (os.environ["REMOTE_ADDR"]))
if res[0][0] == None:
sql.insert("val", "login:%s" % (os.environ["REMOTE_ADDR"]), 0)
else:
sql.modify("val", "login:%s" % (os.environ["REMOTE_ADDR"]), 0)
gen.print_header()
print "\
Logged out successfully\
<p><a href=login.py>Log back in</a>\
</html>\
"
def insert(self, table_name, table_fields, tuples):
db_cursor = self.db_conn.cursor()
insert_statement = sql.insert(table_name, table_fields)
for tup in tuples:
db_cursor.execute(insert_statement, tup)
self.db_conn.commit()
keys = ['GameID', 'Genre', 'Review', 'StarRating', 'ClassificationRating', 'PlatformNotes', 'PromotionLink', 'Cost', 'GameName']
ignore = ['Genre', 'Review', 'StarRating', 'ClassificationRating', 'PlatformNotes', 'PromotionLink', 'Cost', 'GameName']
exact_keys = ['GameID', 'StarRating', 'Cost']
pk = ['GameID']
fields = dict.fromkeys(keys)
row = None
message = ""
for key in fields:
fields[key] = form.getvalue(key)
######## If INSERT button is pressed then ... ###########################################################################
if form.getvalue("submit") == "Insert":
message = sql.insert(db, cursor, table, fields, keys)
######## If DELETE button is pressed then ... ###########################################################################
if form.getvalue("submit") == "Delete":
message = sql.delete(db, cursor, table, fields, pk)
######## If UPDATE button is pressed then ... ############################################################################
if form.getvalue("submit") == "Update":
message = sql.update(db, cursor, table, fields, keys, pk)
######## LOAD RESULT ... ############################################################################
if(fields['GameID'] != None):
result = sql.search(db, cursor, table, fields, keys, exact_keys, ignore=ignore, limit=10, fetch_one=True)
row = result[0];
else:
def insert(self, table_name, table_fields, tuples):
db_cursor = self.db_conn.cursor()
insert_statement = sql.insert(table_name, table_fields)
for tup in tuples:
db_cursor.execute(insert_statement, tup)
self.db_conn.commit()
def insert():
print(request.method)
table_to_class = {
"patient_records": i_forms.AddPatient(),
"billed_medicine": i_forms.AddBilledMedicine(),
"billed_service": i_forms.AddBilledService(),
"doctors": i_forms.AddDoctors(),
"departments": i_forms.AddDepartments(),
"worksfor": i_forms.AddWorksFor(),
"treatedby": i_forms.AddTreatedBy(),
"service": i_forms.AddService(),
"medicine": i_forms.AddMedicine(),
"rooms": i_forms.AddRooms(),
"stays_in": i_forms.AddStaysIn(),
}
# list of tables that need to provide id's when creating a new row.
# for the remaining tables, id's are auto-generated
table_with_id = ["billed_medicine", "billed_service", "rooms", "stays_in"]
error = request.args.get('error')
# main select table
select = request.args.get('select')
# query data from the selected table
data = sql.get_query(select)
existing_ids = sql.get_ids(select)
# get insert form
form = table_to_class[select]
# get update form
form_update = i_forms.Update()
# get delete form
form_delete = i_forms.Delete()
# print("form", form.validate_on_submit())
# print("update", form_update.update.data, form_update.validate_on_submit())
# print("delete", form_delete.delete.data, form_delete.validate_on_submit())
# print("\n\n\n\n")
header = sql.get_header(select)
# get columns
columns = table_to_properties[select]
need_id = select in table_with_id
#if table need an accompanying table
if need_id:
second_table = accomp_table[select]
data_acc = sql.get_query(second_table)
header_acc = sql.get_header(second_table)
else:
second_table, data_acc, header_acc = None, None, None
if form.validate_on_submit():
params = {}
for prop in table_to_properties[select]:
value = getattr(form, prop).data
if isinstance(value, datetime.date):
value = value.strftime('%Y-%m-%d')
params[prop] = value
error = sql.insert(params, select, table_with_id)
# Display updated table
data = sql.get_query(select)
return render_template('insert.html',
title='insert',
form=form,
data=data,
header=header,
select=select,
need_id=need_id,
columns=columns,
form_update=form_update,
form_delete=form_delete,
second_table=second_table,
data_acc=data_acc,
header_acc=header_acc,
error=error)
if form_delete.delete.data and form_delete.validate_on_submit():
if form_delete.id.data in existing_ids:
return redirect(
url_for('delete', id=form_delete.id.data, select=select))
else:
error = "ID does not exist in " + select
return redirect(url_for('insert', select=select, error=error))
if form_update.update.data and form_update.validate_on_submit():
if form_update.id.data in existing_ids:
return redirect(
url_for('update', id=form_update.id.data, select=select))
else:
error = "ID does not exist in " + select
return redirect(url_for('insert', select=select, error=error))
return render_template('insert.html',
title='insert',
form=form,
data=data,
header=header,
select=select,
need_id=need_id,
columns=columns,
form_update=form_update,
form_delete=form_delete,
second_table=second_table,
data_acc=data_acc,
header_acc=header_acc,
error=error)
api = tweepy.API(auth)
import sql as s
c = s.connect_db()
for tweet in tweepy.Cursor(api.search,
q="ebola OR isis OR world series",
count=100,
result_type="recent",
include_entities=True,
lang="en").items():
if(tweet.geo):
coords = tweet.geo['coordinates']
s.insert(c,tweet.created_at, tweet.text.strip().encode('ascii','ignore'), str(coords))
#print ("%s,%s,%s") % (tweet.created_at, tweet.text.encode('utf-8').strip(), coords)
else:
s.insert(c,tweet.created_at, tweet.text.strip().encode('ascii','ignore'), "None")
#print ("%s,%s,%s") % (tweet.created_at, tweet.text.encode('utf-8').strip(), tweet.geo)
#print tweet._json
#print "\n"
''' Attributes of tweet:
['__class__', '__delattr__', '__dict__', '__doc__', '__format__',
'__getattribute__', '__getstate__', '__hash__', '__init__', '__module__',
'__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__',
'__sizeof__', '__str__', '__subclasshook__', '__weakref__', '_api',
'_json', 'author', 'contributors', 'coordinates', 'created_at', 'destroy',
'entities', 'favorite', 'favorite_count', 'favorited', 'geo', 'id',
</div>
<div class="textbox">
<label for="PostalArea">Postal Area:</label>
<input name="PostalArea" id="PostalArea" type="text"/>
</div>
<div class="textbox">
<label for="Country">Country:</label>
<input name="Country" id="Country" type="text"/>
</div>
</fieldset>
<div id="buttons" class="button_select">
<input type="reset" value="Reset" />
<input type="submit" name="submit" value="Insert" />
</div>
</form>
</div>
""".format(table, id)
######## If INSERT button is pressed then ... ###########################################################################
if form.getvalue("submit") == "Insert":
print sql.insert(db, cursor, "Address", fields, keys)
if(fields['AddressID'] == None):
fields['AddressID'] = cursor.lastrowid
print sql.insert(db, cursor, "{}Address".format(table), fields, keys_a)
def main(argv):
argparser = argparse.ArgumentParser(description='')
argparser.add_argument('files', nargs='+')
args = argparser.parse_args(argv)
for fn in args.files:
if isdir(fn): continue
# read in file
print 'opening %s' % fn
f = open(fn, 'r')
lines_raw = f.readlines()
raw_id = None
if sql.insert('raws', { 'data': ''.join(lines_raw), 'filename': basename(fn) }):
raw_id = sql.lastrowid()
progress("Added %d to raws" % raw_id)
# preprocess
lines = []
try:
line_nr = 0
for line in lines_raw:
line_nr += 1
line = line.rstrip('\r\n')
line = re.split(r'\t|;', line)
line = preprocess_line(line)
lines.append(line)
except:
print "%d: %s" % (line_nr, line)
raise
# add a dummy plant/culture/subspecies, just in case samples can't be connected just yet.
if not sql.exists('cultures', 1): sql.insert('cultures', {'id': DUMMY_CULTURE_ID, 'name': 'placeholder' })
if not sql.exists('plants' , 1): sql.insert('plants' , {'id': DUMMY_PLANT_ID, 'name': 'placeholder', 'culture_id': DUMMY_CULTURE_ID })
if not sql.exists('subspecies' , 1): sql.insert('subspecies' , {'id': DUMMY_SUBSPECIES_ID, 'species_id': SPECIES_ID })
# some lines need to be sent back to LIMS, this is where we store them
lims_lines = []
# save!
line_nr = 0
try:
for line in lines:
line_nr += 1
program_id = get_program_id(line)
if is_sample_plant(line):
line[8] = int(line[8]) # plant_id is still str
#save_sample_plant(sample_id=line[7], plant_id=line[8], date=date) # skipped because made redundant when preloading all samples/plants
lims_lines.append("\t".join([ str(item) for item in line ]))
#elif program_id == 1 and is_freshweight_between(line):
# lims_lines.append("\t".join([ str(item) for item in line ]))
else:
phenotype = format_line(line) # create a readable program
# add the actual phenotype
phenotype_id = None
if sql.insert('phenotypes', {
'version': phenotype['version'],
'object' : phenotype['object'],
'program_id': phenotype['program_id'],
'date': phenotype['date'],
'time': phenotype['time'],
'entity_id': phenotype['entity_id'],
'value_id': phenotype['value_id'],
'number': phenotype['number']
}):
phenotype_id = sql.lastrowid()
progress('Added %d to phenotype' % phenotype_id)
# if plant, add it to plants, otherwise to samples
if ora_sql.is_plant(phenotype['sample_id']) or ora_sql.was_plant(phenotype['sample_id']):
sql.insert('phenotype_plants', { 'phenotype_id': phenotype_id, 'plant_id': phenotype['sample_id'] })
elif ora_sql.is_sample(phenotype['sample_id']):
sql.insert('phenotype_samples', { 'phenotype_id': phenotype_id, 'sample_id': phenotype['sample_id'] })
elif ora_sql.is_aliquot(phenotype['sample_id']):
sql.insert('phenotype_aliquots', { 'phenotype_id': phenotype_id, 'aliquot_id': phenotype['sample_id'] })
else:
print "%s NOT found!!" % phenotype['sample_id']
sql.insert('phenotype_raws' , { 'phenotype_id': phenotype_id, 'raw_id': raw_id, 'line_nr': line_nr })
if program_id > 1:
sql.insert('phenotype_bbches', { 'phenotype_id': phenotype_id, 'bbch_id': phenotype['bbch_id']})
except:
progress("%d: %s" % (line_nr, line))
raise
# save the current saved lines for LIMS
write_lims_lines(lims_lines, fn)
sql.commit()
db = MySQLdb.connect("info20003db.eng.unimelb.edu.au", "info20003g29", "enigma29", "info20003g29", 3306)
cursor = db.cursor()
table = "Achievement"
keys = ['AchievementID', 'InstanceRunID', 'WhenAchieved', 'AchievementName', 'RewardBody']
exact_keys = ['AchievementID', 'InstanceRunID', 'WhenAchieved']
pk = ['AchievementID']
fields = dict.fromkeys(keys)
for key in fields:
fields[key] = form.getvalue(key)
######## If INSERT button is pressed then ... ###########################################################################
if form.getvalue("submit") == "Insert":
print sql.insert(db, cursor, table, fields, keys)
######## If DELETE button is pressed then ... ###########################################################################
if form.getvalue("submit") == "Delete":
print sql.delete(db, cursor, table, fields, pk)
######## If UPDATE button is pressed then ... ############################################################################
if form.getvalue("submit") == "Update":
print sql.update(db, cursor, table, fields, keys, pk)
####### GENERATE AND EXECUTE SEARCH QUERY ################################################################################
result = sql.search(db, cursor, table, fields, keys, exact_keys, limit=10)
rows = result[0];
print result[1];
# Provider
if bool(location and lng and lat):
# Remove white spaces in location name
location = location.strip()
# Check if location already exists in Geocoder DB
c.execute(sql.exists(),
(provider, location))
if not c.fetchone():
# Select your provider for geocoding
if provider == 'Bing':
g = geocoder.bing(location)
elif provider == 'Google':
g = geocoder.google(location)
# Geocode must be correct
if g.ok:
# Calculate Distance
c.execute(sql.distance(lat, lng, g.lat, g.lng))
distance = c.fetchone()['distance']
# Insert Into Rows
fieldnames = ['location', 'data', 'provider', 'distance', 'geom']
c.execute(sql.insert(fieldnames, lat, lng),
(location, json.dumps(g.json), provider, distance))
# Print Statement
if distance > 500:
print distance, '-', location
conn.commit()
######## If UPDATE button is pressed then ... ############################################################################
ignore_update = [];
message2 = ""
if form.getvalue("submit") == "Update":
fields['Salt'] = uuid.uuid4().hex
if fields["HashedPassword"] != None:
fields["HashedPassword"] = hashlib.sha512(fields['HashedPassword'] + fields['Salt']).hexdigest()
else:
ignore_update = ['HashedPassword', 'Salt']
error = 0
if(fields['ViewerType'] != fields['ViewerTypeOld']): #insert more tables if applicable if viewer type is changed
if(fields['ViewerType'] == 'B' or fields['ViewerType'] == 'C'):
if(fields['ViewerTypeOld'] == 'N' or fields['ViewerTypeOld'] == 'P'):
message = sql.insert(db, cursor, 'CrowdFundingViewer', fields, keys_c)
print "insert"
if 'Error' in message:
print message
error = 1
if(fields['ViewerType'] == 'B' or fields['ViewerType'] == 'P'):
if(fields['ViewerTypeOld'] == 'N' or fields['ViewerTypeOld'] == 'C'):
message = sql.insert(db, cursor, 'PremiumViewer', fields, keys_p)
print "insert"
if 'Error' in message:
print message
error = 1
if error == 1: #delete tables if there are any error
if(fields['ViewerTypeOld'] == 'N' or fields['ViewerTypeOld'] == 'P'):
sql.delete(db, cursor, 'CrowdFundingViewer', fields, ['ViewerID'])
