#實(shí)例：用數(shù)據(jù)庫(kù)存儲(chǔ)日記，實(shí)現(xiàn)日記本功能

#流程

?

#創(chuàng)建數(shù)據(jù)庫(kù)

1 #coding:utf-8 2 import sqlite3 3 connect=sqlite3.connect('test.db') 4 connect.close()

#效果

　　

?

#用sqlite3模塊對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作

1 #coding:utf-8 2 import sqlite3 3 #創(chuàng)建數(shù)據(jù)庫(kù) 4 connect=sqlite3.connect('test.db') 5 cursor = connect.cursor() 6 #為了多次執(zhí)行該段程序所以在開(kāi)頭刪除表diary1 7 cursor.execute("drop table diary1") 8 9 #創(chuàng)建表 10 cursor.execute("create table diary1 (id integer primary key autoincrement,title text,content text)") 11 #插入數(shù)據(jù)，id自增所以可以寫(xiě)null 12 cursor.execute("insert into diary1 values (null,'title1','content1')") 13 #插入數(shù)據(jù) 14 cursor.execute("insert into diary1(title,content) values ('title2','content2')") 15 16 #查詢表中所有數(shù)據(jù) 17 for row in cursor.execute("select * from diary1"): 18 print row 19 print '*******增*******' 20 21 22 #刪除id為1的數(shù)據(jù) 23 cursor.execute("delete from diary1 where id=1") 24 for row in cursor.execute("select * from diary1"): 25 print row 26 print '******刪除*******' 27 28 #修改數(shù)據(jù) 29 cursor.execute("update diary1 set title='title0',content='content0' where id =2") 30 for row in cursor.execute("select * from diary1"): 31 print row 32 print '*****改******' 33 34 #一次性插入多條數(shù)據(jù) 35 items=[('title0','content0'),('title1','content1'),('title2','content2')] 36 cursor.executemany("insert into diary1 (title,content) values(?,?)",items) 37 for row in cursor.execute("select * from diary1"): 38 print row 39 print "******插入多條數(shù)據(jù)******" 40 41 #查詢數(shù)據(jù) 42 print '查詢數(shù)據(jù)' 43 cursor.execute("select * from diary1 where id=4") 44 print cursor.fetchall() 45 46 cursor.execute("select * from diary1 where content='content0'") 47 print cursor.fetchall() 48 49 #關(guān)閉游標(biāo)和數(shù)據(jù)庫(kù) 50 cursor.close() 51 connect.close()

#效果

#注意到在python的sqlite3模塊中，數(shù)據(jù)庫(kù)中的每個(gè)記錄都是以一個(gè)tuple的形式存儲(chǔ)

#表中數(shù)據(jù)除了主鍵外其他字段的值都可以重復(fù)

?

#獲取當(dāng)前數(shù)據(jù)庫(kù)中的所有表

1 #coding:utf-8 2 import sqlite3 3 connect = sqlite3.connect("test.db") 4 cursor = connect.cursor() 5 6 7 8 #插入記錄,參數(shù)為表名（str）,數(shù)據(jù)(元組) 9 def insertData(table,tup): 10 global cursor 11 global connect 12 sql="insert into "+table+"(title,content) values (?,?)" 13 cursor.execute(sql,tup) 14 #對(duì)數(shù)據(jù)庫(kù)操作事務(wù)的提交 15 connect.commit() 16 17 #刪除特定id的記錄 18 def deleteData(name,id): 19 global cursor 20 global connect 21 sql="delete from "+name+" where id="+str(id) 22 cursor.execute(sql) 23 connect.commit() 24 25 #根據(jù)id修改數(shù)據(jù)，參數(shù)為表名，id（整型），數(shù)據(jù)（元組） 26 def updateData(name,id,tup): 27 global cursor 28 global connect 29 sql="UPDATE "+name+" SET title='"+tup[0]+"',content='"+tup[1]+"' WHERE id="+str(id) 30 cursor.execute(sql) 31 connect.commit() 32 33 #根據(jù)id查找數(shù)據(jù)，參數(shù)為表名（str）,id 34 def searchData(name,id): 35 #為什么這里又不需要global connect 36 global cursor 37 sql="select * from "+name+" where id="+str(id) 38 cursor.execute(sql) 39 #cursor.fetchall()和cursor.fetchone()區(qū)別？ 40 print u"要查找的數(shù)據(jù)為：",cursor.fetchone() 41 42 43 44 insertData('mydiary',('title0','content0')) 45 updateData("mydiary",2,('title3','content3')) 46 #deleteData('mydiary',1) 47 insertData('mydiary',('title9','content9')) 48 searchData("mydiary",6)

?

轉(zhuǎn)載于:https://www.cnblogs.com/IcarusYu/p/7536246.html

總結(jié)

以上是生活随笔為你收集整理的程序媛计划——python数据库的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。