Python 数据库(13) 持续更新

简单数据库可以通过shelve和pickle做简单的单键查找。复杂的同时使用多个数据字段或属性进行复杂的搜索。

阅读 http://www.cnblogs.com/IPrograming/p/Python-database.html

Sqlite

导入数据库示例

文件名  importdata.py

import sqlite3

def convert(value):
    if value.startswith('~'):
        return value.strip('~')
    if not value:
        value = '0'
    return float(value)

conn = sqlite3.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
    )
''')

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

# for line in open('ABBREV.txt'):
#     fields = line.split('^')
#     vals = [convert(f) for f in fields[0:10]]
#     curs.execute(query, vals)

allDatum = []
for line in open('ABBREV.txt'):
    fields = line.split('^')
    vals = [convert(f) for f in fields[0:10]]
    allDatum.append(vals)

curs.executemany(query, allDatum)

conn.commit()
conn.close()

ABBREV.txt 内容

~01001~^~BUTTER,WITH SALT~^15.87^717^0.85^81.11^2.11^0.06^0.0^0.06^24^0.02^2^24^24^714^0.09^0.000^0.000^1.0^0.0^0.005^0.034^0.042^0.110^0.003^3^0^3^3^18.8^0.17^2499^684^671^0^158^0^0^0^2.32^1.5^60^7.0^51.368^21.021^3.043^215^227^~1 cup~^14.2^~1 tbsp~^0
~01002~^~BUTTER,WHIPPED,WITH SALT~^15.87^717^0.85^81.11^2.11^0.06^0.0^0.06^24^0.16^2^23^26^827^0.05^0.016^0.004^1.0^0.0^0.005^0.034^0.042^0.110^0.003^3^0^3^3^18.8^0.13^2499^684^671^0^158^0^0^0^2.32^1.5^60^7.0^50.489^23.426^3.012^219^151^~1 cup~^9.4^~1 tbsp~^0
~01003~^~BUTTER OIL,ANHYDROUS~^0.24^876^0.28^99.48^0.00^0.00^0.0^0.00^4^0.00^0^3^5^2^0.01^0.001^0.000^0.0^0.0^0.001^0.005^0.003^0.010^0.001^0^0^0^0^22.3^0.01^3069^840^824^0^193^0^0^0^2.80^1.8^73^8.6^61.924^28.732^3.694^256^205^~1 cup~^12.8^~1 tbsp~^0
~01004~^~CHEESE,BLUE~^42.41^353^21.40^28.74^5.11^2.34^0.0^0.50^528^0.31^23^387^256^1395^2.66^0.040^0.009^14.5^0.0^0.029^0.382^1.016^1.729^0.166^36^0^36^36^15.4^1.22^763^198^192^0^74^0^0^0^0.25^0.5^21^2.4^18.669^7.778^0.800^75^28.35^~1 oz~^17^~1 cubic inch~^0
~01005~^~CHEESE,BRICK~^41.11^371^23.24^29.68^3.18^2.79^0.0^0.51^674^0.43^24^451^136^560^2.60^0.024^0.012^14.5^0.0^0.014^0.351^0.118^0.288^0.065^20^0^20^20^15.4^1.26^1080^292^286^0^76^0^0^0^0.26^0.5^22^2.5^18.764^8.598^0.784^94^132^~1 cup, diced~^113^~1 cup, shredded~^0
~01006~^~CHEESE,BRIE~^48.42^334^20.75^27.68^2.70^0.45^0.0^0.45^184^0.50^20^188^152^629^2.38^0.019^0.034^14.5^0.0^0.070^0.520^0.380^0.690^0.235^65^0^65^65^15.4^1.65^592^174^173^0^9^0^0^0^0.24^0.5^20^2.3^17.410^8.013^0.826^100^240^~1 cup, melted~^144^~1 cup, sliced~^0
~01007~^~CHEESE,CAMEMBERT~^51.80^300^19.80^24.26^3.68^0.46^0.0^0.46^388^0.33^20^347^187^842^2.38^0.021^0.038^14.5^0.0^0.028^0.488^0.630^1.364^0.227^62^0^62^62^15.4^1.30^820^241^240^0^12^0^0^0^0.21^0.4^18^2.0^15.259^7.023^0.724^72^246^~1 cup~^28.35^~1 oz~^0
~01008~^~CHEESE,CARAWAY~^39.28^376^25.18^29.20^3.28^3.06^0.0^^673^0.64^22^490^93^690^2.94^0.024^0.021^14.5^0.0^0.031^0.450^0.180^0.190^0.074^18^0^18^18^^0.27^1054^271^262^^^^^^^^^^18.584^8.275^0.830^93^28.35^~1 oz~^^~~^0
~01009~^~CHEESE,CHEDDAR~^36.75^403^24.90^33.14^3.93^1.28^0.0^0.52^721^0.68^28^512^98^621^3.11^0.031^0.010^13.9^0.0^0.027^0.375^0.080^0.413^0.074^18^0^18^18^16.5^0.83^1002^265^258^0^85^0^0^0^0.29^0.6^24^2.8^21.092^9.391^0.942^105^132^~1 cup, diced~^244^~1 cup, melted~^0
~01010~^~CHEESE,CHESHIRE~^37.65^387^23.37^30.60^3.60^4.78^0.0^^643^0.21^21^464^95^700^2.79^0.042^0.012^14.5^0.0^0.046^0.293^0.080^0.413^0.074^18^0^18^18^^0.83^985^233^220^^^^^^^^^^19.475^8.671^0.870^103^28.35^~1 oz~^^~~^0
~01011~^~CHEESE,COLBY~^38.20^394^23.76^32.11^3.36^2.57^0.0^0.52^685^0.76^26^457^127^604^3.07^0.042^0.012^14.5^0.0^0.015^0.375^0.093^0.210^0.079^18^0^18^18^15.4^0.83^994^264^257^0^82^0^0^0^0.28^0.6^24^2.7^20.218^9.280^0.953^95^132^~1 cup, diced~^113^~1 cup, shredded~^0
~01012~^~CHEESE,COTTAGE,CRMD,LRG OR SML CURD~^79.79^98^11.12^4.30^1.41^3.38^0.0^2.67^83^0.07^8^159^104^364^0.40^0.029^0.002^9.7^0.0^0.027^0.163^0.099^0.557^0.046^12^0^12^12^18.4^0.43^140^37^36^0^12^0^0^0^0.08^0.1^3^0.0^1.718^0.778^0.123^17^113^~4 oz~^210^~1 cup, large curd (not packed)~^0
~01013~^~CHEESE,COTTAGE,CRMD,W/FRUIT~^79.64^97^10.69^3.85^1.20^4.61^0.2^2.38^53^0.16^7^113^90^344^0.33^0.040^0.003^7.7^1.4^0.033^0.142^0.150^0.181^0.068^11^0^11^11^17.5^0.53^146^38^37^0^14^0^0^0^0.04^0.0^0^0.4^2.311^1.036^0.124^13^226^~1 cup,  (not packed)~^113^~4 oz~^0
~01014~^~CHEESE,COTTAGE,NONFAT,UNCRMD,DRY,LRG OR SML CURD~^81.01^72^10.34^0.29^1.71^6.66^0.0^1.85^86^0.15^11^190^137^330^0.47^0.030^0.022^9.4^0.0^0.023^0.226^0.144^0.446^0.016^9^0^9^9^17.9^0.46^8^2^2^0^0^0^0^0^0.01^0.0^0^0.0^0.169^0.079^0.003^7^145^~1 cup,  (not packed)~^113^~4 oz~^0
~01015~^~CHEESE,COTTAGE,LOWFAT,2% MILKFAT~^80.69^86^11.83^2.45^1.36^3.66^0.0^3.67^91^0.15^7^163^84^330^0.41^0.030^0.007^9.9^0.0^0.041^0.198^0.108^0.253^0.022^10^0^10^10^16.3^0.45^74^20^19^0^6^0^0^0^0.04^0.0^0^0.0^0.979^0.443^0.070^10^226^~1 cup,  (not packed)~^113^~4 oz~^0
~01016~^~CHEESE,COTTAGE,LOWFAT,1% MILKFAT~^82.48^72^12.39^1.02^1.39^2.72^0.0^2.72^61^0.14^5^134^86^406^0.38^0.028^0.003^9.0^0.0^0.021^0.165^0.128^0.215^0.068^12^0^12^12^17.5^0.63^41^11^11^0^3^0^0^0^0.01^0.0^0^0.1^0.645^0.291^0.031^4^226^~1 cup,  (not packed)~^113^~4 oz~^0
~01017~^~CHEESE,CREAM~^54.44^342^5.93^34.24^1.32^4.07^0.0^3.21^98^0.38^9^106^138^321^0.51^0.019^0.011^2.4^0.0^0.020^0.125^0.145^0.570^0.035^11^0^11^11^27.2^0.25^1343^366^359^0^88^0^0^0^0.29^0.6^25^2.9^19.292^8.620^1.437^110^232^~1 cup~^14.5^~1 tbsp~^0
~01018~^~CHEESE,EDAM~^41.56^357^24.99^27.80^4.22^1.43^0.0^1.43^731^0.44^30^536^188^965^3.75^0.036^0.011^14.5^0.0^0.037^0.389^0.082^0.281^0.076^16^0^16^16^15.4^1.54^825^243^242^0^11^0^0^0^0.24^0.5^20^2.3^17.572^8.125^0.665^89^28.35^~1 oz~^198^~1 package,  (7 oz)~^0
~01019~^~CHEESE,FETA~^55.22^264^14.21^21.28^5.20^4.09^0.0^4.09^493^0.65^19^337^62^1116^2.88^0.032^0.028^15.0^0.0^0.154^0.844^0.991^0.967^0.424^32^0^32^32^15.4^1.69^422^125^125^0^3^0^0^0^0.18^0.4^16^1.8^14.946^4.623^0.591^89^150^~1 cup, crumbled~^28.35^~1 oz~^0

运行之后，会生成一个 food.db 文件，如果报错，检查是否有 food.db 删除它，即可。

 这里插入数据也可以用 curs.executemany,先从文件提取所有数据。在插入数据。在这个例子中只会带来轻微提速。如果是 C/S SQL系统，则会大大提供速度。

相关阅读

http://blog.csdn.net/colourless/article/details/41444069

 

查询数据库

文件名 food_query.py

# coding: utf-8
import sqlite3, sys

default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    reload(sys)
    sys.setdefaultencoding(default_encoding)

conn = sqlite3.connect('food.db')
curs = conn.cursor()

argv = "kcal <=100 AND fiber >= 10 ORDER BY sugar"
query = 'SELECT * FROM food WHERE %s' % argv
print 'SQL语句', query
curs.execute(query)
names = [f[0] for f in curs.description]
for row in curs.fetchall():
    for pair in zip(names, row):
        print '%s：%s' % pair
    print

python 处理中文时出现的错误'ascii' codec can't decode byte 0xe9 in position 0: ordinal not in range(128)" 解决方法

python 编码篇

代码文件  https://code.csdn.net/a457636876/python_basic_course/tree/master