python连接mysql、hive、clickhouse数据库封装类（2023年2月）

最新推荐文章于 2024-06-21 16:49:30 发布
最新推荐文章于 2024-06-21 16:49:30 发布
阅读量1.1k 收藏 9
点赞数
分类专栏： python使用技巧 文章标签： 数据库 python mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/my_name_is_learn/article/details/129243809
版权

写这篇笔记的背景：

现在的数据库种类有很多，有数据仓库hive类型、有mysql数据库类型、也有ClickHouse数据库类型，针对不同的数据库类型，python读取的方式和代码都不一样。所以本文以读取数据库操作为例子，将这些连接不同数据库的操作整合成类。便于代码的使用和扩展。代码可以直接复制过去，修改ip、账号、密码、端口后直接使用

文章目录

工具库sqlalchemy简单介绍

需要用到的工具包是pandas和sqlalchemy。pandas是python的一种数据分析库。sqlalchemy是Python中最有名的ORM工具，特点是操纵Python对象而不是SQL查询，也就是在代码层面考虑的是对象，而不是SQL，体现的是一种程序化思维，这样使得Python程序更加简洁易读。

例如下面的User类表示的就是

包含id和name的user表

class User(object):
    def __init__(self, id, name):
        self.id = id
        self.name = name
# 下面就是user表
[
    User('1', 'Michael'),
    User('2', 'Bob'),
    User('3', 'Adam')
]

我们可以通过sqlalchemy连接mysql数据库，然后进行各种增删改查，这里只演示查询读取数据。

读取mysql数据的最小单元例子

  • 连接数据库，生成数据库引擎

    # 各种信息需要按照格式这样子编排
connection_str = 'mysql+pymysql://%s:%s@%s:%d/%s' %(
            self.user, self.password, self.host, self.port, self.database
        )
# 创建一个连接引擎
engine = sqlalchemy.create_engine(connection_str)

  • 根据数据库引擎，创建连接

    conn = engine.connect()

  • 根据连接读取数据

    data = pd.read_sql(sql, conn)  # sql就是你自己的sql语句

  • 第四步，关闭连接，然后释放引擎

    conn.close()  # 关闭连接
engine.dispose()  # 释放引擎

连接mysql的代码封装成一个类

封装成一个类的好处就是你想要用，直接把这个类拿出来即可。

相关的说明在代码里面有详细的注释，里面的ip、账号、密码、等信息是虚假的。可直接复制过去使用。

import logging
import pandas as pd
import sqlalchemy
from sqlalchemy.orm import sessionmaker
import os
import time


class MySqlHelper(object):
    def __init__(
        self,
        host='192.168.15.124',
        port=3306,
        database='tert_ims',
        user='spkjz_wyiter',
        password='7cmoP3PDtueVJQj2q4Az',
        logger:logging.Logger=None
        ):
        self.host = host
        self.port = port
        self.database = database
        self.user = user
        self.password = password
        self.logger = logger

        self.connection_str = 'mysql+pymysql://%s:%s@%s:%d/%s' %(
            self.user, self.password, self.host, self.port, self.database
        )

        self.conn = None
        self.cursor = None
        self.engine = None
        self.session = None

    def create_table_code(self, file_name):
        '''创建表类代码'''
        os.system(f'sqlacodegen {self.connection_str} > {file_name}')
        return self.conn

    def get_conn(self):
        '''创建连接或获取连接'''
        if self.conn is None:
            engine = self.get_engine()
            self.conn = engine.connect()
        return self.conn

    def get_engine(self):
        '''创建连接或获取连接'''
        if self.engine is None:
            self.engine = sqlalchemy.create_engine(self.connection_str)
        return self.engine

    def get_cursor(self):
        '''创建连接或获取连接'''
        if self.cursor is None:
            self.cursor = self.conn.cursor()
        return self.cursor

    def get_session(self) -> sessionmaker:
        '''创建连接或获取连接'''
        if self.session is None:
            engine = self.get_engine()
            Session = sessionmaker(bind=engine)
            self.session = Session()
        return self.session
        
    def close_conn(self):
        '''关闭连接'''
        if self.conn is not None:
            self.conn.close()
            self.conn = None
        self.dispose_engine()
        
    def close_session(self):
        '''关闭连接'''
        if self.session is not None:
            self.session.close()
            self.session = None
        self.dispose_engine()
        
    def dispose_engine(self):
        '''释放engine'''
        if self.engine is not None:
            # self.engine.dispose(close=False)
            self.engine.dispose()
            self.engine = None
        
    def close_cursor(self):
        '''关闭cursor'''
        if self.cursor is not None:
            self.cursor.close()
            self.cursor = None

    def get_data(self, sql, auto_close=True) -> pd.DataFrame:
        '''查询数据'''
        conn = self.get_conn()
        data = None
        try:
            # 异常重试3次
            for i in range(3):
                try:
                    data = pd.read_sql(sql, conn)
                    break
                except Exception as ex:
                    if i == 2:
                        raise ex # 往外抛出异常
                    time.sleep(60) # 一分钟后重试
        except Exception as ex:
            self.logger.exception(ex)
            raise ex # 往外抛出异常
        finally:
            if auto_close:
                self.close_conn()
        return data

