python sqlite3

尝试了 python sqlite3 操作，不多解释，直接粘测试代码

<pre name="code" class="python"># Python test 2016-10-31

import sqlite3

# function to get per value in one sentence
def convert( value ):
    if value.startswith('~'):
        return value.strip('~')
    if not value:
        value = 0
    return float(value)

# build the database
conn = sqlite3.connect('food.db')
cur  = conn.cursor()
cur.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
   )
    ''')

sqlquery = 'INSERT INTO food VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'

for line in open('/Users/apple/Downloads/ABBREV.txt'):
    fields = line.split('^')
    fields_count = 10
    values = [convert(f) for f in fields[0:fields_count]]
    cur.execute(sqlquery, values)

conn.commit()
conn.close()

执行和查询DB内容如下：

python XX/dbtest.py

<pre name="code" class="python">values = cur.execute('select * from food')
for val in values:
    print ("id = %s", val[0])
    print ("name = %s", val[1])

完成数据库的写入和读出