python 操作sqlite3数据库

jopen 8年前

#!/usr/bin/env python  #coding:utf-8     import os  import sqlite3     try:      os.remove("test.db")  except:      pass     ##create/open the db  conn = sqlite3.connect("test.db")  c = conn.cursor()     ##create the table in db  c.execute('''CREATE TABLE infors  (id int primary key,sort int,name text)''')     c.execute('''CREATE TABLE book      (id int primary key,      sort int,      name text,      price real,      infors int,      FOREIGN KEY (infors) REFERENCES infors(id)      )''')     ##insert into values in table  books = [(1, 1, 'Cook Recipe', 3.12, 1),              (2, 3, 'Python Intro', 17.5, 2),              (3, 2, 'OS Intro', 13.6, 2),             ]  c.execute("INSERT INTO infors VALUES (1,1,'kitchen')")  c.execute("INSERT INTO infors VALUES (?,?,?)",(2,2,'computer'))  c.executemany("INSERT INTO book VALUES (?,?,?,?,?)",books)     ##select * from table  c.execute('SELECT name FROM infors ORDER BY sort')  print c.fetchone()  print c.fetchone()  print "**********************************************************"     c.execute('SELECT * FROM book WHERE book.infors=1')  print c.fetchall()     print "**********************************************************"     for row in c.execute('SELECT name,price FROM book ORDER BY sort'):      print row     ##update and delete  c.execute('UPDATE book SET price=? WHERE id=?',(1000, 1))  c.execute('DELETE FROM book WHERE id=2')     conn.commit()  conn.close()