USDA database import failure

Asked by Theresa Kehoe on 2013-12-11

I tried the steps listed on " restart USDA database download". Now, when I try to activate the Nutritional Information plugin, Reading Nutritional Data very quickly reads 7143 entries. But, it then freezes on Reading Weight Data on 0 of 7143 entries.

At this point, the only fix is to delete the ~/.gourmet folder and restore from backup.

Your instructions had the user delete the tables nutrition, nutritionaliases, and nutritionconversions. Are there other tables that need to be deleted? Or has the USDA somehow changed their data formatting, so that weight data causes the import to fail?

Thanks for your help!

Question information

Language:
English Edit question
Status:
Solved
For:
Gourmet Edit question
Assignee:
No assignee Edit question
Solved by:
Thomas M. Hinkle
Solved:
2014-01-05
Last query:
2014-01-05
Last reply:
2014-01-05
Theresa Kehoe (tmk-n) said : #1

Part of the problem is, the database creates a table named usda_weights_temp, but the python code seems to be looking for usda_weights.

Launchpad Janitor (janitor) said : #2

This question was expired because it remained in the 'Open' state without activity for the last 15 days.

Theresa Kehoe (tmk-n) said : #3

This problem still exists. The perl script is looking for a table which
doesn't exist in the database. I tried renaming the temp table with the
permanent table name, but then I get an error that immutable object is
immutable.

Please fix this bug! -- I really need to be able to generate nutritional
information on my recipes.

Thank you!!

> Your question #240599 on Gourmet changed:
> https://answers.launchpad.net/gourmet/+question/240599
>
> Status: Open => Expired
>
> Launchpad Janitor expired the question:
> This question was expired because it remained in the 'Open' state
> without activity for the last 15 days.
>
> --
> If you're still having this problem, you can reopen your question either
> by replying to this email or by going to the following page and
> entering more information about your problem:
> https://answers.launchpad.net/gourmet/+question/240599
>
> You received this question notification because you asked the question.
>

Bernhard Reiter (ockham-razor) said : #4

Sorry for not replying sooner, and for letting this question expire.

I'll certainly look into the nutrition data plugin, which currently seems notoriously buggy -- part of which might be due to it employing multiple threads to gather and process data in order to allow the user continue working with the program. (I've been just a bit busy working on a Windows release lately so I haven't found the time yet.)

Theresa Kehoe (tmk-n) said : #5

Yay! If I can help by testing/etc, let me know. Thank you!

My guess is something went wrong with update code somewhere since this involves a temp table. It looks like the code has an update for the weight version between Gourmet versions < 0.14 and those thereafter. This is probably what broke. Any chance you can reproduce the error running from a terminal and post the output/debug info?

Theresa Kehoe (tmk-n) said : #7

tmk@Freedom ~ $ gourmet
sqlite:////home/tmk/.gourmet/recipes.db
RECREATE USDA WEIGHTS TABLE
Attempting to alter usda_weights <bound method NutritionDataPlugin.setup_usda_weights_table of <nutritional_information.data_plugin.NutritionDataPlugin instance at 0x28e7b00>> {} ['ndbno', 'seq', 'amount', 'unit', 'gramwt', 'ndata', 'stdev']
Problem updating plugin <nutritional_information.data_plugin.NutritionDataPlugin instance at 0x28e7b00> nutritondata
WARNING: PLUGIN FAILED TO LOAD <nutritional_information.data_plugin.NutritionDataPlugin instance at 0x28e7b00>
Traceback (most recent call last):
  File "/usr/share/gourmet/gourmet/plugin_loader.py", line 315, in plugin_plugin
    plugin_instance.activate(self)
  File "/usr/share/gourmet/gourmet/plugin.py", line 240, in activate
    db.update_plugin_version(self)
  File "/usr/share/gourmet/gourmet/backends/db.py", line 640, in update_plugin_version
    plugin_current = plugin.version,
  File "/usr/share/gourmet/gourmet/plugins/nutritional_information/data_plugin.py", line 68, in update_version
    [name for lname,name,typ in parser_data.WEIGHT_FIELDS])
  File "/usr/share/gourmet/gourmet/backends/db.py", line 944, in alter_table
    del self.metadata.tables[table_name]
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/_collections.py", line 38, in _immutable
    raise TypeError("%s object is immutable" % self.__class__.__name__)
