Using only what I know I have implemented this...
def rebuild_database:
import sys
data = open("/home/jason/Desktop/colorant_tints.txt")
parsed_data = []
for line in data.readlines():
parsed_data.append(line.rsplit(","))
sys.stdout.write(".")
for line in parsed_data:
paint_name = line.pop(0)
base_paint = line.pop(0)
#split them up
tint_amount = []
tint_names = []
tint = True
#Add the paint before we see what tints are available
if db(db.paint.name == paint_name):
db(db.paint.name == paint_name).update(base_paint =
base_paint)
else:
db.paint.insert(name = paint_name, base_coat=base_coat)
for item in line:
if tint:
tint_names.append(item)
tint = False
else:
amount.append(item)
tint = True
#add the tints
for item in range(len(tint_names)):
if not db(db.tint.name == tint_names[item]):
db.tint.insert(name = tint_names[item])
#Add the paints tints and their amounts
tint_item = db(db.tint.name ==
tint_names[item]).select()
paint_item = db(db.paint.name == paint_name).select()
paint_item = db(db.paint.name == paint_name).select()
db.paint_tints.insert(paint_id = paint_item.id, tint_id
= tint_item.id, amount = tint_amounts[item])
return dict()
Weird enough, I haven't played with the DAL to get what I am doing wrong
in those last 3 or 4 lines. Then again, your code semes alot cooler. (:
---
Best Regards,
Jason Brower
On Sat, 2010-01-09 at 07:32 -0800, Brian M wrote:
> paint_formulas is supposed to get the contents of the csv file. (See
> http://docs.python.org/library/csv.html) It should be populated by the
> line:
> paint_formulas = csv.reader(csvfile)
> where csv file should be the path to your csv file.
>
> Actually, I think that line should read like this instead:
> paint_formulas = csv.reader(open(csvfile)) #was missing the open()
> before
>
> Try this as a test of reading the csv file in a controller (should
> only show the first few records [0:10] instead of all 14K or whatever
> you've got).
>
> def read_csv():
> import csv
> paint_formulas = csv.reader(open(csvfile))
> return dict(paint_formulas = paint_formulas[0:10], count = len
> (paint_formulas))
>
> On Jan 9, 1:36 am, Jason Brower <encomp...@gmail.com> wrote:
> > It is much more understandable. But it seems that the name
> > paint_formulas is not defined. What is supposed to be populated there?
> > Best Regards,
> > Jason
> >
> >
> >
> > On Fri, 2010-01-08 at 20:43 -0800, Brian M wrote:
> > > Jason,
> > > What's the syntax error?
> > > Try this, it's untested but should be verbose enough to get you there.
> > > If you have trouble perhaps provide a sample file (few dozen records)
> > > that we can test against.
> >
> > > import csv
> > > paint_formulas = csv.reader(csvfile)
> > > header = None
> >
> > > #variable to store the paints we've already put in
> > > #database (avoiding duplications and/or having to
> > > #constantly query db)
> > > paints = dict()
> >
> > > #variable to store the colorants already put in database
> > > known_colorants = dict()
> >
> > > #these are the indexes that have colorant names
> > > #we'll use them to help get all the colorants
> > > colorant_indexes = [2,4,6,8,10]
> >
> > > for formula in paint_formulas:
> > > formula_colorants = [] #will hold the colorants in this formula,
> > > however many there are
> > > if not header:
> > > header = formula #give value
> > > #and do nothing else cause it's just the column names
> > > else:
> > > #it's a paint formula
> > > name = row[0]
> > > base = row[1]
> > > #deal with the colorants
> > > for c in colorant_indexes:
> > > if row[c] <> None:
> > > colorant = row[c]
> > > amount = row[c+1] #next index is the amount
> > > #there's a colorant value to add
> > > if colorant in known_colorants:
> > > #already have this colorant in the database
> > > colorant_id = known_colorants[colorant]#retreive
> > > id from our knowns
> > > else:
> > > #new colorant, add to database
> > > known_colorants[colorant]=db.colorant.insert
> > > (name=colorant)
> > > colorant_id = known_colorants[colorant]
> >
> > > #now add to this formula's list of colorants
> > > formula_colorants.append(dict(id = colorant_id, amount
> > > = amount))
> > > else:
> > > #no more colorants
> > > pass
> >
> > > #add the paint
> > > if not name in paints:
> > > paints[name]=db.paint.insert(name=name)
> >
> > > #then add each of the colorants
> > > for colorant in formula_colorants:
> > > db.paint_colorants.insert(paint_id=paints[name],
> > > base_coat = base,
> > > colorant_id=colorant[id], units = colorant
> > > [amount])
> >
> > > Good Luck,
> > > Brian
> >
> > > On Jan 8, 7:57 pm, Jason Brower <encomp...@gmail.com> wrote:
> > > > I tried the script but couldn't get through the syntax error. Sorry,
> > > > lots of commands I don't know there. :/
> > > > BR,
> > > > Jason
> >
> > > > On Fri, 2010-01-08 at 09:17 -0800, Brian M wrote:
> > > > > I have been working on using web2py to import csv files and find that
> > > > > in general it works very well. You will need to be careful with
> > > > > pulling in the colorant and amount fields - if you use the
> > > > > csv.DictReader() to refer to columns in the CSV file by name instead
> > > > > of index you'll find that you only get the value for the *last*
> > > > > colorant and amount. In other words, just using row['Colorant'] and
> > > > > row
> > > > > ['Amount'] will very likely cause you to loose data - you've got 5
> > > > > 'Colorant' entries in a single row, so each one in a row simply over-
> > > > > writes the earlier ones. You will most likely need to use the
> > > > > indexes, or just rename the column headers in the csv file so they are
> > > > > unique.
> >
> > > > > ~Brian
> >
> > > > > On Jan 8, 4:51 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> > > > > > Something like this?
> >
> > > > > > import csv
> > > > > > reader = csv.reader(csvfile)
> > > > > > header = None
> > > > > > paints = {}
> > > > > > colorants = {}
> > > > > > for line in reader:
> > > > > > if not header: header = line
> > > > > > else:
> > > > > > row = dict([header[i],item) for i,item in enumerate(line)])
> > > > > > name = row['Color Name']
> > > > > > colorant = row['Colorant']
> > > > > > if not name in paints:
> > > > > > paints[name]=db.paint.insert(name=name)
> > > > > > if not colorant in colorants:
> > > > > > colorants[colorant]=db.colorant.insert(name=colorant)
> > > > > > db.paint_colorants.insert(paint_id=paints
> > > > > > [name],colorant_id=colorants[colorant])
> >
> > > > > > On Jan 8, 4:10 am, Jason Brower <encomp...@gmail.com> wrote:
> >
> > > > > > > I have data like this in a csv file....
> > > > > > > "Color Name", Base, Colorant, Amount, Colorant, Amount, Colorant,
> > > > > > > Amount, Colorant, Amount, Colorant, Amount
> > > > > > > "10 PORDRR",G,fo1,76,da1,32,ro1,111,yi1,1,,
> > > > > > > It is for a paint database.
> > > > > > > Is it at all possible to import that into this model?
> > > > > > > Normally I would just take a few minutes and do it by hand. But
> > > > > > > I have
> > > > > > > 14,000+ entries, so that's out of the question. Any ideas? In
> > > > > > > particular, how do you handle the variance in how many kinds of
> > > > > > > colorants are added. Notice in this example that is only 4
> > > > > > > colorants,
> > > > > > > and 5+ could be in the page. (Currently from this extraction,
> > > > > > > only 5
> > > > > > > colorants and their amounts.
> >
> > > > > > > db = SQLDB('sqlite://paint.sqlite')
> >
> > > > > > > db.define_table('paint',
> > > > > > > Field('name', length=30, requires=IS_NOT_EMPTY(),
> > > > > > > unique=True),
> > > > > > > Field('base_coat', length=3, requires=IS_NOT_EMPTY()),
> > > > > > > Field('hex_color', length=6))
> >
> > > > > > > db.define_table('colorant',
> > > > > > > Field('name', length=3, requires=IS_NOT_EMPTY()))
> >
> > > > > > > db.define_table('paint_colorants',
> > > > > > > Field('paint_id', db.paint),
> > > > > > > Field('colorant_id', db.colorant),
> > > > > > > Field('units', 'integer'))
> >
> > > > > > > db.paint_colorants.paint_id.requires=IS_IN_DB(db, 'paint.id',
> > > > > > > '%(name)s')
> > > > > > > db.paint_colorants.colorant_id.requires=IS_IN_DB(db, 'paint.id',
> > > > > > > '%(name)s')
> >
> > > > > > > Best Regards,
> > > > > > > Jason Brower
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
