【第一行Python代码】Python连接Mysql数据库执行SQL，根据数据库中的URL迁移文件到S3对象存储服务器

1、安装pymysql

pip3 install pymysql
pip3 install boto3

2、代码

import os
import pymysql
import oss.S3_H3C as h3c_util
import datetime


def get_file(img_url):
    urls = ["/etmpfile/file/", "/dtmpfile/file/", "/tmpfile/file/", "/file/", "/opt/wisdom/"]
    for i in urls:
        if os.path.exists(i + img_url):
            return i + img_url
        else:
            return None


def load_data():
   connection = pymysql.connect(host="127.0.0.1", user="root", password="123456",
                                 database="oss", charset="utf8", port=3306)
    print("Connected Mysql")

    cursor = connection.cursor()
    table = input("input table name \n")
    col_name = input("input column name \n")
    sql1 = "select id, " + col_name + " from " + table + " where " + col_name + " is not null"
    print("exec sql :", sql1)

    cursor.execute(sql1)
    data = cursor.fetchall()
    rows = cursor.rowcount
    print(rows, " data select")
    bucket_name = input("input bucket name \n")

    c = h3c_util.S3_H3C()

    # 数据迁移
    mv_data(bucket_name, c, connection, cursor, data, rows, table)


def mv_data(bucket_name, c, connection, cursor, data, rows, table):
    # c.create_bucket(bucket_name)
    for i in data[:rows]:
        if i[1] is None:
            continue
        exist_url = get_file(i[1])
        if exist_url is not None:
            urls = exist_url.split('/')
            key = urls[len(urls) - 1]
            now = datetime.datetime.now()
            time = now.strftime("%Y%m%d")

			// 重新存一个S3可以访问的图片路径（也可以直接存S3的预览路径）
            update_url = "api/system/upload/getObject/" + bucket_name + "/" + time + "/" + key
            print("img url update to ", update_url)
			
			// 上传后更新数据库
            c.upload(exist_url, bucket_name, time + "/" + key)
            sql2 = "update " + table + " set img_url = '" + update_url + "' where id = " + i[0]
            print("exec update sql :", sql2)
            cursor.execute(sql2)
            
            // 删除旧文件
            print("delete local file :", exist_url)
            os.remove(exist_url)
    
    connection.commit()
    cursor.close()
    connection.close()


if __name__ == '__main__':
    load_data()

from boto3.session import Session

access_key = "key"
access_secret_key = "secret"
endpoint = "endpoint"


class S3_H3C:
    def __init__(self):
        url = "http://{}".format(endpoint)
        session = Session(access_key, access_secret_key)
        self.s3_client = session.client('s3', endpoint_url=url)

    def upload(self, file_name, bucket_name, key):
        self.s3_client.upload_file(file_name, bucket_name, key)

    def create_bucket(self, bucket_name):
        response = self.s3_client.create_bucket(Bucket=bucket_name)
        print(response)

    def delete_bucket(self, bucket_name):
        response = self.s3_client.delete_bucket(Bucket=bucket_name)
        print(response)

3、总结

python3还是很方便的，代码比java实现同样的功能少了很多，也省去了麻烦的打包环节，放到服务器上直接跑，没有什么问题，除了前期配置环境浪费了一些时间。