PyTables hints for SQL users

[Ivan Vilata i Balaguer]

Hi all,

We've published a new tutorial/cookbook page in pytables.org which is
intended as a gentle guide to PyTables for users of SQL and relational
databases. It describes the equivalent way in PyTables to perform most
basic operations under SQL. You can find it at:

http://www.pytables.org/moin/HintsForSQLUsers

The current contents of the document are:

1. Creating a new database
1. A note on concurrency under PyTables
2. Creating a table
1. Table descriptions
2. Column type declarations
3. Using a description
3. Creating an index
4. Altering a table
5. Dropping a table
6. Inserting data
1. A note on transactions
7. Updating data
8. Deleting data
9. Reading data
1. Iterating over rows
2. Reading rows at once
10. Selecting data
1. Iterating over selected rows
2. Reading seleted rows at once
3. Getting the coordinates of selected rows
4. A note on table joins
11. Summary of row selection methods
12. Sorting the results of a selection
13. Grouping the results of a selection

Since it's a wiki page, you can add your own recipes and hints. If you
think the page is missing some content you may want to see added, just
request it in the users mailing list or create an empty section in the
page. We hope that this page proves itself useful to new users coming
from the relational world.

We thank you for your contributions! Best regards,

::

Ivan Vilata i Balaguer >qo< http://www.carabos.com/
Cárabos Coop. V. V V Enjoy Data
""

[Paddy Ganut]

Hi, I arrived here via the pytables hints for sql users which I found very
useful, thanks. There is a section on sorting that is blank with an
invitation for someone to add something. I don't seem to be able to get
access to the wiki but maybe someone who can update it will add a bit on
sorting. This is my contribution, the critical bit being:
r_array = table.read_where('(x>1018.0) & (x<1020.0) & (y>390.0) & (y<400.0)
& (z>100.0)')
# if results need to be written back might need to do something like
# r_coords = table.get_where_list('(x>1010.0) & (x<...### etc.
# r_array = table[r_coords]
# ... after modification of r_array ...
# table[r_coords] = r_array

# sorting with numpy ###################################################
r_index = np.lexsort((r_array['activation'],)) #NB notice the extra comma
if only one field
for i in r_index[::-1]: #i.e. reverse order could do in lexsort with
-r_array['activation']
print(r_array[i]) # or other more meaningful operations (at least some
formatting!)

# sorting with pytables is possible for the whole table based on a single
# indexed column using Table.itersorted() or Table.read_sorted()