2021-12-10 sqlite数据库 sqlite表

建设表
CREATE TABLE arg_info(no TEXT,name TEXT,energy REAL,protein REAL);

增加
INSERT INTO arg_info(no,name,energy,protein) VALUES ('001','芝麻',10,1);
INSERT INTO arg_info(no,name,energy,protein) VALUES ('002','玉米',11,2);
INSERT INTO arg_info(no,name,energy,protein) VALUES ('003','大豆',12,3);
INSERT INTO arg_info(no,name,energy,protein) VALUES ('004','花生',13,4);

删除
DELETE FROM arg_info WHERE no='004';

修改
UPDATE arg_info SET energy=37.5 WHERE no='002';

查询
SELECT * FROM arg_info;

升序
SELECT * FROM arg_info ORDER by energy ASC;

降序
SELECT * FROM arg_info ORDER by energy DESC;

条件查询
SELECT * FROM arg_info WHERE protein<3;

创建索引
CREATE INDEX name_INDEX on arg_info(name);

Pycharm

import sqlite3
conn=sqlite3.connect(r'C:\Program Files\DB Browser for SQLite\b.db')#创建文件，生产对象
cursor=conn.cursor()#创建游标
cursor.execute('CREATE TABLE black(id PRIMARY KEY,name TEXT)')#建表
cursor.executemany('insert into black(id,name)values(?,?)',[(1,'中国'),(2,'武汉'),(3,'上海'),(4,'北京')])#添加数据
conn.commit()#提交
conn.close()#关闭

import sqlite3
def convert(value):
    if value.startswith('~'):
        return value.strip('~')
conn=sqlite3.connect('d:/food.db')
curs=conn.cursor( )
if not curs:
    raise Exception('Fail to connect database!')
curs.execute('''CREATE TABLE foodgroup(id text PRIMARY KEY,foodname text)''')
message='Insert into foodgroup VALUES(?,?)'
for line in open('d:/FD_GROUP.txt'):
    fields=line.split('~')
    vals=[convert(f) for f in fields[:2]]
    curs.execute(message,vals)
conn.commit( )
conn.close( )