def update_DB(wikipedia_id, title, wikidata_id, labels, sitelinks, description, properties, conn, cursor, schema):
cursor.execute('INSERT INTO %s.wikidata (wikipedia_id, title, wikidata_id, labels, sitelinks, description, properties)' % schema +
'VALUES (%s, %s, %s, %s, %s, %s, %s)'
'ON CONFLICT (wikidata_id) DO UPDATE SET wikipedia_id = EXCLUDED.wikipedia_id, title = EXCLUDED.title, labels = EXCLUDED.labels, sitelinks = EXCLUDED.sitelinks,'
'description = EXCLUDED.description, properties = EXCLUDED.properties;',
(wikipedia_id, title, wikidata_id, extras.Json(labels), extras.Json(sitelinks), description, extras.Json(properties)))
cursor.execute('INSERT into %s.geo (wikidata_id, geometry) ' % schema +
'SELECT wikidata_id, ST_SETSRID(ST_MAKEPOINT((properties->\'coordinate location\'->>\'lng\')::DECIMAL, '
'(properties->\'coordinate location\'->>\'lat\')::DECIMAL), 4326) AS geometry '
'FROM %s.wikidata ' % schema +
'WHERE properties->\'coordinate location\' IS NOT NULL AND wikidata_id = %s '
'ON CONFLICT (wikidata_id) DO UPDATE SET geometry = EXCLUDED.geometry;',
(wikidata_id, ))
cursor.execute('INSERT INTO %s.labels (label, wikidata_id) SELECT distinct(jsonb_array_elements_text(labels)), wikidata_id ' % schema +
'FROM %s.wikidata ' % schema +
'WHERE wikidata_id = %s ON CONFLICT (wikidata_id, label) DO NOTHING;',
(wikidata_id, ))
cursor.execute('INSERT INTO %s.instance (wikidata_id, instance_of) ' % schema +
'SELECT wikidata_id, lower(properties->>\'instance of\')::jsonb '
'FROM %s.wikidata ' % schema +
'WHERE jsonb_typeof(properties->\'instance of\') = \'array\' AND wikidata_id = %s '
'ON CONFLICT (wikidata_id) DO UPDATE SET instance_of = EXCLUDED.instance_of;',
(wikidata_id, ))
cursor.execute('INSERT INTO %s.instance (wikidata_id, instance_of) ' % schema +
'SELECT wikidata_id, jsonb_build_array(lower(properties->>\'instance of\')) '
'FROM %s.wikidata ' % schema +
'WHERE jsonb_typeof(properties->\'instance of\') = \'string\' AND wikidata_id = %s '
'ON CONFLICT (wikidata_id) DO UPDATE SET instance_of = EXCLUDED.instance_of;',
(wikidata_id, ))
def to_tuple(item, position):
return (item.date,
item.station_id,
item.name,
extras.Json(item.value),
item.unit,
item.interval,
extras.Json(item.information),
position)
def on_message(self, message):
try:
message = loads(message)
except Exception as e:
self.write_message('{"error":"wrong data"}')
return
log('ws_messages', 'message:' + str(message))
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth')
squery = "select * from framework.fn_fapi(injson:=%s,apititle:='chats_messages',apitype:='1',sessid:=%s,primaryauthorization:=%s)"
result = None
oldresult = None
while self.opened:
yield gen.sleep(2)
try:
result = yield self.db.execute(squery, (
extras.Json(message),
sesid,
str(primaryAuthorization),
))
except Exception as err:
err = str(err)
self.write_message('{"error":"chats_messages' +
(err[err.find('HINT:') +
5:err.find('+++___')]).split('\n')[0] +
'"}')
self.close()
return
result = result.fetchone()[0].get('outjson')
if str(oldresult) != str(result):
oldresult = result
self.write_message(dumps(result))
#self.finish()
return
def write_event_entry(conn, ltype, ldescr, ldata):
ins = """
insert into yenotsys.eventlog (logtype, logtime, descr, logdata)
values (%(lt)s, current_timestamp, %(ld)s, %(lj)s)
returning id, logtype, logtime;"""
dumps = lambda x: json.dumps(x, cls=rtlib.DateTimeEncoder)
with conn.cursor(cursor_factory=extras.NamedTupleCursor) as cursor:
cursor.execute(
ins, {"lt": ltype, "ld": ldescr, "lj": extras.Json(ldata, dumps=dumps)}
)
row = list(cursor.fetchall())[0]
return row.id, row.logtype, row.logtime
def to_tuple(item):
return (
item.timestamp,
Point(x=item.longitude, y=item.latitude, srid=4326),
item.name,
extras.Json(item.value),
item.unit,
item.mars_type,
item.mars_class,
item.param_id,
item.source,
'({}, {})'.format(item.latitude, item.longitude),
)
def post(self, url):
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth') #get session id cookie
if primaryAuthorization == '1' and sesid is None:
self.set_status(401,None)
self.write('{"message":"No session"}')
return
squery = 'select * from framework.fn_userjson(%s)'
userdetail = []
try:
userdetail = yield self.db.execute(squery,(sesid,))
except Exception as e:
showError(str(e), self)
return
userdetail = userdetail.fetchone()[0]
if userdetail is None:
self.set_status(401,None)
self.write('{"message":"no session or session was killed"}')
return
roles = userdetail.get('roles')
if int(developerRole) not in roles:
self.set_status(403,None)
self.write('{"message":"access denied"}')
return
body = loads(self.request.body.decode('utf-8'))
#settingsFile = body.get('settings')
settingsFile = body
squery = 'select * from framework.fn_mainsettings_save(%s)'
try:
userdetail = yield self.db.execute(squery,(extras.Json(settingsFile),))
except Exception as e:
showError(str(e), self)
return
if settingsFile:
try:
df = open('./settings.json','wt')
df.write(dumps(settingsFile))
df.close()
df = open('./settings.py','at')
df.write(' ')
df.close
except Exception as e:
showError(str(e), self)
return
log('/admin/savesettings',' settingsFile:' + str(settingsFile) + ' userdetail: ' + str(userdetail))
self.write('{"message":"OK"}')
def on_message(self, message):
try:
message = loads(message)
except Exception as e:
self.write_message('{"error":"wrong data"}')
return
log('ws', 'message:' + str(message))
#print('self.sending', self.opened)
viewpath = message.get('viewpath')
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth')
if viewpath is None:
self.write_message('{"error":"view path is None"}')
return
squery = '''
SELECT *
FROM framework.fn_fapi(injson:=%s,apititle:='notifs',apitype:='1',sessid:=%s,primaryauthorization:=%s)
'''
result = None
oldresult = []
while self.opened:
yield gen.sleep(5)
#print('self.ws_connection', self.ws_connection)
try:
result = yield self.db.execute(squery, (
extras.Json(message),
sesid,
str(primaryAuthorization),
))
except Exception as err:
err = str(err)
self.opened = False
self.write_message('{"error":"' +
(err[err.find('HINT:') +
5:err.find('+++___')]).split('\n')[0] +
'"}')
self.close()
return
result = result.fetchone()[0].get('outjson')
if len(oldresult) != len(result):
oldresult = result
self.write_message(dumps(result))
self.close()
return
def main(dump, cursor):
"""We do two scans:
- first collect the id -> name / wikipedia title
- then store the actual objects with a json property.
The first step takes quite a bit of memory (5Gb) - could possibly be done using a temporary table in postgres.
"""
c = 0
skip = 0
id_name_map = {}
for d in parse_wikidata(
subprocess.Popen(['bzcat'],
stdin=file(dump),
stdout=subprocess.PIPE).stdout):
c += 1
if c % 1000 == 0:
print c, skip
if d.get('sitelinks') and d['sitelinks'].get('enwiki'):
value = d['sitelinks']['enwiki']['title']
elif d['labels'].get('en'):
value = id_name_map[d['id']] = d['labels']['en']['value']
else:
skip += 1
continue
id_name_map[d['id']] = value
wp_ids = set()
c = 0
rec = 0
dupes = 0
for d in parse_wikidata(
subprocess.Popen(['bzcat'],
stdin=file(dump),
stdout=subprocess.PIPE).stdout):
c += 1
if c % 1000 == 0:
print c, rec, dupes
wikipedia_id = d.get('sitelinks', {}).get('enwiki', {}).get('title')
title = d['labels'].get('en', {}).get('value')
description = d['descriptions'].get('en', {}).get('value')
wikidata_id = d['id']
properties = {}
if wikipedia_id and title:
# There are some duplicate wikipedia_id's in there. We could make wikidata_id the primary key
# but that doesn't fix the underlying dupe
if wikipedia_id in wp_ids:
dupes += 1
continue
wp_ids.add(wikipedia_id)
# Properties are mapped in a way where we create lists as values for wiki entities if there is more
# than one value. For other types, we always pick one value. If there is a preferred value, we'll
# pick that one.
# Mostly this does what you want. For filtering on colors for flags it alllows for the query:
# SELECT title FROM wikidata WHERE properties @> '{"color": ["Green", "Red", "White"]}'
# However, if you'd want all flags that have Blue in them, you'd have to check for just "Blue"
# and also ["Blue"].
for prop_id, claims in d['claims'].items():
prop_name = id_name_map.get(prop_id)
if prop_name:
ranks = defaultdict(list)
for claim in claims:
mainsnak = claim.get('mainsnak')
if mainsnak:
data_value = map_value(mainsnak.get('datavalue'),
id_name_map)
if data_value:
lst = ranks[claim['rank']]
if mainsnak['datavalue'].get(
'type') != 'wikibase-entityid':
del lst[:]
lst.append(data_value)
for r in 'preferred', 'normal', 'depricated':
value = ranks[r]
if value:
if len(value) == 1:
value = value[0]
else:
value = sorted(value)
properties[prop_name] = value
break
rec += 1
cursor.execute(
'INSERT INTO wikidata (wikipedia_id, title, wikidata_id, description, properties) VALUES (%s, %s, %s, %s, %s)',
(wikipedia_id, title, wikidata_id, description,
extras.Json(properties)))
def post(self, url):
method = url[
5:] #cut 4 symbols from url start, work only if it will be auth/
log(url, str(self.request.body))
self.clear_cookie('sesid')
if method == 'logout':
sesid = self.get_cookie('sesid')
if sesid:
squery = 'select * from framework.fn_logout(%s)'
result = None
try:
result = yield self.db.execute(squery, (sesid, ))
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
self.write('{"message":"OK"}')
elif method == 'auth_f':
body = loads(self.request.body.decode('utf-8'))
login = body.get('login')
passw = body.get('pass')
sesid = self.request.headers.get('Auth')
passw = sha224(passw.encode('utf-8')).hexdigest()
if login is None or passw is None:
self.write('{"message":"login or password is null"}')
self.set_status(500, None)
return
squery = 'select * from framework.fn_sess(%s,%s,%s);'
try:
result = yield self.db.execute(squery, (login, passw, sesid))
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
result = result.fetchone()[0]
self.set_cookie('sesid', result)
self.write('{"message":"OK"}')
elif method == 'auth_crypto':
body = loads(self.request.body.decode('utf-8'))
sesid = self.request.headers.get('Auth')
squery = 'select * from framework.fn_cryptosess(%s,%s);'
try:
result = yield self.db.execute(squery, (
extras.Json(body),
sesid,
))
except Exception as e:
showError(str(e), self)
log(url + '_Error', str(e))
return
result = result.fetchone()[0]
self.set_cookie('sesid', result)
self.write('{"message":"OK"}')
else:
self.set_status(404, None)
self.write('{"message":"method not found"}')
def onRequest(self, url, type):
'''
Function for get,post,put and delete requests on universal api (for class FApi)
'''
args = {} #variable for arguments or body
method = url[4:] #cut 4 symbols from url start, work only if it will be api/
files = [] #variable for files
sesid = self.get_cookie("sesid") or self.request.headers.get('Auth') #get session id cookie
if type != 1 and self.request.headers.get('Content-Type').find('multipart/form-data') == -1:
log(url, 'args: ' + str(self.request.arguments) + '; body: ' + str(self.request.body.decode('utf-8')) +
'; sess: ' + str(sesid) + '; type: ' + str(type))
else:
log(url, 'args: ' + str(self.request.arguments) +
'; sess: ' + str(sesid) + '; type: ' + str(type))
if primaryAuthorization == "1" and sesid == '':
self.set_status(401,None)
self.write('{"message":"No session"}')
return
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
if type in (2,4):
files = self.request.files
body = {}
if files:
value = args.get('value')
if not value:
value = '[]'
value = loads(value)
if args.get('config') and loads(args.get('config')).get('type') in ('file','image') and len(value) > 0:
showError('for type file/image can not be more then 1 file',self)
return
value = value + savefile(self)
args['value'] = dumps(value)
else:
body = loads(self.request.body.decode('utf-8')) #request body, expecting application/json type
for k in args:
body[k] = args.get(k)
args = body
for k in args:
if args[k] == '':
args[k] = None
squery = 'select * from framework.fn_fapi(injson:=%s,apititle:=%s,apitype:=%s,sessid:=%s,primaryauthorization:=%s)'
result = None
try:
result = yield self.db.execute(squery,(extras.Json(args),method,str(type),sesid,str(primaryAuthorization),))
except Exception as e:
log(url + '_Error',' args: ' +
str(extras.Json(args)) + '; sess: ' +
str(sesid) + '; type: ' + str(type) + '; Error:' + str(e))
showError(str(e), self)
return
result = result.fetchone()[0]
self.set_header("Content-Type",'application/json charset="utf-8"')
self.write(dumps(result, indent=4, default=lambda x:str(x),ensure_ascii=False))
self.set_status(200,None)
self.finish()
def Report(self, url):
"""
Function for call node js report method and get xls or xlsx file
"""
args = {} #variable for arguments or body
report_path = url[4:] #cut 4 symbols from url start, work only if it will be rep/
sesid = self.get_cookie('sesid') or self.request.headers.get('Auth') #get session id cookie
log(url, 'args: ' + str(self.request.arguments) +
'; sess: ' + sesid + '; type: 1')
if primaryAuthorization == "1" and sesid == '':
self.set_status(401,None)
self.write('{"message":"No session"}')
return
args = self.request.arguments
for k in args:
args[k] = args.get(k)[0].decode('utf-8')
if args.get('filename') is None:
showError('{"message":"filename is empty"}', self)
return
injson = {'injson':args, 'sess':sesid, 'report_path':report_path}
squery = 'select * from reports.fn_call_report(injson:=%s)'
result = None
try:
result = yield self.db.execute(squery,(extras.Json(injson),))
except Exception as e:
log(url + '_Error',' args: ' +
str(extras.Json(args)) + '; sess: ' +
sesid + '; type: 1; Error:' + str(e))
showError(str(e), self)
return
res = result.fetchone()[0]
data = res.get('outjson')
reqBody = {'template':'..' + res.get('template_path'),'data':dumps(data), 'filename':args.get('filename')}
http_client = AsyncHTTPClient();
req = HTTPRequest(
url=reports_url,
method='POST',
headers={'Content-Type':'application/json'},
body=dumps(reqBody),
connect_timeout=200.0,
request_timeout=200.0
);
try:
req = yield http_client.fetch(req)
except HTTPError as e:
if e.response and e.response.body:
e = e.response.body.decode('utf-8')
log(url + '_Error_NodeJs',' args: ' +
str(extras.Json(args)) + '; sess: ' +
sesid + '; type: 1; Error:' + str(e))
showError(str(e), self)
return
except Exception as err:
system('cd reports && node index.js') # try start reports server
try:
req = yield http_client.fetch(req)
except Exception as err:
showError('No connection to the report server',self)
return
if res.get('ishtml'):
html_report = StringIO()
reportFilePath = './files/' + str(uuid4()) + '.xlsx'
reportFile = open(reportFilePath, 'wb')
reportFile.write(req.buffer.read())
reportFile.close()
html = xlsx2html(reportFilePath, html_report)
html_report.seek(0)
html_report = html_report.read()
self.set_header('Content-Type', 'text/html')
html_report += (
'<script>window.print()</script>' +
'<style type="text/css" media="print">' +
'@page { size: auto; margin: 0mm; } </style>'
)
self.write(html_report)
else:
self.set_header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
self.set_header('Cache-Control', 'public')
self.set_header('Content-Disposition', 'attachment; filename=' + args.get('filename') + '.xlsx')
self.set_header('Content-Description', 'File Transfer')
self.write(req.body)
self.set_status(200)
self.finish()
def insert_rows(self,
vals,
cols,
on_conflict='error',
conflict_cols='auto',
update_cols='all',
where=None):
"""Manual insertion into tables
Parameters
----------
vals : list of tuple
The records to insert. Each tuple
is one row.
cols : list of str
The columns to insert into.
on_conflict : 'nothing' | 'update' | 'error'
What to do when a conflict is encountered
conflict_cols : 'auto' | str | list
If 'auto', it uses primary key when on_conflict is 'update'.
If list, uses the list of columns to create a unique index to infer
conflicts.
update_cols : 'all' | str | list
If 'all', updates all the columns with the new values.
If list, updates only those columns.
where : str | None
Condition to filter rows by. If None,
keep all rows where primary key is not NULL.
Returns
-------
pk_val : str | None
The primary keys of the row inserted into.
If multiple rows are inserted, returns None.
Notes
-----
When conflict_cols is a list, a unique index must be set for the target
columns. The following SQL command is handy:
create unique index subject_identifier on
subject (first_name_birth, last_name_birth, date_of_birth);
"""
if not isinstance(vals, list):
raise ValueError(f'vals must be a list of tuple. Got {type(vals)}')
vals_clean = list()
for val in vals:
if not isinstance(val, tuple):
raise ValueError(
f'entries in vals must be tuples. Got {type(val)}')
if len(val) != len(cols):
raise ValueError(
f'tuple length must match number of columns ({len(cols)})')
val = tuple([
extras.Json(this_val)
if isinstance(this_val, dict) else this_val for this_val in val
])
vals_clean.append(val)
vals = vals_clean
if on_conflict not in ('nothing', 'update', 'error'):
raise ValueError(
f'on_conflict must be one of (nothing, update, error)',
f'Got {on_conflict}')
if conflict_cols == 'auto':
conflict_cols = self.primary_key
if isinstance(conflict_cols, str):
conflict_cols = [conflict_cols]
conflict_cols = ', '.join(conflict_cols)
if update_cols == 'all':
update_cols = cols.copy()
if isinstance(update_cols, str):
update_cols = [update_cols]
if where is None:
where = f'{self.table_id}.{self.primary_key[0]} is NOT NULL'
str_format = ','.join(len(cols) * ['%s'])
col_names = cols.copy()
cols = ','.join([f'"{col}"' for col in cols])
insert_cmd = f'INSERT INTO {self.table_id}({cols}) VALUES({str_format}) '
if on_conflict == 'nothing':
insert_cmd += f'ON CONFLICT DO NOTHING '
elif on_conflict == 'update':
insert_cmd += f'ON CONFLICT ({conflict_cols})'
insert_cmd += ' DO UPDATE SET '
update_cmd = list()
for col_name in update_cols:
update_cmd.append(f'"{col_name}" = excluded."{col_name}"')
insert_cmd += ', '.join(update_cmd) + ' '
insert_cmd += f'WHERE {where} '
insert_cmd += f'RETURNING {self.primary_key[0]}'
_execute_batch(self.conn, self.cursor, insert_cmd, vals)
if len(vals) == 1 and on_conflict != 'do_nothing':
return self.cursor.fetchone()[0]
