import pandas as pd
import time
from pandas import DataFrame
import pandas
from sqlalchemy import create_engine

# 连接mysql
import MySQLdb
db1 = MySQLdb.connect(host='8.141.58.121', user='mysqltest', passwd='6ZW7ZfpSLbS63k2a', db='mysqltest', charset='utf8',port=3306)
cursor1 = db1.cursor()
# 连接mysql

# 连接sqlserver
import pymssql  # 引入pymssql模块
db2 = pymssql.connect('127.0.0.1', 'sa', 'root', 'sqlservertest', charset='GBK')  # 建立连接
cursor2 = db2.cursor()  # 创建一个游标对象,python里的sql语句都要通过cursor来执行
# 连接sqlserver


search = input('请输入你要查找的姓名:')
sql1 = "SELECT * FROM mysqltest.test1 where name='%s'" % search
sql2 = "select * from sqlservertest.dbo.test2 where name=N'%s'" % search

# sql1 = "SELECT * FROM mysqltest.test1"
# sql2 = "select * from sqlservertest.dbo.test2"

# try:
# 执行MYSQL语句
cursor1.execute(sql1)
results1 = cursor1.fetchall()
cursor2.execute(sql2)
results2 = cursor2.fetchall()




"""
形成表数据
"""
x = DataFrame(results1, columns=['id_num', 'name', 'age'])
y = DataFrame(results2, columns=['id_num', 'name','tel_number', 'sex'])

print(x)
print(y)

# x.to_excel(excel_writer=r"./mysql.xlsx" , index=False)
# x.to_csv(path_or_buf=r"./mysql.csv" , index=False)
# y.to_excel(excel_writer=r"./sqlserver.xlsx" , index=False)
# y.to_csv(path_or_buf=r"./sqlserver.csv" , index=False)


time = time.strftime("%Y%m%d%H%M%S", time.localtime())
e = pd.merge(x, y, on='name')

print('-------------------------------------------------')
print(e)
# e.to_excel(excel_writer=r"./%s.xlsx" % time, index=False)
# e.to_csv(path_or_buf=r"./%s.csv" % time, index=False)

# 关闭mysql数据库连接
cursor1.close()
db1.close()

# 关闭sqlserver数据库连接
cursor2.close()
db2.close()