TypeError: immutabledict object is immutable
Grabbing nutrition database!
Error appending {'seq': 1.0, 'ndbno': 1001, 'amount': 1.0, 'unit': u'cup', 'gramwt': 227.0} to usda_weights_table
WARNING: PLUGIN FAILED TO LOAD <nutritional_information.main_plugin.NutritionMainPlugin instance at 0x297ac20>
Traceback (most recent call last):
  File "/usr/share/gourmet/gourmet/plugin_loader.py", line 315, in plugin_plugin
    plugin_instance.activate(self)
  File "/usr/share/gourmet/gourmet/plugins/nutritional_information/main_plugin.py", line 13, in activate
    nutritionGrabberGui.check_for_db(pluggable.rd)
  File "/usr/share/gourmet/gourmet/plugins/nutritional_information/nutritionGrabberGui.py", line 70, in check_for_db
    dgg.load_db()
  File "/usr/share/gourmet/gourmet/plugins/nutritional_information/nutritionGrabberGui.py", line 39, in load_db
    self.grab_data(datad)
  File "/usr/share/gourmet/gourmet/plugins/nutritional_information/databaseGrabber.py", line 99, in grab_data
    os.path.join(directory,self.WEIGHT_FILE_NAME)))
  File "/usr/share/gourmet/gourmet/plugins/nutritional_information/databaseGrabber.py", line 86, in get_weight
    self.parse_weightfile(wfi)
  File "/usr/share/gourmet/gourmet/plugins/nutritional_information/databaseGrabber.py", line 199, in parse_weightfile
    self.db.do_add_fast(self.db.usda_weights_table,d)
  File "/usr/share/gourmet/gourmet/backends/db.py", line 1274, in do_add_fast
    return self.do_add(table,dic)
  File "/usr/share/gourmet/gourmet/backends/db.py", line 1280, in do_add
    result_proxy = insert_statement.execute(**dic)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py", line 2734, in execute
    return e._execute_clauseelement(self, multiparams, params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2304, in _execute_clauseelement
    return connection._execute_clauseelement(elem, multiparams, params)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1538, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1646, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1639, in _execute_context
    context)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 330, in do_execute
    cursor.execute(statement, parameters)
OperationalError: (OperationalError) no such table: usda_weights u'INSERT INTO usda_weights (ndbno, seq, amount, unit, gramwt) VALUES (?, ?, ?, ?, ?)' (1001, 1.0, 1.0, u'cup', 227.0)

Ok -- this is helpful and confirms my suspicion. It looks like the update
code is failing on the line }

del self.metadata.tables[table_name]

I see a note in the latest db.py that newer SQLAlchemy versions (>=0.7)
don't allow this syntax, and then I see a line below it fixing it.

I'm not sure what version you're running, but a quick hack to try to fix
your issue might be to open db.py and make the following change:

REMOVE>>> del self.metadata.tables[table_name]
INSERT >>> self.metadata._remove_table(table_name, self.metadata.schema)

Tom

On Sun, Jan 5, 2014 at 2:06 PM, Theresa Kehoe <
<email address hidden>> wrote:

> Question #240599 on Gourmet changed:
> https://answers.launchpad.net/gourmet/+question/240599
>
> Status: Needs information => Open
>
> Theresa Kehoe gave more information on the question:
> tmk@Freedom ~ $ gourmet
> sqlite:////home/tmk/.gourmet/recipes.db
> RECREATE USDA WEIGHTS TABLE
> Attempting to alter usda_weights <bound method
> NutritionDataPlugin.setup_usda_weights_table of
> <nutritional_information.data_plugin.NutritionDataPlugin instance at
> 0x28e7b00>> {} ['ndbno', 'seq', 'amount', 'unit', 'gramwt', 'ndata',
> 'stdev']
> Problem updating plugin
> <nutritional_information.data_plugin.NutritionDataPlugin instance at
> 0x28e7b00> nutritondata
> WARNING: PLUGIN FAILED TO LOAD
> <nutritional_information.data_plugin.NutritionDataPlugin instance at
> 0x28e7b00>
> Traceback (most recent call last):
> File "/usr/share/gourmet/gourmet/plugin_loader.py", line 315, in
> plugin_plugin
> plugin_instance.activate(self)
> File "/usr/share/gourmet/gourmet/plugin.py", line 240, in activate
> db.update_plugin_version(self)
> File "/usr/share/gourmet/gourmet/backends/db.py", line 640, in
> update_plugin_version
> plugin_current = plugin.version,
> File
> "/usr/share/gourmet/gourmet/plugins/nutritional_information/data_plugin.py",
> line 68, in update_version
> [name for lname,name,typ in parser_data.WEIGHT_FIELDS])
> File "/usr/share/gourmet/gourmet/backends/db.py", line 944, in
> alter_table
> del self.metadata.tables[table_name]
> File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/_collections.py",
> line 38, in _immutable
> raise TypeError("%s object is immutable" % self.__class__.__name__)
> TypeError: immutabledict object is immutable
> Grabbing nutrition database!
> Error appending {'seq': 1.0, 'ndbno': 1001, 'amount': 1.0, 'unit':
> u'cup', 'gramwt': 227.0} to usda_weights_table
> WARNING: PLUGIN FAILED TO LOAD
> <nutritional_information.main_plugin.NutritionMainPlugin instance at
> 0x297ac20>
> Traceback (most recent call last):
> File "/usr/share/gourmet/gourmet/plugin_loader.py", line 315, in
> plugin_plugin
> plugin_instance.activate(self)
> File
> "/usr/share/gourmet/gourmet/plugins/nutritional_information/main_plugin.py",
> line 13, in activate
> nutritionGrabberGui.check_for_db(pluggable.rd)
> File
> "/usr/share/gourmet/gourmet/plugins/nutritional_information/nutritionGrabberGui.py",
> line 70, in check_for_db
> dgg.load_db()
> File
> "/usr/share/gourmet/gourmet/plugins/nutritional_information/nutritionGrabberGui.py",
> line 39, in load_db
> self.grab_data(datad)
> File
> "/usr/share/gourmet/gourmet/plugins/nutritional_information/databaseGrabber.py",
> line 99, in grab_data
> os.path.join(directory,self.WEIGHT_FILE_NAME)))
> File
> "/usr/share/gourmet/gourmet/plugins/nutritional_information/databaseGrabber.py",
> line 86, in get_weight
> self.parse_weightfile(wfi)
> File
> "/usr/share/gourmet/gourmet/plugins/nutritional_information/databaseGrabber.py",
> line 199, in parse_weightfile
> self.db.do_add_fast(self.db.usda_weights_table,d)
> File "/usr/share/gourmet/gourmet/backends/db.py", line 1274, in
> do_add_fast
> return self.do_add(table,dic)
> File "/usr/share/gourmet/gourmet/backends/db.py", line 1280, in do_add
> result_proxy = insert_statement.execute(**dic)
> File "/usr/lib/python2.7/dist-packages/sqlalchemy/sql/expression.py",
> line 2734, in execute
> return e._execute_clauseelement(self, multiparams, params)
> File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line
> 2304, in _execute_clauseelement
> return connection._execute_clauseelement(elem, multiparams, params)
> File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line
> 1538, in _execute_clauseelement
> compiled_sql, distilled_params
> File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line
> 1646, in _execute_context
> context)
> File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line
> 1639, in _execute_context
> context)
> File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py",
> line 330, in do_execute
> cursor.execute(statement, parameters)
> OperationalError: (OperationalError) no such table: usda_weights u'INSERT
> INTO usda_weights (ndbno, seq, amount, unit, gramwt) VALUES (?, ?, ?, ?,
> ?)' (1001, 1.0, 1.0, u'cup', 227.0)
>
> --
> You received this question notification because you are a direct
> subscriber of the question.
>

Ok -- this is helpful and confirms my suspicion. It looks like the update code is failing on the line }

del self.metadata.tables[table_name]

I see a note in the latest backends/db.py that newer SQLAlchemy versions (>=0.7) don't allow this syntax, and then I see a line below it fixing it.

I'm not sure what version you're running, but a quick hack to try to fix your issue might be to open db.py and make the following change:

REMOVE>>> del self.metadata.tables[table_name]
INSERT >>> self.metadata._remove_table(table_name, self.metadata.schema)

See if you can make those changes in your source code and if that fixes things or not.

Theresa Kehoe (tmk-n) said : #10

Quick hack worked! Thank you! =)