读取hive数据的最小单元例子,需要用到impala

  • 连接数据库，生成数据库引擎

    from impala.dbapi import connect
import sqlalchemy
impala_conn = connect(
                host=self.host,
                port=self.port,
                database=self.database,
                auth_mechanism=self.auth_mechanism,
                user=self.user,
                password=self.password
                )
engine = sqlalchemy.create_engine('impala://', creator=get_impala_conn)

  • 根据数据库引擎，创建连接

    conn = engine.connect()

  • 根据连接读取数据

    data = pd.read_sql(sql, conn)  # sql就是你自己的sql语句

  • 第四步，关闭连接，然后释放引擎

    conn.close()  # 关闭连接
engine.dispose()  # 释放引擎

可以看到，使用sqlalchemy的话，对于hive数据库而言，跟mysql唯一不同的只是在第一步生成引擎的时候有差异，其它用法都没有差异

连接hive的代码封装成一个类

import logging
import pandas as pd
from impala.dbapi import connect
import sqlalchemy
from sqlalchemy.orm import sessionmaker
import os
import time


class HiveHelper(object):
    def __init__(
        self,
        host='10.2.32.12',
        port=21051,
        database='ur_ti_dw',
        auth_mechanism='LDAP',
        user='urberi',
        password='Ur#730exd',
        logger:logging.Logger=None
        ):
        self.host = host
        self.port = port
        self.database = database
        self.auth_mechanism = auth_mechanism
        self.user = user
        self.password = password
        self.logger = logger

        self.impala_conn = None
        self.conn = None
        self.cursor = None  # 这里没有详细解释cursor的用法，感兴趣可以去sqlalchemy官网了解
        self.engine = None
        self.session = None  # 这里没有详细解释session的用法，感兴趣可以去sqlalchemy官网了解

    def create_table_code(self, file_name):
        '''创建表类代码'''
        os.system(f'sqlacodegen {self.connection_str} > {file_name}')
        return self.conn

    def get_conn(self):
        '''创建连接或获取连接'''
        if self.conn is None:
            engine = self.get_engine()
            self.conn = engine.connect()
        return self.conn

    def get_impala_conn(self):
        '''创建连接或获取连接'''
        if self.impala_conn is None:
            self.impala_conn = connect(
                host=self.host,
                port=self.port,
                database=self.database,
                auth_mechanism=self.auth_mechanism,
                user=self.user,
                password=self.password
                )
        return self.impala_conn

    def get_engine(self):
        '''创建连接或获取连接'''
        if self.engine is None:
            self.engine = sqlalchemy.create_engine('impala://', creator=self.get_impala_conn)
        return self.engine

    def get_cursor(self):
        '''创建连接或获取连接'''
        if self.cursor is None:
            self.cursor = self.conn.cursor()
        return self.cursor

    def get_session(self) -> sessionmaker:
        '''创建连接或获取连接'''
        if self.session is None:
            engine = self.get_engine()
            Session = sessionmaker(bind=engine)
            self.session = Session()
        return self.session
        
    def close_conn(self):
        '''关闭连接'''
        if self.conn is not None:
            self.conn.close()
            self.conn = None
        self.dispose_engine()
        self.close_impala_conn()
        
    def close_impala_conn(self):
        '''关闭impala连接'''
        if self.impala_conn is not None:
            self.impala_conn.close()
            self.impala_conn = None
        
    def close_session(self):
        '''关闭连接'''
        if self.session is not None:
            self.session.close()
            self.session = None
        self.dispose_engine()
        
    def dispose_engine(self):
        '''释放engine'''
        if self.engine is not None:
            # self.engine.dispose(close=False)
            self.engine.dispose()
            self.engine = None
        
    def close_cursor(self):
        '''关闭cursor'''
        if self.cursor is not None:
            self.cursor.close()
            self.cursor = None

    def get_data(self, sql, auto_close=True) -> pd.DataFrame:
        '''查询数据'''
        conn = self.get_conn()
        data = None
        try:
            # 异常重试3次
            for i in range(3):
                try:
                    data = pd.read_sql(sql, conn)
                    break
                except Exception as ex:
                    if i == 2:
                        raise ex # 往外抛出异常
                    time.sleep(60) # 一分钟后重试
        except Exception as ex:
            self.logger.exception(ex)
            raise ex # 往外抛出异常
        finally:
            if auto_close:
                self.close_conn()
        return data

