python连接SQL server实现insert() 和读取数据

发布日期：2022/9/2 6:42:33      浏览量：

首先需要安装一个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() # 获取光标

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