Relational (sqlite) Databases Module¶
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:
- R. Andrew Ohana (2011-07-16): refactored and rewrote most of the code; merged the Generic classes into the non-Generic versions; changed the skeleton format to include a boolean indicating whether the column stores unique keys; changed the index names so as to avoid potential ambiguity
- Emily A. Kirkman (2008-09-20): added functionality to generate plots and reformat output in show
- Emily A. Kirkman and Robert L. Miller (2007-06-17): initial version
-
class
sage.databases.sql_db.SQLDatabase(filename=None, read_only=None, skeleton=None)¶ Bases:
sage.structure.sage_object.SageObjectA SQL Database object corresponding to a database file.
INPUT:
filename– a stringskeleton– 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
SQLDatabaseclass 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), aSQLDatabasemay not be what you are looking for. The strength ofSQLDatabasesis 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: from collections import OrderedDict sage: table_skeleton = OrderedDict([ ....: ('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') graph6 vertices edges ------------------------------------------------------------
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',(G.graph6_string(), 0, 0)) sage: D.show('simon') graph6 vertices edges ------------------------------------------------------------ ? 0 0
Next, add the graph on one vertex.:
sage: G.add_vertex() 0 sage: D.add_row('simon',(G.graph6_string(), 1, 0)) sage: D.show('simon') graph6 vertices edges ------------------------------------------------------------ ? 0 0 @ 1 0
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(algorithm='sage') ....: if g not in labels[i]: ....: labels[i].append(g) ....: D.add_row('simon', (g.graph6_string(), g.order(), g.size())) sage: D.show('simon') graph6 vertices edges ------------------------------------------------------------ ? 0 0 @ 1 0 A? 2 0 A_ 2 1 B? 3 0 BG 3 1 BW 3 2 Bw 3 3 C? 4 0 C@ 4 1 CB 4 2 CF 4 3 CJ 4 3 CK 4 2 CL 4 3 CN 4 4 C] 4 4 C^ 4 5 C~ 4 6
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_colsare 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') graph6 vertices edges ------------------------------------------------------------ ? 0 0 @ 1 0 A? 2 0 A_ 2 1 B? 3 0 BG 3 1 BW 3 2 Bw 3 3 C? 4 0 C@ 4 1 CB 4 2 CF 4 3 CJ 4 3 CK 4 2 CL 4 3 CN 4 4 C] 4 4 C^ 4 5 C~ 4 6 sage: E.drop_table('simon') Traceback (most recent call last): ... RuntimeError: Cannot drop tables from a read only database.
-
add_column(table_name, col_name, col_dict, default='NULL')¶ Add a column named
col_nameto tabletable_name, whose data types are described bycol_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 keyindex– boolean, whether column has been set as indexunique– boolean, weather column has been set as uniquesql– one of'TEXT','BOOLEAN','INTEGER','REAL', or other user defined type
EXAMPLES:
sage: from collections import OrderedDict sage: MonicPolys = SQLDatabase() sage: MonicPolys.create_table('simon', OrderedDict([('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 n_squared ---------------------------------------- 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 0
-
add_data(table_name, rows, entry_order=None)¶ INPUT:
rows– a list of tuples that represent one row of data to add (types should match col types in order)entry_order– an ordered list or tuple overrides normal order with user defined order
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_row(table_name, values, entry_order=None)¶ Add the row described by
valuesto the tabletable_name. Values should be a tuple, of same length and order as columns in given table.NOTE:
If
valuesis 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)]
-
add_rows(table_name, rows, entry_order=None)¶ INPUT:
rows– a list of tuples that represent one row of data to add (types should match col types in order)entry_order– an ordered list or tuple overrides normal order with user defined order
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)]
-
commit()¶ 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()
-
create_table(table_name, table_skeleton)¶ 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 stringtable_skeleton– a double-indexed dictionaryouter key – column name
inner key – one of the following:
primary_key– boolean, whether column has been set asprimary keyindex– boolean, whether column has been set as indexunique– boolean, whether column has been set as uniquesql– 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: from collections import OrderedDict sage: D = SQLDatabase() sage: table_skeleton = OrderedDict([ ....: ('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') graph6 vertices edges ------------------------------------------------------------
-
delete_rows(query)¶ Uses a
SQLQueryinstance to modify (delete rows from) the database.SQLQuerymust 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 todelete_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:
query– aSQLQuery(Delete the rows returned when query is run).
EXAMPLES:
sage: from collections import OrderedDict sage: DB = SQLDatabase() sage: DB.create_table('lucy', OrderedDict([ ....: ('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') id a1 b2 ------------------------------------------------------------ 0 1 1 1 1 4 2 0 7 3 1 384 4 1 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') id a1 b2 ------------------------------------------------------------ 0 1 1 1 1 4 2 0 7
-
drop_column(table_name, col_name)¶ Drop the column
col_namefrom tabletable_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
-
drop_data_from_table(table_name)¶ 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 --------------------
-
drop_index(table_name, index_name)¶ Set the column
index_namein tabletable_nameto not be an index. Seemake_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}}}
-
drop_primary_key(table_name, col_name)¶ Set the column
col_namein tabletable_namenot 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}}}
-
drop_table(table_name)¶ Delete table
table_namefrom database.INPUT:
table_name– a string
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() {}
-
drop_unique(table_name, col_name)¶ Set the column
col_namein tabletable_namenot store unique values.NOTE:
This function only removes the requirement for entries in
col_nameto 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}}}
-
get_connection(ignore_warning=None)¶ 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
-
get_cursor(ignore_warning=None)¶ 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)]
-
get_skeleton(check=False)¶ 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:
check– if True, checks to make sure the database’s actual structure matches the skeleton on record.
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'}}}
-
make_index(col_name, table_name, unique=False)¶ Set the column
col_namein tabletable_nameto be an index, that is, a column set up to do quick searches on.INPUT:
col_name– a stringtable_name– a stringunique– requires that there are no multiple entries in the column, makes searching faster
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}}}
-
make_primary_key(table_name, col_name)¶ Set the column
col_namein tabletable_nameto 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}}}
-
make_unique(table_name, col_name)¶ Set the column
col_namein tabletable_nameto store unique values.NOTE:
This function only adds the requirement for entries in
col_nameto 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}}}
-
query(*args, **kwds)¶ Creates a
SQLQueryon this database. For full class details, typeSQLQuery?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',)
-
rename_table(table_name, new_name)¶ Renames the table
table_nametonew_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(filename)¶ 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(table_name, **kwds)¶ 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
-
vacuum()¶ 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()
-
class
sage.databases.sql_db.SQLQuery(database, *args, **kwds)¶ Bases:
sage.structure.sage_object.SageObjectA query for a SQLite database.
INPUT:
database– a SQLDatabase objectquery_dict– a dictionary specifying the query itself. The format is:{'table_name':'tblname', 'display_cols':['col1', 'col2','col3'], 'expression': [col, operator, value]}
- NOTE:
- Every SQL type we are using is ultimately represented as a string, so if you wish to save actual strings to a database, you actually need to do something like: ‘“value”’.
See the documentation of
SQLDatabasefor 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
Test that trac ticket #27562 is fixed:
sage: D = SQLDatabase() sage: r = SQLQuery(D, {'table_name':'simon', 'display_cols':['a1'], 'expression':['b2','<=', 3]}) Traceback (most recent call last): ... ValueError: Database has no table simon sage: D.create_table('simon',{'a1':{'sql':'bool', 'primary_key':False}, 'b2':{'sql':'int'}}) sage: D.create_table('simon',{'a1':{'sql':'bool', 'primary_key':False}, 'b2':{'sql':'int'}}) Traceback (most recent call last): ... ValueError: Database already has a table named simon sage: SQLQuery(D, {'table_name':'simon', 'display_cols':['a1'], 'expression':['c1','>',2]}) Traceback (most recent call last): ... ValueError: Table has no column c1
-
get_query_string()¶ 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'
-
intersect(other, join_table=None, join_dict=None, in_place=False)¶ Returns a new
SQLQuerythat is the intersection ofselfandother.join_tableandjoin_dictcan beNoneiff 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– theSQLQueryto intersect withjoin_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_table1is to be joined withjoin_tableonjoin_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
-
query_results()¶ 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~',)]
-
show(**kwds)¶ Displays the result of the query in table format.
KEYWORDS:
max_field_size– how wide each field can beformat_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]
-
union(other, join_table=None, join_dict=None, in_place=False)¶ Returns a new
SQLQuerythat is the union of self and other.join_tableandjoin_dictcan beNoneiff 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– theSQLQueryto union withjoin_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_tableonjoin_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)]
-
sage.databases.sql_db.construct_skeleton(database)¶ 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: sorted(construct_skeleton(G)) [u'aut_grp', u'degrees', u'graph_data', u'misc', u'spectrum']
-
sage.databases.sql_db.regexp(expr, item)¶ Function to define regular expressions in pysqlite. Returns
Trueif parameteritemmatches the regular expression parameterexpr. ReturnsFalseotherwise (i.e.: no match).REFERENCES:
EXAMPLES:
sage: from sage.databases.sql_db import regexp sage: regexp('.s.*','cs') True sage: regexp('.s.*','ccs') False sage: regexp('.s.*','cscccc') True
-
sage.databases.sql_db.verify_column(col_dict)¶ Verify that
col_dictis 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'}.
-
sage.databases.sql_db.verify_operator(operator)¶ Checks that
operatoris one of the allowed strings. Legal operators include the following strings:- ‘=’
- ‘<=’
- ‘>=’
- ‘<’
- ‘>’
- ‘<>’
- ‘like’
- ‘regexp’
- ‘is null’
- ‘is not null’
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.
-
sage.databases.sql_db.verify_type(type)¶ Verify that the specified
typeis 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.