读取ClickHouse数据库的最小单元例子

  • 连接数据库，生成数据库引擎

    
import sqlalchemy
connection_str = 'clickhouse://%s:%s@%s:%d/%s?ssl=True' %(
            self.user, self.password, self.host, self.port, self.database
        )
engine = sqlalchemy.create_engine(self.connection_str)

  • 根据数据库引擎，创建连接

    conn = engine.connect()

  • 根据连接读取数据

    data = pd.read_sql(sql, conn)  # sql就是你自己的sql语句

  • 第四步，关闭连接，然后释放引擎

    conn.close()  # 关闭连接
engine.dispose()  # 释放引擎

显而易见，更上面的差别也只是在生成引擎部分而已。不严谨得推测，不管是什么类型数据库，用上面这个流程，根据官网的提示去生成对应的引擎即可。这就是sqlalchemy的优点之处，对所有数据库进行了整合。

连接ClickHouse的代码封装成一个类

同学们，阅读一遍代码很重要，代码都有注释

import logging
import pandas as pd
import sqlalchemy
from sqlalchemy.orm import sessionmaker
import os
import time


class ClickhouseHelper(object):
    def __init__(
        self,
        host='10.2.160.11',
        port=8123,
        database='urbidb',
        user='admin',
        password='UrClickhouse123admin',
        logger:logging.Logger=None
        ):
        self.host = host
        self.port = port
        self.database = database
        self.user = user
        self.password = password
        self.logger = logger

        self.connection_str = 'clickhouse://%s:%s@%s:%d/%s?ssl=True' %(
            self.user, self.password, self.host, self.port, self.database
        )

        self.conn = None
        self.cursor = None
        self.engine = None
        self.session = None

    def create_table_code(self, file_name):
        '''创建表类代码'''
        os.system(f'sqlacodegen {self.connection_str} > {file_name}')
        return self.conn

    def get_conn(self):
        '''创建连接或获取连接'''
        if self.conn is None:
            engine = self.get_engine()
            self.conn = engine.connect()
        return self.conn

    def get_engine(self):
        '''创建连接或获取连接'''
        if self.engine is None:
            self.engine = sqlalchemy.create_engine(self.connection_str)
        return self.engine

    def get_cursor(self):
        '''创建连接或获取连接'''
        if self.cursor is None:
            self.cursor = self.conn.cursor()
        return self.cursor

    def get_session(self) -> sessionmaker:
        '''创建连接或获取连接'''
        if self.session is None:
            engine = self.get_engine()
            Session = sessionmaker(bind=engine)
            self.session = Session()
        return self.session
        
    def close_conn(self):
        '''关闭连接'''
        if self.conn is not None:
            self.conn.close()
            self.conn = None
        self.dispose_engine()
        
    def close_session(self):
        '''关闭连接'''
        if self.session is not None:
            self.session.close()
            self.session = None
        self.dispose_engine()
        
    def dispose_engine(self):
        '''释放engine'''
        if self.engine is not None:
            # self.engine.dispose(close=False)
            self.engine.dispose()
            self.engine = None
        
    def close_cursor(self):
        '''关闭cursor'''
        if self.cursor is not None:
            self.cursor.close()
            self.cursor = None

    def get_data(self, sql, auto_close=True) -> pd.DataFrame:
        '''查询数据'''
        conn = self.get_conn()
        data = None
        try:
            # 异常重试3次
            for i in range(3):
                try:
                    data = pd.read_sql(sql, conn)
                    break
                except Exception as ex:
                    if i == 2:
                        raise ex # 往外抛出异常
                    time.sleep(60) # 一分钟后重试
        except Exception as ex:
            self.logger.exception(ex)
            raise ex # 往外抛出异常
        finally:
            if auto_close:
                self.close_conn()
        return data

