python 连接sqlserver

最新推荐文章于 2025-10-29 18:11:54 发布

转载最新推荐文章于 2025-10-29 18:11:54 发布 · 3k 阅读

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/iamoldpan/article/details/78077983

本文详细介绍使用Python的pymssql模块进行SQL Server数据库的基本操作，包括连接数据库、创建表、批量插入数据、删除记录、查询数据及更新操作。通过具体代码示例，帮助读者快速掌握如何在Python中高效地进行SQL Server数据库的CRUD操作。

# -*- encoding: utf-8 -*-

import pymssql

# server    数据库服务器名称或IP
# user      用户名
# password  密码
# database  数据库名称

server = "."
user = "***"
password = "***"
database = "***"
conn = pymssql.connect(server, user, password, database)
cursor = conn.cursor()


# 新建表


def CreateTable():
    sql = """
    IF OBJECT_ID('persons', 'U') IS NOT NULL DROP TABLE persons
    CREATE TABLE persons (id INT NOT NULL identity(1,1),name VARCHAR(100),age int,PRIMARY KEY(id))
    """
    cursor.execute(sql)
    conn.commit()


# 批量插入数据
def InsertData():
    sql = "INSERT INTO persons(name,age) VALUES (%s, %d)"
    data = [
        ('zhangsan', 15),
        ('lisi', 16),
        ('wangwu T.', 17)]
    cursor.executemany(sql, data)
    # 如果没有指定autocommit属性为True的话就需要调用commit()方法
    conn.commit()


# 删除操作
def DeleteData():
    sql = "delete persons where id=5"
    cursor.execute(sql)
    conn.commit()


# 查询操作
def SelectTable():
    sql = "SELECT * FROM sys_user"
    cursor.execute(sql)
    row = cursor.fetchone()
    while row:
        print("ID=%s, Name=%s" % (row[0], row[1]))
        row = cursor.fetchone()
    # 也可以使用for循环来迭代查询结果
    # for row in cursor:
    #     print("ID=%d, Name=%s" % (row[0], row[1]))


# 修改操作
def UpdateData():
    sql = "update [persons] set name ='Python1' where id<3"
    cursor.execute(sql)
    conn.commit()


def main():
    # CreateTable()
    # InsertData()
    # DeleteData()
    # UpdateData()
    SelectTable()
    conn.close()


if __name__ == '__main__':
    main()