sqlite 3学習ノート

12月5日にpython標準倉庫の中のsqlite 3を集中的に勉強しました。廖雪峰、vameiを見ました。

#       http://www.liaoxuefeng.com/wiki/001374738125095c955c1e6d8bb493182103fac9270762a000/001388320596292f925f46d56ef4c80a1c9d8e47e2d5711000
import sqlite3   #   SQLite  :
#    SQLite   ,       test.db
#        ，          :
conn = sqlite3.connect('test.db')
#     Cursor:
cursor = conn.cursor()
#     SQL  ，  user :
cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
#       SQL  ，      :
cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
cursor.rowcount   #  rowcount       :
cursor.close()    #  Cursor:
conn.commit()     #    :
conn.close()      #  Connection


# By Vamei        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     
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       
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')  #