需要下载pymssql
查询数据库
import pymssql serverName = '127.0.0.1'#服务器本机是127.0.0.1,也可以是1433端口,但要保证1433端口打开 userName = 'sa' #账号 passWord = '123456' #密码 sql_select = "SELECT*FROM S"#查询语句 sql_insert = "insert into S(SNO,SNAME,AGE,SEX,DNAME) values(%s,%s,%d,%s,%s)"#插入语句 dan={0,0,0,0,0} #sql_update = "update S set SNO='s20' where SNO='s21'"#耿欣语句 sql_delete = "delete form S where S=sll"#删除语句 try: connect = pymssql.connect(server=serverName, user=userName, password=passWord, database='python') # 服务器名,账号,密码,数据库名 sql_update=input("请输入需要更新:") #dan1=input("更新的学号为:") # dan=(dan0,dan1) cursor = connect.cursor() #sql_update = input("请输入需要更新的:") cursor.execute(sql_update) # 执行sql语句 print("更新成功") connect.commit() connect.close()
插入并查询数据库
import pymssql serverName = '127.0.0.1' userName = 'sa' passWord = '123456' sql_select = "SELECT*FROM S" sql_insert = "insert into S(SNO,SNAME,AGE,SEX,DNAME) values(%s,%s,%d,%s,%s)" sql_update = ("update S set SNO=s11 where SNO=s20") sql_delete = "delete form S where S=sll" try: connect = pymssql.connect(server=serverName, user=userName, password=passWord, database='python') print("请输入:") dan0=input("学号:") dan1 = input("姓名:") dan2 = input("年龄:") dan3 = input("性别:") dan4 = input("专业:") # 服务器名,账号,密码,数据库名 cursor = connect.cursor() dan=(dan0,dan1,dan2,dan3,dan4) sql_insert = "insert into S(SNO,SNAME,AGE,SEX,DNAME) values(%s,%s,%d,%s,%s)" cursor.execute(sql_insert, dan) # 执行sql语句 print("插入成功") connect.commit() connect.close() conn = pymssql.connect(serverName, userName, passWord, 'python', charset='cp936') cursor = conn.cursor() cursor.execute(sql_select) # 执行语句 results = cursor.fetchall() # 获取所有记录列表 for result in results: result = list(result) # 元组转化为列表 for res in range(len(result)): if isinstance(result[res], str): result[res] = result[res].replace(' ', '') result = tuple(result) # 列表再转换为元组 print("处理后:", end="") print(result) # conn.commit() conn.close() except Exception as e: print("插入失败 已经存在") conn = pymssql.connect(serverName, userName, passWord, 'python', charset='cp936') cursor = conn.cursor() cursor.execute(sql_select) # 执行语句 results = cursor.fetchall() # 获取所有记录列表 for result in results: result = list(result) # 元组转化为列表 for res in range(len(result)): if isinstance(result[res], str): result[res] = result[res].replace(' ', '') result = tuple(result) # 列表再转换为元组 print("处理后:", end="") print(result) # conn.commit() conn.close()