python脚本数据库连接简单封装（工具）

最新推荐文章于 2024-08-14 12:00:04 发布

乡村蜗牛

最新推荐文章于 2024-08-14 12:00:04 发布

阅读量88

点赞数 3

文章标签： python 数据库开发语言

本文链接：https://blog.csdn.net/qq969887453/article/details/140955307

版权

# coding=utf8
import pymysql
from pymysql import err
from sshtunnel import SSHTunnelForwarder


"""
获取crm的数据到plm
1、项目相关数据
2、合同相关数据
"""


class MSP_Connect:
    def __init__(self, mysql_info: dict):
        try:
            self.host = mysql_info['host']
            self.user = mysql_info['user']
            self.password = mysql_info['password']
            self.db = mysql_info['db']
            self.port = mysql_info['port']
            flag = 'ssh' in mysql_info.keys()

            if flag:
                server = SSHTunnelForwarder(
                    (mysql_info['ssh']['host'], mysql_info['ssh']['port']),
                    ssh_username=mysql_info['ssh']['user'],
                    ssh_password=mysql_info['ssh']['password'],
                    remote_bind_address=(self.host, self.port)
                )
                server.start()

                cur = pymysql.connect(
                    host=self.host,
                    port=server.local_bind_port,
                    user=self.user,
                    password=self.password,
                    db=self.db,
                    charset="utf8"
                ).cursor()
                self.cur = cur
            else:
                self.con = pymysql.connect(
                    host=self.host,
                    user=self.user,
                    password=self.password,
                    db=self.db,
                    charset="utf8"
                )
                self.cur = self.con.cursor()
        except KeyError:
            print("数据库配置信息异常，请检查")
        except err.OperationalError as e:
            if e.args[0] == 1045:
                print("MySql密码错误")
            else:
                print("发生了其它连接错误")

    # 执行sql
    def execute(self, sql, values=None):
        if values == None:
            self.cur.execute(sql)
            return self.cur.fetchall()
        return self.cur.execute(sql, values)

    # 执行多条sql
    def execute_many(self, sql, values):
        return self.cur.executemany(sql, values)


    # 提交事务
    def commit(self):
        self.con.commit()

    # 释放资源
    def close(self):
        self.cur.close()
        self.con.close()

# coding=utf8

"""
数据库配置信息
"""

mysql_data = {
    "CRM": {
        "host": "192.168.66.180",
        "user": "**************",
        "password": "**************",
        "db": "msp_base_crm",
        "port": 3306
    },
    "PLM": {
        "host": "192.168.66.180",
        "user": "**************",
        "password": "**************",
        "db": "msp_platform_plm",
        "port": 3306
    },
    "MES": {
        "host": "192.168.66.180",
        "user": "**************",
        "password": "**************",
        "db": "msp_produce_mes",
        "port": 3306
    },
    "SCM": {
        "host": "192.168.66.180",
        "user": "**************",
        "password": "**************",
        "db": "msp_supply_scm",
        "port": 3306
    },
    "UC": {
        "host": "192.168.66.180",
        "user": "**************",
        "password": "**************",
        "db": "base_uc_uas",
        "port": 3306
    },
    "GDM": {
        "host": "192.168.66.180",
        "user": "**************",
        "password": "**************",
        "db": "msp_public_gdm",
        "port": 3306
    },
    "ESM": {
        "host": "192.168.66.180",
        "user": "**************",
        "password": "**************",
        "db": "msp_engineering_esm",
        "port": 3306
    },
    "LOCAL": {
        "host": "127.0.0.1",
        "user": "root",
        "password": "123456",
        "db": "update_test",
        "port": 3306
    },
    "78": {
        'host': '127.0.0.1',
        "user": '**************',
        'password': '**************',
        "db": "geto.pro",
        "port": 3306
    },
    "PLM-PRE": {
        'host': '192.168.67.183',
        "user": '**************',
        'password': '**************',
        "db": "msp_platform_plm",
        "port": 3306
    },
    "86": {
        'host': '127.0.0.1',
        "user": '**************',
        'password': '**************.',
        "db": "master.pro",
        "port": 3306,
        "ssh": {
            "host": "192.168.67.86",
            "user": "**************",
            "password": "**************",
            "port": 22
        }
    }
}