1、引言
小屌丝：鱼哥，需要你的协助。
小鱼：不好意思，不在线。
小屌丝：别这么搞，有重要事情需要你的支持。
小鱼：哦，啥时呢？
小屌丝：我需要对比两个数据库的数据。
小鱼：这还不好办，你直接用眼睛看就行了。
小屌丝：搞啥呢？ 每个表都是500W条数据起步， 你要搞我？
小鱼：没有啊， 我以为就几条数据了。
小屌丝：你才几条数据…
小鱼：没事了，我忙了。
小屌丝：别别别… 我就几条数据，但是为了节省时间，还是希望鱼哥帮忙搞一个自动对比工具。
小鱼：什么本？
小屌丝：数据对比脚本
小鱼：数什么？
小屌丝：数据对比脚本
小鱼：数据什么？
小屌丝：数据对比脚本
小鱼：昂，
小屌丝：这会听清楚了吧
小鱼：听清楚了。
小屌丝：那鱼哥帮忙搞一下？
小鱼：昂，晚上去按脚啊
小屌丝：好，你搞完，咱就去按脚。
小鱼：啊？还要去洗澡？
小屌丝：… 好，去去，去洗澡。
小鱼：啊，还要吃海胆？
小屌丝：行， 可以， 都整。
小鱼：那等我一会，我换个衣服。
小屌丝：…
2、代码实战
2.1 MySQL数据连接
如果要直连数据库，那么就需要借助于第三方库。
这里，小鱼介绍两款 直接连接mysql数据库的三方库：
- mysql.connector；
- pymysql；
接下来，就跟着小鱼的脚步，来了解这两个库吧
2.1.1 mysql.connector
-
定义：
- mysql.connector是MySQL官方提供的Python连接器，它允许Python程序员使用Python来访问MySQL数据库。
- 它是基于Python的C语言扩展，为Python提供了与MySQL数据库进行交互的功能。
-
用法：
import mysql.connector
'''
实现功能：
mysql.connector 连接MySQL数据库
'''
# 创建连接
cnx = mysql.connector.connect(
host="localhost", # 数据库主机地址
user="username", # 数据库用户名
password="password", # 数据库密码
database="mydatabase" # 连接的数据库名
)
# 创建游标
cursor = cnx.cursor()
# 执行SQL语句
cursor.execute("SELECT * FROM mytable")
# 获取查询结果
results = cursor.fetchall()
# 处理查询结果
for row in results:
print(row)
# 关闭游标和连接
cursor.close()
cnx.close()
2.1.2 pymysql
-
定义：
- pymysql是一个纯Python实现的MySQL客户端，它提供了与MySQL服务器进行交互的功能。
- 该库是在PyMySQL的基础上进行改进和优化得到的，它更加易用、稳定并且支持Python3。
-
用法：
import pymysql
'''
实现功能：
pymysql 连接MySQL数据库
'''
# 创建连接
with pymysql.connect(
host="localhost", # 数据库主机地址
user="username", # 数据库用户名
password="password", # 数据库密码
database="mydatabase" # 连接的数据库名
) as conn:
# 创建游标
with conn.cursor() as cursor:
# 执行SQL语句
cursor.execute("SELECT * FROM mytable")
# 获取查询结果
results = cursor.fetchall()
# 处理查询结果
for row in results:
print(row)
2.2 SqlServer数据连接
关于SqlServer数据库链接，同样也是两种：
- pyodbc；
- pymssql；
2.2.1 pyodbc
-
定义：
- pyodbc是Python的一个开放源代码的数据库连接库，支持ODBC v3和v4连接。
- 它基于Python的C语言扩展，为Python提供了与ODBC数据库进行交互的功能。
- 可以使用pyodbc来连接各种支持ODBC的数据库，包括SQL Server、MySQL等。
-
用法：
import pyodbc
'''
实现功能：
pyodbc 连接SqlServer数据库
'''
# 创建连接
cnxn = pyodbc.connect(
'DRIVER={SQL Server};SERVER=server_name;DATABASE=database_name;UID=username;PWD=password'
)
# 创建游标
cursor = cnxn.cursor()
# 执行SQL语句
cursor.execute('SELECT * FROM table_name')
# 获取查询结果
results = cursor.fetchall()
# 处理查询结果
for row in results:
print(row)
# 关闭游标和连接
cursor.close()
cnxn.close()
这里要说明一下：
- DRIVER：数据库驱动;
- SERVER：服务器地址;
- DATABASE：数据库名称;
- UID：用户名;
- PWD：密码；
2.2.2 pymssql
-
定义：
- pymssql是Python的一个简单数据库接口，它建立在FreeTDS的基础上，为微软SQL Server提供Python DB-API(PEP-249)接口。
- pymssql可以用于连接SQL Server数据库并执行各种操作，包括创建表格、插入数据、查询数据等。
- 它支持Python3和多操作系统，例如Windows、Linux和macOS等。
-
用法：
import pymssql
'''
实现功能：
pymssql 连接SqlServer数据库
'''
# 创建连接
conn = pymssql.connect(
server='server_name',
user='username',
password='password',
database='database_name'
)
# 创建游标
cursor = conn.cursor()
# 执行SQL语句
cursor.execute('SELECT * FROM table_name')
# 获取查询结果
results = cursor.fetchall()
# 处理查询结果
for row in results:
print(row)
# 关闭游标和连接
cursor.close()
conn.close()
2.3 代码示例
了解完 mysql和SqlServer数据库链接方式，
我们就来在代码中实现。
2.3.1 安装
在使用这些三方库前，需要安装，这里pip 方式安装
pip install pymssql ,pymysql ,pyodbc, mysql.connector
其余安装方式，可以参照这两篇：
安装完成，就可以进行代码编写了。
2.3.2 代码示例
代码示例
# -*- coding: utf-8 -*-
# @Time : 2023-09-07
# @Author : Carl_DJ
"""
实现功能：
1、python直接链接SqlServer数据库可以MySQL数据库，进行数据对比
2、对比mysql和SqlServer两个数据库的表名以及表内数据的差异：
3、对比的差异分别输出到文件中
---->数据差异，存到 .xlsx文件
---->表的差异，存到 .log文件
4、因为MC表及数据较多，使用多线程进行并发比较数据，提升效率
5、如果对比其他环境的数据，只需换更换 数据库信息即可
6、推荐使用pymssql、pymysql 链接数据库
"""
import mysql.connector
import pyodbc
import pymssql
import pymysql
import time
import pandas as pd
import threading
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
'''
使用mysql.connector 链接 mysql，
使用pyodbc 链接 SqlServer
'''
# MySQL 数据库连接配置
# mysql_conn = mysql.connector.connect(
# host='localhost', # MySQL主机地址
# user='username', # MySQL用户名
# password='password', # MySQL密码
# database='DBname', # 需要对比的数据库名
# )
#
#
# # SQL Server 数据库连接配置
# sql_server_conn = pyodbc.connect(
# r'DRIVER={SQL Server};SERVER=server_name;DATABASE=dbname;UID=username;PWD=password;DATABASE=DBname')
'''
使用 pymysql 链接 mysql数据库
使用pymssql 链接 SqlServer数据库
'''
# MySQL 数据库连接配置
mysql_conn = pymysql.connect(host="localhost",
port=port, user="username",
password="password",
db="DBname"
)
if mysql_conn:
print("mysql数据库链接成功")
time.sleep(1)
# 链接SqlServer
sql_server_conn = pymssql.connect(host="localhost",
port="port",
user="username",
password="password",
database="DBname"
)
if sql_server_conn:
print("sqlserver数据库链接成功")
time.sleep(1)
#时间
now = time.strftime("%Y-%m-%d_%H%M%S",time.localtime())
print(f'开始执行差异对比：{now}')
# 获取MySQL数据库中所有表名（忽略表名大小写）
def get_mysql_table_names():
cursor = mysql_conn.cursor()
cursor.execute("SHOW TABLES")
tables = [table[0].lower() for table in cursor.fetchall()]
cursor.close()
return tables
# 获取SQL Server数据库中所有表名（忽略表名大小写）
def get_sql_server_table_names():
cursor = sql_server_conn.cursor()
cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE'")
tables = [table[0].lower() for table in cursor.fetchall()]
cursor.close()
return tables
# 比较两个数据库中指定表的差异数据，并将结果存储到Excel表格中
def compare_tables(table_name):
try:
# 从MySQL数据库中获取表数据
mysql_cursor = mysql_conn.cursor()
mysql_cursor.execute(f"SELECT * FROM {table_name}")
mysql_data = mysql_cursor.fetchall()
mysql_columns = [column[0] for column in mysql_cursor.description]
# 从SQL Server数据库中获取表数据
sql_server_cursor = sql_server_conn.cursor()
sql_server_cursor.execute(f"SELECT * FROM {table_name}")
sql_server_data = sql_server_cursor.fetchall()
sql_server_columns = [column[0] for column in sql_server_cursor.description]
# 将数据转换为pandas DataFrame进行比较
mysql_df = pd.DataFrame(mysql_data, columns=mysql_columns)
sql_server_df = pd.DataFrame(sql_server_data, columns=sql_server_columns)
diff = mysql_df.compare(sql_server_df)
# 如果存在差异，将差异数据存储到Excel表格中
if not diff.empty:
diff_data_name = ('different_data' + now + '.xlsx')
with pd.ExcelWriter(diff_data_name) as writer:
diff.to_excel(writer, sheet_name=table_name)
print(f"表 {table_name}存在差异，并存在{diff_data_name}文件中")
except Exception as e:
print(f"比较表 {table_name}出错，错误原因: {str(e)}")
pass
# 获取MySQL和SQL Server数据库中所有不同的表名，并将它们写入日志文件
def get_different_tables():
mysql_tables = get_mysql_table_names()
sql_server_tables = get_sql_server_table_names()
different_tables = set(mysql_tables).symmetric_difference(set(sql_server_tables))
different_tables_log = ('different_tables' + now + '.log')
with open(different_tables_log, 'w') as f:
for table in different_tables:
f.write(f"Different table: {table}" + '\n')
print(f"Different table: {table}")
return different_tables
# 使用多线程进行数据校验
def thread_worker(table):
compare_tables(table)
# 运行
if __name__ == '__main__':
# 获取MySQL和SQL Server数据库中所有不同的表名，并将它们写入日志文件
different_tables = get_different_tables()
print("Different tables:")
print(list(different_tables))
运行结果：
根据职业操守，我只把生成的差异文件展示一下。
内容不能展示。
3、总结
看到这里，今天的分享就该结束了。
主要介绍了
- MySQL 和SqlServer的链接方式
- 数据差异对比，并发差异结果输出到文档中
这些在平时工作中很常用，所以，需要常备技能。
我是小鱼：
- CSDN 博客专家；
- 阿里云 专家博主；
- 51CTO博客专家；
- 51认证讲师等；
- 认证金牌面试官；
- 职场面试及培训规划师；
- 多个国内主流技术社区的认证专家博主；
- 多款主流产品(阿里云等)测评一、二等奖获得者；
关注我，带你学习更多更专业更前言的Python技术。
568
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
