Class GenericSQLQuery

                      object --+    
                               |    
structure.sage_object.SageObject --+
                                   |
                                  GenericSQLQuery

Known Subclasses:

graphs.graph_database.GenericGraphQuery, SQLQuery

A query for a SQLite database.

INPUT:
    database -- a SQLDatabase or GenericSQLDatabase object
    query_string -- a string representing the SQL query
    param_tuple -- a tuple of strings - what to replace question marks in
        query_string with

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...

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 = GenericSQLQuery(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                   

TUTORIAL:
The SQLDatabase class is for interactively building databases intended for
queries. This may sound redundant, but it is important. If you want a
database intended for quick lookup of entries in very large tables, much
like a hash table (such as a Python dictionary), a SQLDatabase may not be
what you are looking for. The strength of SQLDatabases is in queries,
searches through the database with complicated criteria.

The class GenericSQLDatabase is for developers to provide a static
database. The class does not support modification, and is meant to be a
base class for specific classes of databases, such as the graph database.

For example, we create a new database for storing isomorphism classes of
simple graphs:
    sage: D = SQLDatabase()

In order to generate representatives for the classes, we will import a
function which generates all labeled graphs (noting that this is not the
optimal way):
    sage: from sage.graphs.graph_isom import all_labeled_graphs

We will need a table in the database in which to store the graphs, and we
specify its structure with a Python dictionary, each of whose keys is the
name of a column:
    sage: table_skeleton = {
    ... 'graph6':{'sql':'TEXT', 'index':True, 'primary_key':True},
    ... 'vertices':{'sql':'INTEGER'},
    ... 'edges':{'sql':'INTEGER'}
    ... }

Then we create the table:
    sage: D.create_table('simon', table_skeleton)
    sage: D.show('simon')
    edges                graph6               vertices            
    ------------------------------------------------------------

Now that we have the table, we will begin to populate the table with
rows. First, add the graph on zero vertices.
    sage: G = Graph()
    
    sage: D.add_row('simon',(0, G.graph6_string(), 0))
    
    sage: D.show('simon')
    edges                graph6               vertices            
    ------------------------------------------------------------
    0                    ?                    0                   

Next, add the graph on one vertex.
    sage: G.add_vertex()
    sage: D.add_row('simon',(0, G.graph6_string(), 1))
    sage: D.show('simon')
    edges                graph6               vertices            
    ------------------------------------------------------------
    0                    ?                    0                   
    0                    @                    1                   

Say we want a database of graphs on four or less vertices:
    sage: labels = {}
    sage: for i in range(2, 5):
    ...       labels[i] = []
    ...       for g in all_labeled_graphs(i):
    ...           g = g.canonical_label()
    ...           if g not in labels[i]:
    ...               labels[i].append(g)
    ...               D.add_row('simon', (g.size(), g.graph6_string(), g.order()))
    ...             
    sage: D.show('simon')
    edges                graph6               vertices            
    ------------------------------------------------------------
    0                    ?                    0                   
    0                    @                    1                   
    0                    A?                   2                   
    1                    A_                   2                   
    0                    B?                   3                   
    1                    BG                   3                   
    2                    BW                   3                   
    3                    Bw                   3                   
    0                    C?                   4                   
    1                    C@                   4                   
    2                    CB                   4                   
    3                    CF                   4                   
    3                    CJ                   4                   
    2                    CK                   4                   
    3                    CL                   4                   
    4                    CN                   4                   
    4                    C]                   4                   
    5                    C^                   4                   
    6                    C~                   4                   

We can then query the database-- let's ask for all the graphs on four
vertices with three edges. We do so by creating two queries and asking for
rows that satisfy them both:
    sage: Q = SQLQuery(D, {'table_name':'simon', 'display_cols':['graph6'], 'expression':['vertices','=',4]})
    sage: Q2 = SQLQuery(D, {'table_name':'simon', 'display_cols':['graph6'], 'expression':['edges','=',3]})
    sage: Q = Q.intersect(Q2) 
    sage: Q.run_query()
    [(u'CF', u'CF'), (u'CJ', u'CJ'), (u'CL', u'CL')]

NOTE - The values of display_cols are always concatenated in intersections
and unions.

Of course, we can save the database to file:
    sage: D.save('simon.db')

Now the database's hard link is to this file, and not the temporary db
file. For example, let's say we open the same file with another class
instance. We can load the file as an immutable database:
    sage: E = GenericSQLDatabase('simon.db')
    sage: E.show('simon')
    edges                graph6               vertices            
    ------------------------------------------------------------
    0                    ?                    0                   
    0                    @                    1                   
    0                    A?                   2                   
    1                    A_                   2                   
    0                    B?                   3                   
    1                    BG                   3                   
    2                    BW                   3                   
    3                    Bw                   3                   
    0                    C?                   4                   
    1                    C@                   4                   
    2                    CB                   4                   
    3                    CF                   4                   
    3                    CJ                   4                   
    2                    CK                   4                   
    3                    CL                   4                   
    4                    CN                   4                   
    4                    C]                   4                   
    5                    C^                   4                   
    6                    C~                   4                   
    sage: E.drop_table('simon')
    Traceback (most recent call last):
    ...
    AttributeError: 'GenericSQLDatabase' object has no attribute 'drop_table'
    
    

x.__init__(...) initializes x; see x.__class__.__doc__ for signature

File: sage/structure/sage_object.pyx (starting at line 86)

Runs the query by executing the __query_string__.  Returns the results
of the query in a list.

EXAMPLES:
    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 = GenericSQLQuery(G,q,param)
    sage: Q.run_query()
    [(18, u'D??', 5, 0),
     (19, u'D?C', 5, 1),
     (20, u'D?K', 5, 2),
     (21, u'D@O', 5, 2),
     (22, u'D?[', 5, 3)]
     
    sage: R = SQLQuery(G,{'table_name':'graph_data', 'display_cols':['graph6'], 'expression':['num_vertices','=',4]})
    sage: R.run_query()
    [(u'C?',),
     (u'C@',),
     (u'CB',),
     (u'CK',),
     (u'CF',),
     (u'CJ',),
     (u'CL',),
     (u'CN',),
     (u'C]',),
     (u'C^',),
     (u'C~',)]

Displays the result of the query in table format.

INPUT:
    max_field_size -- how wide each field can be

EXAMPLE:
    sage: DB = SQLDatabase()
    sage: DB.create_table('simon',{'a1':{'sql':'bool','primary_key':False}, 'b2':{'sql':'int', 'primary_key':False}})
    sage: DB.add_data('simon',[(0,0),(1,1),(1,2)])
    sage: r = SQLQuery(DB, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 6]})
    sage: p = SQLQuery(DB, {'table_name':'simon', 'display_cols':['b2'], 'expression':['b2','<=', 6]})
    sage: s = p.intersect(r)
    sage: s.show()
    b2                   a1                  
    ----------------------------------------
    0                    0                   
    1                    1                   
    2                    1