1 python读取mysql数据
import pymysql
import pandas as pd
def load_data_from_mysql():
conn = pymysql.connect(host="127.0.0.1",
port=3306,
user="test",
password="test",
db="database",
charset="utf8")
cursor = conn.cursor()
sql = "SELECT * FROM table1"
cursor.execute(sql)
result = cursor.fetchall()
return result
if __name__=="__main__":
load_data_from_mysql()
2 pandas加载mysql数据
import pymysql
import pandas as pd
def load_data_frame_from_mysql():
conn = pymysql.connect(host="127.0.0.1",
port=3306,
user="test",
password="test",
db="database",
charset="utf8")
sql = "SELECT * FROM table1"
data_frame = pd.read_sql(sql, conn)
return data_frame
3 Python通过SSH读取mysql
在实际应用中,我们可能有使用SSH读取MySQL数据库的情况。由于限制了两台机器之间只能通过私钥连接,所以整个过程的结果如下。
- 安装
sshtunnel
pip install sshtunnel
- python代码
with SSHTunnelForwarder(
ssh_address_or_host=('跳板机地址', 跳板机端口),
ssh_username='跳板机账户名',
ssh_pkey='本机id_rsa地址',
remote_bind_address=('远程数据库地址', 数据库端口)
) as tunnel:
conn = pymysql.connect(host="127.0.0.1", //必须是127.0.0.1
port=server.local_bind_port,
user="数据库用户名",
password="数据库密码",
db="数据库")
cursor = conn.cursor()
sql = "SELECT * FROM 表名"
cursor.execute(sql)
result = cursor.fetchall()
print(result)