Python–连接数据库
目的:python通过ssh连接数据库
import MySQLdb
from sshtunnel import SSHTunnelForwarder
import re
class SSHMySQL(object):
def __init__(self):
self.server = self.get_server()
self.conn = self.get_conn()
self.cur = self.conn.cursor()
def __enter__(self):
return self
def get_server(self):
# 使用SSH隧道,通过跳板机连接数据库
server = SSHTunnelForwarder(
('192.168.2.180', 22), # 跳板机地址
ssh_username='****', # 跳板机账号
ssh_password='****', # 跳板机密码
remote_bind_address=('127.0.0.1', 3306) # MySql服务器
)
return server
def get_conn(self):
# 开启隧道
self.server.start()
# 使用MySQLdb的connect()方法连接数据库
conn = MySQLdb.connect(
host='127.0.0.1', # 此处必须是127.0.0.1
port=self.server.local_bind_port,
user='****',
password='',
db='ecos',
charset='utf8'
)
return conn
def get_query_one(self, query, param=None):
try:
# 使用execute()方法执行SQL语句
self.cur.execute(query, param)
# 提交当前事务
self.conn.commit()
# 使用fetchone()方法获取第一条数据
data = self.cur.fetchone()
if data is not None:
response = dict(zip([k[0] for k in self.cur.description], data))
else:
response = data
return response
except Exception as e:
# 回滚当前事务
self.conn.rollback()
raise e
def get_query_all(self, query, param=None):
try:
# 使用execute()方法执行SQL语句
self.cur.execute(query, param)
# 提交当前事务
self.conn.commit()
# 使用fetchall()方法获取全部数据
data = self.cur.fetchall()
if data is not None:
response = [dict(zip([k[0] for k in self.cur.description], row)) for row in data]
else:
response = data
return response
except Exception as e:
# 回滚当前事务
self.conn.rollback()
raise e
def __exit__(self, exc_type, exc_val, exc_tb):
# 关闭游标
self.cur.close()
# 关闭数据库连接
self.conn.close()
# 关闭隧道
self.server.close()
def db_query(self, query, param):
res = self.get_query_one(query, param)
print(res)
if __name__ == '__main__':
with SSHMySQL() as db:
query = "select name,cpu,memoryGB,hdGB from pricingTemplate where name like %s;"
param = ('yx_testt' + '%',)
res = db.get_query_all(query, param)
print(res)
结果:
出现如下图所示,即为连接成功
测试过程中,遇到的问题:
问题1:
若出现如下截图字符集报错,将‘utf-8’改为‘utf8’即可
问题2:
若出现如下截图(password:YES)报错,将connect处的密码去掉即可(不知道为啥,就是这么神奇╮(╯▽╰)╭)
注:其中跳板机、各个ip、用户名、账号、密码的含义,可详见以下连接:https://blog.csdn.net/zgbzbl/article/details/107081320