python入门23 pymssql模块(python连接sql server增删改数据 )
增删改数据必须connect.commit()才会生效
回滚函数 connect.rollback()
连接数据库
''' dinghanhua sql server增删改 ''' import pymssql server = '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") #假设忘记加delete
dbcursor.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!
当情绪低落,烦躁的时候,学技术并整理成博客能忘却所有烦恼。技术改变心境。