连接数据库
由于服务器数据库的访问限制,所以只能从云服务器本地IP 127.0.0.1 进行数据库访问
所以连接数据库操作顺序为
- 连接云服务器
- 连接数据库
- 执行查询语句
- 读取数据
- 关闭数据库
- 关闭云服务器连接
"""
Created on 2022-1-13 15:14:07
@author: JiangDong
"""
from sshtunnel import SSHTunnelForwarder
import pymysql
IP = ''
admin = 'root'
pwd = '.'
try:
server = SSHTunnelForwarder(
ssh_address_or_host=(IP, 22),
ssh_username=admin,
ssh_password=pwd,
remote_bind_address=('localhost', 3306)
)
print("服务器连接成功")
except:
print("服务器连接失败")
server.start()
try:
con = pymysql.connect(host='127.0.0.1',
port=server.local_bind_port,
user=admin,
password=pwd,
db="demo",
charset='utf8')
print("数据库连接成功")
except pymysql.Error as e:
print('数据库连接失败'+str(e))
cur = con.cursor()
cur.execute('DROP TABLE IF EXISTS Student')
sqlQuery = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Class CHAR(20),No CHAR(20))"
value = "INSERT INTO Student(Name, Class, No) VALUES ('name', '嵌入式', '5400001')"
cur.execute(sqlQuery)
cur.execute(value)
cur.execute('SELECT * from Student')
con.commit()
data = cur.fetchall()
for item in data:
print(item)
cur.close()
con.close()
server.close()