mysql本地上传_连接mysql并将数据保存到本地，并上传到本地 虚拟机

连接本地mysql，查询students数据库中students表的所有数据，将数据保存到本地C:stu_excelstudents.txt文档中，最后将该文件上传到本地虚拟机linux系统的/home目录下即可。

注：文档中写入数据的格式如下：

姓名：[年龄，性别，电话，学历....]

import pymysql

import os

import paramiko

class test2(object):

def __init__(self, host, Port, user, password, databasename, ip, port, localpath, linuxpath):

self.host = host

self.Port = Port

self.user = user

self.password = password

self.databasename = databasename

self.localpath = localpath

self.linuxpath = linuxpath

self.ip = ip

self.port = port

def myconnect(self):

try:

db = pymysql.connect(self.host, self.user, self.password, self.databasename, self.Port)

cursor = db.cursor()

return cursor

except Exception as e:

print(e)

def get_write(self, cursor):

sql = "select * from students"

cursor.execute(sql)

res = cursor.fetchall()

print(res)

if os.path.exists(self.localpath):

os.remove(self.localpath)

self.get_write(cursor)

else:

with open(self.localpath, 'a+') as s:

for i in res:

s.write(i[1]+'：'+str(i[2:])+'n')

self.put_txt()

def put_txt(self):

host = paramiko.Transport(self.ip, self.port)

host.connect(username=self.user, password=self.password)

sftp = paramiko.SFTPClient.from_transport(host)

sftp.put(self.localpath, self.linuxpath+'students.txt')

if __name__ == '__main__':

t2 = test2('127.0.0.1', 3306, 'root', '123456', 'students', '192.168.56.1', 22, 'C:stu_excelstudents.txt', '/home/')

cursor = t2.myconnect()

t2.get_write(cursor)

t2.put_txt()