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')
