def __init__(self, query_string, database=None, param_tuple=None): """ A query for a :class:`~GraphDatabase`. INPUT: - ``query_string`` -- a string representing the SQL query - ``database`` -- (default: ``None``); the :class:`~GraphDatabase` instance to query (if ``None`` then a new instance is created) - ``param_tuple`` -- a tuple of strings (default: ``None``); what to replace question marks in ``query_string`` with (optional, but a good idea) .. NOTE:: This query class is generally intended for developers and more advanced users. It allows you to execute any query, and so may be considered unsafe. EXAMPLES: See :class:`~GraphDatabase` class docstrings or enter:: sage: G = GraphDatabase() sage: G.get_skeleton() {... to see the underlying structure of the database. Also see :class:`sage.databases.sql_db.SQLQuery` in :mod:`sage.databases.sql_db` for more info and a tutorial. A piece of advice about '?' and param_tuple: it is generally considered safer to query with a '?' in place of each value parameter, and using a second argument (a tuple of strings) in a call to the ``sqlite`` database. Successful use of the ``param_tuple`` argument is exemplified:: sage: G = GraphDatabase() sage: q = 'select graph_id,graph6,num_vertices,num_edges from graph_data where graph_id<=(?) and num_vertices=(?)' sage: param = (22,5) sage: Q = SQLQuery(G, q, param) sage: Q.show() graph_id graph6 num_vertices num_edges -------------------------------------------------------------------------------- 18 D?? 5 0 19 D?C 5 1 20 D?K 5 2 21 D@O 5 2 22 D?[ 5 3 """ if database is None: database = GraphDatabase() if not isinstance(database, GraphDatabase): raise TypeError('%s is not a valid GraphDatabase' % database) SQLQuery.__init__(self, database, query_string, param_tuple)
def __init__(self, query_string, database=None, param_tuple=None): """ A query for a GraphDatabase. INPUT: - ``database`` - the GraphDatabase instance to query (if None then a new instance is created) - ``query_string`` - a string representing the SQL query - ``param_tuple`` - a tuple of strings - what to replace question marks in query_string with (optional, but a good idea) .. note:: This query class is generally intended for developers and more advanced users. It allows you to execute any query, and so may be considered unsafe. See GraphDatabase class docstrings or enter:: sage: G = GraphDatabase() sage: G.get_skeleton() {... to see the underlying structure of the database. Also see SQLQuery in sage.databases.database for more info and a tutorial. A piece of advice about '?' and param_tuple: It is generally considered safer to query with a '?' in place of each value parameter, and using a second argument (a tuple of strings) in a call to the sqlite database. Successful use of the param_tuple argument is exemplified:: sage: G = GraphDatabase() sage: q = 'select graph_id,graph6,num_vertices,num_edges from graph_data where graph_id<=(?) and num_vertices=(?)' sage: param = (22,5) sage: Q = SQLQuery(G,q,param) sage: Q.show() graph_id graph6 num_vertices num_edges -------------------------------------------------------------------------------- 18 D?? 5 0 19 D?C 5 1 20 D?K 5 2 21 D@O 5 2 22 D?[ 5 3 """ if database is None: database = GraphDatabase() if not isinstance(database, GraphDatabase): raise TypeError('%s is not a valid GraphDatabase'%database) SQLQuery.__init__(self,database,query_string,param_tuple)
def show(self, max_field_size=20, with_picture=False): """ Display the results of a query in table format. INPUT: - ``max_field_size`` -- integer (default: 20); width of fields in command prompt version - ``with_picture`` -- boolean (default: ``False``); whether or not to display results with a picture of the graph (available only in the notebook) EXAMPLES:: sage: G = GraphDatabase() sage: Q = GraphQuery(G, display_cols=['graph6','num_vertices','aut_grp_size'], num_vertices=4, aut_grp_size=4) sage: Q.show() Graph6 Num Vertices Aut Grp Size ------------------------------------------------------------ C@ 4 4 C^ 4 4 :: sage: R = GraphQuery(G, display_cols=['graph6','num_vertices','degree_sequence'], num_vertices=4) sage: R.show() Graph6 Num Vertices Degree Sequence ------------------------------------------------------------ C? 4 [0, 0, 0, 0] C@ 4 [0, 0, 1, 1] CB 4 [0, 1, 1, 2] CF 4 [1, 1, 1, 3] CJ 4 [0, 2, 2, 2] CK 4 [1, 1, 1, 1] CL 4 [1, 1, 2, 2] CN 4 [1, 2, 2, 3] C] 4 [2, 2, 2, 2] C^ 4 [2, 2, 3, 3] C~ 4 [3, 3, 3, 3] Show the pictures (in notebook mode only):: sage: S = GraphQuery(G, display_cols=['graph6','aut_grp_size'], num_vertices=4) sage: S.show(with_picture=True) Traceback (most recent call last): ... NotImplementedError: Cannot display plot on command line. Note that pictures can be turned off:: sage: S.show(with_picture=False) Graph6 Aut Grp Size ---------------------------------------- C? 24 C@ 4 CB 2 CF 6 CJ 6 CK 8 CL 2 CN 2 C] 8 C^ 4 C~ 24 Show your own query (note that the output is not reformatted for generic queries):: sage: (GenericGraphQuery('select degree_sequence from degrees where max_degree=2 and min_degree >= 1', G)).show() degree_sequence -------------------- 211 222 2211 2222 21111 22211 22211 22222 221111 221111 222211 222211 222211 222222 222222 2111111 2221111 2221111 2221111 2222211 2222211 2222211 2222211 2222222 2222222 """ relabel = {} for col in valid_kwds: relabel[col] = ' '.join( [word.capitalize() for word in col.split('_')]) if re.search('SELECT .*degree_sequence.* FROM', self.__query_string__): format_cols = { 'degree_sequence': (lambda x, y: data_to_degseq(x, y)) } else: format_cols = {} if with_picture: SQLQuery.show(self, max_field_size=max_field_size, plot_cols={'graph6': (lambda x: graph6_to_plot(x))}, format_cols=format_cols, id_col='graph6', relabel_cols=relabel) else: SQLQuery.show(self, max_field_size=max_field_size, format_cols=format_cols, relabel_cols=relabel, id_col='graph6')
def __init__(self, graph_db=None, query_dict=None, display_cols=None, **kwds): """ A query for an instance of :class:`~GraphDatabase`. This class nicely wraps the :class:`sage.databases.sql_db.SQLQuery` class located in :mod:`sage.databases.sql_db` to make the query constraints intuitive and with as many pre-definitions as possible. (i.e.: since it has to be a :class:`~GraphDatabase`, we already know the table structure and types; and since it is immutable, we can treat these as a guarantee). .. NOTE:: :class:`sage.databases.sql_db.SQLQuery` functions are available for :class:`~GraphQuery`. See :mod:`sage.databases.sql_db` for more details. INPUT: - ``graph_db`` -- :class:`~GraphDatabase` (default: ``None``); instance to apply the query to (If ``None``, then a new instance is created) - ``query_dict`` -- dict (default: ``None``); a dictionary specifying the query itself. Format is: ``{'table_name': 'tblname', 'display_cols': ['col1', 'col2'], 'expression': [col, operator, value]}``. If not ``None``, ``query_dict`` will take precedence over all other arguments. - ``display_cols`` -- list of strings (default: ``None``); a list of column names (strings) to display in the result when running or showing a query - ``kwds`` -- the columns of the database are all keywords. For a database table/column structure dictionary, call :func:`~graph_db_info`. Keywords accept both single values and lists of length 2. The list allows the user to specify an expression other than equality. Valid expressions are strings, and for numeric values (i.e. Reals and Integers) are: '=','','','=','='. String values also accept 'regexp' as an expression argument. The only keyword exception to this format is ``induced_subgraphs``, which accepts one of the following options: - ``['one_of', String, ..., String]`` -- will search for graphs containing a subgraph isomorphic to *any* of the ``graph6`` strings in the list - ``['all_of', String, ..., String]`` -- will search for graphs containing a subgraph isomorphic to *each* of the ``graph6`` strings in the list EXAMPLES:: sage: Q = GraphQuery(display_cols=['graph6', 'num_vertices', 'degree_sequence'], num_edges=['<=', 5], min_degree=1) sage: Q.number_of() 35 sage: Q.show() Graph6 Num Vertices Degree Sequence ------------------------------------------------------------ A_ 2 [1, 1] BW 3 [1, 1, 2] CF 4 [1, 1, 1, 3] CK 4 [1, 1, 1, 1] CL 4 [1, 1, 2, 2] CN 4 [1, 2, 2, 3] D?{ 5 [1, 1, 1, 1, 4] D@s 5 [1, 1, 1, 2, 3] D@{ 5 [1, 1, 2, 2, 4] DBg 5 [1, 1, 2, 2, 2] DBk 5 [1, 1, 2, 3, 3] DIk 5 [1, 2, 2, 2, 3] DK[ 5 [1, 2, 2, 2, 3] D_K 5 [1, 1, 1, 1, 2] D`K 5 [1, 1, 2, 2, 2] E?Bw 6 [1, 1, 1, 1, 1, 5] E?Fg 6 [1, 1, 1, 1, 2, 4] E?N? 6 [1, 1, 1, 1, 2, 2] E?NG 6 [1, 1, 1, 1, 3, 3] E@FG 6 [1, 1, 1, 2, 2, 3] E@N? 6 [1, 1, 2, 2, 2, 2] E@Q? 6 [1, 1, 1, 1, 1, 1] E@QW 6 [1, 1, 1, 2, 2, 3] E@YO 6 [1, 1, 2, 2, 2, 2] E_?w 6 [1, 1, 1, 1, 1, 3] E_Cg 6 [1, 1, 1, 1, 2, 2] E_Cw 6 [1, 1, 1, 2, 2, 3] E_Ko 6 [1, 1, 2, 2, 2, 2] F??^? 7 [1, 1, 1, 1, 1, 2, 3] F?LCG 7 [1, 1, 1, 1, 2, 2, 2] FK??W 7 [1, 1, 1, 1, 1, 1, 2] FK?GW 7 [1, 1, 1, 1, 2, 2, 2] F_?@w 7 [1, 1, 1, 1, 1, 1, 4] F_?Hg 7 [1, 1, 1, 1, 1, 2, 3] F_?XO 7 [1, 1, 1, 1, 2, 2, 2] """ if graph_db is None: graph_db = GraphDatabase() if query_dict is not None: if query_dict['expression'][0] == 'degree_sequence': query_dict['expression'][3] = degseq_to_data( query_dict['expression'][3]) elif query_dict['expression'][0] == 'induced_subgraphs': query_dict['expression'][3] = subgraphs_to_data( query_dict['expression'][3]) SQLQuery.__init__(self, graph_db, query_dict) else: # construct a query from the given parameters SQLQuery.__init__(self, graph_db) # if display_cols is None: # raise TypeError, 'Nonetype display_cols cannot retrieve data.' master_join = {} for key in kwds: # check validity if not key in valid_kwds: raise KeyError('%s is not a valid key for this database.' % str(key)) # designate a query_dict qdict = { 'display_cols': None } # reserve display cols until end # (database.py currently concatenates # them including repeats) # set table name if key in graph_data: qdict['table_name'] = 'graph_data' elif key in aut_grp: qdict['table_name'] = 'aut_grp' elif key in degrees: qdict['table_name'] = 'degrees' elif key in misc: qdict['table_name'] = 'misc' elif key in spectrum: qdict['table_name'] = 'spectrum' # set expression if not isinstance(kwds[key], list): if key == 'induced_subgraphs': qdict['expression'] = [ key, 'regexp', '.*%s.*' % (graph.Graph( kwds[key]).canonical_label()).graph6_string() ] else: qdict['expression'] = [key, '=', kwds[key]] elif key == 'degree_sequence': qdict['expression'] = [key, '=', degseq_to_data(kwds[key])] elif key != 'induced_subgraphs': qdict['expression'] = [key] + kwds[key] # add key parameter to query join_dict = {qdict['table_name']: ('graph_id', 'graph_id')} if key == 'induced_subgraphs' and isinstance(kwds[key], list): self.intersect(subgraphs_to_query(kwds[key], graph_db), 'graph_data', join_dict, in_place=True) else: self.intersect(SQLQuery(graph_db, qdict), 'graph_data', join_dict, in_place=True) # include search params (keys) in join clause # again, we exclude graph_data because it is the base table if qdict['table_name'] != 'graph_data': master_join[qdict['table_name']] = ('graph_id', 'graph_id') # display columns from each table aut_grp_disp = ['aut_grp'] degrees_disp = ['degrees'] misc_disp = ['misc'] spectrum_disp = ['spectrum'] graph_data_disp = ['graph_data'] disp_tables = [ aut_grp_disp, degrees_disp, misc_disp, spectrum_disp ] # graph_data intentionally left out because it is always called # organize display if display_cols is not None: for col in display_cols: if col in graph_data: graph_data_disp.append(col) elif col in aut_grp: aut_grp_disp.append(col) elif col in degrees: degrees_disp.append(col) elif col in misc: misc_disp.append(col) elif col in spectrum: spectrum_disp.append(col) # finish filling master join with display tables for tab in disp_tables: if len(tab) > 1: master_join[tab[0]] = ('graph_id', 'graph_id') # join clause for display tables join_str = 'FROM graph_data ' for tab in master_join: join_str += 'INNER JOIN %s ON graph_data.graph_id=%s.graph_id ' % ( tab, tab) # construct sql syntax substring for display cols disp_list = ['SELECT graph_data.graph6, '] for col in graph_data_disp[1:]: if col != 'graph6': disp_list.append('graph_data.%s, ' % col) for col in aut_grp_disp[1:]: disp_list.append('aut_grp.%s, ' % col) for col in degrees_disp[1:]: disp_list.append('degrees.%s, ' % col) for col in misc_disp[1:]: disp_list.append('misc.%s, ' % col) for col in spectrum_disp[1:]: disp_list.append('spectrum.%s, ' % col) disp_list[-1] = disp_list[-1].rstrip(', ') + ' ' disp_str = ''.join(disp_list) # substitute disp_str and join_str back into self's query string self.__query_string__ = re.sub('SELECT.*WHERE ', disp_str + join_str + \ 'WHERE ', self.__query_string__) self.__query_string__ += ' ORDER BY graph_data.graph6'
def show(self, max_field_size=20, with_picture=False): """ Displays the results of a query in table format. INPUT: - ``max_field_size`` - width of fields in command prompt version - ``with_picture`` - whether or not to display results with a picture of the graph (available only in the notebook) EXAMPLES:: sage: G = GraphDatabase() sage: Q = GraphQuery(G, display_cols=['graph6','num_vertices','aut_grp_size'], num_vertices=4, aut_grp_size=4) sage: Q.show() Graph6 Num Vertices Aut Grp Size ------------------------------------------------------------ C@ 4 4 C^ 4 4 :: sage: R = GraphQuery(G, display_cols=['graph6','num_vertices','degree_sequence'], num_vertices=4) sage: R.show() Graph6 Num Vertices Degree Sequence ------------------------------------------------------------ C? 4 [0, 0, 0, 0] C@ 4 [0, 0, 1, 1] CB 4 [0, 1, 1, 2] CF 4 [1, 1, 1, 3] CJ 4 [0, 2, 2, 2] CK 4 [1, 1, 1, 1] CL 4 [1, 1, 2, 2] CN 4 [1, 2, 2, 3] C] 4 [2, 2, 2, 2] C^ 4 [2, 2, 3, 3] C~ 4 [3, 3, 3, 3] Show the pictures (in notebook mode only):: sage: S = GraphQuery(G, display_cols=['graph6','aut_grp_size'], num_vertices=4) sage: S.show(with_picture=True) Traceback (most recent call last): ... NotImplementedError: Cannot display plot on command line. Note that pictures can be turned off:: sage: S.show(with_picture=False) Graph6 Aut Grp Size ---------------------------------------- C? 24 C@ 4 CB 2 CF 6 CJ 6 CK 8 CL 2 CN 2 C] 8 C^ 4 C~ 24 Show your own query (note that the output is not reformatted for generic queries):: sage: (GenericGraphQuery('select degree_sequence from degrees where max_degree=2 and min_degree >= 1',G)).show() degree_sequence -------------------- 211 222 2211 2222 21111 22211 22211 22222 221111 221111 222211 222211 222211 222222 222222 2111111 2221111 2221111 2221111 2222211 2222211 2222211 2222211 2222222 2222222 """ relabel = {} for col in valid_kwds: relabel[col] = ' '.join([word.capitalize() for word in col.split('_')]) if re.search('SELECT .*degree_sequence.* FROM',self.__query_string__): format_cols = {'degree_sequence': (lambda x,y: data_to_degseq(x,y))} else: format_cols = {} if with_picture: SQLQuery.show(self, max_field_size=max_field_size, \ plot_cols={'graph6': (lambda x: graph6_to_plot(x))}, \ format_cols=format_cols, id_col='graph6', \ relabel_cols=relabel) else: SQLQuery.show(self, max_field_size=max_field_size, \ format_cols=format_cols, relabel_cols=relabel, \ id_col='graph6')
def __init__(self, graph_db=None, query_dict=None, display_cols=None, **kwds): """ A query for an instance of GraphDatabase. This class nicely wraps the SQLQuery class located in sage.databases.database.py to make the query constraints intuitive and with as many pre-definitions as possible. (i.e.: since it has to be a GraphDatabase, we already know the table structure and types; and since it is immutable, we can treat these as a guarantee). .. note:: SQLQuery functions are available for GraphQuery. See sage.dataabases.database.py for more details. INPUT: - ``graph_db`` - The GraphDatabase instance to apply the query to. (If None, then a new instance is created). - ``query_dict`` - A dictionary specifying the query itself. Format is: 'table_name': 'tblname', 'display_cols': ['col1', 'col2'], 'expression':[col, operator, value] If not None, query_dict will take precedence over all other arguments. - ``display_cols`` - A list of column names (strings) to display in the result when running or showing a query. - ``kwds`` - The columns of the database are all keywords. For a database table/column structure dictionary, call graph_db_info. Keywords accept both single values and lists of length 2. The list allows the user to specify an expression other than equality. Valid expressions are strings, and for numeric values (i.e. Reals and Integers) are: '=','','','=','='. String values also accept 'regexp' as an expression argument. The only keyword exception to this format is induced_subgraphs, which accepts one of the following options: 1. ['one_of',String,...,String] Will search for graphs containing a subgraph isomorphic to any of the graph6 strings in the list. 2. ['all_of',String,...,String] Will search for graphs containing a subgraph isomorphic to each of the graph6 strings in the list. EXAMPLES:: sage: Q = GraphQuery(display_cols=['graph6','num_vertices','degree_sequence'],num_edges=['<=',5],min_degree=1) sage: Q.number_of() 35 sage: Q.show() Graph6 Num Vertices Degree Sequence ------------------------------------------------------------ A_ 2 [1, 1] BW 3 [1, 1, 2] CF 4 [1, 1, 1, 3] CK 4 [1, 1, 1, 1] CL 4 [1, 1, 2, 2] CN 4 [1, 2, 2, 3] D?{ 5 [1, 1, 1, 1, 4] D@s 5 [1, 1, 1, 2, 3] D@{ 5 [1, 1, 2, 2, 4] DBg 5 [1, 1, 2, 2, 2] DBk 5 [1, 1, 2, 3, 3] DIk 5 [1, 2, 2, 2, 3] DK[ 5 [1, 2, 2, 2, 3] D_K 5 [1, 1, 1, 1, 2] D`K 5 [1, 1, 2, 2, 2] E?Bw 6 [1, 1, 1, 1, 1, 5] E?Fg 6 [1, 1, 1, 1, 2, 4] E?N? 6 [1, 1, 1, 1, 2, 2] E?NG 6 [1, 1, 1, 1, 3, 3] E@FG 6 [1, 1, 1, 2, 2, 3] E@N? 6 [1, 1, 2, 2, 2, 2] E@Q? 6 [1, 1, 1, 1, 1, 1] E@QW 6 [1, 1, 1, 2, 2, 3] E@YO 6 [1, 1, 2, 2, 2, 2] E_?w 6 [1, 1, 1, 1, 1, 3] E_Cg 6 [1, 1, 1, 1, 2, 2] E_Cw 6 [1, 1, 1, 2, 2, 3] E_Ko 6 [1, 1, 2, 2, 2, 2] F??^? 7 [1, 1, 1, 1, 1, 2, 3] F?LCG 7 [1, 1, 1, 1, 2, 2, 2] FK??W 7 [1, 1, 1, 1, 1, 1, 2] FK?GW 7 [1, 1, 1, 1, 2, 2, 2] F_?@w 7 [1, 1, 1, 1, 1, 1, 4] F_?Hg 7 [1, 1, 1, 1, 1, 2, 3] F_?XO 7 [1, 1, 1, 1, 2, 2, 2] """ if graph_db is None: graph_db = GraphDatabase() if query_dict is not None: if query_dict['expression'][0] == 'degree_sequence': query_dict['expression'][3] = degseq_to_data(query_dict['expression'][3]) elif query_dict['expression'][0] == 'induced_subgraphs': query_dict['expression'][3] = subgraphs_to_data(query_dict['expression'][3]) SQLQuery.__init__(self, graph_db, query_dict) else: # construct a query from the given parameters SQLQuery.__init__(self, graph_db) #if display_cols is None: # raise TypeError, 'Nonetype display_cols cannot retrieve data.' master_join = {} for key in kwds: # check validity if not key in valid_kwds: raise KeyError('%s is not a valid key for this database.'%str(key)) # designate a query_dict qdict = {'display_cols': None} # reserve display cols until end # (database.py currently concatenates # them including repeats) # set table name if key in graph_data: qdict['table_name'] = 'graph_data' elif key in aut_grp: qdict['table_name'] = 'aut_grp' elif key in degrees: qdict['table_name'] = 'degrees' elif key in misc: qdict['table_name'] = 'misc' elif key in spectrum: qdict['table_name'] = 'spectrum' # set expression if not isinstance(kwds[key],list): if key == 'induced_subgraphs': qdict['expression'] = [key, 'regexp', '.*%s.*'%(graph.Graph(kwds[key]).canonical_label()).graph6_string()] else: qdict['expression'] = [key, '=', kwds[key]] elif key == 'degree_sequence': qdict['expression'] = [key, '=', degseq_to_data(kwds[key])] elif key != 'induced_subgraphs': qdict['expression'] = [key] + kwds[key] # add key parameter to query join_dict = {qdict['table_name']: ('graph_id', 'graph_id')} if key == 'induced_subgraphs' and isinstance(kwds[key],list): self.intersect(subgraphs_to_query(kwds[key], graph_db), 'graph_data', join_dict, in_place=True) else: self.intersect(SQLQuery(graph_db, qdict), 'graph_data', join_dict,in_place=True) # include search params (keys) in join clause # again, we exclude graph_data because it is the base table if qdict['table_name'] != 'graph_data': master_join[qdict['table_name']] = ('graph_id', 'graph_id') # display columns from each table aut_grp_disp = ['aut_grp'] degrees_disp = ['degrees'] misc_disp = ['misc'] spectrum_disp = ['spectrum'] graph_data_disp = ['graph_data'] disp_tables = [aut_grp_disp, degrees_disp, misc_disp, spectrum_disp] # graph_data intentionally left out because it is always called # organize display if display_cols is not None: for col in display_cols: if col in graph_data: graph_data_disp.append(col) elif col in aut_grp: aut_grp_disp.append(col) elif col in degrees: degrees_disp.append(col) elif col in misc: misc_disp.append(col) elif col in spectrum: spectrum_disp.append(col) # finish filling master join with display tables for tab in disp_tables: if len(tab) > 1: master_join[tab[0]] = ('graph_id', 'graph_id') # join clause for display tables join_str = 'FROM graph_data ' for tab in master_join: join_str += 'INNER JOIN %s ON graph_data.graph_id=%s.graph_id '%(tab, tab) # construct sql syntax substring for display cols disp_str = 'SELECT graph_data.graph6, ' for col in graph_data_disp[1:]: if col != 'graph6': disp_str += 'graph_data.%s, '%col for col in aut_grp_disp[1:]: disp_str += 'aut_grp.%s, '%col for col in degrees_disp[1:]: disp_str += 'degrees.%s, '%col for col in misc_disp[1:]: disp_str += 'misc.%s, '%col for col in spectrum_disp[1:]: disp_str += 'spectrum.%s, '%col disp_str = disp_str.rstrip(', ') + ' ' # substitue disp_str and join_str back into self's query string self.__query_string__ = re.sub('SELECT.*WHERE ', disp_str + join_str + \ 'WHERE ', self.__query_string__) self.__query_string__ += ' ORDER BY graph_data.graph6'