Pymssql 基本操作

import pymssql

class ConnectUtils:

    def __init__(self, db_name='tempdb'):
        server = '10.35.16.60'
        user = 'qadu008'
        password = '!zyn123!'
        self.conn = pymssql.connect(server, user, password, db_name)

    def qeury_all_table_name(self, db_name=None):
        with self.conn.cursor() as cursor:
            if db_name:
                cursor.execute(f'USE {db_name}')
            cursor.execute('select table_name FROM information_schema.tables')
            result = cursor.fetchall()
            db_names = [x[0] for x in result]
        return db_names

    def qeury_all_table(self, db_name=None):
        """

        :param db_name:
        :return: the table name and object id
        """
        with self.conn.cursor() as cursor:
            if db_name:
                cursor.execute(f'USE {db_name}')
            cursor.execute('select name,object_id FROM sys.tables')
            result = cursor.fetchall()
            db_names = [(x[0], x[1]) for x in result]
        return db_names

    def query_any_field(self,table_name, object_id, target, system_type_id):
        """
        There will be error when target type is not the same as column type
        :param table_name:
        :param object_id:
        :param target:
        :return:
        """
        with self.conn.cursor() as cursor:
            cursor.execute(
                f"select name from sys.columns  where  object_id ={object_id} and system_type_id = {system_type_id}")
            result = cursor.fetchall()
            result = [x[0] for x in result]
            if not result:
                return 0
            result = ','.join(result)
            cursor.execute(f"select count(*) from {table_name} where '{target}' in ({result})")
            result = cursor.fetchall()

            return result[0][0]

    def qeury_all_db_name(self):
        with self.conn.cursor() as cursor:
            cursor.execute('select name FROM sys.databases;')
            result = cursor.fetchall()
            db_names = [x[0] for x in result]
        return db_names

    def query_table(self, table_name, db_name=None):
        with self.conn.cursor() as cursor:
            if db_name:
                cursor.execute(f'USE {db_name}')
            sql = f"select * from {table_name}"
            cursor.execute(sql)
            for row in cursor:
                yield row

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值