Extracting DB schema (newbie Q)

[Chris Angelico]

On Tue, May 15, 2012 at 5:09 AM, Steve Sawyer <ssawyer at stephensawyer.com> wrote:
> Thanks - now, given my query that returns the table structure, this
> works fine:
>
> table_dict = {}
> table_specs = cursor.execute(query_string)
> for row in table_specs:
>        row_dict = {}
>        row_dict['type'] = row.DataType
>        row_dict['size'] = row.Length
>        table_dict[row.name] = row_dict
>
> table_dict['path']['type'] #-> 'nvarchar'
> table_dict['path']['size'] # -> 200
> table_dict['Artist']['size'] #-> 50
>
> Is this (nesting dictionaries) a good way to store multiple attributes
> associated with a single key value?

There's lots of options. The dictionary works; or you could create a
class for your record - also, check out namedtuple from the
collections module, which is a shortcut to the second option.

# Option 2
class Field:
    def __init__(self,datatype,length):
        self.datatype=datatype
        self.length=length

# Option 3:
import collections
Field=collections.namedtuple('Field',('datatype','length'))

table_dict = {}
table_specs = cursor.execute(query_string)
for row in table_specs:
    # Option 1: no need to start with an empty dictionary and then populate it
    table_dict[row.name] = {'type': row.DataType, 'size': row.Length}
    # Option 2 or 3: your own record type, or a namedtuple
    table_dict[row.name] = Field(row.DataType,row.Length)

# Option 1:
table_dict['path']['type'] #-> 'nvarchar'
table_dict['path']['size'] # -> 200
table_dict['Artist']['size'] #-> 50

# Option 2 or 3
table_dict['path'].datatype #-> 'nvarchar'
table_dict['path'].size # -> 200
table_dict['Artist'].size #-> 50

You'll notice that I've used 'datatype' rather than 'type' - the
latter would work, but since 'type' has other meaning (it's the class
that all classes subclass, if that makes sense), I like to avoid using
it.

The choice between these three options comes down to style, so pick
whichever one "feels best" to you.

ChrisA