def __init__(self, host, port, database, user, password): Repository.__init__(self, host, port, database, user, password) self.__address_repo = AddressRepository(host, port, database, user, password)
class EventRepository(Repository): __insert_sql = ( """insert into event (eventname, addressid, startdate, enddate, description) VALUES (%s, %s, %s, %s, %s)""" ) __select_by_id_sql = "select *, ST_AsGeoJSON(a.location) as location_json from event e join address a on e.addressid = a.addressid where eventid = %s" __select_by_name_sql = "select *, ST_AsGeoJSON(a.location) as location_json from event e join address a on e.addressid = a.addressid where eventname = %s" __select_with_people_and_location_sql = """select * from event e join address a on a.address_id = e.address_id join eventdriver ed on ed.event_id = e.event_id join user ud on ed.driver_id = ud.user_id join event_passenger ep on e.event_id = ep.event_id join user up on ep.passenger_id = up.user_id where e.event_id = %s""" __update_sql = """update event set name = %s , addressid = %s , startdate = %s , enddate = %s , description = %s where event_id = %s""" __delete_sql = """delete from event where event_id = %s""" __address_repo = None def __init__(self, host, port, database, user, password): Repository.__init__(self, host, port, database, user, password) self.__address_repo = AddressRepository(host, port, database, user, password) def save(self, event): if event.event_id is None: self.create(event) else: self.update(event) def create(self, event): with self._get_connection() as conn: with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur: cur.execute( self.__insert_sql, [event.event_name, event.address.addressid, event.start_date, event.end_date, event.description], ) # TODO: persist address, drivers and passengers # TODO: wrap in transaction if not event.address.address_id is None: self.__address_repo.create(event.address) if not event.drivers is None or event.drivers.len() > 0: # persist driver associations pass def update(self, event): with self._get_connection() as conn: with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur: cur.execute( self.__update_sql, [event.name, event.address.addressid, event.start_date, event.end_date, event.description], ) # TODO: persist address, drivers and passengers # TODO: wrap in transaction if not event.address.address_id is None: self.__address_repo.update(event.address) if not event.drivers is None or event.drivers.len() > 0: # persist driver associations pass if not event.passengers is None or event.passengers.len() > 0: # persist passenger associations pass def get_by_id(self, event_id): conn = None cur = None event = None with self._get_connection() as conn: with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur: cur.execute(self.__select_by_id_sql, [event_id]) records = cur.fetchone() if records is not None and records["eventid"] is not None: event = Event(records) if records.get("addressid") is not None: address = Address(records) event.address = address return event def get_by_name(self, event_name): conn = None cur = None event = None with self._get_connection() as conn: with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur: cur.execute(self.__select_by_name_sql, [event_name]) records = cur.fetchone() if records is not None and records["eventid"] is not None: event = Event(records) if records.get("addressid") is not None: address = Address(records) event.address = address return event def delete(self, event_id): with self._get_connection() as conn: with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur: cur.execute(self.__delete_sql, [event_id])