Jason, glad you've got things working! :)
No, you don't want to try showing that many records on screen all at
once. Well at least not if you're letting web2py format it
automatically, in experimenting with my own good sized datasets I've
found that if you code out the table yourself in a view it isn't
nearly so resource intensive as just doing {{=big_dataset_var}}
Yes, the code I provided assumed that you are always starting from
scratch with no paints in the database. You could modify it fairly
easily to only add new paints though. You'd want to have a 3 phase
process for each paint and colorant:
1) Check if it's in the known_colorants or paints dictionary
variables, if it is you've already got it and can use the "cached"
colorant id or in the case of a paint just skip it all together.
2) If you don't have the id of the paint or colorant in the knowns
variables, then query the database for them. If you find them in the
database then "cache" the name and ID in your local variables so you
don't have to look them up in the database anymore.
3) If you don't find the paint/colorant in the dictionary of knowns or
in the database, then add them to the database and then once you've
got back the ID add the info into the dictionary of knowns.
Of course if there is a chance that you may need to update the
colorants that go into a paint, then you'll have to deal with that -
I'd probably just delete the paint_colorants for the paint and start
over rather than trying to figure out what's actually changed.
~Brian
On Jan 12, 4:35 am, Jason Brower <encomp...@gmail.com> wrote:
> Cool! That seemed to be importing properly. I have to be careful with
> displaying that much data in my browser. Actually filled my 2gb of ram!
> One more small thing to note here is that it doesn't account for old
> paint names in the database. So running the same file twice dupplicates,
> what seems to be everything. This doesn't bother me as I will be
> dropping all data before importing anything from the file. (at least for
> now.)
> Thank you for your patients and help. I have learned a lot!
> Best Regards,
> Jason
>
>
>
> On Mon, 2010-01-11 at 07:23 -0800, Brian M wrote:
> > Jason, put your csv file in the private subfolder of your application
> > and call it "paint.csv" and then add the following code to your
> > controller and it should work. (Well at least it imported the few
> > records you provided, I haven't taken the time to totally check that
> > everything lines up.
> > ~Brian
>
> > def import_formulas():
> > import csv
> > import os.path
> > csv_path = os.path.join(request.folder,'private','paint.csv')
> > paint_formulas = csv.reader(open(csv_path))
>
> > 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()
>
> > #we'll use them to help get all the colorants
> > #these are the indexes that have colorant names
> > colorant_indexes = [2,4,6,8,10]
>
> > db.paint.truncate()#forget all paints for now so don't duplicate.
>
> > 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
> > #print formula
> > #print "=================="
> > name = formula[0]
> > base_coat = formula[1]
> > #deal with the colorants
> > for c in colorant_indexes:
> > if formula[c] <> None and formula[c] <> '':
> > colorant = formula[c]
> > amount = formula[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]
> > #print "colorant id", colorant_id
>
> > #now add to this formula's list of colorants
> > formula_colorants.append([colorant_id, amount])
> > else:
> > #no more colorants
> > pass
>
> > #add the paint
> > if not name in paints:
> > paints[name]=db.paint.insert(name=name, base_coat =
> > base_coat)
>
> > #then add each of the colorants
> > for colorant in formula_colorants:
>
> > db.paint_colorants.insert(paint_id=paints[name],
> > colorant_id=colorant[0], units = colorant[1])
>
> > my_paints = db((db.paint.id>0) & (db.paint_colorants.paint_id ==
> > db.paint.id) & (db.colorant.id ==
> > db.paint_colorants.colorant_id)).select(orderby=db.paint.name|
> > db.colorant.name)
>
> > return dict(my_paints = my_paints)
>
> > On Jan 11, 7:42 am, Jason Brower <encomp...@gmail.com> wrote:
> > > Your requested some more data...
> > > "paint_name","base_paint","colorant","amount","colorant","amount","colorant
> > > ","amount","colorant","amount","colorant","amount"
> > > "10 JORASDUN",C,DD1,56,fS1,42,So1,110,AA1,8,,
> > > "10 SVFGH",W,To1,180,,,,,,,,
> > > 1002-DG5R,R,Co2,30,la1,4,To1,5,,,,
> > > 1008-Y24R,A,DD2,88,la1,17,Fo1,60,,,,
> > > "147 BARWSSFD",E,do1,74,fa1,38,Ha1,44,,,,
> > > "148 DUNKNMFRUN",D,Do1,56,Fa1,42,Go1,110,ti1,8,,
> > > Thank you for your help in this regard, it's a complicated process
> > > relative to what I am used to doing.
> > > BR,
> > > 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)])
>
> ...
>
> read more »
--
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.
