def test_close(self): _ = database_connection() self.proj.close() with self.assertRaises(FileExistsError): _ = database_connection()
def delete(self): conn = database_connection() curr = conn.cursor() curr.execute( f'DELETE FROM link_types where link_type_id="{self.link_type_id}"') conn.commit() del self
def renumber(self, new_id: int): """Renumbers the node in the network Raises a warning if another node already exists with this node_id Args: *new_id* (:obj:`int`): New node_id """ new_id = int(new_id) if new_id == self.node_id: warn("This is already the node number") return conn = database_connection() curr = conn.cursor() curr.execute("BEGIN;") curr.execute("Update Nodes set node_id=? where node_id=?", [new_id, self.node_id]) curr.execute("Update Links set a_node=? where a_node=?", [new_id, self.node_id]) curr.execute("Update Links set b_node=? where b_node=?", [new_id, self.node_id]) curr.execute("COMMIT;") conn.close() logger.info(f"Node {self.node_id} was renumbered to {new_id}") self.__dict__["node_id"] = new_id self.__original__["node_id"] = new_id
def save(self): """Saves/Updates the zone data to the database""" if self.zone_id != self.__original__["zone_id"]: raise ValueError("One cannot change the zone_id") conn = database_connection() curr = conn.cursor() curr.execute( f'select count(*) from zones where zone_id="{self.zone_id}"') if curr.fetchone()[0] == 0: data = [self.zone_id, self.geometry.wkb] curr.execute( "Insert into zones (zone_id, geometry) values(?, ST_Multi(GeomFromWKB(?, 4326)))", data) for key, value in self.__dict__.items(): if key != "zone_id" and key in self.__original__: v_old = self.__original__.get(key, None) if value != v_old and value is not None: self.__original__[key] = value if key == "geometry": sql = "update 'zones' set geometry=ST_Multi(GeomFromWKB(?, 4326)) where zone_id=?" curr.execute(sql, [value.wkb, self.zone_id]) else: curr.execute( f"update 'zones' set '{key}'=? where zone_id=?", [value, self.zone_id]) conn.commit() conn.close()
def __init__(self, mode_id: str) -> None: if mode_id is None: raise ValueError('Mode IDs cannot be None') if len(mode_id) != 1 or mode_id not in string.ascii_letters: raise ValueError('Mode IDs must be a single ascii character') conn = database_connection() curr = conn.cursor() curr.execute('pragma table_info(modes)') table_struct = curr.fetchall() self.__fields = [x[1] for x in table_struct] self.__original__ = {} # data for the mode curr.execute(f"select * from 'modes' where mode_id='{mode_id}'") dt = curr.fetchone() if dt is None: # if the mode was not found, we return a new one for k in self.__fields: self.__dict__[k] = None self.__original__[k] = None self.__dict__['mode_id'] = mode_id self.__original__['mode_id'] = mode_id else: for k, v in zip(self.__fields, dt): self.__dict__[k] = v self.__original__[k] = v conn.close()
def save(self): if self.mode_id not in self.__alowed_characters: raise ValueError('mode_id needs to be a ascii letter') for letter in self.mode_name: if letter not in self.__alowed_characters: raise ValueError('mode_name can only contain letters and "_"') conn = database_connection() curr = conn.cursor() curr.execute( f'select count(*) from modes where mode_id="{self.mode_id}"') if curr.fetchone()[0] == 0: raise ValueError( "Mode does not exist in the model. You need to explicitly add it" ) curr.execute('pragma table_info(modes)') table_struct = [x[1] for x in curr.fetchall()] for key, value in self.__dict__.items(): if key in table_struct and key != 'mode_id': v_old = self.__original__.get(key, None) if value != v_old and value is not None: self.__original__[key] = value curr.execute( f"update 'modes' set '{key}'=? where mode_id='{self.mode_id}'", [value]) conn.commit() conn.close()
def __run_query_fetch_all(self, qry: str): conn = database_connection() curr = conn.cursor() curr.execute(qry) dt = curr.fetchall() conn.close() return dt
def delete(self): """Deletes link from database""" conn = database_connection() curr = conn.cursor() curr.execute(f'DELETE FROM links where link_id="{self.link_id}"') conn.commit() self.__stil_exists = False
def open(self, project_path: str) -> None: """ Loads project from disk Args: *project_path* (:obj:`str`): Full path to the project data folder. If the project inside does not exist, it will fail. """ if self.__other_project_still_open(): raise Exception("You already have a project open. Close that project before opening another one") file_name = os.path.join(project_path, "project_database.sqlite") if not os.path.isfile(file_name): raise FileNotFoundError("Model does not exist. Check your path and try again") self.project_base_path = project_path self.path_to_file = file_name self.source = self.path_to_file os.environ[ENVIRON_VAR] = self.project_base_path self.conn = database_connection() self.__load_objects() self.__set_logging_path() logger.info(f"Opened project on {self.project_base_path}") self.logger = logger clean()
def save_results(self, table_name: str) -> None: """Saves the assignment results to results_database.sqlite Method fails if table exists Args: table_name (:obj:`str`): Name of the table to hold this assignment result """ df = self.results() conn = sqlite3.connect( path.join(environ[ENVIRON_VAR], "results_database.sqlite")) df.to_sql(table_name, conn) conn.close() conn = database_connection() report = { "convergence": str(self.assignment.convergence_report), "setup": str(self.info()) } data = [ table_name, "traffic assignment", self.procedure_id, str(report), self.procedure_date, self.description ] conn.execute( """Insert into results(table_name, procedure, procedure_id, procedure_report, timestamp, description) Values(?,?,?,?,?,?)""", data, ) conn.commit() conn.close()
def delete(self): """Removes the zone from the database""" conn = database_connection() curr = conn.cursor() curr.execute(f'DELETE FROM zones where zone_id="{self.zone_id}"') conn.commit() self.__zoning._remove_zone(self.zone_id) del self
def __init__(self, network): self.network = network self.__all_types = [] self.conn = database_connection() self.__curr = self.conn.cursor() self.__fields = [] if self.__has_zoning(): self.__load()
def __run_query_commit(self, qry: str, values=None) -> None: conn = database_connection() if values is None: conn.execute(qry) else: conn.execute(qry, values) conn.commit() conn.close()
def _read_assignment_results(self) -> pd.DataFrame: conn = database_connection() results_df = pd.read_sql("SELECT * FROM 'results'", conn) conn.close() res = results_df.loc[results_df.table_name == self.table_name] assert len( res ) == 1, f"Found {len(res)} assignment result with this table name, need exactly one" return res
def __setattr__(self, instance, value) -> None: if instance == 'name': value = str(value).lower() conn = database_connection() curr = conn.cursor() curr.execute('Select count(*) from matrices where LOWER(name)=?', [value]) if sum(curr.fetchone()) > 0: raise ValueError('Another matrix with this name already exists') conn.close() elif instance == 'file_name': conn = database_connection() curr = conn.cursor() curr.execute('Select count(*) from matrices where LOWER(file_name)=?', [str(value).lower()]) if sum(curr.fetchone()) > 0: raise ValueError('There is another matrix record for this file') self.__dict__[instance] = value if instance in ['file_name', 'cores']: self.__dict__['cores'] = self.__get_cores()
def clean(): # Since we cannot decide the order of trigger execution in SQLITE, we make sure to remove any # extraneous nodes at a few key moments (i.e. opening and closing the model) conn = database_connection() sqls = ['''DELETE from Nodes where is_centroid=0 and (SELECT count(*) FROM links WHERE a_node = node_id OR b_node = node_id) = 0;'''] for sql in sqls: conn.execute(sql) conn.commit() conn.close()
def delete(self): """Deletes this matrix record and the underlying data from disk""" conn = database_connection() curr = conn.cursor() curr.execute('DELETE FROM matrices where name=?', [self.name]) conn.commit() if isfile(join(self.fldr, self.file_name)): try: unlink(join(self.fldr, self.file_name)) except Exception as e: logger.error(f'Could not remove matrix from disk: {e.args}') conn.close() self._exists = False
def test_connection_with_new_project(self): temp_proj_folder = os.path.join(tempfile.gettempdir(), uuid.uuid4().hex) proj = Project() proj.new(temp_proj_folder) proj.close() proj = Project() proj.open(temp_proj_folder) conn = database_connection() cursor = conn.cursor() cursor.execute('select count(*) from links') self.assertEqual(cursor.fetchone()[0], 0, "Returned more links thant it should have") proj.close()
def save(self): """Saves matrix record to the project database""" conn = database_connection() curr = conn.cursor() curr.execute('select count(*) from matrices where name=?', [self.name]) if curr.fetchone()[0] == 0: data = [str(self.name), str(self.file_name), int(self.cores)] curr.execute('Insert into matrices (name, file_name, cores) values(?,?,?)', data) for key, value in self.__dict__.items(): if key != 'name' and key in self.__original__: v_old = self.__original__.get(key, None) if value != v_old and value: self.__original__[key] = value curr.execute(f"update matrices set '{key}'=? where name=?", [value, self.name]) conn.commit() conn.close()
def __init__(self): self.conn = database_connection() self.curr = self.conn.cursor() self.fldr = os.path.join(os.environ.get(ENVIRON_VAR), "matrices") tl = TableLoader() matrices_list = tl.load_table(self.curr, "matrices") self.__fields = [x for x in tl.fields] if matrices_list: self.__properties = list(matrices_list[0].keys()) for lt in matrices_list: if lt["name"] in self.__items: if not self.__items[lt["name"]]._exists: del self.__items[lt["name"]] if lt["name"] not in self.__items: if isfile(join(self.fldr, lt["file_name"])): lt["fldr"] = self.fldr self.__items[lt["name"].lower()] = MatrixRecord(lt)
def __create_empty_project(self): # We create the project folder and create the base file os.mkdir(self.project_base_path) shutil.copyfile(spatialite_database, self.path_to_file) self.conn = database_connection() # Write parameters to the project folder p = Parameters() p.parameters["system"]["logging_directory"] = self.project_base_path p.write_back() _ = StartsLogging() # Create actual tables cursor = self.conn.cursor() cursor.execute("PRAGMA foreign_keys = ON;") self.conn.commit() initialize_tables(self.conn)
def save(self): """Saves link to database""" conn = database_connection() curr = conn.cursor() if self.__new: data, sql = self._save_new_with_geometry() else: data, sql = self.__save_existing_link() if data: curr.execute(sql, data) conn.commit() conn.close() self.__new = False for key in self.__original__.keys(): self.__original__[key] = self.__dict__[key]
def save(self): """Saves node to database""" conn = database_connection() curr = conn.cursor() if self.node_id != self.__original__["node_id"]: raise ValueError("One cannot change the node_id") if self.__new: data, sql = self._save_new_with_geometry() else: data, sql = self.__save_existing_node() if data: curr.execute(sql, data) conn.commit() conn.close() self.__new = False
def assign_matrix(self, matrix: AequilibraeMatrix, result_name: str): conn = database_connection() sql = f"select link_id, direction, a_node, b_node, distance, 1 capacity from {DELAUNAY_TABLE}" df = pd.read_sql(sql, conn) centroids = np.array(np.unique(np.hstack((df.a_node.values, df.b_node.values))), int) g = Graph() g.mode = 'delaunay' g.network = df g.prepare_graph(centroids) g.set_blocked_centroid_flows(True) tc = TrafficClass('delaunay', g, matrix) ta = TrafficAssignment() ta.set_classes([tc]) ta.set_time_field('distance') ta.set_capacity_field('capacity') ta.set_vdf('BPR') ta.set_vdf_parameters({"alpha": 0, "beta": 1.0}) ta.set_algorithm('all-or-nothing') ta.execute() report = {"setup": str(ta.info())} data = [result_name, "Delaunay assignment", self.procedure_id, str(report), ta.procedure_date, ''] conn.execute("""Insert into results(table_name, procedure, procedure_id, procedure_report, timestamp, description) Values(?,?,?,?,?,?)""", data) conn.commit() conn.close() cols = [] for x in matrix.view_names: cols.extend([f'{x}_ab', f'{x}_ba', f'{x}_tot']) df = ta.results()[cols] conn = sqlite3.connect(join(environ[ENVIRON_VAR], "results_database.sqlite")) df.to_sql(result_name, conn) conn.close()
def save(self): conn = database_connection() curr = conn.cursor() curr.execute( f'select count(*) from link_types where link_type_id="{self.link_type_id}"' ) if curr.fetchone()[0] == 0: data = [self.link_type_id, self.link_type] curr.execute( 'Insert into link_types (link_type_id, link_type) values(?,?)', data) for key, value in self.__dict__.items(): if key != 'link_type_id' and key in self.__original__: v_old = self.__original__.get(key, None) if value != v_old and value is not None: self.__original__[key] = value curr.execute( f"update 'link_types' set '{key}'=? where link_type_id='{self.link_type_id}'", [value]) conn.commit() conn.close()
def create_network(self, source='zones', overwrite=False): """Creates a delaunay network based on the existing model Args: *source* (:obj:`str`, `Optional`): Source of the centroids/zones. Defaults to *zones*, but can be *network* *overwrite path* (:obj:`bool`, `Optional`): Whether we should overwrite am existing Delaunay Network. Defaults to False """ if source not in ['zones', 'network']: raise ValueError("Source must be 'zones' or 'network'") conn = database_connection() tables = pd.read_sql("SELECT name FROM sqlite_master WHERE type ='table'", conn) if DELAUNAY_TABLE in tables.name.values: if not overwrite: raise ValueError("Delaunay network already exist. Use the overwrite flag to re-run it") conn.execute(f'DROP TABLE {DELAUNAY_TABLE}') conn.execute('delete from geometry_columns where f_table_name=?', [DELAUNAY_TABLE]) conn.commit() zone_sql = 'select zone_id node_id, X(st_centroid(geometry)) x, Y(st_centroid(geometry)) y from zones' network_sql = 'select node_id, X(geometry) x, Y(geometry) y from nodes where is_centroid=1' points = pd.read_sql(zone_sql, conn) if source == 'zones' else pd.read_sql(network_sql, conn) dpoints = np.array(points[['x', 'y']]) all_edges = Delaunay(np.array(dpoints)).simplices edges = [] for triangle in all_edges: links = list(combinations(triangle, 2)) for i in links: f, t = sorted(list(i)) edges.append([points.at[f, 'node_id'], points.at[t, 'node_id']]) edges = pd.DataFrame(edges) edges.drop_duplicates(inplace=True) edges.columns = ['a_node', 'b_node'] edges = edges.assign(direction=0, distance=0, link_id=np.arange(edges.shape[0]) + 1) edges = edges[['link_id', 'direction', 'a_node', 'b_node', 'distance']] edges.to_sql(DELAUNAY_TABLE, conn, index=False) # Now we create the geometries for the delaunay triangulation conn.execute(f"select AddGeometryColumn( '{DELAUNAY_TABLE}', 'geometry', 4326, 'LINESTRING', 'XY', 0);") conn.execute("CREATE UNIQUE INDEX unique_link_id_delaunay on delaunay_network(link_id)") node_geo_sql = '''INSERT INTO delaunay_network (link_id, geometry) select lnk.link_id, MakeLine(nd.geometry, nf.geometry) from delaunay_network lnk inner join nodes nd on lnk.a_node=nd.node_id inner join nodes nf on lnk.b_node=nf.node_id ON CONFLICT(link_id) DO UPDATE SET geometry=excluded.geometry''' zone_geo_sql = '''INSERT INTO delaunay_network (link_id, geometry) select lnk.link_id, MakeLine(st_centroid(za.geometry), st_centroid(zb.geometry)) from delaunay_network lnk inner join zones za on lnk.a_node=za.zone_id inner join zones zb on lnk.b_node=zb.zone_id ON CONFLICT(link_id) DO UPDATE SET geometry=excluded.geometry;''' sql = zone_geo_sql if source == 'zones' else node_geo_sql conn.execute(sql) # Updates link distance conn.execute('update delaunay_network set distance=GeodesicLength(geometry);') conn.commit() conn.close()
def connector_creation(geo, zone_id: int, srid: int, mode_id: str, link_types="", connectors=1): if len(mode_id) > 1: raise Exception( "We can only add centroid connectors for one mode at a time") conn = database_connection() curr = conn.cursor() curr.execute("select count(*) from nodes where node_id=?", [zone_id]) if curr.fetchone() is None: warn("This centroid does not exist. Please create it first") return proj_nodes = network.Nodes() node = proj_nodes.get(zone_id) curr.execute( "select count(*) from links where a_node=? and instr(modes,?) > 0", [zone_id, mode_id]) if curr.fetchone()[0] > 0: warn("Mode is already connected") return if len(link_types) > 0: lt = f"*[{link_types}]*" else: curr.execute("Select link_type_id from link_types") lt = "".join([x[0] for x in curr.fetchall()]) lt = f"*[{lt}]*" sql = """select node_id, ST_asBinary(geometry), modes, link_types from nodes where ST_Within(geometry, GeomFromWKB(?, ?)) and (nodes.rowid in (select rowid from SpatialIndex where f_table_name = 'nodes' and search_frame = GeomFromWKB(?, ?))) and link_types glob ? and instr(modes, ?)>0""" # We expand the area by its average radius until it is 20 times # beginning with a strict search within the zone buffer = 0 increase = sqrt(geo.area / pi) dt = [] while dt == [] and buffer <= increase * 10: wkb = geo.buffer(buffer).wkb curr.execute(sql, [wkb, srid, wkb, srid, lt, mode_id]) dt = curr.fetchall() buffer += increase if buffer > increase: msg = f"Could not find node inside zone {zone_id}. Search area was expanded until we found a suitable node" logger.warning(msg) if dt == []: warn( f"FAILED! Could not find suitable nodes to connect within 5 times the diameter of zone {zone_id}." ) return coords = [] nodes = [] for node_id, wkb, modes, link_types in dt: geo = shapely.wkb.loads(wkb) coords.append([geo.x, geo.y]) nodes.append(node_id) num_connectors = connectors if len(nodes) == 0: raise Exception( "We could not find any candidate nodes that satisfied your criteria" ) elif len(nodes) < connectors: warn( f"We have fewer possible nodes than required connectors for zone {zone_id}. Will connect all of them." ) num_connectors = len(nodes) if num_connectors == len(coords): all_nodes = nodes else: features = np.array(coords) whitened = whiten(features) centroids, allocation = kmeans2(whitened, num_connectors) all_nodes = set() for i in range(num_connectors): nds = [x for x, y in zip(nodes, list(allocation)) if y == i] centr = centroids[i] positions = [x for x, y in zip(whitened, allocation) if y == i] if positions: dist = cdist(np.array([centr]), np.array(positions)).flatten() node_to_connect = nds[dist.argmin()] all_nodes.add(node_to_connect) nds = list(all_nodes) data = [zone_id] + nds curr.execute( f'select b_node from links where a_node=? and b_node in ({",".join(["?"] * len(nds))})', data) data = [x[0] for x in curr.fetchall()] if data: qry = ",".join(["?"] * len(data)) dt = [mode_id, zone_id] + data curr.execute( f"Update links set modes=modes || ? where a_node=? and b_node in ({qry})", dt) nds = [x for x in nds if x not in data] logger.warning( f"Mode {mode_id} added to {len(data)} existing centroid connectors for zone {zone_id}" ) conn.commit() curr.close() links = network.Links() for node_to_connect in nds: link = links.new() node_to = proj_nodes.get(node_to_connect) link.geometry = LineString([node.geometry, node_to.geometry]) link.modes = mode_id link.direction = 0 link.link_type = "centroid_connector" link.name = f"centroid connector zone {zone_id}" link.capacity_ab = INFINITE_CAPACITY link.capacity_ba = INFINITE_CAPACITY link.save() if nds: logger.warning( f"{len(nds)} new centroid connectors for mode {mode_id} added for centroid {zone_id}" ) conn.commit()
def __init__(self): self.conn = database_connection() self.curr = self.conn.cursor() if self.sql == "": self.refresh_fields()
def test_database_connection(self): # Errors when project does not exist with self.assertRaises(FileExistsError): _ = database_connection()