title: How to implement database-style table in Python
body: <p>I am implementing a class that resembles a typical database table: </p>
  
  <ul>
  <li>has named columns and unnamed rows</li>
  <li>has a primary key by which I can refer to the rows</li>
  <li>supports retrieval and assignment by primary key and column title</li>
  <li>can be asked to add unique or non-unique index for any of the columns, allowing fast retrieval of a row (or set of rows) which have a given value in that column</li>
  <li>removal of a row is fast and is implemented as "soft-delete": the row is kept physically, but is marked for deletion and won't show up in any subsequent retrieval operations</li>
  <li>addition of a column is fast</li>
  <li>rows are rarely added</li>
  <li>columns are rarely deleted</li>
  </ul>
  
  <p>I decided to implement the class directly rather than use a wrapper around sqlite. </p>
  
  <p>What would be a good data structure to use?</p>
  
  <hr>
  
  <p>Just as an example, one approach I was thinking about is a dictionary. Its keys are the values in the primary key column of the table; its values are the rows implemented in one of these ways:</p>
  
  <ol>
  <li><p>As lists. Column numbers are mapped into column titles (using a list for one direction and a map for the other). Here, a retrieval operation would first convert column title into column number, and then find the corresponding element in the list.</p></li>
  <li><p>As dictionaries. Column titles are the keys of this dictionary.</p></li>
  </ol>
  
  <p>Not sure about the pros/cons of the two.</p>
  
  <hr>
  
  <p>The reasons I want to write my own code are:</p>
  
  <ul>
  <li>I need to track row deletions. That is, at any time I want to be able to report which rows where deleted and for what "reason" (the "reason" is passed to my delete method).</li>
  <li>I need some reporting during indexing (e.g., while an non-unique index is being built, I want to check certain conditions and report if they are violated)</li>
  </ul>