Vittorio wrote:
> I am reading "Beginning Python from Novice to Professional" and the book
> is really awesome. Nonetheless on ch 13 "Database Support" I found this
> code to import data (in a txt file) into a SQLite Database:
>
> #this was corrected because original "import sqlite" does not work
> from pysqlite2 import dbapi2 as sqlite
>
> #this function strips the txt file from special chars
> def convert(value):
> if value.startswith('~'):
> return value.strip('~')
> if not value:
> value = '0'
> return float(value)
>
> conn = sqlite.connect('food.db')
> curs = conn.cursor()
>
> curs.execute('''
> CREATE TABLE food (
> id TEXT PRIMARY KEY,
> desc TEXT,
> water FLOAT,
> kcal FLOAT,
> protein FLOAT,
> fat FLOAT,
> ash FLOAT,
> carbs FLOAT,
> fiber FLOAT,
> sugar FLOAT
> )
> ''')
>
> field_count = 10
>
> #following is the line I suspect mistyped
> markers = ', '.join(['%s']*field_count)
>
> query = 'INSERT INTO food VALUES (%s)' % markers
>
>
> for line in open('ABBREV.txt'):
> fields = line.split('^')
> vals = [convert(f) for f in fields[:field_count]]
> #the following line raises error
> curs.execute(query,vals)
>
> conn.commit()
> conn.close
>
>
> The error was "Traceback (most recent call last):
> File "C:\Python24\food.py", line 39, in ?
> curs.execute(query,vals)
> pysqlite2.dbapi2.OperationalError: near "%": syntax error"
>
> After two hours of trying (did I say I am a beginner?) and after some
> documentation about PySqlite I suspect the error is in:
> markers = ', '.join(['%s']*field_count)
>
> I think Magnus intended:
> markers = ', '.join(['?']*field_count)
>
>
> Did I found an errata or my Python is still too green?
>
>
No, you actually did quite a creditable piece of debugging. The DB-API
specifications allow database modules to substitute parameters into SQL
commands in a number of different ways, and they are supposed to
indicate the technique they use by setting a module variable
"paramstyle" to one of five possible values.
Magnus' original code was written to use a different (but valid)
paramstyle, so I'm guessing that his sqlite module and your sqlite2
simply use different paramstyles. Whether that's because a change was
made in developing the pysqlite code or because pysqlite and pysqlite2
come from different developers I couldn't say, but you have nailed the
problem. Well done!
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC www.holdenweb.com
PyCon TX 2006 www.python.org/pycon/
--
http://mail.python.org/mailman/listinfo/python-list
