Python批量写入数据库

以音乐文件为例，对文件进行操作以及批量写入数据库

import pymysql
import re
import os
import librosa


class Music(object):

    def __init__(self, path):
        self.path = path

    # 返回歌曲节拍总数 beats_time
    def beats(self):
        y, sr = librosa.load(self.path)
        tempo, beat_frames = librosa.beat.beat_track(y=y, sr=sr)
        return tempo

    # 得出歌曲时长times 获取到的时长单位为秒
    def times(self):
        long = librosa.get_duration(filename=self.path)
        return int(long)

    # 计算歌曲的bpm
    def bpm(self):
        beats_times = self.beats() / self.times() * 60
        beats_times = int(beats_times * 4)
        return beats_times

    # 计算每拍的时间间隔
    def interval(self):
        times = 60 / self.bpm()
        return times


# 获取音乐文件地址
def getfiles(path):
    name = []
    for dirpath, dirname, filenames in os.walk(path):
        for i in range(len(filenames)):
            filepath = os.path.join(dirpath, filenames[i])
            mus_path = os.path.abspath(filepath)
            name.append(mus_path)
    return name


music_path_list = getfiles(r'./music')
music_name = []
singer = []
music_time_str = []
music_time_int = []
music_bpm = []
music_path = []
for i in range(len(music_path_list)):
    strs = re.findall(r'[^\\/:*?"<>|\r\n]+$', music_path_list[i])[0]
    rex = re.compile('(.*) -')
    s = rex.findall(strs)[0]  # 歌手名称
    rex1 = re.compile('- (.*).(mp3|ogg)')
    s1 = rex1.findall(strs)[0][0]  # 歌曲名称
    mus = Music(music_path_list[i])
    bpms = mus.bpm()  # BPM
    long = mus.times()  # 时间
    minute = long // 60
    second = long % 60
    if len(str(second)) == 1:
        second = '0' + str(second)
    tim = '{}:{}'.format(minute, second)
    music_name.append(s1)
    singer.append(s)
    music_time_str.append(tim)
    music_time_int.append(long)
    music_bpm.append(bpms)
    music_path.append(music_path_list[i])
#  建立链接
con = pymysql.connect(host='localhost',
                      port=3306,
                      db='dmc',
                      user='root',
                      password='123456',
                      charset='utf8')
# 获取游标
cur = con.cursor()

for itms in range(len(music_name)):
    sql = 'insert into `music` values (%s,%s,%s,%s,%s,%s,%s);'
    cur.execute(sql,
                (None, music_name[itms], singer[itms], music_time_str[itms],
                 music_time_int[itms], music_bpm[itms], music_path[itms]))

# 统一提交
con.commit()
# 关闭游标
cur.close()
# 关闭连接
con.close()

显示如图所示