使用python连接oracle/mysql/sqlserver

最新推荐文章于 2024-07-30 17:23:09 发布

we6100

最新推荐文章于 2024-07-30 17:23:09 发布

阅读量164

点赞数

分类专栏： Python 文章标签： oracle sqlserver python mysql

本文链接：https://blog.csdn.net/we6100/article/details/127026370

版权

Python 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

原文：https://www.yuque.com/wei01/tl0iqs/yae8gi

# @Time      : 2020/4/21
# @File      : db.py
# @Software  : PyCharm
# @Desc      :

import cx_Oracle
import pymysql
import pymssql
import logging

logging.basicConfig(level=logging.ERROR,
                    format='[%(asctime)s] - [%(threadName)5s] - [%(filename)s-line:%(lineno)d] [%(levelname)s] %(message)s',
                    filename='error.log',
                    filemode='a'
                    )


class DbHelper:
    def __init__(self, dbtype, host, user, password, database, port):
        '''
        使用办法：
        from __future__ import division
        import sys
        reload(sys)
        sys.path.append('/path/')
        from db import DbHelper
        先创建类对象：对象名= DbHelper('数据库类型：MYSQL/ORACLE/SQLSERVER','IP','用户名','密码','数据库名','端口')
        再调用对象的函数
        例如 mh = DbHelper('MYSQL','192.168.100.100', 'root', '123456', 'test', '3306')
        sql = "select * from user where id=%s"
        params = list(range(1,10))
        for i in params:
            newsql = sql % i
            print(mh.query(newsql)
        '''
        self.dbtype = dbtype
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.charset = 'utf8'
        self.port = int(port)
        self.conn = None
        self.curs = None

    def open(self):
        # 数据库连接
        try:
            # 支持oracle多线程,普通用户连接
            if self.dbtype == 'ORACLE':
                # self.conn = cx_Oracle.connect('{0}/{1}@{2}:{3}/{4}'.format(self.user, self.password, self.host, self.port, self.database),threaded=True, events=True,mode=cx_Oracle.SYSDBA)
                self.conn = cx_Oracle.connect(
                    '{0}/{1}@{2}:{3}/{4}'.format(self.user, self.password, self.host, self.port, self.database),
                    threaded=True, events=True)
            elif self.dbtype == 'MYSQL':
                self.conn = pymysql.connect(host=self.host, user=self.user, password=self.password, db=self.database,
                                        port=self.port, charset=self.charset)
            elif self.dbtype == 'SQLSERVER':
                self.conn = pymssql.connect(host=self.host, user=self.user, password=self.password, db=self.database,
                                             port=self.port, charset=self.charset)
            self.curs = self.conn.cursor()
        except Exception as e:
            logging.error('%s %s connect failed' % (self.database, self.host))
            logging.error(e)

    def close(self):
        # 数据库关闭
        self.curs.close()
        self.conn.close()

    def idu(self, sql):
        '''
        数据增删改，使用idu函数
        :param sql: 执行的sql
        :return: 返回结果
        '''
        self.open()
        try:
            self.curs.execute(sql)
            self.conn.commit()
        except Exception as e:
            logging.error('%s %s exec failed' % (self.database, self.host))
            logging.error(e)
            self.conn.rollback()
        self.close()

    def query(self, sql):
        '''
        数据查询函数，有返回值
        :param sql: 执行的SQL
        :return: 返回结果
        '''
        self.open()
        try:
            result = self.curs.execute(sql)
            results = self.curs.fetchall()
            self.close()
            return results
        except Exception as e:
            logging.error('%s %s exec failed' % (self.database, self.host))
            logging.error(e)