有了这个基础的连接类之后，你可以根据不同类的不同对象去实现数据库的所有增删改查。因为我是做人工智能算法的，所以写入操作和读取操作用得比较多，修改和更新操作用得不多。

是猪哥不是诸葛
关注
  • 0
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sqlalchemy-clickhouse
05-02
sqlalchemy-clickhouse 用于SQLAlchemy的ClickHouse方言。 安装 该软件包可通过PIP安装： pip install sqlalchemy-clickhouse 用法 DSN格式与常规Postgres格式相似： >>> import sqlalchemy as sa >>> sa.create_engine('clickhouse://username:password@hostname:port/database') Engine('clickhouse://username:password@hostname:port/database') 对于SSL，请将ssl参数添加到URL： >>> import sqlalchemy as sa >>> sa.create_engine('clickhouse://username:password
支持hive访问的sqlalchemy库离线安装
建模分析师胡选来
08-22 1829
需要安装sqlalchemy和pyhive库。 下载wheel的命令： pip2 wheel --trusted-host pypi.douban.com -i http://pypi.douban.com/simple -r python-dependencies.txt -w packs 其中packs为wheel下载到的目录，python-dependencies.txt为要下载的库列表文件，格式如下： ----- pandas==0.23.4 numpy==1.16.0 sqlalchemy py
Python 连接clickhouse常用的三种方式
最新发布
qiuweifan的博客
06-21 1726
ClickHouse是一个开源的分布式列式数据库管理系统，它被设计用于存储和分析大规模数据。Python是一种流行的编程语言，凭借其简洁的语法和丰富的生态系统，成为了数据处理和分析的首选语言之一。在Python中，我们可以使用多种方式与ClickHouse进行连接并操作数据。本文将详细介绍Python连接ClickHouse的三种常用方式，分别是使用clickhouse-driver、clickhouse-sqlalchemy和pyclickhouse库。
SQLAlchemy
星际老男孩
06-03 4204
基于SQLAlchemy连接mysql库（pymsql插件） # -*- coding:UTF -8-*- import os import pandas as pd # 检查是否有pymysql这个包存在 import pymysql # SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件。 from sqlalchemy import create_engine,Se...
sqlalchemy连接clickhouse批量插入数据
qq_41562377的博客
08-11 2204
sqlalcgemy连接clickhouse
使用python连接数据库
夏日麦香的博客
05-08 1535
import pandas as pd import sqlalchemy import pymysql import pymssql import pyhive # 连接hive def con_hive(sql): user_name = "hive链接地址" hive_engine = sqlalchemy.create_engine(user_name) data = pd.read_sql(sql, hive_engine) print ("\n数据读取完毕\n.
Python操作Mysql的简单封装
什么都干的派森
05-16 2154
文章目录一、安装依赖二、实现类 一、安装依赖 pip install mysqlclient 二、实现类 # encoding: utf-8 # sql导出 # mysqldump -u root -p db_name > test_db.sql import MySQLdb class MySqlOp(object): def __init__(self, host, port, username, password, db): self.host = host
SpringBoot2 整合 ClickHouse数据库案例解析
08-25
SpringBoot2 整合 ClickHouse数据库案例解析是一个深入讲解如何在SpringBoot2应用程序中集成ClickHouse数据库的过程。ClickHouse是一个由Yandex开发的开源数据库，专门用于数据分析（OLAP）场景，尤其适合处理大规模...
使用mysql作为hive的元数据库
12-14
`，以验证Hive是否能成功连接到MySQL元数据库并执行操作。 通过上述步骤，你就成功地将MySQL配置为Hive的元数据库，提高了Hive在大数据处理中的性能和稳定性。这使得Hive在处理大量数据时能够更有效地管理元数据，...
在python中使用pyspark读写Hive数据操作
09-16
2. **Hive**: Hive是基于Hadoop的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供SQL查询功能。Hive提供了数据汇总、分析和查询的高效方法，特别适合大数据处理场景。 3. **读取Hive数据**: - `...
Python 连接Hive离线方式1
08-08
Python 连接 Hive 离线方式1 是指使用 pyhs2 库连接 Hive 数据库的离线方式。这种方式可以在不依赖 Hive 服务端的情况下，连接 Hive 数据库实现数据交互。 描述解释 Python 连接 Hive 离线方式1 的描述是指使用 ...
sparksql连接mysql，hive
05-02
使用`SparkSession.builder().appName("yourApp").enableHiveSupport().getOrCreate()`来创建一个支持Hive的SparkSession，然后可以执行如`spark.read.format("jdbc").options(...).load()`来连接MySQL数据库。...
python连接hive
haveanybody的博客
09-18 3816
前言 之前在读hive数据的时候，经常使用读hdfs parquet文件的方法，虽然封装函数一样，调用起来也方便，但是总觉得不得劲，既然我需要的是hive数据，为何不直接读hive呢？刚好今天又遇到了这个问题，就花了两个小时，研究了不同的方法，mark一下，以便查阅。好了，进入正题，下面列出了两种方法，但大体上差不多，可根据需要选择。另外，还看到使用 impala 操作hive的方法，没有详细研...
python 操作 mysql (简单好用的封装)
qq_42722515的博客
12-15 441
@[TOC](python 操作 mysql (简单好用的封装)) 本文需要使用 pymysql 包 导包(使用代理网址，速度快些) pip install -i https://pypi.douban.com/simple PyMySQL 工具类的封装 工具简单的封装查询和修改方法，revise 主要执行对数据库的增删改查 import pymysql class SqlUtil(object): def __init__(self,host,port,username,passw
python3安装clickhouse_sqlalchemy(greenlet) 失败
高性价比服务器就选：蓝易云
01-28 661
如果上述方法仍然无法解决问题，建议查阅相关错误信息和官方文档，以获取更详细的帮助。确保你的Python环境和依赖库都在最新版本，有时问题可能会因为版本不兼容而导致安装失败。在使用虚拟环境（virtual environment）时，有时可以避免依赖冲突问题。创建虚拟环境并激活后，再安装相关包。如果仍然遇到问题，尝试使用源代码手动编译安装。时可能遇到依赖问题，特别是。
【flask + sqlalchemy】连接clickhouse数据库的踩的坑，在这里记录一下
weixin_40293999的博客
05-23 955
使用clickhouse有一段时间了，现在要重构一个项目，重度依赖clickhouse，现在终于理顺了，记录一下。
在Python项目中，如何进行数据库连接、数据操作封装和错误处理？
sdasdas12的博客
02-23 597
在Python项目中，进行数据库连接和数据操作的封装是非常重要的，可以提高代码的可维护性和可扩展性，同时也有利于统一管理数据库连接和操作逻辑。在封装数据库连接和数据操作时，我们通常会考虑以下几个方面：连接管理、数据操作封装和错误处理。下面我将详细介绍如何进行数据库连接和数据操作的封装以及错误处理的最佳实践。
flask--基础知识点--5--clickhouse_sqlalchemy的使用
Raging__Fire的博客
03-06 868
【代码】flask--基础知识点--5--clickhouse_sqlalchemy的使用。
Python连接操作常见数据库的封装
seven的博客
08-09 1415
在工作中经常会使用python连接常见的数据库编写python脚本，用于数据分析中一些过程表的清洗与汇总操作，为了方便现将常见的数据库的操作封装在一起，方便调用使用，Spark连接操作后期补充欢迎指正。 import sys import logging import pymysql import cx_Oracle from pyhive import sqlalchemy_hive, hive from clickhouse_driver import Client # from pyspark.sq
Python使用pyspark连接远程hive数据库
05-31
要在 PySpark 中连接远程 Hive 数据库，可以按照以下步骤进行操作： 1. 确保远程 Hive 服务器已经启动，并且您已经拥有连接远程 Hive 的权限。 2. 在 PySpark 中创建 SparkSession 对象，并将 Hive 库设置为默认库： ```python from pyspark.sql import SparkSession spark = SparkSession.builder \ .appName("HiveExample") \ .config("hive.metastore.uris", "thrift://<host>:<port>") \ # 替换为远程Hive服务的主机名和端口号 .config("spark.sql.warehouse.dir", "/user/hive/warehouse") \ # 替换为Hive库的路径 .enableHiveSupport() \ .getOrCreate() ``` 3. 输入上述代码后，您可以使用 spark 对象连接远程 Hive 数据库，并执行 SQL 查询，例如： ```python df = spark.sql("SELECT * FROM <table_name> LIMIT 10") # 替换为要查询的表名 df.show() ``` 请注意，连接远程 Hive 数据库可能需要额外的配置和权限，具体取决于您使用的 Hive 版本和设置。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值