Viewer Classes

The planned interaction with the database was only possible by the creation of 3 viewer classes. This classes act like a view of Numpy arrays, but interfacing with all the SQL commands sqlite3 needs. So, they make possible the use of all __getitem__ and __setitem__ methods ([] operator) inside a database. ` These classes do not store any data itself. Instead, they just store references of rows, columns or tables to query and access the database. So, any changes in the data inside the database also affects the data returned by the viewer classes.

Also, it is not recommended to create them manually. Let the SQLDatabase create them for you.

SQLTable

SQLTable is a class that can be used to view and manage a single table inside the database. It is return with [table] operator of SQLDatabase or with SQLDatabase.get_table method.

>>> from dbastable import SQLDatabase
>>> db = SQLDatabase()
>>> db.add_table('table1', columns=['name', 'age'],
...              data=[['John', 20], ['Mary', 30]])
>>> table = db['table1']
>>> print(table)
SQLTable 'table1' in database 'None':(2 columns x 2 rows)
name age
---- ---
John  20
Mary  30

It perform almost any important operation of SQLDatabase, but dismissing the table argument in all methods that methods. For example, add_rows, delete_row, add_column, delete_column, select, index_of and more. More detais about these functions in the SQLTable API documentation.

One important thing to mention is how [] operator work in this class. It can be used to access a single row, column or cell inside the table. If a int number is used in the operator, it will return a SQLRow object. If a str is used, it will return a SQLColumn object. If a tuple of int and str is used, it will return a single cell value. No other combination is supported, so you cannot access multiple rows or columns at the same time.

>>> # int returns a row
>>> print(table[0])
SQLRow 0 in table 'table1' {'name': 'John', 'age': 20}
>>> # str returns a column
>>> print(table['name'])
SQLColumn 'name' in table 'table1' (2 rows)
>>> # tuple of int and str returns a cell
>>> print(table[0, 'name'])
John
>>> print(table['name', 1])
Mary

SQLRow and SQLColumn

SQLRow and SQLColumn are classes that can be used to access and change data of a single row or column inside the database. They are able to query data from the database, change data of a single cell, but they don’t inherit methods like add_rows or delete_row. So you can’t change the structure of the table using these classes. For this, you need to use SQLTable.

They are returned with [row] and [column] operators of SQLTable or with SQLTable.get_row and SQLTable.get_column methods.

>>> row = table[0]
>>> print(row)
SQLRow 0 in table 'table1' {'name': 'John', 'age': 20}
>>> column = table['name']
>>> print(column)
SQLColumn 'name' in table 'table1' (2 rows)

>>> print(table.get_row(0))
SQLRow 0 in table 'table1' {'name': 'John', 'age': 20}
>>> print(table.get_column('name'))
SQLColumn 'name' in table 'table1' (2 rows)

Also, they can be directly created with get_row and get_column methods.

>>> row = db.get_row('table1', 0)
>>> print(row)
SQLRow 0 in table 'table1' {'name': 'John', 'age': 20}
>>> column = db.get_column('table1', 'name')
>>> print(column)
SQLColumn 'name' in table 'table1' (2 rows)

Both classes have a values property, which returns a list containing copies of the data from the given row or column. It is important to remember that the values returned are not linked anymore with the original database and any change in this list will not imply in changes in database.

>>> print(row.values)
('John', 20)
>>> print(column.values)
['John', 'Mary']

Also, both methods are iterable using for loops. The iteration is always performed in the order of the table.

>>> for value in row:
...     print(value)
John
20
>>> for value in column:
...     print(value)
John
Mary

The usage of in comparison statment is also possible. It will return True if the given value is in the row or column values.

>>> print('Mary' in row)
False
>>> print('Mary' in column)
True

SQLRow Specifics

SQLRow have another special features that make it act like a dict. First of all, you can create a dictionary from it using as_dict method.

>>> print(row.as_dict())
{'name': 'John', 'age': 20}

Also, it is iterable just like dicts using keys and items properties.

>>> for key in row.keys():
...     print(key)
name
age
>>> for key, value in row.items():
...     print(key, value)
name John
age 20