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]