Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
""" 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 type
An 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
""" # FUTURE TODOs (Ignore for now): # - order by clause in query strings # - delete from query containing joins # - add data by column # - wrap sqlalchemy # - create query interface (with interact) # - allow kwds arguments to SQLQuery (like GraphQuery)
#***************************************************************************** # Copyright (C) 2011 R. Andrew Ohana <andrew.ohana@gmail.com> # Copyright (C) 2007 Emily A. Kirkman # Robert L. Miller # # Distributed under the terms of the GNU General Public License (GPL) # as published by the Free Software Foundation; either version 2 of # the License, or (at your option) any later version. # http://www.gnu.org/licenses/ #***************************************************************************** from __future__ import print_function
import sqlite3 as sqlite import os import re from sage.misc.all import tmp_filename from sage.structure.sage_object import SageObject
sqlite_keywords = ['ABORT','ACTION','ADD','AFTER','ALL','ALTER','ANALYZE', 'AND','AS','ASC','ATTACH','AUTOINCREMENT','BEFORE','BEGIN','BETWEEN','BY', 'CASCADE','CASE','CAST','CHECK','COLLATE','COLUMN','COMMIT','CONFLICT', 'CONSTRAINT','CREATE','CROSS','CURRENT_DATE','CURRENT_TIME', 'CURRENT_TIMESTAMP','DATABASE','DEFAULT','DEFERRABLE','DEFERRED','DELETE', 'DESC','DETACH','DISTINCT','DROP','EACH','ELSE','END','ESCAPE','EXCEPT', 'EXCLUSIVE','EXISTS','EXPLAIN','FAIL','FOR','FOREIGN','FROM','FULL', 'GLOB','GROUP','HAVING','IF','IGNORE','IMMEDIATE','IN','INDEX','INDEXED', 'INITIALLY','INNER','INSERT','INSTEAD','INTERSECT','INTO','IS','ISNULL', 'JOIN','KEY','LEFT','LIKE','LIMIT','MATCH','NATURAL','NO','NOT','NOTNULL', 'NULL','OF','OFFSET','ON','OR','ORDER','OUTER','PLAN','PRAGMA','PRIMARY', 'QUERY','RAISE','REFERENCES','REGEXP','REINDEX','RELEASE','RENAME', 'REPLACE','RESTRICT','RIGHT','ROLLBACK','ROW','SAVEPOINT','SELECT','SET', 'TABLE','TEMP','TEMPORARY','THEN','TO','TRANSACTION','TRIGGER','UNION', 'UNIQUE','UPDATE','USING','VACUUM','VALUES','VIEW','VIRTUAL','WHEN', 'WHERE']
def regexp(expr, item): """ 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:
- [Ha2005]_
EXAMPLES::
sage: from sage.databases.sql_db import regexp sage: regexp('.s.*','cs') True sage: regexp('.s.*','ccs') False sage: regexp('.s.*','cscccc') True """
def verify_type(type): """ 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.
"""
def verify_column(col_dict): """ 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'}. """
def verify_operator(operator): """ Checks that ``operator`` is 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. """
def 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: construct_skeleton(G).keys() [u'aut_grp', u'degrees', u'spectrum', u'misc', u'graph_data'] """ typ = u'NOTYPE' else: 'primary_key':(col[5]!=0), 'index':(col[5]!=0), 'unique': False} os.path.join(GRAPHS_DATA_DIR,'graphs.db'): else: else:
p = 0 def _create_print_table(cur, col_titles, **kwds): """ Creates a nice printable table from the cursor given with the given column titles.
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
- ``html_table`` -- boolean that if True creates an html table instead of a print table. Always set to True in the notebook.
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: from sage.databases.sql_db import _create_print_table sage: cur = r.__database__.__connection__.cursor() sage: exe = cur.execute(r.__query_string__, r.__param_tuple__) sage: _create_print_table(cur, [des[0] for des in cur.description]) 'a1 \n--------------------\n0 \n1 \n1 ' """
else 20 else None
except KeyError: continue global p global p plot = pcol_map[p%len(pcol_map)](row[index]) plot.save('%d.png'%p, figsize=[1,1]) field_val = ' <td bgcolor=white align=center> ' \ + '%s <br> <img src="cell://%d.png"> '%(row[index],p) \ + '</td>\n' p += 1 else: + 'command line.') else: field_val = fcol_map[f](row[index]) else: else: field_val = ' <td bgcolor=white align=center> ' \ + str(field_val) + ' </td>\n' else:
# Notebook Version ret = '<html><!--notruncate-->\n' ret += ' <table bgcolor=lightgrey cellpadding=0>\n' ret += ' <tr>\n <td bgcolor=white align=center> ' ret += (' </td>\n <td bgcolor=white ' \ + 'align=center> ').join(col_titles) ret += ' </td>\n </tr>\n' ret += '\n'.join([' <tr>\n ' + row_str(row, True) + ' </tr>' \ for row in cur]) ret += '\n </table>\n</html>' else: # Command Prompt Version
class SQLQuery(SageObject): def __init__(self, database, *args, **kwds): """ 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]}
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 ``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 """ raise TypeError('%s is not a valid SQLDatabase'%database) 'query_string' in kwds) or ('query_dict' in kwds and\ ('param_tuple' in kwds or 'query_string' in kwds)): raise ValueError('Query must be constructed with either a ' \ + 'dictionary or a string and tuple')
else: else:
raise ValueError("Database has no table" \ + str(query_dict['table_name']) + ".") raise ValueError("Table has no column %s."%column) raise ValueError("Table has no column " \ + str(query_dict['expression'][0]) + ".")
+ '%s.%s '%(table_name, query_dict['expression'][0]) \ + '%s ?'%query_dict['expression'][1] else: for x in query_dict['display_cols']] + ', '.join(query_dict['display_cols']) + ' FROM ' \ + '%s WHERE %s.'%(table_name, table_name) \ + '%s '%query_dict['expression'][0] \ + '%s ?'%query_dict['expression'][1] else: self.__query_dict__ = {} self.__param_tuple__ = tuple() self.__query_string__ = ''
def __repr__(self): """ Overrides the print output to display useful info regarding the query.
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) Query for sql database: ...graphs.db Query string: SELECT graph_id,graph6,num_vertices,num_edges FROM graph_data WHERE graph_id<=(?) AND num_vertices=(?) Parameter tuple: ('22', '5') sage: r = 'SELECT graph6 FROM graph_data WHERE num_vertices<=3' sage: SQLQuery(G,r) Query for sql database: ...graphs.db Query string: SELECT graph6 FROM graph_data WHERE num_vertices<=3 """ return 'Empty query on %s.'%self.__database__.__dblocation__ + "\nQuery string: %s"%self.__query_string__ \ + ("\nParameter tuple: %s"%str(self.__param_tuple__) if \ self.__param_tuple__ else "")
def get_query_string(self): """ 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' """
def __iter__(self): """ Returns an iterator over the results of the query.
EXAMPLES::
sage: G = GraphDatabase() sage: q = 'SELECT graph_id,graph6 FROM graph_data WHERE num_vertices=(?)' sage: param = (5,) sage: Q = SQLQuery(G,q,param) sage: it = Q.__iter__() sage: next(it) (18, u'D??') sage: next(it) (19, u'D?C') sage: skip = [next(it) for _ in range(15)] sage: next(it) (35, u'DBk') """ except sqlite.OperationalError: raise RuntimeError('Failure to fetch query.')
def query_results(self): """ 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~',)] """
def show(self, **kwds): """ 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] """
except Exception: raise RuntimeError('Failure to fetch query.')
**kwds))
def __copy__(self): """ Returns a copy of itself.
EXAMPLES::
sage: G = GraphDatabase() sage: Q = SQLQuery(G, {'table_name':'graph_data', 'display_cols':['graph_id','graph6','num_vertices'], 'expression':['num_edges','<',3]}) sage: R = copy(Q) sage: R.__query_string__ = '' sage: Q.__query_string__ == '' False """
def intersect(self, other, join_table=None, join_dict=None, \ in_place=False): """ 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 """ raise RuntimeError('Queries must be constructed using a ' \ + 'dictionary in order to be intersected.') raise TypeError('Queries %s and %s must be '%(self, other) \ + 'attached to the same database.')
else: else: join_dict, 'AND')
def _merge_queries(self, other, ret, join_table, join_dict, operator): """ The query ``ret`` is set to a new ``SQLQuery`` that is combines self and other through ``operator``. The other arguments are the same as ``intersect`` and ``union``.
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._merge_queries(r, copy(q), 'simon', {'lucy':('a1','a1')}, 'OR') 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)] """ or re.search(pattern, other.__query_string__): raise TypeError('Input queries have joins but join ' \ + 'parameters are NoneType') + 'join parameters are NoneType')
# inner join clause + '%s=%s.'%(join_dict[table][0], table) \ + '%s '%join_dict[table][1] + 'WHERE ', self.__query_string__)
# concatenate display cols
# concatenate where clause other.__query_string__)
def union(self, other, join_table=None, join_dict=None, in_place=False): """ 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)] """ raise RuntimeError('Queries must be constructed using a ' \ + 'dictionary in order to be unioned.') raise TypeError('Queries %s and %s must be '%(self, other) \ + 'attached to the same database.')
if self.__query_string__ and other.__query_string__: self._merge_queries(other, self, join_table, join_dict, 'OR') else: join_dict, 'OR')
class SQLDatabase(SageObject): def __init__(self, filename=None, read_only=None, skeleton=None): r""" 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. """ raise ValueError('Please enter a valid database path (file name ' \ + '%s does not end in .db).'%filename)
check_same_thread=False) # this is to avoid the multiple thread problem with dsage: # pysqlite does not trust multiple threads for the same connection
# construct skeleton (from provided database)
# add bones from new skeleton to database, # without changing existing structure for table in skeleton: if table not in self.__skeleton__: self.create_table(table, skeleton[table]) else: for column in skeleton[table]: if column not in self.__skeleton__[table]: self.add_column(table, column, \ skeleton[table][column]) else: print('Column attributes were ignored for ' \ 'table {}, column {} -- column is ' \ 'already in table.'.format(table, column)) raise RuntimeError('Cannot update skeleton of a read only ' \ + 'database.')
def __repr__(self): """ Overrides the print output to display useful info regarding the database.
EXAMPLES::
sage: replace_with_filepath = tmp_dir() + 'test.db' sage: SD = SQLDatabase(replace_with_filepath, False) sage: SD.create_table('simon', {'n':{'sql':'INTEGER', 'index':True}}) sage: print(SD) table simon: column n: index: True; unique: False; primary_key: False; sql: INTEGER; """ + str(self.__skeleton__[table][column][data]) + '; '
def __copy__(self): """ Returns an instance of ``SQLDatabase`` that points to a copy database, and allows modification.
EXAMPLES::
sage: DB = SQLDatabase() sage: DB.create_table('lucy',{'id':{'sql':'INTEGER', 'primary_key':True, 'index':True}, 'a1':{'sql':'bool'}, 'b2':{'sql':'int', 'primary_key':False}}) sage: DB.add_rows('lucy', [(0,1,1),(1,1,4),(2,0,7),(3,1,384), (4,1,978932)],['id','a1','b2']) sage: d = copy(DB) sage: d == DB False sage: d.show('lucy') a1 id b2 ------------------------------------------------------------ 1 0 1 1 1 4 0 2 7 1 3 384 1 4 978932 sage: DB.show('lucy') a1 id b2 ------------------------------------------------------------ 1 0 1 1 1 4 0 2 7 1 3 384 1 4 978932 """ # copy .db file
def save(self, 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 """
def get_skeleton(self, 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'}}} """ d = construct_skeleton(self) if d == self.__skeleton__: return d else: raise RuntimeError("Skeleton structure is out of whack!")
def query(self, *args, **kwds): """ 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',) """
__call__ = query
def show(self, 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 """ **kwds))
def get_cursor(self, 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)] """ if ignore_warning is None: ignore_warning = self.ignore_warnings if not ignore_warning: import warnings warnings.warn('Database is read only, using the cursor can ' \ + 'alter the stored data. Set self.ignore_warnings to ' \ + 'True in order to mute future warnings.', RuntimeWarning)
def get_connection(self, 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 """ + 'can alter the stored data. Set self.ignore_warnings ' \ + 'to True in order to mute future warnings.', \ RuntimeWarning)
def create_table(self, 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 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 ------------------------------------------------------------ """ raise RuntimeError('Cannot add table to a read only database.') raise ValueError('Database already has a table named' \ + '%s.'%table_name) raise ValueError('Table names cannot contain spaces.') raise ValueError('Table names cannot be a SQLite keyword.') raise ValueError("Column names cannot contain 'sqlite'.") raise ValueError('Column names cannot be a SQLite keyword.') typ = '' statement.append(col + ' ' + typ + ' UNIQUE') else: col) + ' ON %s(%s);\n'%(table_name, col)
def add_column(self, table_name, col_name, col_dict, default='NULL'): """ 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 """ raise RuntimeError('Cannot add columns to a read only database.') # Check input: raise ValueError("Column names cannot contain 'sqlite'.") raise ValueError("Column names cannot be SQLite keywords.") raise ValueError("Database has no table %s."%table_name) raise ValueError("Table %s already has column %s."%(table_name,col_name))
# Update the skeleton:
except sqlite.Error as e: # delete added column from skeleton self.__skeleton__[table_name].pop(col_name)
print('A sqlite error occurred: ', e.args[0])
def _rebuild_table(self, table_name, col_name=None, default=''): """ Rebuilds the table ``table_name`` adding column ``col_name`` if not ``None``. If a new column is added, each rows' value is set to ``default``.
Used in the methods ``add_column``, ``drop_column``, ``make_unique``, ``drop_unique``, ``make_primary_key``, and ``drop_primary_key`` to get around the limitations of SQLite's ``ALTER TABLE``. We have set up the creation of a temporary database in this method. Please feel free to improve on this by sending a patch or suggestion.
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.show('simon') n -------------------- 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 sage: MonicPolys._rebuild_table('simon') 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 """ # gather column names and attributes for new table
# Silly SQLite -- we have to make a temp table to hold info... CREATE TEMPORARY TABLE spam(%s); INSERT INTO spam SELECT %s FROM %s; DROP TABLE %s; CREATE TABLE %s (%s); """%(cols_attr, original, table_name, table_name, table_name, cols_attr)) # Update indices in new table col) + '%s(%s);\n'%(table_name, col) for col in \ self.__skeleton__[table_name] if \ self.__skeleton__[table_name][col]['index'] and not \ self.__skeleton__[table_name][col]['primary_key']])
# Now we can plop our data into the *new* table: INSERT INTO %s SELECT %s FROM spam; DROP TABLE spam; """%(table_name, cols))
def drop_column(self, table_name, col_name): """ 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 """ raise RuntimeError('Cannot drop columns in a read only database.') # Check input: raise ValueError("Database has no table %s."%table_name) raise ValueError("Table %s has no column %s."%(table_name,col_name))
# Update the skeleton:
def rename_table(self, table_name, new_name): """ 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 --------------------
""" raise RuntimeError('Cannot rename tables in a read only database.') # Check input: raise ValueError('Database has no table %s.'%table_name) raise ValueError('Database already has table %s.'%new_name)
+ new_name)
# Update skeleton:
def drop_table(self, table_name): """ Delete table ``table_name`` from 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() {} """ + 'database.') raise ValueError("Database has no table %s."%table_name)
# Update Skeleton
def drop_data_from_table(self, 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 -------------------- """ raise RuntimeError('Cannot remove data from a read only database.') raise ValueError("Database has no table %s."%table_name)
def make_index(self, col_name, table_name, unique=False): """ 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:
- ``col_name`` -- a string - ``table_name`` -- a string - ``unique`` -- 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}}} """ raise RuntimeError('Cannot modify a read only database.') raise ValueError("Database has no table %s."%table_name) raise ValueError("Table %s has no column %s."%(table_name,col_name))
index_string = 'CREATE UNIQUE INDEX ' + col_name + ' ON ' \ + table_name + ' (' + col_name + ')' else: + ' (' + col_name + ')'
# Update Skeleton self.__skeleton[table_name][col_name]['unique'] = True
def drop_index(self, table_name, index_name): """ 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}}} """ raise RuntimeError('Cannot modify a read only database.') raise ValueError("Database has no table %s."%table_name) raise ValueError("Table %s has no column %s."%(table,index_name)) return # silently
# Update Skeleton
def make_unique(self, table_name, col_name): """ 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}}}
""" raise RuntimeError('Cannot modify a read only database') raise ValueError("Database has no table %s."%table_name) raise ValueError("Table %s has no column %s."%(table_name, col_name))
# Update the skeleton:
def drop_unique(self, table_name, col_name): """ 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}}} """ raise RuntimeError('Cannot modify a read only database.') raise ValueError("Database has no table %s."%table_name) raise ValueError("Table %s has no column %s."%(table_name, col_name)) raise ValueError("Primary keys must be unique.")
# Update the skeleton:
def make_primary_key(self, table_name, col_name): """ 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}}} """ raise RuntimeError('Cannot modify a read only database.') raise ValueError("Database has no table %s."%table_name) raise ValueError("Table %s has no column %s."%(table_name, col_name))
# Update the skeleton:
def drop_primary_key(self, table_name, col_name): """ 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}}} """ raise RuntimeError('Cannot modify a read only database.') raise ValueError("Database has no table %s."%table_name) raise ValueError("Table %s has no column %s."%(table_name,col_name)) return # silently
# Update the skeleton:
def add_row(self, table_name, values, entry_order=None): """ 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)] """
def delete_rows(self, query): """ 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:
- ``query`` -- a ``SQLQuery`` (Delete the rows returned when query is run).
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 """ raise RuntimeError('Cannot delete rows from a read only database.') # Check query is associated with this database raise TypeError('%s is not a valid SQLQuery'%query) raise ValueError('%s is not associated to this database.'%query) raise ValueError('%s is not a valid query. Can only '%query \ + 'delete from one table at a time.')
query.__query_string__)
except Exception: raise RuntimeError('Failure to complete delete. Check your data.')
def add_rows(self, 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)] """ raise RuntimeError('Cannot add rows to read only database.')
+ str(tuple(entry_order)) + ' VALUES ' + quest, strows) else: + ' VALUES ' + quest, strows)
add_data = add_rows
def vacuum(self): """ 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() """
def commit(self): """ 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() """ raise RuntimeError("Cannot commit read only database.") # Not sure why this throws an exception - but without it, # the changes are not committed so it is necessary. |