Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Having a "table" in memory that needs lookups, sorting, and arbitrary aggregation really does call out for SQL. You said you tried SQLite, but did you realize that SQLite can use an in-memory-only database?</p> <pre><code>connection = sqlite3.connect(':memory:') </code></pre> <p>Then you can create/drop/query/update tables in memory with all the functionality of SQLite and no files left over when you're done. And as of Python 2.5, <code>sqlite3</code> is in the standard library, so it's not really "overkill" IMO.</p> <p>Here is a sample of how one might create and populate the database:</p> <pre><code>import csv import sqlite3 db = sqlite3.connect(':memory:') def init_db(cur): cur.execute('''CREATE TABLE foo ( Row INTEGER, Name TEXT, Year INTEGER, Priority INTEGER)''') def populate_db(cur, csv_fp): rdr = csv.reader(csv_fp) cur.executemany(''' INSERT INTO foo (Row, Name, Year, Priority) VALUES (?,?,?,?)''', rdr) cur = db.cursor() init_db(cur) populate_db(cur, open('my_csv_input_file.csv')) db.commit() </code></pre> <p>If you'd really prefer not to use SQL, you should probably use a list of dictionaries:</p> <pre><code>lod = [ ] # "list of dicts" def populate_lod(lod, csv_fp): rdr = csv.DictReader(csv_fp, ['Row', 'Name', 'Year', 'Priority']) lod.extend(rdr) def query_lod(lod, filter=None, sort_keys=None): if filter is not None: lod = (r for r in lod if filter(r)) if sort_keys is not None: lod = sorted(lod, key=lambda r:[r[k] for k in sort_keys]) else: lod = list(lod) return lod def lookup_lod(lod, **kw): for row in lod: for k,v in kw.iteritems(): if row[k] != str(v): break else: return row return None </code></pre> <p>Testing then yields:</p> <pre><code>&gt;&gt;&gt; lod = [] &gt;&gt;&gt; populate_lod(lod, csv_fp) &gt;&gt;&gt; &gt;&gt;&gt; pprint(lookup_lod(lod, Row=1)) {'Name': 'Cat', 'Priority': '1', 'Row': '1', 'Year': '1998'} &gt;&gt;&gt; pprint(lookup_lod(lod, Name='Aardvark')) {'Name': 'Aardvark', 'Priority': '1', 'Row': '4', 'Year': '2000'} &gt;&gt;&gt; pprint(query_lod(lod, sort_keys=('Priority', 'Year'))) [{'Name': 'Cat', 'Priority': '1', 'Row': '1', 'Year': '1998'}, {'Name': 'Dog', 'Priority': '1', 'Row': '3', 'Year': '1999'}, {'Name': 'Aardvark', 'Priority': '1', 'Row': '4', 'Year': '2000'}, {'Name': 'Wallaby', 'Priority': '1', 'Row': '5', 'Year': '2000'}, {'Name': 'Fish', 'Priority': '2', 'Row': '2', 'Year': '1998'}, {'Name': 'Zebra', 'Priority': '3', 'Row': '6', 'Year': '2001'}] &gt;&gt;&gt; pprint(query_lod(lod, sort_keys=('Year', 'Priority'))) [{'Name': 'Cat', 'Priority': '1', 'Row': '1', 'Year': '1998'}, {'Name': 'Fish', 'Priority': '2', 'Row': '2', 'Year': '1998'}, {'Name': 'Dog', 'Priority': '1', 'Row': '3', 'Year': '1999'}, {'Name': 'Aardvark', 'Priority': '1', 'Row': '4', 'Year': '2000'}, {'Name': 'Wallaby', 'Priority': '1', 'Row': '5', 'Year': '2000'}, {'Name': 'Zebra', 'Priority': '3', 'Row': '6', 'Year': '2001'}] &gt;&gt;&gt; print len(query_lod(lod, lambda r:1997 &lt;= int(r['Year']) &lt;= 2002)) 6 &gt;&gt;&gt; print len(query_lod(lod, lambda r:int(r['Year'])==1998 and int(r['Priority']) &gt; 2)) 0 </code></pre> <p>Personally I like the SQLite version better since it preserves your types better (without extra conversion code in Python) and easily grows to accommodate future requirements. But then again, I'm quite comfortable with SQL, so YMMV.</p>
    singulars
    1. This table or related slice is empty.
    1. POData structure for maintaining tabular data in memory?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRick Copeland
    1. USRick Copeland
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POData structure for maintaining tabular data in memory?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat's the recommended approach for populating the database in this example?
      singulars
      1. PO
      1. USRoee Adler
    2. COI have edited the answer to include sample code to populate the database from a CSV file.
      singulars
      1. PO
      1. USRick Copeland
    3. CO-1: overkill, a list of dictionaries is probably more useful for this application.
      singulars
      1. PO
      1. USS.Lott
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload