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)