12月5日集中學(xué)習了一下python標準庫中的sqlite3，看了廖雪峰、vamei，《python語言及其運用》三個教程，筆記如下

#廖雪峰教程  http://www.liaoxuefeng.com/wiki/001374738125095c955c1e6d8bb493182103fac9270762a000/001388320596292f925f46d56ef4c80a1c9d8e47e2d5711000
import sqlite3   # 導(dǎo)入SQLite驅(qū)動:
# 連接到SQLite數(shù)據(jù)庫, 數(shù)據(jù)庫文件是test.db
# 如果文件不存在，會自動在當前目錄創(chuàng)建:
conn = sqlite3.connect('test.db')
# 創(chuàng)建一個Cursor:
cursor = conn.cursor()
# 執(zhí)行一條SQL語句，創(chuàng)建user表:
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
# 繼續(xù)執(zhí)行一條SQL語句，插入一條記錄:
cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
cursor.rowcount   #通過rowcount獲得插入的行數(shù):
cursor.close()    #關(guān)閉Cursor:
conn.commit()     #提交事務(wù):
conn.close()      #關(guān)閉Connection


# By Vamei  創(chuàng)建數(shù)據(jù)庫 http://www.cnblogs.com/vamei/p/3794388.html
import sqlite3
# test.db is a file in the working directory.
conn = sqlite3.connect("test.db")
cursor = conn.cursor()
# create tables
cursor.execute('''CREATE TABLE category
      (id int primary key, sort int, name text)''')
cursor.execute('''CREATE TABLE book
      (id int primary key, 
       sort int, 
       name text, 
       price real, 
       category int,
       FOREIGN KEY (category) REFERENCES category(id))''')
conn.commit()   # save the changes
conn.close()     # close the connection with the database


#Vamei 插入數(shù)據(jù)
import sqlite3
conn = sqlite3.connect("test.db")
c    = conn.cursor()
books = [(1, 1, 'Cook Recipe', 3.12, 1),
            (2, 3, 'Python Intro', 17.5, 2),
            (3, 2, 'OS Intro', 13.6, 2)]
 # execute "INSERT" 
c.execute("INSERT INTO category VALUES (1, 1, 'kitchen')")

# using the placeholder
c.execute("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])

# execute multiple commands
c.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)
conn.commit()
conn.close()


# By Vamei   查詢數(shù)據(jù)
import sqlite3
conn = 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()
c.execute('DROP TABLE book')  #刪除整張表