def parse(self, s, cur, _bsdec=_re.compile(r"\\(.)")): """Parse an hstore representation in a Python string. The hstore is represented as something like:: "a"=>"1", "b"=>"2" with backslash-escaped strings. """ if s is None: return None rv = {} start = 0 for m in self._re_hstore.finditer(s): if m is None or m.start() != start: raise psycopg2.InterfaceError( "error parsing hstore pair at char %d" % start) k = _bsdec.sub(r'\1', m.group(1)) v = m.group(2) if v is not None: v = _bsdec.sub(r'\1', v) rv[k] = v start = m.end() if start < len(s): raise psycopg2.InterfaceError( "error parsing hstore: unparsed data after char %d" % start) return rv
def get_all_notes(connection=None): if not connection: global myConnection connection = myConnection if connection == None: raise psycopg2.InterfaceError("No connection made") notes = {} with connection.cursor() as cur: # cur.execute(""" # SELECT note.id, # note.name, # note.description, # link.url, # array_agg(tag.tagname), # array_agg(category.name) # FROM note LEFT JOIN tag ON tag.note = note.id # LEFT JOIN category ON tag.category = category.id # LEFT JOIN link ON note.id = link.note # GROUP BY note.id""") cur.execute(""" SELECT note.id, note.name, note.description, array_agg(link.url), array_agg(tag.tagname) FROM note LEFT JOIN tag ON tag.note = note.id LEFT JOIN link ON note.id = link.note GROUP BY note.id, link.url""") return fetched_to_notes(cur.fetchall())
def execute(self, sql, *params): if sql.startswith('blabla'): raise psycopg2.OperationalError() elif sql.startswith('InterfaceError'): raise psycopg2.InterfaceError() elif sql.startswith('SELECT slot_name'): self.results = [('blabla',), ('foobar',)] elif sql.startswith('SELECT pg_current_xlog_location()'): self.results = [(0,)] elif sql.startswith('SELECT pg_is_in_recovery(), %s'): if params[0][0] == 1: raise psycopg2.OperationalError() elif params[0][0] == 2: self.results = [(True, -1)] else: self.results = [(False, 0)] elif sql.startswith('SELECT CASE WHEN pg_is_in_recovery()'): self.results = [(0,)] elif sql.startswith('SELECT pg_is_in_recovery()'): self.results = [(False, )] elif sql.startswith('SELECT to_char(pg_postmaster_start_time'): self.results = [('', True, '', '', '', False)] else: self.results = [( None, None, None, None, None, None, None, None, None, None, )]
def insert_one(self, table_name: str, row: dict, retcol: str): """ inserts a single row into the specified table and returns the value of the specified column. row - a dict containing all row values to insert returns: new id """ if not self.__conn or not row: raise psycopg2.InterfaceError("null connection") try: columns = "(" + ",".join(row.keys()) + ")" holders = "(" + ",".join(["%s"] * len(row.keys())) + ")" fillers = tuple(row.values()) query = "insert into {table_name} {columnnames} values {values} returning {retcol}".format( **{ "table_name": table_name, "columnnames": columns, "values": holders, "retcol": retcol }) with self.__conn.cursor() as curs: curs.execute(query, fillers) return curs.fetchone()[0] except Exception as e: tb.print_exc() raise (e)
def insert(self, table_name: str, rows: list) -> int: """ inserts the given data into the specified table. An individual insert statement is generated per row. The query is handled as a prepared statement so multi-inserts are more efficient. table_name -- rows -- list of dicts. each dict representing a row to be inserted returns: the number of rows inserted """ n_inserted = 0 if not self.__conn or not rows: raise psycopg2.InterfaceError("null connection") try: for row in rows: columns = "(" + ",".join( row.keys()) + ")" # "(col1, col2, col3...)" params = ",".join(["%s"] * len(row.keys())) # "%s, %s, %s..." query = "insert into {table_name} {columns} select {params}".format( **{ "table_name": table_name, "columns": columns, "params": params, }) n_inserted += self.execute_write_query(query, tuple(row.values())) return n_inserted except Exception as e: tb.print_exc() raise (e)
def _cast_edge(val, cur): if val is None: return None edge_match = _edge_matcher.match(val) if edge_match is None: return ValueError label = edge_match.group(1) ID = edge_match.group(2) sid = edge_match.group(3) tid = edge_match.group(4) properties = json.loads(edge_match.group(5)) try: if isinstance(cur, Graph): edge = GraphEdge(ID, cur, sid, tid, label, properties) else: edge = { 'id': ID, 'sid': sid, 'tid': tid, 'label': label, 'properties': properties } except: return psycopg2.InterfaceError('Bad edge representation: %s' % val) return edge
def _cast_path(value, cur): if value is None: return None try: p = Path(value) except: return psycopg2.InterfaceError("bad path representation: %s" % value) return p
def _cast_vertex(value, cur): if value is None: return None try: v = Vertex(value) except: return psycopg2.InterfaceError("bad vertex representation: %s" % value) return v
def process(value): if value is None: return None m = re.match(r"\(([^)]+),([^)]+)\)", value) if m: return (float(m.group(1)), float(m.group(2))) else: raise psycopg2.InterfaceError("bad point representation: %r" % value)
def _cast_edge(value, cur): if value is None: return None try: e = Edge(value) except: return psycopg2.InterfaceError("bad edge representation: %s" % value) return e
def __init__(self, conn): """ initializing the class with connection parameters and establishing a connection if possible """ self.__conn = conn try: self.__conn = psycopg2.connect(self.__conn) except: raise psycopg2.InterfaceError('Could not connect to database!')
def cast_point(value, cur): if value is None: return None # Convert from (f1, f2) syntax using a regular expression. m = re.match(r"\(([^)]+),([^)]+)\)", value) if m: return Point.from_coordinates(float(m.group(1)), float(m.group(2))) else: raise psycopg2.InterfaceError("bad point representation: %r" % value)
def tokenize(self, s): rv = [] for m in self._re_tokenize.finditer(s): if m is None: raise psycopg2.InterfaceError("can't parse type: %r" % s) if m.group(1) is not None: rv.append(None) elif m.group(2) is not None: rv.append(self._re_undouble.sub(r"\1", m.group(2))) else: rv.append(m.group(3)) return rv
def edge_array_parser(value, cur): if value is None: return None try: edge_array_string = _e_array_matcher.findall(value[1:-1]) edge_array = map(edge_parser, edge_array_string) return [a for a in edge_array] except Exception as e: return psycopg2.InterfaceError("Bad path representation: {}".format(value), e)
def from_string(self, s): rv = [] for m in self._re_tokenize.finditer(s): if m is None: raise psycopg2.InterfaceError("bad pgtype representation: %r" % s) if m.group(1) is not None: rv.append(None) elif m.group(2) is not None: rv.append(self._re_undouble.sub(r"\1", m.group(2))) else: rv.append(m.group(3)) self.from_tuple(tuple(rv))
def close(self): """Explicitly close the connection and remove it from the connection pool if pooling is enabled. If the connection is already closed :raises: psycopg2.InterfaceError """ if not self._conn: raise psycopg2.InterfaceError('Connection not open') LOGGER.info('Closing connection %r in %s', self._conn, self.pid) self._pool_manager.free(self.pid, self._conn) self._pool_manager.remove_connection(self.pid, self._conn) # Un-assign the connection and cursor self._conn, self._cursor = None, None
def generate_query(self, query: str, args: tuple = ()) -> str: """ return the query string, with the given parameters, that would be executed against the database. nothing is executed. """ if not self.__conn: raise psycopg2.InterfaceError("null connection") try: with self.__conn.cursor() as curs: query_string = curs.mogrify(query, args) except Exception as e: tb.print_exc() raise (e) return query_string.decode("utf-8")
def update_exact(self, table_name: str, conditions: dict, newvalues: dict) -> int: """ updates rows that match the given equality conditions with new values. both conditions and newvalues are dictionaries. kv pairs in conditions are translated into 'column = value' conditions and kv pairs in newvalues are used in the SET clause returns the number of rows updated """ if not self.__conn: raise psycopg2.InterfaceError("null connection") try: pass except Exception as e: tb.print_exc() raise (e)
def _cast_vertex(val, cur): if val is None: return None vertex_match = _vertex_matcher.match(val) if vertex_match is None: return ValueError label = vertex_match.group(1) ID = vertex_match.group(2) properties = json.loads(vertex_match.group(3)) try: if isinstance(cur, Graph): vertex = GraphVertex(ID, cur, label, properties) else: vertex = {'id': ID, 'label': label, 'properties': properties} except: return psycopg2.InterfaceError('Bad vertex representation: %s' % val) return vertex
def cast_point(value, cur): """ Postgres Databases have a neat datatype called a point, these are two dimenional data points in the form (float x, float y). Natively, psycopg2 doesn't support this in python, but with this function, we can implement the feature. * Note: Not sure if this project will require this functionality. """ if value is None: return None # Convert from (f1, f2) syntax using a regular expression. m = re.match(r"\(([^)]+),([^)]+)\)", value) if m: return (float(m.group(1)), float(m.group(2))) else: raise psycopg2.InterfaceError("bad point representation: %r" % value)
def vertex_parser(value, cur=None): if value is None: return None try: match = _v_matcher.match(value) if not match: return {} result = {} result["label"] = match.group(1) result["vid"] = "{}.{}".format(match.group(2), match.group(3)) result.update(json.loads(match.group(4))) return result except Exception as e: return psycopg2.InterfaceError("Bad vertex representation: {}".format(value))
def path_parser(value, cur=None): def _tokenize(value_to_tokenize): i = 0 s = 0 depth = 0 in_gid = False character_list = [] clean_value = value_to_tokenize[1:-1] # remove '[' and ']' for char in clean_value: if char == '{': depth += 1 elif char == '}': depth -= 1 elif depth == 0 and char == '[': # for GID in_gid = True elif in_gid and char == ']': in_gid = False elif depth == 0 and not in_gid and char == ',': character_list.append(clean_value[s:i]) s = i + 1 if depth < 0: raise ValueError i += 1 character_list.append(clean_value[s:i]) return character_list if value is None: return None try: objects_in_path = _tokenize(value) if not objects_in_path: return {} result = {} result["vertices"] = map(vertex_parser, objects_in_path[0::2]) result["edges"] = map(edge_parser, objects_in_path[1::2]) return result except Exception as e: return psycopg2.InterfaceError("Bad path representation: {}".format(value), e)
def insertObs(self, theObservation): if self.theConn is not None: with self.theConn as conn: with self.theConn.cursor() as c: dic = theObservation.to_dict() dic['esDocId'] = theObservation.meta.id if dic['temperature'] == -999: dic['temperature'] = None if dic['pressure'] == -999: dic['pressure'] = None if dic['humidity'] == -999: dic['humidity'] = None c.execute(_INSERT_OBS, dic) conn.commit() WeatherDB._logger.logMessage( level="DEBUG", message="Inserted row: {0}".format(theObservation.tsa)) else: raise pg.InterfaceError()
def execute_read_query(self, query: str, args: tuple = ()) -> (list, list): """ results are returned as a list of dicts""" if not self.__conn: raise psycopg2.InterfaceError("null connection") rows = None try: with self.__conn.cursor( cursor_factory=psycopg2.extras.RealDictCursor) as curs: curs.execute(query, args) rows = curs.fetchall() except Exception as e: tb.print_exc() raise (e) if len(rows) == 0: return [] return rows
def execute_write_query(self, query: str, args: tuple = ()) -> int: """ perform a write query query -- the query string with optional %s placeholders args -- optional tuple of values for %s placeholders returns number of rows modified """ if not self.__conn: raise psycopg2.InterfaceError("null connection") try: with self.__conn.cursor() as curs: curs.execute(query, args) except Exception as e: tb.print_exc() raise (e) return curs.rowcount
def edge_parser(value, cur=None): if value is None: return None try: match = _e_matcher.match(value) if not match: return {} result = {} result["label"] = match.group(1) result["eid"] = "{}.{}".format(match.group(2), match.group(3)) result["source_vid"] = "{}.{}".format(match.group(4), match.group(5)) result["destination_vid"] = "{}.{}".format(match.group(6), match.group(7)) result.update(json.loads(match.group(8))) return result except Exception as e: return psycopg2.InterfaceError("Bad edge representation: {}".format(value), e)
def error_once(*a, **k): if calls[0] > 0: return 'foo' calls[0] += 1 raise psycopg2.InterfaceError('herp derp')
def cursor(self): raise psycopg2.InterfaceError("I'm broken!")
def exc(*args, **kwargs): raise psycopg2.InterfaceError()
def fail(*args, **kw): raise psycopg2.InterfaceError('boom')