解决python 使用MySQL进行source *.sql文件报错问题

今天碰到了个问题，我要完成一个项目数据库初始化功能。

刚接到任务，心想这不是小菜一碟。直接

cur.execute('/home/myname/database.sql')不就可以了....
结果无情的报错：

Traceback (most recent call last):
  File "C:/Users/john/Desktop/work/pycharmcode/connectmysql.py", line 37, in <module>
    initial_db()
  File "C:/Users/john/Desktop/work/pycharmcode/connectmysql.py", line 32, in initial_db
    cur.execute('source %s'%sqlfile)
  File "C:\python36\lib\site-packages\MySQLdb\cursors.py", line 206, in execute
    res = self._query(query)
  File "C:\python36\lib\site-packages\MySQLdb\cursors.py", line 312, in _query
    db.query(q)
  File "C:\python36\lib\site-packages\MySQLdb\connections.py", line 224, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source /home/chenyq/abc.sql' at line 1")

网上查来查去，大概的原因是python的MySQLdb模块不支持source命令。在网上查不到好的结果。

因为我要实现的功能是在云服务器上的。就突然想到了可不可以使用python ssh连接服务器，读取目标.sql文件。然后赋成变量去执行。

结果发现果然成功了。

下面是代码：

import MySQLdb
import paramiko

hostname = '172.21.108.111'
username = 'test'
user = 'root'
password = '123456'
sqlfile = '/home/chenyq/abc.sql'
port = 22


def read_sql():
    try:
        ssh = paramiko.SSHClient()
        ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh.connect(hostname=hostname, port=port, username=username, password=password)
        stdin, stdout, stderr = ssh.exec_command('cat %s' % sqlfile)
        sqllist = stdout.read()
        ssh.close()
        return sqllist
    except Exception as e:
        print(e)

def initial_db():
    try:
        conn = MySQLdb.connect(host=hostname, user=user, password=password)
        cur = conn.cursor()
        cur.execute('DROP DATABASE IF EXISTS Mytest')
        cur.execute('CREATE database Mytest')
        cur.execute('use Mytest')
        cur.execute('set names utf8')
        cur.execute(read_sql())
        print('初始化完成~')
    except Exception as e:
        print(e)

if __name__ == '__main__':
    initial_db()

希望能给后来碰到这种问题的人提供思路。