sqlserver视图 to mysql表
有两台服务器，是同一个内网。
sqlserver这台只能内网访问
mysql这台可内可外
现在需要将sqlserver的一个视图数据，每天凌晨同步到另外一台服务器的mysql数据库里
共参考：
- 安装sqlserver https://www.linuxprobe.com/centos7-instal-mssql-2019.html
- 创建视图 https://www.linuxprobe.com/centos7-instal-mssql-2019.html
- python操作mysql https://www.jb51.net/article/268793.htm
- python操作mssql https://www.jb51.net/article/262739.htm
一、实现思路
1、虚拟机&本地数据库模拟环境
2、py脚本测试本地环境同步
3、 生产环境做定时任务执行此脚本
二、 本地安装测试
1、 centos7虚拟机安装sqlserver
参考 https://www.linuxprobe.com/centos7-instal-mssql-2019.html
系统版本：Centos7.7 ，内存4G（SQL Server内存要求至少2G），硬盘40G，1核CPU
SQL Server版本：SQL Server 2019，下载地址
SQL Server Management Studio 版本：V18.4，安装在windows客户端下载地址，Windows 2008R2(64位)或Windows8.1(64位)以上
# 1. 关闭SELINUX
sed -i '/^SELINUX/s/enforcing/disabled/g' /etc/selinux/config && setenforce 0
# 2. 添加软件源并安装依赖包 都选择Yes
curl https://packages.microsoft.com/config/rhel/7/prod.repo -o /etc/yum.repos.d/msprod.repo
yum -y install mssql-tools unixODBC-devel bzip2 gdb libsss_nss_idmap cyrus-sasl cyrus-sasl-gssapi
# 3. 安装SQL Server2019, 将下载好的安装包上传上来
rpm -ivh mssql-server-15.0.4003.23-3.x86_64.rpm
# 4. 运行初始化配制
/opt/mssql/bin/mssql-conf setup
# 注意：以下是执行上边命令后提示的
usermod: no changes
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID) - CPU Core utilization restricted to 20 physical/40 hyperthreaded
7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum
8) I bought a license through a retail sales channel and have a product key to enter.
Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=2109348&clcid=0x409
Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.
Enter your edition(1-8): 3 #这里选择了Express版本
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=2104294&clcid=0x409
The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409
Do you accept the license terms? [Yes/No]:Yes #输入Yes接受许可条目
Enter the SQL Server system administrator password: #设置SA管理员密码
Confirm the SQL Server system administrator password:
Configuring SQL Server...
The licensing PID was successfully processed. The new edition is [Express Edition].
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql- server.service.
Setup has completed successfully. SQL Server is now starting.
# 安装完成
# 5. 添加环境变量
echo 'export PATH=$PATH:/opt/mssql-tools/bin' > /etc/profile.d/mssql.sh
source !$
# 6. 防火墙添加服务
firewall-cmd --permanent --add-service=mssql
firewall-cmd --reload
# 7. 命令行工具连接测试
[root@localhost ~]# sqlcmd -S localhost -U sa
Password:
# 显示系统数据库
1> select name,database_id from sys.databases;
# 执行
2> go
name database_id
-------------------------------------------------------------------------------------------------------------------------------- -----------
master 1
tempdb 2
model 3
msdb 4
(4 rows affected)
# 查看软件版本
1> SELECT @@VERSION
2> go
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU1) (KB4527376) - 15.0.4003.23 (X64)
Dec 6 2019 14:53:33
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Linux (CentOS Linux 7 (Core))
(1 rows affected)
1>
# 至此，centos7安装sqlserver完成
2、 宿主机安装mysql
# 省略
# 可以手动创建好库、表方便测试
3、宿主机访问sqlserver
# navicat 直接链接 注意用户名是 sa
# 可以手动创建好库、表 方便测试
# 根据表创建一个视图
CREATE VIEW myview
AS
SELECT
*
from
stu
4、py脚本本地测试
① sqlserver读
# 1. 安装sqlserver包
pip install pymssql
pip install pymysql
# 2. 测试读取
def select_sqlserver():
'''
:return: 返回视图结果， 返回一个元组列表
'''
# 导包
import pymssql
# 创建连接字符串 （sqlserver默认端口为1433）
conn = pymssql.connect(host='192.168.1.201', # 这里的host='_'可以用本机ip或ip+端口号
server="192.168.1.201", # 本地服务器
port="1433", # TCP端口
user="sa", password="abc.123",
database="hahahaha",
charset="UTF-8",
# timeout=10*6000
# 这里设置全局的GBK，如果设置的是UTF—8需要将数据库默认的GBK转化成UTF-8
);
# if conn:
# print('连接数据库成功!') # 测试是否连接上
# 查询语句
cursor = conn.cursor() # 使用cursor()方法获取操作游标
sql_select = "select * from myview " # 数据库查询语句---这个是我本地测试自定义的视图
cursor.execute(sql_select) # 执行语句
results = cursor.fetchall() # 获取所有记录列表, [(1, 'curry ', 'nan ', 9), (2, 'grren ', 'nv ', 20)],默认会有空格，需要单独做处理
# print(results)
# return_res = []
# for result in results:
# result = list(result) # 元组转化为列表
# for res in range(len(result)):
# if isinstance(result[res], str):
# result[res] = result[res].replace(' ', '') # 查出来的有空格,刚开始测试的时候看的是网络上的方法，但是后来实际测试发现第二次并没有空格了
# result = tuple(result) # 列表再转换为元组
# return_res.append(result)
# print(return_res)
conn.commit()
conn.close()
# return return_res
return results
② mysql写
def insert_mysql(items):
'''
:param items: 插入mysql的元组列表
:return: affect_row_count 影响条数
'''
# 导入pymysql包
import pymysql
# 创建数据库连接
conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="123123", db="test")
# 获取一个游标对象
cursor = conn.cursor()
# 批量插入数据
sql = "insert into test.test1 values(%s,%s,%s,%s)"
# 1. 先清空
cursor.execute('truncate test.test1')
# 2. 再插入，使用executemany方法批量插入数据
cursor.executemany(sql, items)
affect_row_count = cursor.rowcount
# 提交
conn.commit()
# 关闭
conn.close()
cursor.close()
return affect_row_count
③ 脚本完整版
# 1. 安装sqlserver包
pip install pymssql
pip install pymysql
# 2. 测试读取
def select_sqlserver():
'''
:return: 返回视图结果， 返回一个元组列表
'''
# 导包
import pymssql
# 创建连接字符串 （sqlserver默认端口为1433）
conn = pymssql.connect(host='192.168.1.201', # 这里的host='_'可以用本机ip或ip+端口号
server="192.168.1.201", # 本地服务器
port="1433", # TCP端口
user="sa", password="abc.123",
database="hahahaha",
charset="UTF-8",
# timeout=10*6000
# 这里设置全局的GBK，如果设置的是UTF—8需要将数据库默认的GBK转化成UTF-8
);
# if conn:
# print('连接数据库成功!') # 测试是否连接上
# 查询语句
cursor = conn.cursor() # 使用cursor()方法获取操作游标
sql_select = "select * from myview " # 数据库查询语句---这个是我本地测试自定义的视图
cursor.execute(sql_select) # 执行语句
results = cursor.fetchall() # 获取所有记录列表, [(1, 'curry ', 'nan ', 9), (2, 'grren ', 'nv ', 20)],默认会有空格，需要单独做处理
# print(results)
# return_res = []
# for result in results:
# result = list(result) # 元组转化为列表
# for res in range(len(result)):
# if isinstance(result[res], str):
# result[res] = result[res].replace(' ', '') # 查出来的有空格,刚开始测试的时候看的是网络上的方法，但是后来实际测试发现第二次并没有空格了
# result = tuple(result) # 列表再转换为元组
# return_res.append(result)
# print(return_res)
conn.commit()
conn.close()
# return return_res
return results
def insert_mysql(items):
'''
:param items: 插入mysql的元组列表
:return: affect_row_count 影响条数
'''
# 导入pymysql包
import pymysql
# 创建数据库连接
conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="123123", db="test")
# 获取一个游标对象
cursor = conn.cursor()
# 批量插入数据
sql = "insert into test.test1 values(%s,%s,%s,%s)"
# 1. 先清空
cursor.execute('truncate test.test1')
# 2. 再插入，使用executemany方法批量插入数据
cursor.executemany(sql, items)
affect_row_count = cursor.rowcount
# 提交
conn.commit()
# 关闭
conn.close()
cursor.close()
return affect_row_count
# 执行方法
iv_list = select_sqlserver()
res = insert_mysql(iv_list)
print(res)