pycharm连接MSSQL数据库

需要下载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()