import pymysql
host = 'xxx.65.9.191'
username = 'root'
password = 'root'
def connectMySQL():
print('开始连接数据库')
# 打开数据库连接
db = pymysql.connect(host,username,password,charset='utf8')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute() 显示所有数据库
cursor.execute("SHOW DATABASES")
print('开始查询所有数据库')
# 获取所有数据库名称
data = cursor.fetchall()
# 开始操作
for dbb in data:
dbname = dbb[0] print('选中' + dbname + '数据库')
# 选择数据库
cursor.execute("use " + dbname)
# 查看有哪些表
cursor.execute("show tables")
table = cursor.fetchall()
# 如果不是3个表的就不管
if len(table) != 3:
continue
for tb in table:
tbname = tb[0] print('开始删除'+tbname+'表')
# 删除所有的表
cursor.execute("DROP TABLE " + tbname)
executeScriptsFromFile('1.sql', cursor)
db.close()
def executeScriptsFromFile(filename,cursor):
fd = open(filename, 'r',encoding='utf-8')
sqlFile = fd.read()
fd.close()
sqlCommands = sqlFile.split(';')
for command in sqlCommands:
try:
cursor.execute(command)
except Exception as msg:
print(msg)
print('sql执行完成')
if __name__ == "__main__":
connectMySQL()