def adapt_range(pgrange, pyrange): if isinstance(pgrange, ustr): pgrange = pgrange.encode("utf8") if not isinstance(pyrange, range_): raise ValueError(( "Trying to adapt range {range.__class__.__name__} which does not " "extend base range type.").format(range=pyrange)) if not pyrange: return AsIs((b"'empty'::" + pgrange).decode("utf8")) lower = b"NULL" if not pyrange.lower_inf: lower = adapt(pyrange.lower).getquoted() upper = b"NULL" if not pyrange.upper_inf: upper = adapt(pyrange.upper).getquoted() return AsIs(b"".join([ pgrange, b"(", lower, b", ", upper, b", '", b"[" if pyrange.lower_inc else b"(", b"]" if pyrange.upper_inc else b")", b"')", ]).decode("utf8"))
def getquoted(self): if self.name is None: raise NotImplementedError( 'RangeAdapter must be subclassed overriding its name ' 'or the getquoted() method') r = self.adapted if r.isempty: return b"'empty'::" + self.name.encode('utf8') if r.lower is not None: a = adapt(r.lower) if hasattr(a, 'prepare'): a.prepare(self._conn) lower = a.getquoted() else: lower = b'NULL' if r.upper is not None: a = adapt(r.upper) if hasattr(a, 'prepare'): a.prepare(self._conn) upper = a.getquoted() else: upper = b'NULL' return self.name.encode('utf8') + b'(' + lower + b', ' + upper \ + b", '" + r._bounds.encode('utf8') + b"')"
def getquoted(self): if self.name is None: raise NotImplementedError( "RangeAdapter must be subclassed overriding its name " "or the getquoted() method" ) r = self.adapted if r.isempty: return b"'empty'::" + self.name.encode("utf8") if r.lower is not None: a = adapt(r.lower) if hasattr(a, "prepare"): a.prepare(self._conn) lower = a.getquoted() else: lower = b"NULL" if r.upper is not None: a = adapt(r.upper) if hasattr(a, "prepare"): a.prepare(self._conn) upper = a.getquoted() else: upper = b"NULL" return self.name.encode("utf8") + b"(" + lower + b", " + upper + b", '" + r._bounds.encode("utf8") + b"')"
def getSingleTableSql(host, name, interval=None): if interval==None: interval = "AND tsd_timestamp > ('now'::timestamp - '8 days'::interval)" else: if 'interval' in interval: interval = "AND tsd_timestamp > %s::interval" % (adapt(interval['interval']), ) else: interval = "AND tsd_timestamp BETWEEN %s::timestamp and %s::timestamp" % (adapt(interval['from']),adapt(interval['to']), ) sql = """ SELECT tsd_table_id, tsd_timestamp, tsd_table_size, tsd_index_size, tsd_seq_scans, tsd_index_scans, tsd_tup_ins, tsd_tup_upd, tsd_tup_del, tsd_tup_hot_upd FROM monitor_data.table_size_data WHERE tsd_table_id = ( SELECT t_id FROM monitor_data.tables WHERE t_schema || '.' || t_name = """ + str(adapt(name)) + """ AND t_host_id = """ + str(adapt(host)) + """ ) AND tsd_host_id = """ + str(adapt(host)) + """ """+interval+""" ORDER BY tsd_timestamp ASC """ return sql
def updateRequest(self, team_id, requestDict): ''' Method to a update a request in missions_request table @param team_id: team_id code e.g index in DB table of the team... as returned by rest api @param requestDict: request dictiionary - keys have to be the same key of Db ''' self.checkConnection() # preare dictionary to be used for DB requestOdered = self.prepareRequestDict(team_id, requestDict) # create query sqlquery = "UPDATE missions_request SET " for k,v in requestOdered.items(): if k == "id": continue sqlquery += '%s=%s, ' % (k,adapt(v)) sqlquery = sqlquery[0:-2] + " " sqlquery += "WHERE id=%s" % adapt(requestOdered["id"]) QgsLogger.debug(self.tr("Aggiorna request con la query: %s" % sqlquery), 1 ) try: self.cursor.execute(sqlquery) except Exception as ex: raise(ex)
def updateTeam(self, teamDict): ''' Method to a update a team in organization_team table @param teamDict: team dictiionary - keys have to be the same key of Db ''' self.checkConnection() # preare dictionary to be used for DB teamOdered = self.prepareTeamDict(teamDict) # create query sqlquery = "UPDATE organization_team SET " for k,v in teamOdered.items(): if k == "id": continue sqlquery += '%s=%s, ' % (k,adapt(v)) sqlquery = sqlquery[0:-2] + " " sqlquery += "WHERE id=%s" % adapt(teamOdered["id"]) QgsLogger.debug(self.tr("Aggiorna team con la query: %s" % sqlquery), 1 ) try: self.cursor.execute(sqlquery) except Exception as ex: raise(ex)
def getquoted(self): if self.name is None: raise NotImplementedError( "RangeAdapter must be subclassed overriding its name " "or the getquoted() method" ) r = self.adapted if r.isempty: return b("'empty'::" + self.name) if r.lower is not None: a = adapt(r.lower) if hasattr(a, "prepare"): a.prepare(self._conn) lower = a.getquoted() else: lower = b("NULL") if r.upper is not None: a = adapt(r.upper) if hasattr(a, "prepare"): a.prepare(self._conn) upper = a.getquoted() else: upper = b("NULL") return b(self.name + "(") + lower + b(", ") + upper + b(", '%s')" % r._bounds)
def add_command(self,command): TAG = 'add_command' if isinstance(command, dict): if command.get(C.SAMSUNG_COMMANDS_TABLE_ACTION) and \ command.get(C.SAMSUNG_COMMANDS_TABLE_DEVICE) \ and command.get(C.SAMSUNG_COMMANDS_TABLE_UUID): if not command[C.SAMSUNG_COMMANDS_TABLE_ATTRIBUTE]: command[C.SAMSUNG_COMMANDS_TABLE_ATTRIBUTE]= "{}" self.cursor.execute(""" insert into samsung_commands (device_id, action,attribute,command_uuid) values( %s,%s,%s,%s) returning id""", [command[C.SAMSUNG_COMMANDS_TABLE_DEVICE], command[C.SAMSUNG_COMMANDS_TABLE_ACTION], adapt(command[C.SAMSUNG_COMMANDS_TABLE_ATTRIBUTE]), adapt(command[C.SAMSUNG_COMMANDS_TABLE_UUID])]) if self.cursor.rowcount>0: row = self.cursor.fetchone(); return row[0] else: return None else: self.log.i(TAG, 'Not all required parameters sent in '+command.__repr__()) return None
def adapt_range(pgrange, pyrange): if not isinstance(pyrange, range_): raise ValueError(( "Trying to adapt range {range.__class__.__name__} which does not " "extend base range type.").format(range=pyrange)) if not pyrange: return AsIs("'empty'::" + pgrange) lower = b("NULL") if not pyrange.lower_inf: lower = adapt(pyrange.lower).getquoted() upper = b("NULL") if not pyrange.upper_inf: upper = adapt(pyrange.upper).getquoted() return AsIs(b"".join([ b(pgrange), b("("), lower, b(", "), upper, b(", '"), b("[" if pyrange.lower_inc else "("), b("]" if pyrange.upper_inc else ")"), b("')") ]).decode("utf8"))
def execute(self, operation, parameters=None): if self.closed(): raise errors.Error('Cursor is closed') if parameters: # optional requirement from psycopg2.extensions import adapt if isinstance(parameters, dict): for key in parameters: v = adapt(parameters[key]).getquoted() operation = operation.replace(':' + key, v) elif isinstance(parameters, tuple): operation = operation % tuple(adapt(p).getquoted() for p in parameters) else: raise errors.Error("Argument 'parameters' must be dict or tuple") self.rowcount = 0 self.buffered_rows = collections.deque() self.last_execution = operation self.connection.write(messages.Query(operation)) self.fetch_rows() if self.error is not None: raise self.error
def add_device(self, device): TAG = 'add_device' ## if user_id, udid and os is present then only we can do entry if type(device) == dict and C.DEVICE_TABLE_USER in device: if UserDBHelper().is_user_valid(str(device.get('user_id'))): if device.has_key(C.DEVICE_TABLE_OS): if device.has_key(C.DEVICE_TABLE_UDID): try: self.cursor.execute("""INSERT INTO devices (os, udid, user_id) VALUES({0}, {1}, {2}) RETURNING id""".format(adapt(device.get('os')), adapt(device.get('udid')), adapt(device.get('user_id')))) except Exception,err: self.log.e(TAG,'Exception : ' + repr(err)) return None if self.cursor.rowcount > 0: row = self.cursor.fetchone() return row[0] else: self.log.e(TAG,'Not abe to insert in device table') return None else: self.log.e(TAG,'UDID not found in dictionary') return None else: self.log.e(TAG, 'OS is not found in dictionary') return None else: self.log.e(TAG, 'USER ID not found in dictionary') return None
def topologyInsert(con, simulationId, atomNum, atomName, atomType, residueCode, residueNumber, chainCode, description): sql_insert_topology="INSERT INTO dnamodel.topology (simulationId, atomNum, atomName, atomType, residueNum, residueCode, chainCode, description) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING topologyId;" cur = con.cursor() cur.execute(sql_insert_topology, (simulationId, atomNum, adapt(atomName), adapt(atomType), residueNumber, adapt(residueCode), adapt(chainCode), adapt(description))) topologyId = cur.fetchone()[0] cur.close() return topologyId
def adapt_box(box): return AsIs("'(%s,%s),(%s,%s)'::box" % ( adapt(box.init_point.x), adapt(box.init_point.y), adapt(box.end_point.x), adapt(box.end_point.y) ))
def adapt_lseg(l): return AsIs("'[(%s,%s), (%s,%s)]'::lseg" % (\ adapt(l.init_point.x), adapt(l.init_point.y), adapt(l.end_point.x), adapt(l.end_point.y) ))
def adapt_box(box): return AsIs("'(%s,%s),(%s,%s)'::box" % ( adapt(box.first_vertex.x), adapt(box.first_vertex.y), adapt(box.second_vertex.x), adapt(box.second_vertex.y) ))
def test_adapt_subtype(self): from psycopg2.extensions import adapt class Sub(str): pass s1 = "hel'lo" s2 = Sub(s1) self.assertEqual(adapt(s1).getquoted(), adapt(s2).getquoted())
def getquoted(self): r = [] for k in self.attnames: if self.adapted.has_key(k): r.append(adapt(self.adapted[k])) else: r.append(None) return adapt(r)
def prepare_select_processes(server_url, identifier): return ( "SELECT id FROM " + tables["processes"] + " " "WHERE wps_server_id IN (" "SELECT id FROM " + tables["wpsServers"] + " " "WHERE url = " + str(adapt(server_url)) + " " ")" "AND identifier = " + str(adapt(identifier)) )
def on_get(self, req, resp): '''Return json object related to GET with lat & lon HTTP parameters ''' raw_lat = req.get_param('lat') raw_lon = req.get_param('lon') if raw_lat is None or raw_lon is None: resp.status = falcon.HTTP_400 lat = None lon = None else: lat = decimal.Decimal(raw_lat) lon = decimal.Decimal(raw_lon) if not (self.lat_is_valid(lat) and self.lon_is_valid(lon)): resp.status = falcon.HTTP_400 else: dbc = db.connect() cur = dbc.cursor() nb_maps_raw = req.get_param('nb_maps') nb_addr_raw = req.get_param('nb_addr') if nb_maps_raw is None: nb_maps_raw = '20' if nb_addr_raw is None: nb_addr_raw = '10' nb_maps = decimal.Decimal(nb_maps_raw) nb_addr = decimal.Decimal(nb_addr_raw) #id (uuid), path (str), geom (geom), address (str), level (str), building (str) loc = "st_setsrid(st_makepoint(%s,%s),4326)" % (lon,lat) query = """SELECT array_to_json(array_agg(row_to_json(t)))::text FROM ( SELECT floor(st_distance(geom::geography, %s::geography)) as dist, ST_X(geom) as lon, ST_Y(geom) as lat, * FROM ( (select * from ban_nomap where ST_DWithin(geom, %s, 0.001) order by st_distance(geom,%s) limit %s) union (select * from map_info where ST_DWithin(geom, %s, 0.1) limit %s)) as d ORDER BY ST_Distance(geom,%s), level) t """ % (loc, loc,loc,adapt(nb_addr),loc,adapt(nb_maps),loc) cur.execute(query) what_is_around = cur.fetchone()[0] resp.set_header('X-Powered-By', 'OpenEvacMap') if what_is_around is None: resp.status = falcon.HTTP_204 else: resp.status = falcon.HTTP_200 resp.set_header('Access-Control-Allow-Origin', '*') resp.set_header('Access-Control-Allow-Headers', 'X-Requested-With') resp.body = (what_is_around) query = """INSERT INTO log (loc, ip) VALUES (ST_SetSRID(ST_GeometryFromText('POINT(%s %s)'),4326),'%s');""" % (lon,lat,req.env['REMOTE_ADDR']) cur.execute(query) dbc.commit() cur.close() dbc.close()
def snmpResult2RRD2(host): connection = psycopg2.connect( host='10.0.10.111', port=54321, database='GIS_2010', user='******', password='******' ) cursor = connection.cursor() dOid_to_Res = dict() for i, o in enumerate(host.oids): dOid_to_Res[o] = host.results[i] data = {} for oid, results in dOid_to_Res.items(): for port, result in results.items(): if data.get(port, None): data[port][oid.name] = int(result) else: data[port] = { oid.name: int(result) } for port, values in data.items(): insert = "INSERT INTO netstats.traf (%s) VALUES (%s)" query = insert % ( ', '.join([ '"%s"' % col for col in ['dev_name', 'port'] \ + values.keys() \ + ['inserted',] ]), ', '.join( [adapt(host.hostname).getquoted(), str(port)] \ + [str(adapt(v)) for v in values.values()] \ # + ['now()',] + ["'%s'" % datetime.now(),] ) ) cursor.execute(query) # print query connection.commit() cursor.close() connection.close() rrdbase = '%s/%s' % (host.hostdir, host.perfbase) updstr = 'N:%s:%s' % (host.snmptime, host.errors) #sys.stderr.write('%s, %s\n' % (rrdbase, updstr)) #sys.stderr.flush() try: rrdtool.update(rrdbase, updstr) except Exception, why: print "Error in update (%s, %s) - %s" % ( host.hostname, index, why )
def _getquoted_9(self): """Use the hstore(text[], text[]) function.""" if not self.wrapped: return b"''::hstore" k = _ext.adapt(self.wrapped.keys()) k.prepare(self.conn) v = _ext.adapt(self.wrapped.values()) v.prepare(self.conn) return b"hstore(" + k.getquoted() + b", " + v.getquoted() + b")"
def _getquoted_9(self): """Use the hstore(text[], text[]) function.""" if not self.wrapped: return b("''::hstore") k = _ext.adapt(self.wrapped.keys()) k.prepare(self.conn) v = _ext.adapt([unicode(x) if x is not None else None for x in self.wrapped.values()]) v.prepare(self.conn) return b("hstore(") + k.getquoted() + b(", ") + v.getquoted() + b(")")
def getSizeTrendSQL(host_id=None, days='8'): days += 'days' host_id = str(adapt(host_id)) if host_id else 'NULL' sql = """ SELECT tsda_timestamp AS tsd_timestamp, tsda_host_id AS tsd_host_id, tsda_db_size AS size, tsda_tup_ins AS s_ins, tsda_tup_upd AS s_upd, tsda_tup_del AS s_del FROM monitor_data.table_size_data_agg WHERE tsda_timestamp > now() - """ + str(adapt(days)) + """::interval AND tsda_timestamp < now() - '2 hours'::interval AND (""" + host_id + """ IS NULL OR tsda_host_id = """ + host_id + """) UNION ALL SELECT * FROM ( SELECT tsd_timestamp, tsd_host_id, SUM(tsd_table_size)+SUM(tsd_index_size) AS size, SUM(tsd_tup_ins) AS s_ins, SUM(tsd_tup_upd) AS s_upd, SUM(tsd_tup_del) AS s_del FROM monitor_data.table_size_data WHERE tsd_timestamp >= now() - '2 hours'::interval AND (""" + host_id + """ IS NULL OR tsd_host_id = """ + host_id + """) GROUP BY tsd_host_id, tsd_timestamp ORDER BY tsd_host_id, tsd_timestamp ) a """ if not tplE._settings.get('run_aggregations'): sql = """SELECT tsd_timestamp, tsd_host_id, ( SUM(tsd_table_size)+SUM(tsd_index_size) ) AS size, SUM(tsd_tup_ins) AS s_ins, SUM(tsd_tup_upd) AS s_upd, SUM(tsd_tup_del) AS s_del FROM monitor_data.table_size_data WHERE tsd_timestamp > now() - """ + str(adapt(days)) + """::interval AND (""" + host_id + """ IS NULL OR tsd_host_id = """ + host_id + """) GROUP BY tsd_host_id, tsd_timestamp ORDER BY tsd_host_id, tsd_timestamp """ return sql
def create_recovery_conf(dirpath, site, *, port=PGHOARD_PORT, primary_conninfo=None, recovery_end_command=None, recovery_target_action=None, recovery_target_name=None, recovery_target_time=None, recovery_target_xid=None, restore_to_master=None): restore_command = [ "pghoard_postgres_command", "--mode", "restore", "--port", str(port), "--site", site, "--output", "%p", "--xlog", "%f", ] lines = [ "# pghoard created recovery.conf", "recovery_target_timeline = 'latest'", "trigger_file = {}".format(adapt(os.path.join(dirpath, "trigger_file"))), "restore_command = '{}'".format(" ".join(restore_command)), ] if not restore_to_master: lines.append("standby_mode = 'on'") if primary_conninfo: lines.append("primary_conninfo = {}".format(adapt(primary_conninfo))) if recovery_end_command: lines.append("recovery_end_command = {}".format(adapt(recovery_end_command))) if recovery_target_action: with open(os.path.join(dirpath, "PG_VERSION"), "r") as fp: pg_version = fp.read().strip() if LooseVersion(pg_version) >= "9.5": lines.append("recovery_target_action = '{}'".format(recovery_target_action)) elif recovery_target_action == "promote": pass # default action elif recovery_target_action == "pause": lines.append("pause_at_recovery_target = True") else: print("Unsupported recovery_target_action {!r} for PostgreSQL {}, ignoring".format( recovery_target_action, pg_version)) if recovery_target_name: lines.append("recovery_target_name = '{}'".format(recovery_target_name)) if recovery_target_time: lines.append("recovery_target_time = '{}'".format(recovery_target_time)) if recovery_target_xid: lines.append("recovery_target_xid = '{}'".format(recovery_target_xid)) content = "\n".join(lines) + "\n" filepath = os.path.join(dirpath, "recovery.conf") filepath_tmp = filepath + ".tmp" with open(filepath_tmp, "w") as fp: fp.write(content) os.rename(filepath_tmp, filepath) return content
def update_login_password(self, login_id, password): TAG = 'update_login_password' try: self.cursor.execute("""UPDATE logins set password={0} WHERE id={1};""".format(adapt(password), adapt(login_id))) except Exception, err: self.log.e(TAG, 'Exception: '+ repr(err)) return False
def getBlockedProcessesCounts(hostId, days='8'): ret = [] days += 'days' sql = """ with q_wait_startpoints as ( select date_trunc('hour'::text, query_start) + floor(date_part('minute'::text, query_start) / 15::double precision) * '00:15:00'::interval as wait_start_timestamp, count(1) as wait_starts from ( select query_start, query, min(bp_timestamp) as wait_start, max(bp_timestamp) as wait_end, count(1) from monitor_data.blocking_processes where bp_host_id = """ + str(adapt(hostId)) + """ and bp_timestamp > now() - """ + str(adapt(days)) + """::interval and waiting group by 1, 2 order by 1, 2 ) a where wait_end - wait_start >= '5 seconds'::interval group by date_trunc('hour'::text, query_start) + floor(date_part('minute'::text, query_start) / 15::double precision) * '00:15:00'::interval ), q_timeline as ( select * from (select generate_series(current_date - """ + str(adapt(days)) + """::interval, now(), '00:15:00'::interval) AS ts) a where ts > now() - """ + str(adapt(days)) + """::interval ) SELECT date_trunc('hour'::text, q_timeline.ts) + floor(date_part('minute'::text, q_timeline.ts) / 15::double precision) * '00:15:00'::interval AS ts, coalesce(wait_starts, 0) as count FROM q_timeline left join q_wait_startpoints on q_wait_startpoints.wait_start_timestamp = q_timeline.ts ORDER BY 1 ASC """ for record in datadb.execute(sql): ret.append( (record['ts'] , record['count'] ) ) return ret
def insert(uid, nickname, password): verify = is_present("uid", 'user_db', uid) if verify['boolean'] == False: query_string = """INSERT INTO user_db (uid, nickname, password) \ VALUES ({0}, {1}, {2})""".format(adapt(uid), adapt(nickname), adapt(password)) verify["cursor"].execute(query_string); end(verify["conn"]) return jsonify({"message":"new user created"}) else: end(verify["conn"]) return jsonify({"message":"uid already exists"}), 404
def create_recovery_conf(dirpath, site, primary_conninfo): content = """# pghoard created recovery.conf standby_mode = 'on' primary_conninfo = {primary_conninfo} trigger_file = {trigger_file} restore_command = 'pghoard_restore get %f %p --site {site}' recovery_target_timeline = 'latest' """.format(primary_conninfo=adapt(primary_conninfo), trigger_file=adapt(os.path.join(dirpath, "trigger_file")), site=site) filepath = os.path.join(dirpath, "recovery.conf") open(filepath, "w").write(content)
def execute(self, operation, parameters=None): if self.closed(): raise errors.Error("Cursor is closed") self.flush_to_query_ready() if parameters: # # optional requirement from psycopg2.extensions import adapt if isinstance(parameters, dict): for key in parameters: param = parameters[key] # Make sure adapt() behaves properly if isinstance(param, str): v = adapt(param.encode("utf8")).getquoted() else: v = adapt(param).getquoted() # Using a regex with word boundary to correctly handle params with similar names # such as :s and :start match_str = u":%s\\b" % str(key) operation = re.sub(match_str, v.decode("utf-8"), operation, flags=re.UNICODE) elif isinstance(parameters, tuple): tlist = [] for p in parameters: if isinstance(p, str): tlist.append(adapt(p.encode("utf8")).getquoted()) else: tlist.append(adapt(p).getquoted()) operation = operation % tuple(tlist) else: raise errors.Error("Argument 'parameters' must be dict or tuple") self.rowcount = -1 self.connection.write(messages.Query(operation)) # read messages until we hit an Error, DataRow or ReadyForQuery while True: message = self.connection.read_message() # save the message because there's no way to undo the read self._message = message if isinstance(message, messages.ErrorResponse): raise errors.QueryError.from_error_response(message, operation) elif isinstance(message, messages.RowDescription): self.description = list(map(lambda fd: Column(fd, self.unicode_error), message.fields)) elif isinstance(message, messages.DataRow): break elif isinstance(message, messages.ReadyForQuery): break else: self.connection.process_message(message)
def test_mro_required(self): import psycopg2 from psycopg2.extensions import adapt, register_adapter, AsIs # Intentionally old-style, they don't expose their MRO. class A: pass class B(A): pass register_adapter(A, lambda a: AsIs("a")) with self.assertRaises(psycopg2.ProgrammingError): adapt(B())
geoname = ast.literal_eval(result) geonames = geoname['geonames'][0] countryName = geonames[u'countryName'] adminName1 = geonames[u'adminName1'] adminName2 = geonames[u'adminName2'] adminName3 = geonames[u'adminName3'] near = geonames[u'toponymName'] distance = geonames[u'distance'] population = geonames[u'population'] tz = geonames[u'timezone'] countrycode = geonames[u'countryCode'] continentcode = geonames[u'continentCode'] print countryName, adminName1, adminName2, adminName3, near, distance, population, tz, countrycode, continentcode cat_src = 'glc' cmd = "UPDATE planet_osm_point SET (countryname, adminname1, adminname2, adminname3, near, distance, population, cat_src, cat_id, tz, countrycode, continentcode ) = (%s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s, %s ) WHERE id= %d;" % \ (adapt(countryName), adapt(adminName1), adapt(adminName2), adapt(adminName3), adapt(near), distance, population, adapt(cat_src), rid, adapt(json.dumps(tz)), adapt(countrycode), adapt(continentcode), rid) print cmd cursor.execute(cmd) time.sleep(2) count += 1 #if count == 2: # break connection.commit() cursor.close() connection.close() sys.exit(0)
def sqlesc(value): adapted = adapt(value.encode('utf-8')) if hasattr(adapted, 'getquoted'): adapted = adapted.getquoted() return adapted
def adapt_macaddr(maddr): from psycopg2.extensions import adapt, AsIs return AsIs("{0}::macaddr".format(adapt(str(maddr))))
def _adapt_datetime(dt): return AsIs("%s" % adapt(DateTime.iso8601(dt)))
def as_sql(self, qn, connection): t = ",".join(str(adapt(x)) for x in self.tags) return 'ARRAY[%s] <@ "%s".%s' % (t, self.table, qn("tags")), []
def as_sql(self, compiler, connection): tags = ",".join( str(adapt(str(x).strip())) for x in self.rhs if str(x).strip()) return "(ARRAY[%s] <@ %s)" % ( tags, self.lhs.as_sql(compiler, connection)[0]), []
def create_recovery_conf(dirpath, site, *, port=PGHOARD_PORT, primary_conninfo=None, recovery_end_command=None, recovery_target_action=None, recovery_target_name=None, recovery_target_time=None, recovery_target_xid=None, restore_to_master=None): restore_command = [ "pghoard_postgres_command", "--mode", "restore", "--port", str(port), "--site", site, "--output", "%p", "--xlog", "%f", ] with open(os.path.join(dirpath, "PG_VERSION"), "r") as fp: pg_version = LooseVersion(fp.read().strip()) if pg_version >= "12": trigger_file_setting = "promote_trigger_file" else: trigger_file_setting = "trigger_file" lines = [ "# pghoard created recovery.conf", "recovery_target_timeline = 'latest'", "{} = {}".format(trigger_file_setting, adapt(os.path.join(dirpath, "trigger_file"))), "restore_command = '{}'".format(" ".join(restore_command)), ] use_recovery_conf = (pg_version < "12" ) # no more recovery.conf in PG >= 12 if not restore_to_master: if use_recovery_conf: lines.append("standby_mode = 'on'") else: # Indicates the server should start up as a hot standby create_signal_file(os.path.join(dirpath, "standby.signal")) if primary_conninfo: lines.append("primary_conninfo = {}".format(adapt(primary_conninfo))) if recovery_end_command: lines.append("recovery_end_command = {}".format( adapt(recovery_end_command))) if recovery_target_action: if pg_version >= "9.5": lines.append( "recovery_target_action = '{}'".format(recovery_target_action)) elif recovery_target_action == "promote": pass # default action elif recovery_target_action == "pause": lines.append("pause_at_recovery_target = True") else: print( "Unsupported recovery_target_action {!r} for PostgreSQL {}, ignoring" .format(recovery_target_action, pg_version)) if recovery_target_name: lines.append( "recovery_target_name = '{}'".format(recovery_target_name)) if recovery_target_time: lines.append( "recovery_target_time = '{}'".format(recovery_target_time)) if recovery_target_xid: lines.append("recovery_target_xid = '{}'".format(recovery_target_xid)) content = "\n".join(lines) + "\n" if use_recovery_conf: # Overwrite the recovery.conf open_mode = "w" filepath = os.path.join(dirpath, "recovery.conf") else: # Append to an existing postgresql.auto.conf (PG >= 12) open_mode = "a" lines.insert( 0, "\n") # in case postgresql.conf does not end with a newline filepath = os.path.join(dirpath, "postgresql.auto.conf") filepath_tmp = filepath + ".tmp" with open(filepath_tmp, open_mode) as fp: fp.write(content) os.rename(filepath_tmp, filepath) return content
def adapt_money(m): return AsIs('(%s,%s)::currency_amount' % (adapt(m.amount), adapt(m.currency)))
def make_query_string(params): s = [] for k, v in params.items(): s.append("%s = %s" % (k, adapt(v))) return " and ".join(s)
def truncate(self): cursor = connections[self.database].cursor() if self.verbosity: logger.info("Emptying database plan tables...") starttime = time() if self.cluster == -1: # Complete export for the complete model cursor.execute("truncate table out_problem, out_resourceplan, out_constraint") cursor.execute(''' update operationplan set owner_id = null where owner_id is not null and exists ( select 1 from operationplan op2 where op2.reference = operationplan.owner_id and (op2.status is null or op2.status = 'proposed') ) ''') cursor.execute(''' delete from operationplanmaterial using operationplan where operationplanmaterial.operationplan_id = operationplan.reference and ( operationplan.status='proposed' or operationplan.status is null or operationplanmaterial.status = 'proposed' or operationplanmaterial.status is null ) ''') cursor.execute(''' delete from operationplanresource using operationplan where operationplanresource.operationplan_id = operationplan.reference and ( (operationplan.status='proposed' or operationplan.status is null) or operationplan.type = 'STCK' or operationplanresource.status = 'proposed' or operationplanresource.status is null ) ''') cursor.execute(''' delete from operationplan where (status='proposed' or status is null) or type = 'STCK' ''') else: # Partial export for a single cluster cursor.execute('create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))') for i in frepple.items(): if i.cluster == self.cluster: cursor.execute(("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode(self.encoding))) cursor.execute("delete from out_constraint where demand in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)") cursor.execute(''' delete from operationplanmaterial using cluster_keys where operationplan_id in ( select reference from operationplan inner join cluster_keys on cluster_keys.name = operationplan.item_id union select reference from operationplan where owner_id in ( select reference from operationplan parent_opplan inner join cluster_keys on cluster_keys.name = parent_opplan.item_id ) ) and operationplanmaterial.status = 'proposed' ''') cursor.execute(''' delete from out_problem where entity = 'demand' and owner in ( select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id ) ''') cursor.execute(''' delete from out_problem where entity = 'material' and owner in (select buffer.name from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id) ''') cursor.execute(''' delete from operationplanresource where operationplan_id in ( select reference from operationplan inner join cluster_keys on cluster_keys.name = operationplan.item_id where status = 'proposed' or status is null or oplan_parent.type='STCK' union select reference from operationplan where owner_id in ( select reference from operationplan parent_opplan inner join cluster_keys on cluster_keys.name = parent_opplan.item_id ) where status = 'proposed' or status is null ) and operationplanresource.status = 'proposed' ''') cursor.execute(''' delete from operationplan using cluster_keys where owner_id in ( select oplan_parent.reference from operationplan as oplan_parent where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK') and oplan_parent.item_id = cluster_keys.name ) ''') cursor.execute(''' delete from operationplan using cluster_keys where (status='proposed' or status is null or type='STCK') and item_id = cluster_keys.name ''') # TODO next subqueries are not efficient - the exists condition triggers a sequential scan cursor.execute("delete from out_constraint where exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_constraint.demand like forecast.name || ' - %')") cursor.execute("delete from out_problem where entity = 'demand' and exists (select 1 from forecast inner join cluster_keys on cluster_keys.name = forecast.item_id and out_problem.owner like forecast.name || ' - %')") cursor.execute("truncate table cluster_keys") for i in frepple.resources(): if i.cluster == self.cluster: cursor.execute(("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode(self.encoding))) cursor.execute("delete from out_problem where entity = 'demand' and owner in (select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id)") cursor.execute('delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name') cursor.execute('delete from out_resourceplan using cluster_keys where resource = cluster_keys.name') cursor.execute("delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name") cursor.execute('truncate table cluster_keys') for i in frepple.operations(): if i.cluster == self.cluster: cursor.execute(("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode(self.encoding))) cursor.execute("delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name") cursor.execute("delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.name = cluster_keys.name") cursor.execute("drop table cluster_keys") if self.verbosity: logger.info("Emptied plan tables in %.2f seconds" % (time() - starttime))
def __conform__(self, protocol): return AsIs( "('fernet',%s,%s)::encrypted" % (adapt(self.payload), adapt(self.ts)) )
def adapt_proxy(proxy): return adapt(proxy._get_current_object())
def test_encoding_error(self): from psycopg2.extensions import adapt snowman = "\u2603" a = adapt(snowman) self.assertRaises(UnicodeEncodeError, a.getquoted)
def test_encoding_default(self): from psycopg2.extensions import adapt a = adapt("hello") self.assertEqual(a.encoding, 'latin1') self.assertEqual(a.getquoted(), b"'hello'")
def quote(x): return adapt(x).getquoted()
def Q(self): """ Returns Q object which can be applied to ManagedObject.objects.filter """ # Exclude NOC internal objects q = ~Q(profile__in=list(Profile.objects.filter(name__startswith="NOC."))) # Exclude objects being wiped q &= ~Q(name__startswith="wiping-") # Filter by is_managed if self.filter_managed is not None: q &= Q(is_managed=self.filter_managed) # Filter by ID if self.filter_id: q &= Q(id=self.filter_id) # Filter by pool if self.filter_pool: q &= Q(pool=self.filter_pool) # Filter by name (regex) if self.filter_name: q &= Q(name__regex=self.filter_name) # Filter by profile if self.filter_profile: q &= Q(profile=self.filter_profile) # Filter by vendor if self.filter_vendor: q &= Q(vendor=self.filter_vendor) # Filter by platform if self.filter_platform: q &= Q(platform=self.filter_platform) # Filter by version if self.filter_version: q &= Q(version=self.filter_version) # Filter by ttsystem if self.filter_tt_system: q &= Q(tt_system=self.filter_tt_system) # Filter by object's profile if self.filter_object_profile: q &= Q(object_profile=self.filter_object_profile) # Filter by address (regex) if self.filter_address: q &= Q(address__regex=self.filter_address) # Filter by prefix table if self.filter_prefix: q &= SQL( """ EXISTS ( SELECT * FROM main_prefixtableprefix p WHERE table_id=%d AND address::inet <<= p.prefix)""" % self.filter_prefix.id ) # Filter by administrative domain if self.filter_administrative_domain: dl = AdministrativeDomain.get_nested_ids(self.filter_administrative_domain) q &= SQL( """ "sa_managedobject"."administrative_domain_id" IN (%s) """ % ", ".join(str(x) for x in dl) ) # Filter by VRF if self.filter_vrf: q &= Q(vrf=self.filter_vrf) # Filter by VC domain if self.filter_vc_domain: q &= Q(vc_domain=self.filter_vc_domain) # Filter by termination group if self.filter_service_group: q &= Q(effective_service_groups=self.filter_service_group.id) # Filter by termination group if self.filter_client_group: q &= Q(effective_client_groups=self.filter_client_group.id) # Filter by username if self.filter_user: q &= Q(user__regex=self.filter_user) # Filter by remote path if self.filter_remote_path: q &= Q(remote_path__regex=self.filter_remote_path) # Filter by description if self.filter_description: q &= Q(description__regex=self.filter_description) # Restrict to tags when necessary if self.filter_tags: q &= Q(tags__contains=self.filter_tags) # Restrict to attributes when necessary for s in self.managedobjectselectorbyattribute_set.all(): q &= SQL( """ ("sa_managedobject"."id" IN ( SELECT managed_object_id FROM sa_managedobjectattribute WHERE key ~ %s AND value ~ %s )) """ % (adapt(s.key_re).getquoted(), adapt(s.value_re).getquoted()) ) # Restrict to sources if self.sources.count(): if self.source_combine_method == "A": # AND for s in self.sources.all(): q &= s.Q else: # OR ql = list(self.sources.all()) q = ql.pop(0).Q for qo in ql: q |= qo.Q return q
def adapt_proxy(proxy): """Get current object and try to adapt it again.""" return adapt(proxy._get_current_object())
def getquoted(self): res = "'(%s,%s,%s)'::sgas_memory" % (adapt( self.amount * self.unit), self.metric, self.type) return res
k int4, d date, t time, dt timestamp, z interval)""") except: conn.rollback() curs.execute("DROP TABLE test_dt") curs.execute("""CREATE TABLE test_dt ( k int4, d date, t time, dt timestamp, z interval)""") conn.commit() # build and insert some data using mx.DateTime mx1 = (1, mx.DateTime.Date(2004, 10, 19), mx.DateTime.Time(0, 11, 17.015), mx.DateTime.Timestamp(2004, 10, 19, 0, 11, 17.5), mx.DateTime.DateTimeDelta(13, 15, 17, 59.9)) from psycopg2.extensions import adapt import psycopg2.extras print adapt(mx1) print "Inserting mx.DateTime values..." curs.execute("INSERT INTO test_dt VALUES (%s, %s, %s, %s, %s)", mx1) # build and insert some values using the datetime adapters dt1 = (2, datetime.date(2004, 10, 19), datetime.time(0, 11, 17, 15000), datetime.datetime(2004, 10, 19, 0, 11, 17, 500000), datetime.timedelta(13, 15 * 3600 + 17 * 60 + 59, 900000)) print "Inserting Python datetime values..." curs.execute("INSERT INTO test_dt VALUES (%s, %s, %s, %s, %s)", dt1) # now extract the row from database and print them print "Extracting values inserted with mx.DateTime wrappers:" curs.execute("SELECT d, t, dt, z FROM test_dt WHERE k = 1")
def adapt_cube(cube): """ Function required by Postgres for inserting/searching cube extension colors """ return AsIs("'(%s, %s, %s)'" % (adapt(cube.red), adapt(cube.green), adapt(cube.blue)))
def __init__(self, fields, table_name): self.table_name = table_name self.fields = [adapt(field) for field in fields]
def adapt(self, value): """Adapt any value into SQL safe string""" return adapt(value)
def adapt_point(point): lat = adapt(point.lat).getquoted() lng = adapt(point.lng).getquoted() return AsIs("'(%s, %s)'" % (lat, lng))
def moneyvalue_adapter(value): return AsIs("(%s,%s)::money_value" % (adapt(value.amount), adapt(value.currency.code)))
p2 = copy.deepcopy(p1) p2['type'] = 'json' pprint(p2) cur.execute("SELECT test_json(%s)", [p2]) pprint(cur.fetchone()[0]) p3 = { 'type': 'hstore', 'key1': 'val1', 'key2': 'val2', 'key3': None, #'key4': 123, #'key5': [1,2,3] } pprint(p3) print cur.mogrify("SELECT test_hstore(%s)", [p3]) cur.execute("SELECT test_hstore(%s)", [p3]) pprint(cur.fetchone()[0]) try: print adapt({}).getquoted() except Exception, e: print e try: print adapt({'type': 'nonexistent'}).getquoted() except Exception, e: print e
def run(cls, cluster=-1, database=DEFAULT_DB_ALIAS, **kwargs): import frepple cursor = connections[database].cursor() if cluster == -1: # Complete export for the complete model cursor.execute( "truncate table out_problem, out_resourceplan, out_constraint") cursor.execute(""" update operationplan set owner_id = null where owner_id is not null and exists ( select 1 from operationplan op2 where op2.reference = operationplan.owner_id and (op2.status is null or op2.status = 'proposed') ) """) cursor.execute(""" truncate operationplanmaterial, operationplanresource """) cursor.execute(""" delete from operationplan where (status='proposed' or status is null) or type = 'STCK' """) else: # Partial export for a single cluster cursor.execute( "create temporary table cluster_keys (name character varying(300), constraint cluster_key_pkey primary key (name))" ) for i in frepple.items(): if i.cluster == cluster: cursor.execute( ("insert into cluster_keys (name) values (%s);\n" % adapt(i.name).getquoted().decode("UTF8"))) cursor.execute(""" delete from out_constraint where demand in ( select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id ) """) cursor.execute(""" delete from out_problem where entity = 'demand' and owner in ( select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id ) """) cursor.execute(""" delete from out_problem where entity = 'material' and owner in ( select buffer.item_id || ' @ ' || buffer.location_id from buffer inner join cluster_keys on cluster_keys.name = buffer.item_id ) """) cursor.execute(""" delete from operationplan using cluster_keys where owner_id in ( select oplan_parent.reference from operationplan as oplan_parent where (oplan_parent.status='proposed' or oplan_parent.status is null or oplan_parent.type='STCK') and oplan_parent.item_id = cluster_keys.name ) """) cursor.execute(""" delete from operationplan using cluster_keys where (status='proposed' or status is null or type='STCK') and item_id = cluster_keys.name """) cursor.execute("truncate table cluster_keys") for i in frepple.resources(): if i.cluster == cluster: cursor.execute( ("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode("UTF8"))) cursor.execute(""" delete from out_problem where entity = 'demand' and owner in ( select demand.name from demand inner join cluster_keys on cluster_keys.name = demand.item_id ) """) cursor.execute( "delete from operationplanresource using cluster_keys where resource_id = cluster_keys.name" ) cursor.execute( "delete from out_resourceplan using cluster_keys where resource = cluster_keys.name" ) cursor.execute( "delete from out_problem using cluster_keys where entity = 'capacity' and owner = cluster_keys.name" ) cursor.execute("truncate table cluster_keys") for i in frepple.operations(): if i.cluster == cluster: cursor.execute( ("insert into cluster_keys (name) values (%s)" % adapt(i.name).getquoted().decode("UTF8"))) cursor.execute("""" delete from out_problem using cluster_keys where entity = 'operation' and owner = cluster_keys.name """) cursor.execute(""" delete from operationplan using cluster_keys where (status='proposed' or status is null) and operationplan.name = cluster_keys.name """) cursor.execute("drop table cluster_keys")
def adapt_set(s): return adapt(tuple(s))
def __adapt_entity(entity): return AsIs( "ROW(%s, %s, %s)::entity_sync" % (adapt(entity.type), adapt(entity.local_id), adapt(entity.remote_id)))
def quoted(o): a = adapt(o) if hasattr(a, 'prepare'): a.prepare(self.conn) return a.getquoted().decode('utf-8')
if not (check): sql = """UPDATE "Exten" SET "DND" = %s where ("BIND" = %s) and ("Exten" = %s);""" % ( adapt(enable), adapt(BIND), adapt(cid)) # syslog.syslog(sql) try: cur.execute(sql) connect.commit() res = cur.rowcount cur.close() return (res) except psycopg2.Error, e: syslog.syslog("SQL ERROR: " + str(e.pgerror)) else: sql = """SELECT "DND" FROM "Exten" where ("BIND" = %s) and ("Exten" = %s);""" % ( adapt(BIND), adapt(cid)) try: cur.execute(sql) if (cur.rowcount < 1): return -1 row = cur.fetchone() syslog.syslog("%s" % row['DND']) cur.close() if row['DND']: return 1 else: return 0 except psycopg2.Error, e: syslog.syslog("SQL ERROR: " + str(e.pgerror))