增刪改數據必須connect.commit()才會生效?
回滾函數 connect.rollback()
?
連接數據庫
''' 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) #連接到數據庫
?
修改數據
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() #增刪改數據后必須commit
?
刪除數據
dbcursor.execute("delete from test_student where sno =1",'peter pan') dbconnect.commit() #增刪改數據后必須commit
?
新增數據
dbcursor.execute("insert into test_teacher values(%d,%s,%s)",(2,'xingxing','111111')) dbconnect.commit() #增刪改數據后必須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之前，游標再執行select取出的都是未提交的數據
?
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() #提交
?
最后關閉連接
dbcursor.close()
dbconnect.close() ?
commit之后數據庫數據已變更，回滾是無效的，必須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") #假設忘記加deletedbcursor.execute("select count(1) from test_teacher") #查詢下刪除后數據個數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? 替代手工關閉
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()) #取出的是未提交的數據 dbconnect.commit()
?
the end！
?
