http://web2py.com/book/default/chapter/06#Indexes
The example appears to be missing a quote.
Adding the missing single-quote I am still getting a syntax
error:
Error traceback
File "/web2py/web2py/gluon/restricted.py", line 186, in
restricted
exec ccode in environment
File "/web2py/web2py/applications/ewc/models/db.py", line 108,
in <module>
db.executesql('CREATE INDEX IF NOT EXISTS idx_filemaker_id
ON recipe filemaker_id;')
File "/web2py/web2py/gluon/sql.py", line 1428, in executesql
self._execute(query)
File "/web2py/web2py/gluon/sql.py", line 947, in <lambda>
self._execute = lambda *a, **b: self._cursor.execute(*a,
**b)
OperationalError: near "filemaker_id": syntax error
it is for the line:
db.executesql('CREATE INDEX IF NOT EXISTS idx_filemaker_id ON
recipe
filemaker_id;')
I have a db.recipe.filemaker_id of type 'integer'
Generally, I am importing some information from a few different
sources
into what will be a unified database. Because of this, there
are
multiple keys to manage.
I have a db.recipes table which contains a field, filemaker_id.
I am importing into db.nutrition_information from a large csv
file, which has as its key a column that
maps to db.recipe.filemaker_id
I am linking db.nutrition_information to db.recipe via
db.nutrition_information.external_key
(things other than recipes will be linked to
db.nutrition_information)
The problem is, for entry into db.nutrition_information, I have
to look up db.recipe.id via db.recipe.filemaker_id
This is why I want to create an index on db.recipe.filemaker_id
as the import process is very slow right now. Although I will
only have to do it once, it is nice to do as many test imports
as possible.
I think the code I am using may be inefficient, I hope someone
will take a look at it:
recipes = db(db.recipe.filemaker_id==rows[0]).select()
external_key = recipes[0].id #filemaker_id is unique
