Standalone DAL Usiage - Insert from a Dictionary (vs bulk_insert)

[BrendanC]

I'm testing standalone DAL usage - basically using the DAL to help move/convert some tables from an external db.

Basically I'm trying to populate a dictionary with a subset of data from an external database and then insert a series of data records into a new table. 

I expected to be able to populate a dictionary with a subset of the selected fields but it appears that the db.insert method will not accept a dictionary  ( I get the error 'insert() takes exactly 1 argument (2 given').

I was able to get this to work using the bulk_insert method instead, but this is not very intuitive.  (See the code below for a specific example)

Is there a reason why the DAL insert method should/could not provide dictionary support (I checked the docs and this is not really covered in any detail). 

>>>> Code Sample <<<<

 

Something like this:

# Source db = sdb() 

sdb = DAL("postgres://postgres:@localhost:5432/testdb")

sdb.define_table('mytbl',

    Field('id', type='integer'),

    Field('fname', type='string', length=10),

    Field('lname', type='string', length=10),

# Target db = ddb()

ddb = DAL("sqlite://test.db")

ddb.define_table('mytbl',

    Field('id', type='integer'),

    Field('fname', type='string', length=10),

    Field('lname', type='string', length=10),

def  populate_table():

  for row in sdb().select(sdb.mytbl.ALL):

    # build a dict for tbl insert

    for fldname in sdb().mytbl.fields:

        ddict[fldname] = row[fldname]

  # commit changes

    try:

        #ddb.mytbl.insert(ddict)           # <--- error - insert() takes exactly 1 argument (2 given)

        ddb.mytbl.bulk_insert([ddict])    # <--- works

        ddb.commit()

    except:

        ddb.rollback()

    

[Bruno Rocha]

You have to unpack the dictionary with **

ddb.mytbl.insert(**ddict)

In Python dictionaries can be unpacked using ** and lists are unpacked with *, it is knows as *args and **kwargs

optionally you can use the db.mytbl.filter_fields to remove the unknown fields from the dict.


--

Bruno Rocha

[http://rochacbruno.com.br]

[Anthony]

def  populate_table():

  for row in sdb().select(sdb.mytbl.ALL):

    # build a dict for tbl insert

    for fldname in sdb().mytbl.fields:

        ddict[fldname] = row[fldname]

Note, each row of the select result is a web2py Row object (see http://web2py.com/books/default/chapter/29/6#Query,-Set,-Rows), which inherits from dict -- so you generally don't need to convert it to a dict because it already acts like one.

If you do need to convert to a dict (which I don't think is necessary in this case), you can convert using the as_dict() method, and you can convert an entire Rows object to a list of dicts via the as_list() method. See http://web2py.com/books/default/chapter/29/6#as_dict-and-as_list.

In terms of simplifying the code, it would probably be easiest to just use bulk_insert (which technically inserts one record at a time via a list comprehension):

ddb.mytbl.bulk_insert(sdb().select(sdb.mytbl.ALL).as_list())

Note, I'm not sure the .as_list() is necessary with bulk_insert -- it might accept the Rows object without converting to a list of dicts.

Finally, to copy the contents of a table from one db to another, another option is the CSV export and import functionality: http://web2py.com/books/default/chapter/29/6#CSV-(one-Table-at-a-time). 

Anthony