# -*- coding: utf-8 -*- import sqlite3 def mykey(x): return x[3] conn=sqlite3.connect("D:\\demo\\my_db.db") sql = "CREATE TABLE IF NOT EXISTS mytb ( a char , b int , c real, d DATE)" # a char , b int , c real 表示該表有三個字段， # a 是字符串類型， b 是整數類型, c 是實數類型。 conn.execute( sql ) cs = conn.cursor() #cs.execute("DELETE FROM mytb WHERE A='張三' ") cs.execute("DELETE FROM mytb ") #刪除所有記錄 ''''' cs.execute( "INSERT INTO mytb ( a,b,c,d ) values('Zhang San',25, 120, '2014-03-04')" ) cs.execute( "INSERT INTO mytb ( a,b,c,d ) values( 'Wang Wu',24, 110, '2014-05-01')" ) cs.execute( "INSERT INTO mytb ( a,b,c,d ) values( 'Li Si',23, 130, '2014-04-06')" ) ''' #批量注入，batchdata是一個列表，列表里每一個元素都是一個元組 batchdata=[('Zhang San',25, 120, '2014-03-04'), ( 'Wang Wu',24, 110, '2014-05-01'), ( 'Li Si',23, 130, '2014-04-06')] cs.executemany('INSERT INTO mytb values (?,?,?,?)',batchdata) conn.commit() #將加入的記錄保存到磁盤，非常重要！ cs.execute("SELECT name, sql FROM sqlite_master WHERE type='table'") recs = cs.fetchall( ) print ( recs ) cs.execute( "SELECT * FROM mytb ")#打開數據表 recs = cs.fetchall()#取出所有記錄 print ( "there is ", len(recs)," notes." ) print recs recs.sort(key = mykey) print recs cs.close() conn.close() |