Non-Conformant Column Names

By default, any operation that interfaces with column names will only accept names that conform to the SQLite names standard. This means that column names must begin with a letter, and can only contain letters, numbers, and underscores. If you try to access a column that does not conform to this standard, you will get an error.

>>> from dbastable import SQLDatabase
>>> db = SQLDatabase()
>>> db.add_table('my_table')
>>> db.add_column('my_table', '1test')  
Traceback (most recent call last):
...
ValueError: Invalid column name: 1test
>>> db.add_column('my_table', 'test column')  
Traceback (most recent call last):
...
ValueError: Invalid column name: test column
>>> db.add_column('my_table', 'test!with!exclamation!points')  
Traceback (most recent call last):
...
ValueError: Invalid column name: test!with!exclamation!points

However, you can force the database to accept these non-conformant column names by setting the allow_b32_colnames parameter to True when you create the database. This will enable the non-conformant names to be encoded to Base32 encoding, and decoded back to their original names when you access them. This name encoding will be done by base64.b32encode and base64.b32decode from the Python standard library automatically.

When using this, the real column name will be __b32__<encoded_name>, already striped by === padding added by the encoder. This will make the string unreadeble by humans, but it will be decoded back to the original name when you access it. For example, if you have a column named 1test, it will be encoded to __b32__GF2GK43U, and if you have a column named test column, it will be encoded to __b32__ORSXG5BAMNXWY5LNNY. So, if you want to access the database outside of Python, it is recomended to do not use this feature. Also, everytime you use this database, remember to set the allow_b32_colnames parameter to True to properly access the column names.

If you are using SQLDatabase, the access of the columns will be fully transparent to the user and you will not need to worry about the encoding and decoding of the column names.

>>> db = SQLDatabase(allow_b32_colnames=True)
>>> db.add_table('my_table')
>>> db.add_column('my_table', '1test', data=[1, 2, 3])
>>> db.add_column('my_table', 'test column', data=[1, 2, 3])
>>> db.add_column('my_table', 'test!with!exclamation!points', data=[1, 2, 3])
>>> db.column_names('my_table')
['1test', 'test column', 'test!with!exclamation!points']

If you want to access the original column names, you can use the do_not_decode parameter of column_names to get the encoded names.

>>> db.column_names('my_table', do_not_decode=True)
['__b32__GF2GK43U',
 '__b32__ORSXG5BAMNXWY5LNNY',
 '__b32__ORSXG5BBO5UXI2BBMV4GG3DBNVQXI2LPNYQXA33JNZ2HG']

If the column does not need to be encoded, it will not be encoded.

>>> db.add_column('my_table', 'test')
>>> db.column_names('my_table')
['1test', 'test column', 'test!with!exclamation!points', 'test']
>>> db.column_names('my_table', do_not_decode=True)
['__b32__GF2GK43U',
 '__b32__ORSXG5BAMNXWY5LNNY',
 '__b32__ORSXG5BBO5UXI2BBMV4GG3DBNVQXI2LPNYQXA33JNZ2HG',
 'test']

Any column access method will work ONLY with the original names, so you do not need to worry about the encoded names.

>>> db['my_table']['1test'].values
[1, 2, 3]
>>> db.get_column('my_table', 'test column').values
[1, 2, 3]
>>> db.select('my_table', columns=['test!with!exclamation!points'])
[(1,), (2,), (3,)]

Where statements also use original column names, and not the encoded ones.

>>> from dbastable import Where
>>> db.select('my_table', where={'test!with!exclamation!points': 1})
[(1, 1, 1, None)]
>>> db.select('my_table', where=Where('1test', '>', 1))
[(2, 2, 2, None), (3, 3, 3, None)]