python3 sqlserver数据库连接

最新推荐文章于 2023-10-23 00:09:15 发布

原创最新推荐文章于 2023-10-23 00:09:15 发布 · 665 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#python3 #sql server

python3 专栏收录该内容

28 篇文章

订阅专栏

本文介绍了一个使用 Python 的 pymssql 库进行 SQL Server 数据库操作的类实现，包括连接数据库、创建表、批量插入数据、删除记录及关闭连接等功能。

import pymssql

from vslogging import logger

class Database(object):
    def __init__(self):
        self.__logger = logger
        self.__dbserver = ''
        self.__dbuser = ''
        self.__dbpassword = ''
        self.__dbdatabase = ''
        self.__conn = self.connectSqlServer()

        if(self.__conn):
            self.__cursor = self.__conn.cursor()


    # 数据库连接
    def connectSqlServer(self):
        conn = False
        try:
            conn = pymssql.connect(server=self.__dbserver,
                                   user=self.__dbuser,
                                   password=self.__dbpassword,
                                   database=self.__dbdatabase)
        except Exception as data:
            self.__logger.error("connect database failed, %s" % data)
            conn = False
        return conn


    def creat_table(self, sql):
        iscreat = False
        if(self.__conn):
            try:
                self.__cursor.execute(sql)
                self.__conn.commit()
                iscreat = True
            except Exception as data:
                self.__conn.rollback()
                iscreat = False
                self.__logger.warn("insert database exception, %s" % data)

        return iscreat


    # 批量插入数据
    def insert_batch(self, sql, record_list):
        flag = False
        if(self.__conn):
            try:
                self.__cursor.executemany(sql, record_list)
                self.__conn.commit()
                flag = True
            except Exception as data:
                self.__conn.rollback()
                flag = False
                self.__logger.warn("insert database exception, %s" % data)

        return flag


    # 删除重复数据
    def delete(self, sql):
        status = False
        if(self.__conn):
            try:
                self.__cursor.execute(sql)
                self.__conn.commit()
                status = True
            except Exception as data:
                self.__conn.rollback()
                status = False
                self.__logger.warn("delete database exception, %s" % data)

        return status


    # 关闭数据库连接
    def close(self):
        if(self.__conn):
            try:
                if(type(self.__cursor)=='object'):
                    self.__cursor.close()
                if(type(self.__cursor)=='object'):
                    self.__cursor.close()
            except Exception as data:
                self.__logger.warn("close database exception, %s,%s,%s"
                                   % (data, type(self.__cursor), type(self.__conn)))