增刪改數(shù)據(jù)必須connect.commit()才會生效?

回滾函數(shù) connect.rollback()

?

連接數(shù)據(jù)庫

''' dinghanhua sql server增刪改 '''import pymssqlserver = '192.168.1.1' user = 'user' password = '111111' database = 'test'dbconnect = pymssql.connect(server = server,user = user,password=password,database = database) #連接到數(shù)據(jù)庫

?

修改數(shù)據(jù)

dbcursor.execute("update test_student set name =%s where sno =1",'peter pan') dbcursor.execute("update test_student set name =%s where sno =2",'silina smith') dbconnect.commit() #增刪改數(shù)據(jù)后必須commit

?

刪除數(shù)據(jù)

dbcursor.execute("delete from test_student where sno =1",'peter pan') dbconnect.commit() #增刪改數(shù)據(jù)后必須commit

?

新增數(shù)據(jù)

dbcursor.execute("insert into test_teacher values(%d,%s,%s)",(2,'xingxing','111111')) dbconnect.commit() #增刪改數(shù)據(jù)后必須commit

?

commit多個

dbcursor.execute("insert into test_teacher values(%d,%s,%s)",(6,'xingxing6','111111')) dbcursor.execute("update test_student set name =%s where sno =2",'peter Panpan') dbconnect.commit() #提交多個

commit之前，游標(biāo)再執(zhí)行select取出的都是未提交的數(shù)據(jù)

?

rollback()

dbcursor.execute("insert into test_teacher values(%d,%s,%s)",(7,'xingxing7','111111')) dbconnect.rollback() #回滾 dbcursor.execute("update test_student set name =%s where sno =2",'peter') dbconnect.commit() #提交

?

最后關(guān)閉連接

dbcursor.close() dbconnect.close()

?

commit之后數(shù)據(jù)庫數(shù)據(jù)已變更，回滾是無效的，必須commit之前回滾。commit之前可以做下判斷。

#避免delete或update未加where語句 with pymssql.connect(server = server,user = user,password=password,database = database) as dbconnect:with dbconnect.cursor() as dbcursor:dbcursor.execute("delete from test_teacher") #假設(shè)忘記加deletedbcursor.execute("select count(1) from test_teacher") #查詢下刪除后數(shù)據(jù)個數(shù)if dbcursor.fetchone()[0] == 0:dbconnect.rollback() #回滾else:dbconnect.commit()

?

?

?cursor.executemany()

with pymssql.connect(server = server,user = user,password=password,database = database) as dbconnect:with dbconnect.cursor() as dbcursor:dbcursor.executemany("insert into test_teacher values (%s,%s,%s)",[(7,'xx','1'),(8,'yy','2'),(9,'zz','3')])dbconnect.commit()

?

with as? 替代手工關(guān)閉

with pymssql.connect(server = server,user = user,password=password,database = database) as dbconnect:with dbconnect.cursor(as_dict=True) as dbcursor:dbcursor.execute("insert into test_teacher values(%d,%s,%s)",(7,'xingxing7','111111'))dbconnect.rollback() #回滾dbcursor.execute("update test_student set name =%s where sno =2",'peter234')dbcursor.execute("select * from test_student")print(dbcursor.fetchall()) #取出的是未提交的數(shù)據(jù) dbconnect.commit()

?

the end！

?

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

總結(jié)

以上是生活随笔為你收集整理的python入门23 pymssql模块（python连接sql server增删改数据 ）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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