之所以写这篇文章,是源于我之前遇到的一个问题:
如何实现不同服务器之间的数据查询?
如果内部数据平台不是非常完善的企业,我们总会难免遇到同一个问题,就是数据分散在各个服务器,存在不同的数据库中。
那么我们如何从不同服务器中查询数据并对数据作关联操作呢?
下文中介绍三种实现方法:
1.在A服务器中远程连接B服务器,查询出B中的数据存于A中2.分别连接A服务器和B服务器,将查询出来的两份数据导入python中进行处理3.分别从A服务器和B服务器中查询出数据,存放在本地同一个数据库进行操作
正文
01 远程连接另一台服务器
程序总共分四个步骤,且需要先后执行(不可一起执行)。基本思路就是:在A服务器中写sql,远程连接B服务器并将查询出的数据建临时表存于A中,得到最终数据后删除临时表,并且关闭服务器连接。
- 远程连接到服务器123.12.123.123
exec sp_addlinkedserver 'mylink', '', 'SQLOLEDB', '123.12.123.123' exec sp_addlinkedsrvlogin 'mylink', 'false', null, 'UserName', 'PassWord'
注释:其中exec sp_addlinkedsrvlogin命令中的UserName和PassWord就是连接数据库时的账号和密码,如下图:
mylink就是为连接取个名字。
- 查询出数据并存于临时表中
CREATE TABLE #TempTable(user_id INT NOT NULL,user_name NVARCHAR(20) NOT NULL,city_id INT NOT NULL)INSERT INTO #TempTableSELECT user_id, user_name, city_idFROM mylink.Test_DB.dbo.Users --表名前需要带上mylink.Test_DB.dbo
--完成两份数据的联查,匹配出用户所在的城市名称SELECT tt.user_id, tt.user_name, cty.cityFROM #TempTable tt LEFT OUTER JOIN Cities cty ON tt.City_id=cty.city_id
- 用完之后即可删除临时表
DROP TABLE #TempTable
- 关闭服务器连接
exec sp_dropserver 'mylink', 'droplogins'
02 将数据导入Python进行处理
基本思路就是将python当成一个中间媒介,先分别连接不同的服务器并读取出所需的数据,再导入python中做相应的数据处理,比如联查等。
- 分别连接服务器读取数据
import pymssqldef sqlserver_connect(server, username, password, database, sql): """ # 连接数据库,并传入sql查询语句 :param server: 数据库服务器名称或IP :param username: 用户名 :param password: 密码 :param database: 数据库名称 :param sql: sql查询语句 :return: 返回sql查询得到的数据和相应的字段名 """ conn = pymssql.connect(server, username, password, database) # 创建连接 cursor = conn.cursor() # 创建游标 cursor.execute(sql) # 执行sql查询语句 data = cursor.fetchall() # 获得查询数据data data_title = [] #获取数据的字段名 for item in cursor.description: data_title.append(item[0]) cursor.close() # 关闭游标 conn.close() # 关闭连接 return data, data_titlesql = "SELECT user_id,user_name,city_id FROM Users"sql_2 = "SELECT city_id,city_name FROM Cities"data,title = sqlserver_connect("123.12.123.123", "UserName", "PassWord", "Test_DB", sql)data2,title2 = sqlserver_connect("111.11.111.11", "UserName_2", "PassWord_2", "Test_DB_2", sql_2)
- 用python代码实现联查效果
data_list=[]for tempItem in data: item=list(tempItem) #将数据转换成列表类型 data_list.append(item) user_id = item[0] v = False for item2 in data2: user_id2 = item2[0] if user_id == user_id2: #若两份数据中的user_id相等 item.insert(-1, item2[1]) #则将data2中索引为1的值插入data的最后 v = True if v == False: item.insert(-1, "")
注释:fetchall方法得到的数据是元组类型,此处需要转换成列表类型,因为列表可以增加元素而元组不可以。
03 将数据存在本地数据库处理
在自己本地数据库建表,用于存放不同服务器查询出来的数据,之后按常规方法进行数据处理即可。
原理和方法1基本相同。
但是不推荐此方法。
因为不管是SQL Server还是Mysql,客户端的安装都比较耗时间且耗空间。
但是空闲的时候进行尝试是可以的。
END