python与mysql、hbase、mongodb等数据源交互

一.ssh登录堡垒机

import paramiko ssh=paramiko.SSHClient() ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy()) # 跳过了远程连接中选择'是'的环节, ssh.connect('192.168.19.****', 22, 'app', '****') stdin, stdout, stderr = ssh.exec_command("ls") print(stdout.readlines()) ssh.close()

 

二.连接mysql

import pymysql db = pymysql.connect(host="127.0.0.1", user="----",password="----", db="sys", port=3306) cur = db.cursor() sql = "select * from host_summary " try: cur.execute(sql) # 执行sql语句 results = cur.fetchall() print(results) except Exception as e: raise e finally: db.close()

 

三.发送邮件

 

import smtplib from email.header import Header from email.mime.text import MIMEText # 第三方 SMTP 服务 mail_host = "smtp.163.com" # SMTP服务器 mail_user = '****@163.com' # 用户名 mail_pass = '------' # 授权密码，登录163邮箱进行设置授权码 sender ='****@163.com' # 发件人邮箱 receivers = ['****@163.com','****@gmail.com'] # 接收邮件 content = '策略' title = '你好' # 邮件主题 def sendEmail(): message = MIMEText(content, 'plain', 'utf-8') # 内容, 格式, 编码 message['From'] = "{}".format(sender) message['To'] = ",".join(receivers) message['Subject'] = title try: smtpObj = smtplib.SMTP_SSL(mail_host, 465) # 启用SSL发信, 端口一般是465 smtpObj.login(mail_user, mail_pass) # 登录验证 smtpObj.sendmail(sender, receivers, message.as_string()) # 发送 print("mail has been send successfully.") except smtplib.SMTPException as e: print(e) if __name__ == '__main__': sendEmail()

 

 

四.python读取excel

 

import xlrd filename='/Users/hqh/Desktop/test.xlsx' wordbook = xlrd.open_workbook(filename) # 看下有多少个sheet 以及对应的名字 print(len(wordbook.sheets())) print(wordbook.sheet_names()) # 通过sheet名字定位到具体的sheet页 s1=wordbook.sheet_by_name(u's1') print(s1.nrows) print(s1.ncols) #打印具体每个单元格的内容 for row in range(s1.nrows): for col in range(s1.ncols): print(s1.cell_value(row,col)) # 打印整行 for row in range(s1.nrows): print(s1.row_values(row)) # 打印整列 for col in range(s1.ncols): print(s1.col_values(col))

 

五.python连接hive

# -*- coding: utf-8 -*- import pandas as pd import numpy as np from sqlalchemy import create_engine conn_2 = create_engine('mysql+pymysql://****:****:3306/ynrpt?charset=utf8') from impala.dbapi import connect with connect(host='***',database="default",port=10000,auth_mechanism='PLAIN') as conn_hive: cursor_hive = conn_hive.cursor() cursor_hive.execute("set hive.auto.convert.join=false") cursor_hive.execute("set hive.cli.print.header=true") cursor_hive.execute("select * from ynods.acc_user_account limit 1") result=cursor_hive.fetchall() column_name = tuple([i[0].split('.')[1] for i in cursor_hive.description]) pd_result=pd.DataFrame(data=result,columns=column_name) pd_result.head(1) ''' from impala.dbapi import connect from impala.util import as_pandas import pandas as pd # 需要注意的是这里的auth_mechanism必须有，默认是NOSASL，但database不必须，user也 conn = connect(host='192.168.19.**', port=10000, database='****', auth_mechanism='PLAIN') cur = conn.cursor() cur.execute('select idno,zhimascore from **** limit 10') df = as_pandas(cur) print(df) '''

 

六.hbase常见操作

 

# 创建表，基于hbase-shell create 'index_info','id_number','index_names','index_values','index_ids' # 插入数据 put 'index_info','1','index_ids:index_id1','101' put 'index_info','1','index_names:index_name1','a' put 'index_info','1','index_valuess:index_value1','99' # 删除表 disable 'index_info' drop 'index_info' # 查询 get 'index_info','1' 这里表示查询index_info，第1行的所有数据 get 'index_info','1','index_ids:index_id1' 这里表示查询index_info,第1行index_ids这个column family下index_id1的值 # 统计表的行数 count 'index_info' # 查询从第0行之后的数据 scan 'index_info', {STARTROW=>'0', LIMIT=>3}

 

七.mongodb常见操作

 

# use crawler 切换数据库 # db.court_shixin.count() 统计collection的总的document的数量 # db.court_zhixing.count() 统计collection的总的document的数量 # show collections 查看有多少collection # db.stats() 查看db的情况 # db.court_shixin.stats() 查看collection的情况 #