python + sqlite + sqlalchemy 批量导入二进制文件

import os
from sqlalchemy import Column, Integer, String, LargeBinary, Date
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

 

# 二进制文件对象 ORM
class BinaryFiles(Base):
    __tablename__ = 'binaryFiles'
    xh = Column(Integer, primary_key=True)
    filename = Column(String(100))
    content = Column(LargeBinary)
    sqlite_autoincrement = True

    def __repr__(self):
        return "<BinaryFiles(xh='%i', filename='%s')>" % (
            self.xh, self.filename)

# 指定目录
sDir = 'E:\\temp\\files'
# sqlite 数据库文件位置
sSqliteDB = 'sqlite:///E:\\temp\\files.db'

# 创建对象的基类: # 初始化数据库连接:# 创建session对象:
Base = declarative_base()
engine = create_engine(sSqliteDB)
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine)
session = DBSession()

for root, dirs, files in os.walk(sDir):
    print('root_dir:', root)  # 当前目录路径
    print('files:', files)  # 当前路径下所有非目录子文件
    for sFileName in files:
        filepath = sDir + '\\' + sFileName
        print(filepath + '......')
        new_file = BinaryFiles(filename=sFileName)
        binFile = open(filepath, 'rb')  # 打开二进制文件
        file_size = os.path.getsize(filepath)
        file_data = binFile.read(file_size)  # 一次读完
        binFile.close()
        new_file.content = file_data
        new_file.filename = sFileName
        session.add(new_file)

        session.commit()

session.close()