sqlobject-discuss

[SQLObject] unknown column table creation..novice

[Brian R. <br...@se...>]

Thanks for SQLObject, it's great!

I have used SQLObject with one project and I am working on my second. Still,
I am rather new to Python and totally new to SQLobject so excuse me if this
question is dumb.

I am looking for a way to created a table from a list of values. Say I have:

     headers = ['bbd_id', 'co_name1', 'address1', 'address2', 'city',
'state', 'zip5', 'zip4', 'first_name', 'surname', 'telephone']

Is there a way to define my class attributes from this list?

I tried playing with the _columns like this:

#.....snip...does not work......

     class mail_list(SQLObject):
          def __init__(self, headers):
             for h in headers:
                  self._columns.append("StringCol("+h+")")
     mail_list.createTable()

#....snip

But I was unsure on how to pass headers while initiating the class with
'createTable'. I end up with a table with only one field, id. Is there a way
to inherit these values from elsewhere?

Then I have made a string of arguments to use when initialize the new
instance:

     new_values  = "bbd_id='1', co_name1='big corp', address1='123
AnyStreet', address2='', city='Sample', state='ST', zip5='12345',
zip4='9999', first_name='Bob', surname='Villa', telephone='555-1212'"

I plan on simply creating each row with the string:

     row = mail_list.new.new(new_values)

Thanks in advance for any help.


SQLObject-0.5.2
Python 2.3

Re: [SQLObject] unknown column table creation..novice

[Ian B. <ia...@co...>]

Brian Ray wrote:
> Thanks for SQLObject, it's great!
> 
> I have used SQLObject with one project and I am working on my second. Still,
> I am rather new to Python and totally new to SQLobject so excuse me if this
> question is dumb.
> 
> I am looking for a way to created a table from a list of values. Say I have:
> 
>      headers = ['bbd_id', 'co_name1', 'address1', 'address2', 'city',
> 'state', 'zip5', 'zip4', 'first_name', 'surname', 'telephone']

class MailList(SQLObject):
     pass

for header in headers:
     MailList.addColumn(Col(name=header))

-- 
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

[SQLObject] Re: unknown column table creation..novice

[Brian R. <br...@se...>]

 
>> I am looking for a way to created a table from a list of values. Say I have:
>> 
>>      headers = ['bbd_id', 'co_name1', 'address1', 'address2', 'city',
>> 'state', 'zip5', 'zip4', 'first_name', 'surname', 'telephone']
> 
> class MailList(SQLObject):
>    pass
> 
> for header in headers:
>    MailList.addColumn(Col(name=header))

This works great, thnx!

Next I want to create a new record:

    rows = zip(headers,cell)
    line_item = MailList.new(*rows)

However the new method does not like this argument list:

    TypeError: new() takes exactly 1 argument (16 given)

When I explicitly write out all 16 arguments, it works.

[SQLObject] Re: unknown column table creation..novice

[Philippe N. <sw...@fr...>]

Le 19/8/2004, "Brian Ray" <br...@se...> a =E9crit:

>
>Next I want to create a new record:
>
>    rows =3D zip(headers,cell)
>    line_item =3D MailList.new(*rows)
>
>However the new method does not like this argument list:
>
>    TypeError: new() takes exactly 1 argument (16 given)
>
>When I explicitly write out all 16 arguments, it works.

Try:

MailList.new(**rows)

Philippe

[SQLObject] Re: unknown column table creation..novice

[Philippe N. <sw...@fr...>]

>>
>>    rows =3D zip(headers,cell)
>>    line_item =3D MailList.new(*rows)
>>
>
>Try:
>
>MailList.new(**rows)
>

Woops, this won't work i think, you have to build a dictionnary to be
able to use **. So:

rows =3D dict(zip(headers,cell))
line_item =3D MailList.new(**rows)

[SQLObject] Re: unknown column table creation..novice

[Brian R. <br...@se...>]

> From: "Philippe Normand" <sw...@fr...>
> 
> 
>>> 
>>>    rows = zip(headers,cell)
>>>    line_item = MailList.new(*rows)
>>> 
>> 
>> Try:
>> 
>> MailList.new(**rows)
>> 
> 
> Woops, this won't work i think, you have to build a dictionnary to be
> able to use **. So:
> 
> rows = dict(zip(headers,cell))
> line_item = MailList.new(**rows)
> 
> 

Works like a charm. Thanks Ian and Philippe!

Complete code for csv2sql.py:



#!/usr/bin/env python
"""
   $Id: csv2sql.py,v 1.1 2004/08/19 16:02:37 brian Exp $

   Quick conversion script of any CSV to
   any SQLObject support database

   Brian Ray

""" 
from SQLObject import *
import csv

__connection__ =  SQLiteConnection('../USERDATA/1/mailist.sqlite')
data_file = "../USERDATA/QM_sample_job/sample.csv"

data_sample = file(data_file).read(8192)
dialect = csv.Sniffer().sniff(data_sample)
data = csv.reader(file(data_file),dialect)

headers = []
row_num = 0
for row in data:
   row_num += 1
   if row_num == 1:
      for cell in row:
         headers.append(cell)
      try:
         class MailList(SQLObject):
            pass
         for header in headers:
            MailList.addColumn(Col(name=header))
         MailList.createTable()
         print MailList._table+" Created."
      except:
         print MailList._table+" Skipped already exists or error."
   else:
      rows = dict(zip(headers,row))
      line_item = MailList.new(**rows)

print "Done: "+str(row_num-1)+" converted"

# Please let me know if this has issues