# By Vamei
import sqlite3# test.db is a file in the working directory.
conn = sqlite3.connect("test.db")c = conn.cursor()# create tables
c.execute('''CREATE TABLE category(id int primary key, sort int, name text)''')
c.execute('''CREATE TABLE book(id int primary key, sort int, name text, price real, category int,FOREIGN KEY (category) REFERENCES category(id))''')# save the changes
conn.commit()# close the connection with the database
conn.close()
# By Vameiimport sqlite3conn = sqlite3.connect('test.db')
c = conn.cursor()# retrieve one record
c.execute('SELECT name FROM category ORDER BY sort')
print(c.fetchone())
print(c.fetchone())# retrieve all records as a list
c.execute('SELECT * FROM book WHERE book.category=1')
print(c.fetchall())# iterate through the records
for row in c.execute('SELECT name, price FROM book ORDER BY sort'):print(row)
更新与删除
你可以更新某个记录，或者删除记录：
# By Vamei
conn = sqlite3.connect("test.db")
c = conn.cursor()c.execute('UPDATE book SET price=? WHERE id=?',(1000, 1))
c.execute('DELETE FROM book WHERE id=2')conn.commit()
conn.close()
