INFO:
This module implements classes (SQLDatabase and SQLQuery (pythonic implementation for the user with little or no knowledge of sqlite)) that wrap the basic functionality of sqlite.
Databases are constructed via a triple indexed dictionary called a skeleton. A skeleton should be constructed to fit the following format:
| - skeleton -- a triple-indexed dictionary | - outer key -- table name | - inner key -- column name | - inner inner key -- one of the following: | - ``primary_key`` -- boolean, whether column has been set as primary key | - ``index`` -- boolean, whether column has been set as index | - ``unique`` -- boolean, whether column has been set as unique | - ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``, ``'INTEGER'``, ``'REAL'``, or other user defined typeAn example skeleton of a database with one table, that table with one column:
{'table1':{'col1':{'primary_key':False, 'index':True, 'sql':'REAL'}}}SQLDatabases can also be constructed via the add, drop, and commit functions. The vacuum function is also useful for restoring hard disk space after a database has shrunk in size.
A SQLQuery can be constructed by providing a query_dict, which is a dictionary with the following sample format:
{'table_name': 'tblname', 'display_cols': ['col1', 'col2', 'col3'], 'expression':[col, operator, value]}Finally a SQLQuery also allows the user to directly input the query string for a database, and also supports the ‘?’ syntax by allowing an argument for a tuple of parameters to query.
For full details, please see the tutorial. sage.graphs.graph_database.py is an example of implementing a database class in Sage using this interface.
AUTHORS:
Bases: sage.structure.sage_object.SageObject
A SQL Database object corresponding to a database file.
INPUT:
filename – a string
skeleton – a triple-indexed dictionary:
| - outer key -- table name
| - inner key -- column name
| - inner inner key -- one of the following:
| - ``primary_key`` -- boolean, whether column has been set
as primary key
| - ``index`` -- boolean, whether column has been set as
index
| - ``unique`` -- boolean, whether column has been set as
unique
| - ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``,
``'INTEGER'``, ``'REAL'``, or other user defined type
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.
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.groups.perm_gps.partn_ref.refinement_graphs 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()
0
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') # random
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: len(Q.query_results())
3
sage: Q.query_results() # random
[(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: replace_with_your_own_filepath = tmp_dir()
sage: D.save(replace_with_your_own_filepath + '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 = SQLDatabase(replace_with_your_own_filepath + 'simon.db')
sage: E.show('simon') # random
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):
...
RuntimeError: Cannot drop tables from a read only database.
Add a column named col_name to table table_name, whose data types are described by col_dict. The format for this is:
{'col1':{'primary_key':False, 'unique': True, 'index':True, 'sql':'REAL'}}
INPUT:
col_dict – a dictionary:
key – column name
inner key – one of the following:
- primary_key – boolean, whether column has been set as primary key
- index – boolean, whether column has been set as index
- unique – boolean, weather column has been set as unique
- sql – one of 'TEXT', 'BOOLEAN', 'INTEGER', 'REAL', or other user defined type
EXAMPLES:
sage: MonicPolys = SQLDatabase()
sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}})
sage: for n in range(20): MonicPolys.add_row('simon', (n,))
sage: MonicPolys.add_column('simon', 'n_squared', {'sql':'INTEGER', 'index':False}, 0)
sage: MonicPolys.show('simon')
n_squared n
----------------------------------------
0 0
0 1
0 2
0 3
0 4
0 5
0 6
0 7
0 8
0 9
0 10
0 11
0 12
0 13
0 14
0 15
0 16
0 17
0 18
0 19
INPUT:
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_rows('simon',[(0,0),(1,1),(1,2)])
sage: DB.add_rows('simon',[(0,0),(4,0),(5,1)], ['b2','a1'])
sage: cur = DB.get_cursor()
sage: (cur.execute('select * from simon')).fetchall()
[(0, 0), (1, 1), (1, 2), (0, 0), (0, 4), (1, 5)]
Add the row described by values to the table table_name. Values should be a tuple, of same length and order as columns in given table.
NOTE:
If values is of length one, be sure to specify that it is a tuple of length one, by using a comma, e.g.:
sage: values = (6,)
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_row('simon',(0,1))
sage: cur = DB.get_cursor()
sage: (cur.execute('select * from simon')).fetchall()
[(0, 1)]
INPUT:
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_rows('simon',[(0,0),(1,1),(1,2)])
sage: DB.add_rows('simon',[(0,0),(4,0),(5,1)], ['b2','a1'])
sage: cur = DB.get_cursor()
sage: (cur.execute('select * from simon')).fetchall()
[(0, 0), (1, 1), (1, 2), (0, 0), (0, 4), (1, 5)]
Commits changes to file.
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_row('simon',(0,1))
sage: DB.add_data('simon',[(0,0),(1,1),(1,2)])
sage: DB.add_data('simon',[(0,0),(4,0),(5,1)], ['b2','a1'])
sage: DB.drop_column('simon','b2')
sage: DB.commit()
sage: DB.vacuum()
Creates a new table in the database.
To create a table, a column structure must be specified. The form for this is a Python dict, for example:
{'col1': {'sql':'INTEGER', 'index':False, 'unique':True, 'primary_key':False}, ...}
INPUT:
table_name – a string
table_skeleton – a double-indexed dictionary
outer key – column name
inner key – one of the following:
- primary_key – boolean, whether column has been set asprimary key
- index – boolean, whether column has been set as index
- unique – boolean, whether column has been set as unique
- sql – one of 'TEXT', 'BOOLEAN', 'INTEGER', 'REAL', or other user defined type
NOTE:
Some SQL features, such as automatically incrementing primary key, require the full word 'INTEGER', not just 'INT'.
EXAMPLES:
sage: D = SQLDatabase()
sage: table_skeleton = {
... 'graph6':{'sql':'TEXT', 'index':True, 'primary_key':True},
... 'vertices':{'sql':'INTEGER'},
... 'edges':{'sql':'INTEGER'}
... }
sage: D.create_table('simon', table_skeleton)
sage: D.show('simon')
edges graph6 vertices
------------------------------------------------------------
Uses a SQLQuery instance to modify (delete rows from) the database.
SQLQuery must have no join statements. (As of now, you can only delete from one table at a time – ideas and patches are welcome).
To remove all data that satisfies a SQLQuery, send the query as an argument to delete_rows. Be careful, test your query first.
Recommended use: have some kind of row identification primary key column that you use as a parameter in the query. (See example below).
INPUT:
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('lucy',{'id':{'sql':'INTEGER', 'primary_key':True, 'index':True}, 'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_rows('lucy', [(0,1,1),(1,1,4),(2,0,7),(3,1,384), (4,1,978932)],['id','a1','b2'])
sage: DB.show('lucy')
a1 id b2
------------------------------------------------------------
1 0 1
1 1 4
0 2 7
1 3 384
1 4 978932
sage: Q = SQLQuery(DB, {'table_name':'lucy', 'display_cols':['id','a1','b2'], 'expression':['id','>=',3]})
sage: DB.delete_rows(Q)
sage: DB.show('lucy')
a1 id b2
------------------------------------------------------------
1 0 1
1 1 4
0 2 7
Drop the column col_name from table table_name.
EXAMPLES:
sage: MonicPolys = SQLDatabase()
sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}})
sage: for n in range(20): MonicPolys.add_row('simon', (n,))
sage: MonicPolys.add_column('simon', 'n_squared', {'sql':'INTEGER'}, 0)
sage: MonicPolys.drop_column('simon', 'n_squared')
sage: MonicPolys.show('simon')
n
--------------------
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Removes all rows from table_name.
EXAMPLES:
sage: D = SQLDatabase()
sage: D.create_table('simon',{'col1':{'sql':'INTEGER'}})
sage: D.add_row('simon',(9,))
sage: D.show('simon')
col1
--------------------
9
sage: D.drop_data_from_table('simon')
sage: D.show('simon')
col1
--------------------
Set the column index_name in table table_name to not be an index. See make_index()
EXAMPLES:
sage: MonicPolys = SQLDatabase()
sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}})
sage: MonicPolys.drop_index('simon', 'n')
sage: MonicPolys.get_skeleton()
{'simon': {'n': {'index': False,
'primary_key': False,
'sql': 'INTEGER',
'unique': False},
'n2': {'index': False,
'primary_key': False,
'sql': 'INTEGER',
'unique': False}}}
Set the column col_name in table table_name not to be a primary key.
A primary key is something like an index, but its main purpose is to link different tables together. This allows searches to be executed on multiple tables that represent maybe different data about the same objects.
NOTE:
This function only changes the column to be non-primary, it does not delete it.
EXAMPLES:
sage: MonicPolys = SQLDatabase()
sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}})
sage: MonicPolys.make_primary_key('simon', 'n2')
sage: MonicPolys.drop_primary_key('simon', 'n2')
sage: MonicPolys.get_skeleton()
{'simon': {'n': {'index': True,
'primary_key': False,
'sql': 'INTEGER',
'unique': False},
'n2': {'index': False,
'primary_key': False,
'sql': 'INTEGER',
'unique': True}}}
Delete table table_name from database.
INPUT:
EXAMPLES:
sage: D = SQLDatabase()
sage: D.create_table('simon',{'col1':{'sql':'INTEGER'}})
sage: D.show('simon')
col1
--------------------
sage: D.drop_table('simon')
sage: D.get_skeleton()
{}
Set the column col_name in table table_name not store unique values.
NOTE:
This function only removes the requirement for entries in col_name to be unique, it does not delete it.
EXAMPLES:
sage: MonicPolys = SQLDatabase()
sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}})
sage: MonicPolys.make_unique('simon', 'n2')
sage: MonicPolys.drop_unique('simon', 'n2')
sage: MonicPolys.get_skeleton()
{'simon': {'n': {'index': True,
'primary_key': False,
'sql': 'INTEGER',
'unique': False},
'n2': {'index': False,
'primary_key': False,
'sql': 'INTEGER',
'unique': False}}}
Returns a pysqlite connection to the database.
You most likely want get_cursor() instead, which is used for executing sqlite commands on the database.
Recommended for more advanced users only.
EXAMPLES:
sage: D = SQLDatabase(read_only=True)
sage: con = D.get_connection()
doctest:...: RuntimeWarning: Database is read only, using the connection can alter the stored data. Set self.ignore_warnings to True in order to mute future warnings.
sage: con = D.get_connection(True)
sage: D.ignore_warnings = True
sage: con = D.get_connection()
sage: t = con.execute('CREATE TABLE simon(n INTEGER, n2 INTEGER)')
sage: for n in range(10):
... t = con.execute('INSERT INTO simon VALUES(%d,%d)'%(n,n^2))
sage: D.show('simon')
n n2
----------------------------------------
0 0
1 1
2 4
3 9
4 16
5 25
6 36
7 49
8 64
9 81
Returns a pysqlite cursor for the database connection.
A cursor is an input from which you can execute sqlite commands on the database.
Recommended for more advanced users only.
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_row('simon',(0,1))
sage: DB.add_rows('simon',[(0,0),(1,1),(1,2)])
sage: DB.add_rows('simon',[(0,0),(4,0),(5,1)], ['b2','a1'])
sage: cur = DB.get_cursor()
sage: (cur.execute('select * from simon')).fetchall()
[(0, 1), (0, 0), (1, 1), (1, 2), (0, 0), (0, 4), (1, 5)]
Returns a dictionary representing the hierarchical structure of the database, in the following format:
| - skeleton -- a triple-indexed dictionary
| - outer key -- table name
| - inner key -- column name
| - inner inner key -- one of the following:
| - ``primary_key`` -- boolean, whether column has been set as
primary key
| - ``index`` -- boolean, whether column has been set as index
| - ``unique`` -- boolean, whether column has been set as unique
| - ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``, ``'INTEGER'``,
``'REAL'``, or other user defined type
For example:
{'table1':{'col1':{'primary_key':False, 'index':True, 'unique': False,'sql':'REAL'}}}
INPUT:
EXAMPLES:
sage: GDB = GraphDatabase()
sage: GDB.get_skeleton() # slightly random output
{u'aut_grp': {u'aut_grp_size': {'index': True,
'unique': False,
'primary_key': False,
'sql': u'INTEGER'},
...
u'num_vertices': {'index': True,
'unique': False,
'primary_key': False,
'sql': u'INTEGER'}}}
Set the column col_name in table table_name to be an index, that is, a column set up to do quick searches on.
INPUT:
EXAMPLES:
sage: MonicPolys = SQLDatabase()
sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}})
sage: MonicPolys.make_index('n2','simon')
sage: MonicPolys.get_skeleton()
{'simon': {'n': {'index': True,
'primary_key': False,
'sql': 'INTEGER',
'unique': False},
'n2': {'index': True,
'primary_key': False,
'sql': 'INTEGER',
'unique': False}}}
Set the column col_name in table table_name to be a primary key.
A primary key is something like an index, but its main purpose is to link different tables together. This allows searches to be executed on multiple tables that represent maybe different data about the same objects.
NOTE:
Some SQL features, such as automatically incrementing primary key, require the full word 'INTEGER', not just 'INT'.
EXAMPLES:
sage: MonicPolys = SQLDatabase()
sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}})
sage: MonicPolys.make_primary_key('simon', 'n2')
sage: MonicPolys.get_skeleton()
{'simon': {'n': {'index': True,
'primary_key': False,
'sql': 'INTEGER',
'unique': False},
'n2': {'index': False,
'primary_key': True,
'sql': 'INTEGER',
'unique': True}}}
Set the column col_name in table table_name to store unique values.
NOTE:
This function only adds the requirement for entries in col_name to be unique, it does not change the values.
EXAMPLES:
sage: MonicPolys = SQLDatabase()
sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}, 'n2':{'sql':'INTEGER'}})
sage: MonicPolys.make_unique('simon', 'n2')
sage: MonicPolys.get_skeleton()
{'simon': {'n': {'index': True,
'primary_key': False,
'sql': 'INTEGER',
'unique': False},
'n2': {'index': False,
'primary_key': False,
'sql': 'INTEGER',
'unique': True}}}
Creates a SQLQuery on this database. For full class details, type SQLQuery? and press shift+enter.
EXAMPLES:
sage: D = SQLDatabase()
sage: D.create_table('simon', {'wolf':{'sql':'BOOLEAN'}, 'tag':{'sql':'INTEGER'}})
sage: q = D.query({'table_name':'simon', 'display_cols':['tag'], 'expression':['wolf','=',1]})
sage: q.get_query_string()
'SELECT simon.tag FROM simon WHERE simon.wolf = ?'
sage: q.__param_tuple__
('1',)
sage: q = D.query(query_string='SELECT tag FROM simon WHERE wolf=?',param_tuple=(1,))
sage: q.get_query_string()
'SELECT tag FROM simon WHERE wolf=?'
sage: q.__param_tuple__
('1',)
Renames the table table_name to new_name.
EXAMPLES:
sage: D = SQLDatabase()
sage: D.create_table('simon',{'col1':{'sql':'INTEGER'}})
sage: D.show('simon')
col1
--------------------
sage: D.rename_table('simon', 'lucy')
sage: D.show('simon')
Traceback (most recent call last):
...
RuntimeError: Failure to fetch data.
sage: D.show('lucy')
col1
--------------------
Save the database to the specified location.
EXAMPLES:
sage: MonicPolys = SQLDatabase()
sage: MonicPolys.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}})
sage: for n in range(20): MonicPolys.add_row('simon', (n,))
sage: tmp = tmp_dir() # replace with your own file path
sage: MonicPolys.save(tmp+'sage.db')
sage: N = SQLDatabase(tmp+'sage.db')
sage: N.show('simon')
n
--------------------
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Show an entire table from the database.
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_data('simon',[(0,0),(1,1),(1,2)])
sage: DB.show('simon')
a1 b2
----------------------------------------
0 0
1 1
1 2
Cleans the extra hard disk space used up by a database that has recently shrunk.
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_row('simon',(0,1))
sage: DB.add_data('simon',[(0,0),(1,1),(1,2)])
sage: DB.add_data('simon',[(0,0),(4,0),(5,1)], ['b2','a1'])
sage: DB.drop_column('simon','b2')
sage: DB.commit()
sage: DB.vacuum()
Bases: sage.structure.sage_object.SageObject
A query for a SQLite database.
INPUT:
database – a SQLDatabase object
query_dict – a dictionary specifying the query itself. The format is:
{'table_name':'tblname', 'display_cols':['col1', 'col2','col3'], 'expression': [col, operator, value]}
See the documentation of SQLDatabase for an introduction to using SQLite in Sage.
EXAMPLES:
sage: D = SQLDatabase()
sage: D.create_table('simon',{'a1':{'sql':'bool', 'primary_key':False}, 'b2':{'sql':'int'}})
sage: D.add_data('simon',[(0,0),(1,2),(2,4)])
sage: r = SQLQuery(D, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 3]})
sage: r.show()
a1
--------------------
0
1
Returns a copy of the query string.
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: SQLQuery(G,q,param).get_query_string()
'SELECT graph_id,graph6,num_vertices,num_edges FROM graph_data
WHERE graph_id<=(?) AND num_vertices=(?)'
sage: r = 'SELECT graph6 FROM graph_data WHERE num_vertices<=3'
sage: SQLQuery(G,r).get_query_string()
'SELECT graph6 FROM graph_data WHERE num_vertices<=3'
Returns a new SQLQuery that is the intersection of self and other. join_table and join_dict can be None iff the two queries only search one table in the database. All display columns will be concatenated in order: self display cols + other display cols.
INPUT:
other – the SQLQuery to intersect with
join_table – base table to join on (This table should have at least one column in each table to join on).
join_dict – a dictionary that represents the join structure for the new query. (Must include a mapping for all tables, including those previously joined in either query). Structure is given by:
{'join_table1':('corr_base_col1', 'col1'), 'join_table2':('corr_base_col2', 'col2')}
where join_table1 is to be joined with join_table on join_table.corr_base_col1 = join_table1.col1
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.create_table('lucy',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_data('simon', [(0,5),(1,4)])
sage: DB.add_data('lucy', [(1,1),(1,4)])
sage: q = SQLQuery(DB, {'table_name':'lucy', 'display_cols':['b2'], 'expression':['a1','=',1]})
sage: r = SQLQuery(DB, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 6]})
sage: s = q.intersect(r, 'simon', {'lucy':('a1','a1')})
sage: s.get_query_string()
'SELECT lucy.b2,simon.a1 FROM simon INNER JOIN lucy ON
simon.a1=lucy.a1 WHERE ( lucy.a1 = ? ) AND ( simon.b2 <= ? )'
sage: s.query_results()
[(1, 1), (4, 1)]
sage: s = q.intersect(r)
Traceback (most recent call last):
...
ValueError: Input queries query different tables but join
parameters are NoneType
sage: s.__query_string__ == q.__query_string__
False
sage: q.intersect(r, 'simon', {'lucy':('a1','a1')}, True)
sage: q.__query_string__ == s.__query_string__
True
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 = SQLQuery(G,q,param)
sage: Q.query_results()
[(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.query_results()
[(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.
KEYWORDS:
max_field_size – how wide each field can be
format_cols – a dictionary that allows the user to specify the format of a column’s output by supplying a function. The format of the dictionary is:
{'column_name':(lambda x: format_function(x))}
plot_cols – a dictionary that allows the user to specify that a plot should be drawn by the object generated by a data slice. Note that plot kwds are permitted. The dictionary format is:
{'column_name':((lambda x: plot_function(x)),**kwds)}
relabel_cols – a dictionary to specify a relabeling of column headers. The dictionary format is:
{'table_name':{'old_col_name':'new_col_name'}}
id_col – reference to a column that can be used as an object identifier for each row
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool', 'primary_key':False}, 'b2':{'sql':'int'}})
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: r.show()
a1
--------------------
0
1
1
sage: D = GraphDatabase()
sage: from sage.graphs.graph_database import valid_kwds, data_to_degseq
sage: relabel = {}
sage: for col in valid_kwds:
... relabel[col] = ' '.join([word.capitalize() for word in col.split('_')])
sage: q = GraphQuery(display_cols=['graph6','degree_sequence'], num_vertices=4)
sage: SQLQuery.show(q, format_cols={'degree_sequence':(lambda x,y: data_to_degseq(x,y))}, relabel_cols=relabel, id_col='graph6')
Graph6 Degree Sequence
----------------------------------------
C? [0, 0, 0, 0]
C@ [0, 0, 1, 1]
CB [0, 1, 1, 2]
CF [1, 1, 1, 3]
CJ [0, 2, 2, 2]
CK [1, 1, 1, 1]
CL [1, 1, 2, 2]
CN [1, 2, 2, 3]
C] [2, 2, 2, 2]
C^ [2, 2, 3, 3]
C~ [3, 3, 3, 3]
Returns a new SQLQuery that is the union of self and other. join_table and join_dict can be None iff the two queries only search one table in the database. All display columns will be concatenated in order: self display cols + other display cols.
INPUT:
other – the SQLQuery to union with
join_table – base table to join on (This table should have at least one column in each table to join on).
join_dict – a dictionary that represents the join structure for the new query. (Must include a mapping for all tables, including those previously joined in either query). Structure is given by:
{'join_table1':('corr_base_col1', 'col1'), 'join_table2':('corr_base_col2', 'col2')}
where join_table1` is to be joined with ``join_table on join_table.corr_base_col1=join_table1.col1
EXAMPLES:
sage: DB = SQLDatabase()
sage: DB.create_table('simon',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.create_table('lucy',{'a1':{'sql':'bool'}, 'b2':{'sql':'int'}})
sage: DB.add_data('simon', [(0,5),(1,4)])
sage: DB.add_data('lucy', [(1,1),(1,4)])
sage: q = SQLQuery(DB, {'table_name':'lucy', 'display_cols':['b2'], 'expression':['a1','=',1]})
sage: r = SQLQuery(DB, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 6]})
sage: s = q.union(r, 'simon', {'lucy':('a1','a1')})
sage: s.get_query_string()
'SELECT lucy.b2,simon.a1 FROM simon INNER JOIN lucy ON
simon.a1=lucy.a1 WHERE ( lucy.a1 = ? ) OR ( simon.b2 <= ? )'
sage: s.query_results()
[(1, 1), (4, 1)]
Constructs a database skeleton from the sql data. The skeleton data structure is a triple indexed dictionary of the following format:
| - skeleton -- a triple-indexed dictionary
| - outer key -- table name
| - inner key -- column name
| - inner inner key -- one of the following:
| - ``primary_key`` -- boolean, whether column has been set as
primary key
| - ``index`` -- boolean, whether column has been set as index
| - ``unique`` -- boolean, whether column has been set as unique
| - ``sql`` -- one of ``'TEXT'``, ``'BOOLEAN'``, ``'INTEGER'``,
``'REAL'``, or other user defined type
An example skeleton of a database with one table, that table with one column:
{'table1':{'col1':{'primary_key':False, 'unique': True, 'index':True, 'sql':'REAL'}}}
EXAMPLES:
sage: G = SQLDatabase(GraphDatabase().__dblocation__, False)
sage: from sage.databases.sql_db import construct_skeleton
sage: construct_skeleton(G).keys()
[u'aut_grp', u'degrees', u'spectrum', u'misc', u'graph_data']
Function to define regular expressions in pysqlite. Returns True if parameter item matches the regular expression parameter expr. Returns False otherwise (i.e.: no match).
REFERENCES:
| [GH2005] | Gerhard Haring. [Online] Available: http://lists.initd.org/pipermail/pysqlite/2005-November/000253.html |
EXAMPLES:
sage: from sage.databases.sql_db import regexp
sage: regexp('.s.*','cs')
True
sage: regexp('.s.*','ccs')
False
sage: regexp('.s.*','cscccc')
True
Verify that col_dict is in proper format, and return a dict with default values filled in. Proper format:
{'primary_key':False, 'index':False, 'unique': False, 'sql':'REAL'}
EXAMPLES:
sage: from sage.databases.sql_db import verify_column
sage: col = {'sql':'BOOLEAN'}
sage: verify_column(col)
{'index': False, 'primary_key': False, 'sql': 'BOOLEAN', 'unique': False}
sage: col = {'primary_key':True, 'sql':'INTEGER'}
sage: verify_column(col)
{'index': True, 'primary_key': True, 'sql': 'INTEGER', 'unique': True}
sage: verify_column({})
Traceback (most recent call last):
...
ValueError: SQL type must be declared, e.g. {'sql':'REAL'}.
Checks that operator is one of the allowed strings. Legal operators include the following strings:
EXAMPLES:
sage: from sage.databases.sql_db import verify_operator
sage: verify_operator('<=')
True
sage: verify_operator('regexp')
True
sage: verify_operator('is null')
True
sage: verify_operator('not_an_operator')
Traceback (most recent call last):
...
TypeError: not_an_operator is not a legal operator.
Verify that the specified type is one of the allowed strings.
EXAMPLES:
sage: from sage.databases.sql_db import verify_type
sage: verify_type('INT')
True
sage: verify_type('int')
True
sage: verify_type('float')
Traceback (most recent call last):
...
TypeError: float is not a legal type.