python 连接 SQL server 实现insert() 和 查询操作；

前提：

安装一个package：pymssql  (如何安装​​参考博文​​)

1、数据库连接

import pymssql as py
import pandas as pd
# 连接数据库，创建学生表，进行表查询，表录入
server = "DESKTOP-G8THN71"# 连接服务器地址
user = "sa"# 连接帐号
password = "1234"# 连接密码
conn = py.connect(server, user, password, "student_message")  #获取连接
cursor = conn.cursor() # 获取光标

server的名字为：

2、数据库插入

def insert(Name, studentID, Sex):
    count_students = 0
    conn = py.connect(server, user, password, "student_message")  # 获取连接
    cursor =conn.cursor()
    cursor.execute('  select count(ID) from students')
    for row in cursor:
        count_students = row[0]
        print(row[0])
    cursor.executemany(
        "INSERT INTO students VALUES (%d, %s, %d,%s)",
        [(count_students+1, Name, studentID, Sex)])
# 你必须调用 commit() 来保持你数据的提交如果你没有将自动提交设置为true
    conn.commit()

 

3、通过某项信息，查询返回信息

def readName(idnum):
    Name = -1
    conn = py.connect(server, user, password, "student_message")  # 获取连接
    cursor =conn.cursor()
    cursor.execute('  select Name from students where ID='+str(idnum))
    for row in cursor:
        if row[0]!=[]:
            Name = row[0]
    conn.commit()
    return Name

其他select 操作只需要改一下就好了。