def cursor(self, name=None):
if name is None:
return _connection.cursor(self, cursor_factory=RealDictCursor)
else:
return _connection.cursor(self,
name,
cursor_factory=RealDictCursor)
def cursor(self, name=None):
self._check()
if name is None:
return _connection.cursor(self,
cursor_factory=MinTimeLoggingCursor)
else:
return _connection.cursor(self,
name,
cursor_factory=MinTimeLoggingCursor)
def poll_times_df(connection: Maybe[connection] = Nothing(), ) -> pd.DataFrame:
with connection.or_else_lazy(default_connection) as connection:
with connection.cursor() as cursor:
cursor.execute(
"select distinct poll_time from passage_responses order by poll_time asc"
)
return pd.DataFrame(cursor.fetchall(), columns=["poll_time"])
def main(dataset_location: str, connection: connection):
"""
Function which takes the connection to the Postgres database as input
and after having produced the metrics, it uploades them in the following format:
"""
if not connection:
print(
'It is not possible to establish a connection to the postgres database, therefore the process could not continue.'
)
exit(1)
# Get the cursor connection
cursor = connection.cursor()
# create the table
create_table(connection, cursor)
# compute the metrics
metrics: list[Metric] = compute_metrics(dataset_location)
# upload the metrics
upload_metrics(connection, cursor, metrics)
# Create index
create_index(connection, cursor)
# close the cursor
cursor.close()
# close the connection
connection.close()
def snapshots(
connection: Optional[connection] = None,
r: Optional[m.RouteId] = None,
d: Optional[date] = None,
date_span: Optional[Tuple[date, date]] = None,
) -> List[BusSnapshot]:
"""Gets entries from the database, optionally filtering by route or date."""
if connection is None:
connection = default_connection()
with connection.cursor() as cu:
query = b"select * from passage_responses"
conditions: List[bytes] = []
if r is not None:
conditions.append(cu.mogrify(" route_id = %s", (r.raw, )))
if d is not None or date_span is not None:
if date_span is not None:
dt1, dt2 = day_span(list(date_span))
elif d is not None:
dt1, dt2 = day_span([d])
conditions.append(
cu.mogrify(" last_modified between %s and %s", (dt1, dt2)))
if conditions != []:
query += b" where" + b" and".join(conditions)
cu.execute(query)
return [BusSnapshot.from_db_row(row) for row in cu.fetchall()]
def execute(connection: connection, statement: str) -> Optional[List[Tuple[str, ...]]]:
"""Execute PGSQL statement and fetches the statement response.
Parameters
----------
connection: psycopg2.extensions.connection
Active connection to a PostGreSQL database.
statement: str
PGSQL statement to run against the database.
Returns
-------
response: list or None
List of tuples, where each tuple represents a formatted line of response from the database engine, where
each tuple item roughly corresponds to a column. For instance, while a raw SELECT response might include
the table headers, psycopg2 returns only the rows that matched. If no response was given, None is returned.
"""
response = list() # type: List
# See the following link for reasoning behind both with statements:
# http://initd.org/psycopg/docs/usage.html#with-statement
#
# Additionally, the with statement makes this library safer to use with
# higher-level libraries (e.g. SQLAlchemy) that don't inherently respect
# PostGreSQL's autocommit isolation-level, since the transaction is
# properly completed for each statement.
with connection:
with connection.cursor(cursor_factory=Psycopg2Cursor) as cursor:
cursor.execute(statement)
connection.commit()
# Get response
try:
response = cursor.fetchall()
if not response:
# Empty response list
log('<No Response>', logger_name=_LOGGER_NAME)
return None
except ProgrammingError as e:
if e.args and e.args[0] == 'no results to fetch':
# No response available (i.e. no response given)
log('<No Response>', logger_name=_LOGGER_NAME)
return None
# Some other programming error; re-raise
raise e
log('Response', logger_name=_LOGGER_NAME)
log('--------', logger_name=_LOGGER_NAME)
for line in response:
log(str(line), logger_name=_LOGGER_NAME)
return response
def cursor (self, fetchsize = None):
"Return a psycopg2 Cursor object for operating on the connection."
if fetchsize:
# Tell psycopg2 to create a server-side cursor so that it will
# return the results in batches of the indicated size instead of
# retrieving all results on the first fetch.
#name = some unique, valid cursor name
#curs = pgConnection.cursor (self, name)
#curs.itersize = fetchsize
raise NotImplementedError ('Need algorithm for unique cursor name.')
else:
curs = pgConnection.cursor (self)
return curs
def store_trip(p: Passage,
poll_time: dt.datetime,
connection: Optional[connection] = None) -> Optional[Exception]:
if connection is None:
try:
connection = default_connection()
except Exception as e:
return e
with connection:
try:
with connection.cursor() as cursor:
cursor.execute(
"""
insert into passage_responses(
last_modified, trip_id, route_id, vehicle_id, pattern_id,
latitude, longitude, bearing, is_accessible, has_bike_rack,
direction, congestion_level, accuracy_level, status, category,
poll_time
) values (
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s)
""",
[
p.last_modified,
p.trip,
p.route,
p.vehicle,
p.pattern,
p.latitude,
p.longitude,
p.bearing,
p.is_accessible,
p.has_bike_rack,
p.direction,
p.congestion,
p.accuracy,
p.status,
p.category,
poll_time,
],
)
return None
except Exception as e:
return e
def main(connection: connection):
"""
Function which deletes the current table
"""
if not connection:
print(
'It is not possible to establish a connection to the postgres database, therefore the process could not continue.'
)
exit(1)
# Get the cursor connection
cursor = connection.cursor()
# create the table
drop_table(connection, cursor)
# close the cursor
cursor.close()
# close the connection
connection.close()
def cursor(self):
# DictCursor allows for both integer and key based row access
return _2connection.cursor(self, cursor_factory=DictCursor)
def cursor(self):
"""cursor() -> new psycopg 1.1.x compatible cursor object"""
return _2connection.cursor(self, cursor_factory=cursor)
def cursor(self, *args, **kwargs):
# type: (*Any, **Any) -> TimeTrackingCursor
kwargs.setdefault('cursor_factory', TimeTrackingCursor)
return connection.cursor(self, *args, **kwargs)
def cursor(self, name=None):
if name is None:
return _connection.cursor(self, cursor_factory=DictCursor)
else:
return _connection.cursor(self, name, cursor_factory=DictCursor)
def cursor(self, name=None):
self._check()
if name is None:
return _connection.cursor(self, cursor_factory=MinTimeLoggingCursor)
else:
return _connection.cursor(self, name, cursor_factory=MinTimeLoggingCursor)
def cursor(self):
return _connection.cursor(self, cursor_factory=DictCursor)
def cursor(self):
self._check()
return _connection.cursor(self, cursor_factory=MinTimeLoggingCursor)
def cursor(self, *args, **kwargs):
kwargs['cursor_factory'] = NamedTupleCursor
return _connection.cursor(self, *args, **kwargs)
def cursor(self, *args, **kwargs):
kwargs.setdefault('cursor_factory', RealDictCursor)
return _connection.cursor(self, *args, **kwargs)
def cursor(self, *args, **kwargs):
kwargs.setdefault('cursor_factory', NamedTupleCursor)
return _connection.cursor(self, *args, **kwargs)
def cursor(self):
self._check()
return _connection.cursor(self, cursor_factory=MinTimeLoggingCursor)
def cursor(self):
"""cursor() -> new psycopg 1.1.x compatible cursor object"""
return _2connection.cursor(self, cursor_factory=cursor)
def cursor(self, *args, **kwargs):
self._check()
kwargs.setdefault('cursor_factory', LoggingCursor)
return _connection.cursor(self, *args, **kwargs)
def cursor(self, *args, **kwargs):
kwargs['cursor_factory'] = NamedTupleCursor
return _connection.cursor(self, *args, **kwargs)
def cursor(self):
return _2connection.cursor(self, cursor_factory=DictCursor)
def cursor(self, *args: Any, **kwargs: Any) -> TimeTrackingCursor:
kwargs.setdefault('cursor_factory', TimeTrackingCursor)
return connection.cursor(self, *args, **kwargs)
def cursor(self):
return _connection.cursor(self, cursor_factory=RealDictCursor)
