Lie Hetland book: Beginning Python..

[Steve Holden]

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/