I have a python project set up with SQLAlchemy for an SQLite database, first I recently introduced async to the database code, where I use os.walk to scan for and add files to the database, as well as hashing the files, it worked fine on a smaller folder structure with only around 14 files, but when I tried on a larger folder with hundreds of files(potentially thousands), it seemed to run into issues with the async nature of my calls, so other database interactions failed due to the database being locked, here is some of the errors:
```
Traceback (most recent call last):
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\app.py", line 154, in register_file
    await db.commit()
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\ext\asyncio\session.py", line 1009, in commit
    await greenlet_spawn(self.sync_session.commit)
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 190, in greenlet_spawn
    result = context.switch(*args, **kwargs)
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\orm\session.py", line 2017, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\orm\state_changes.py", line 103, in _go
    self._raise_for_prerequisite_state(fn.__name__, current_state)
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\orm\session.py", line 973, in _raise_for_prerequisite_state
    raise sa_exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.OperationalError) database is locked
[SQL: INSERT INTO files (path, type, size, file_created_at, file_updated_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) RETURNING id, created_at, updated_at]
[parameters: ('D:\\AI Generation\\_Repository\\models\\lora\\SDXL\\harpSY.prompts.txt', 'text/plain', 810, '2024-07-01 10:29:33.508848', '2024-07-01 10:29:33.509347')]
(Background on this error at: https://sqlalche.me/e/20/e3q8) (Background on this error at: https://sqlalche.me/e/20/7s2a)
Task exception was never retrieved
future: <Task finished name='Task-59' coro=<register_file() done, defined at D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\app.py:107> exception=PendingRollbackError("This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.OperationalError) database is locked\n[SQL: INSERT INTO files (path, type, size, file_created_at, file_updated_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) RETURNING id, created_at, updated_at]\n[parameters: ('D:\\\\AI Generation\\\\_Repository\\\\models\\\\lora\\\\SDXL\\\\shizune-pdxl-nvwls-v1.civitai.model-version.json', 'application/json', 16461, '2024-05-19 09:39:28.685997', '2024-05-19 09:39:28.685997')]\n(Background on this error at: https://sqlalche.me/e/20/e3q8)")>
Traceback (most recent call last):
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\app.py", line 154, in register_file
    await db.commit()
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\ext\asyncio\session.py", line 1009, in commit
    await greenlet_spawn(self.sync_session.commit)
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\util\_concurrency_py3k.py", line 190, in greenlet_spawn
    result = context.switch(*args, **kwargs)
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\orm\session.py", line 2017, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\orm\state_changes.py", line 103, in _go
    self._raise_for_prerequisite_state(fn.__name__, current_state)
  File "D:\AI Generation\Projects\vision-8b-minicpm-gradio-webui\venv\lib\site-packages\sqlalchemy\orm\session.py", line 973, in _raise_for_prerequisite_state
    raise sa_exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (sqlite3.OperationalError) database is locked
[SQL: INSERT INTO files (path, type, size, file_created_at, file_updated_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) RETURNING id, created_at, updated_at]
[parameters: ('D:\\AI Generation\\_Repository\\models\\lora\\SDXL\\shizune-pdxl-nvwls-v1.civitai.model-version.json', 'application/json', 16461, '2024-05-19 09:39:28.685997', '2024-05-19 09:39:28.685997')]       
(Background on this error at: https://sqlalche.me/e/20/e3q8) (Background on this error at: https://sqlalche.me/e/20/7s2a)
```

Now to provide the code context, here is the files and their contents:
app.py:
```
from database import crud
from sqlalchemy.ext.asyncio import AsyncSession

# Start with os.walk
import os, mimetypes, asyncio, threading, hashlib, logging
from tqdm import tqdm
from pathlib import Path
from datetime import datetime


# Configuring logging
# This is all I need for basic logging to file, remove filename to have it go to console instead.
# logging.basicConfig(filename='app.log', level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

# This is to output to both file and the standard output
# Create a logger object
logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)

# Create handlers
console_handler = logging.StreamHandler()
file_handler = logging.FileHandler('app.log')

# Set levels and formatters for each handler
console_handler.setLevel(logging.INFO)
file_handler.setLevel(logging.ERROR) # Example: Maybe you want only errors and above in the file

formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
console_handler.setFormatter(formatter)
file_handler.setFormatter(formatter)

# Add the handlers to the logger
logger.addHandler(console_handler)
logger.addHandler(file_handler)


# Utility Helpers

custom_mimetypes = {
    ".safetensors": "application/x-ai-model.safetensors",
    ".pt": "application/x-ai-model.pytorch",
    # Add more mappings as needed...
}

# def guess_mime_type(filepath: str):
#     type, _ = mimetypes.guess_type(filepath)
#     return type if type else 'unknown'

def guess_mime_type(filepath: str):
    # Try to guess the MIME type using Python's built-in mimetypes
    type, _ = mimetypes.guess_type(filepath)
    
    # If the built-in doesn't recognize it or if we have a custom definition, use our custom types
    if type is None or filepath.endswith(tuple(custom_mimetypes.keys())): 
        file_extension = os.path.splitext(filepath)[-1]
        type = custom_mimetypes.get(file_extension, 'unknown')
    
    # Consider adding more advanced checks here if needed, e.g., by filename patterns for architecture distinction
    
    return type


def get_file_hash(filepath: str, algorithm: str = 'sha512') -> str:
    """
    Calculate the hash of a file using the specified algorithm.
    
    Args:
    filepath (str): The path to the file whose hash is to be calculated.
    algorithm (str): The name of the hashing algorithm to use (default: 'sha512').
                     Supported algorithms are those available in Python's hashlib library.
    
    Returns:
    str: The hexadecimal digest string of the hash.
    
    Raises:
    ValueError: If an unsupported hash algorithm is specified.
    FileNotFoundError: If the file does not exist.
    IOError: If the file cannot be read.
    """
    # TODO: Maybe expand to a registry based system, to register a class to, and can then lookup by name below
    custom_algos = {
        'AutoV1': '',
        'AutoV2': '',
        'AutoV3': '',
    }

    if algorithm in custom_algos:
        pass # TODO: Make a class to support .update() like hashlib

    try:
        hash_func = getattr(hashlib, algorithm)()
    except AttributeError as e:
        raise ValueError(f"Unsupported hash algorithm: {algorithm}") from e

    try:
        with open(filepath, 'rb') as file:
            for chunk in iter(lambda: file.read(4096), b""):
                hash_func.update(chunk)
    except FileNotFoundError:
        raise FileNotFoundError(f"The file {filepath} does not exist.")
    except IOError as e:
        raise IOError(f"Could not read the file {filepath}: {e}")
    
    return hash_func.hexdigest()


async def register_file(filepath: str):
    """
    Processes each file for registration by storing its metadata and fingerprint in the database.

    Args:
    filepath (str): The full path to the file.
    """
    # We establish a new session here, as this should be synchronis within this function
    async with crud.AsyncSessionLocal() as db:
        try:
            file_size = os.path.getsize(filepath)
            mime_type = guess_mime_type(filepath)
            creation_time = datetime.fromtimestamp(os.path.getctime(filepath))
            modification_time = datetime.fromtimestamp(os.path.getmtime(filepath))

            # TODO: DEBUG, print any unknown mime types
            if mime_type == 'unknown':
                logger.info(f"File {filepath} has MIME type: {mime_type}")

            # Create or get the file entry
            db_file = await crud.create_file(db, filepath, mime_type, file_size, creation_time, modification_time)

            # Hashing the file
            file_hash = get_file_hash(filepath)

            # Update or create fingerprint
            await crud.update_or_create_fingerprint(db, db_file.id, file_hash, file_size)

            """
            Hashing algorithms I want to support:
            crc32
            blake3
            sha256
            AutoV1
            AutoV2
            AutoV3
            """

            logger.debug(f"Processed file successfully: {filepath}")

        except FileNotFoundError:
            logger.error(f"File not found: {filepath}")
        except PermissionError:
            logger.error(f"Permission denied for file: {filepath}")
        except Exception as e:
            logger.exception(f"An error occurred while processing {filepath}: {e}")
        finally:
            await db.commit()


# Background tasks
async def scan_directories(start_path: str):
    # Async version:
    paths = [os.path.join(root, file) for root, _, files in os.walk(start_path) for file in files]
    tasks = [register_file(path) for path in paths] # Convert each file processing into a task

    # Intergrate tqdm progress bar with asyncio execution
    for f in tqdm(asyncio.as_completed(tasks), total=len(tasks), unit="files"):
        await f # Each task completion updates the progress bar


async def main():
    await scan_directories(r'D:\AI Generation\_Repository\models\lora\SDXL')
    # path = r"D:\AI Generation\_Repository\models\Stable-diffusion\_SDXL\tunixStylizedPony_v10VAE.txt"
    # mime_type = guess_mime_type(path)

    # print(f"Mime for {path}: {mime_type}")


import asyncio
if __name__ == '__main__':
    crud.create_tables()
    
    asyncio.run(main())
```

database/database.py:
```
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.asyncio import create_async_engine, async_scoped_session, AsyncSession

DATABASE_URL_ASYNC = "sqlite+aiosqlite:///./dili.db"
DATABASE_URL_SYNC = "sqlite+pysqlite:///./dili.db"
MEMORY_URL = "sqlite+aiosqlite:///:memory:?cache=shared"

DB_VERBOSE = False


sync_engine = create_engine(
    DATABASE_URL_SYNC,
    connect_args={"check_same_thread": False},
    echo=DB_VERBOSE
)
async_engine = create_async_engine(
    DATABASE_URL_ASYNC,
    connect_args={"check_same_thread": False},
    echo=DB_VERBOSE
)

# SessionLocal = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
AsyncSessionLocal = sessionmaker(
    bind=async_engine,
    class_=AsyncSession,
    expire_on_commit=False
)
```

database/model.py:
```
from sqlalchemy import DateTime, func, String, Integer, ForeignKey, UniqueConstraint
from sqlalchemy.orm import declarative_base, Mapped, mapped_column, relationship
from typing import List

# Core Model
Model = declarative_base()

class TimeStampedModel(Model):
    """
    Tell my dumbass I missed a docstring.
    """
    __abstract__ = True

    created_at = mapped_column(DateTime(timezone=True), default=func.now())
    updated_at = mapped_column(DateTime(timezone=True), default=func.now(), onupdate=func.now())

"""
TEMPLATE:

class (TimeStampedModel):
"""
"""
    __tablename__ = ""

    # columns

    # relationships

    # methods
    def __repr__(self):
        return f"{self.__tablename__!r}(id={self.id!r})"
"""

class Fingerprint(TimeStampedModel):
    """
    Tell my dumbass I missed a docstring.
    """
    __tablename__ = "fingerprints"

    # columns
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    fingerprint: Mapped[str] = mapped_column(String(128), index=True)
    size: Mapped[int] = mapped_column(Integer)
    
    __table_args__ = (
        UniqueConstraint('fingerprint', 'size'),
    )

    # relationships
    files: Mapped[List['FingerprintFile']] = relationship("FingerprintFile", back_populates="fingerprint_relation", cascade="all, delete")

    # methods
    def __repr__(self):
        return f"{self.__tablename__!r}(id={self.id!r}, fingerprint={self.fingerprint!r}, size={self.size!r})"

class HashType(TimeStampedModel):
    """
    Tell my dumbass I missed a docstring.
    """
    __tablename__ = "hash_types"

    # columns
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(50))

    # relationships

    # methods
    def __repr__(self):
        return f"{self.__tablename__!r}(id={self.id!r}, name={self.name!r})"

class Hash(TimeStampedModel):
    """
    Tell my dumbass I missed a docstring.
    """
    __tablename__ = "hashes"

    # columns
    fingerprint_id: Mapped[int] = mapped_column(ForeignKey(Fingerprint.id), primary_key=True)
    hash_type_id: Mapped[int] = mapped_column(ForeignKey(HashType.id), primary_key=True)
    hash: Mapped[str] = mapped_column(String(256), index=True)

    # __table_args__ = (
    #     UniqueConstraint('fingerprint_id', 'hash_type_id'),
    # )

    # relationships

    # methods
    def __repr__(self):
        return f"{self.__tablename__!r}(fingerprint_id={self.fingerprint_id!r}, hash_type_id={self.hash_type_id!r}, hash={self.hash!r})"


class File(TimeStampedModel):
    """
    Tell my dumbass I missed a docstring.
    """
    __tablename__ = "files"

    # columns
    id: Mapped[int] = mapped_column(primary_key=True)
    path: Mapped[str] = mapped_column(index=True, unique=True)
    type: Mapped[str] = mapped_column(String(100), index=True) # We want to store MIME typing here
    size: Mapped[int] = mapped_column(Integer)
    file_created_at = mapped_column(DateTime(timezone=True))
    file_updated_at = mapped_column(DateTime(timezone=True))

    # relationships
    fingerprint: Mapped['FingerprintFile'] = relationship("FingerprintFile", back_populates="file_relation", cascade="all, delete-orphan")

    # methods
    def __repr__(self):
        return f"{self.__tablename__!r}(id={self.id!r}, path=`{self.path!r}`, type={self.type!r}, size={self.size!r}, file_created_at={self.file_created_at!r}, file_updated_at={self.file_updated_at!r})"


class FingerprintFile(TimeStampedModel):
    """
    Association table for linking fingerprints to files.
    This table supports a one-to-many relationship between Fingerprint and File.
    Each Fingerprint might be related to multiple Files, but each File can be associated
    with only a specific Fingerprint at any given time.
    """
    __tablename__ = "fingerprint_files"

    # columns
    fingerprint_id: Mapped[int] = mapped_column(ForeignKey(Fingerprint.id), primary_key=True)
    file_id: Mapped[int] = mapped_column(ForeignKey(File.id), primary_key=True)

    # relationships
    fingerprint_relation: Mapped['Fingerprint'] = relationship("Fingerprint", back_populates="files")
    file_relation: Mapped['File'] = relationship("File", back_populates="fingerprint")

    # methods
    def __repr__(self):
        return f"{self.__tablename__!r}(fingerprint_id={self.fingerprint_id!r}, file_id={self.file_id!r})"
```

database/crud.py:
```
# Database CRUD
from sqlalchemy import DateTime, select, delete
from sqlalchemy.ext.asyncio import AsyncSession

from .database import sync_engine, AsyncSessionLocal
from .model import Model, File, Fingerprint, FingerprintFile

"""
Global crud
"""


# Global DB access
def get_db():
    db = AsyncSessionLocal()
    try:
        yield db
    finally:
        db.close()

# This cannot be done async
def create_tables():
    Model.metadata.create_all(sync_engine)


"""
Fingerprint
"""

# CREATE
async def create_fingerprint(db: AsyncSession, fingerprint: str, size: int):
    new_fingerprint = Fingerprint(fingerprint=fingerprint, size=size)

    db.add(new_fingerprint)
    await db.commit()
    await db.refresh(new_fingerprint)

    return new_fingerprint

# READ
async def fetch_fingerprint(db: AsyncSession, fingerprint_id: int):
    result = await db.execute(
        select(Fingerprint).where(Fingerprint.id == fingerprint_id)
    )

    return result.scalars().first()

# UPDATE - There should never be a reason to update a fingerprint
# async def update_fingerprint(db: AsyncSession, fingerprint: str, new_size: int):
#     db_fingerprint = await fetch_fingerprint(db, fingerprint)

#     if db_fingerprint:
#         db_fingerprint.size = new_size
#         await db.commit()
#         return db_fingerprint
    
#     return None

# DELETE - We don't delete fingerprints
# async def delete_fingerprint(db: AsyncSession, fingerprint_id: int):
#     db_fingerprint = await fetch_fingerprint(db, fingerprint_id)

#     if db_fingerprint:
#         await db.delete(db_fingerprint)
#         await db.commit()
#         return True
    
#     return False


"""
File
"""



# TODO: Use this as an example to docstring the rest of the project.
# TODO: Also: `Readme and Guides: Maintain a README.md for your repository, outlining project setup, how to install dependencies, how to run the project, and other usage notes.`
# CREATE
async def create_file(db: AsyncSession, path: str, file_type: str, size: int, file_created_at: DateTime, file_updated_at: DateTime):
    """
    Creates a new file record in the database.
    
    Parameters:
        db (Session): The database session.
        path (str): The file path.
        file_type (str): The type of the file.
        size (int): The size of the file in bytes.
        file_created_at (datetime): When the file was created.
        file_updated_at (datetime): When the file was last updated.

    Returns:
        File: The created File object.
    """
    # Check if the entry already exists in the database
    result = await db.execute(
        select(File).where(File.path == path)
    )

    db_file = result.scalars().first()

    if db_file:
        return db_file

    new_file = File(
        path=path,
        type=file_type,
        size=size,
        file_created_at=file_created_at,
        file_updated_at=file_updated_at
    )

    db.add(new_file)
    await db.commit()
    await db.refresh(new_file)

    return new_file

# READ
async def fetch_file(db: AsyncSession, file_id: int):
    result = await db.execute(
        select(File).where(File.id == file_id)
    )

    return result.scalars().first()

# UPDATE
async def update_file(db: AsyncSession, file_id: int, path: str = None, file_type: str = None, size: int = None, file_created_at: DateTime = None, file_updated_at: DateTime = None):
    db_file = await fetch_file(db, file_id)

    if db_file:
        if path:
            db_file.path = path
        if file_type:
            db_file.type = file_type
        if size:
            db_file.size = size
        if file_created_at:
            db_file.file_created_at = file_created_at
        if file_updated_at:
            db_file.file_updated_at = file_updated_at

        await db.commit()
        return db_file
    
    return None

# DELETE
async def delete_file(db: AsyncSession, file_id: int):
    db_file = await fetch_file(db, file_id)

    if db_file:
        await db.delete(db_file)
        await db.commit()
        return True
    
    return False

"""
Fingerprint and File Relationship
"""

# CREATE
async def associate_fingerprint_and_file(db: AsyncSession, fingerprint_id: int, file_id: int):
    existing_association = await db.execute(
        select(FingerprintFile).where(FingerprintFile.fingerprint_id == fingerprint_id, FingerprintFile.file_id == file_id)
    )

    if existing_association.scalars().first():
        return "Association already exists."
    
    association = FingerprintFile(fingerprint_id=fingerprint_id, file_id=file_id)
    db.add(association)
    await db.commit()

    return "Association created."

# READ
async def fetch_files_by_fingerprint(db: AsyncSession, fingerprint_id: int):
    result = await db.execute(
        select(File).join(FingerprintFile).where(FingerprintFile.fingerprint_id == fingerprint_id)
    )

    return result.scalars().all()

# UPDATE OR CREATE
async def update_or_create_fingerprint(db: AsyncSession, file_id: int, new_fingerprint_hash: str, size: int):
    # Starting a transaction
    # async with db.begin():
    # Check if the new fingerprint already exists
    new_fingerprint = await db.get(Fingerprint, new_fingerprint_hash)

    if not new_fingerprint:
        new_fingerprint = Fingerprint(fingerprint=new_fingerprint_hash, size=size)
        db.add(new_fingerprint)
        await db.commit()
        await db.refresh(new_fingerprint)

    # Get the file and update its fingerprint association
    file = await db.get(File, file_id)

    if not file:
        return "404 - File not found."
    
    # Remove old associations (if any)
    await db.execute(
        delete(FingerprintFile).where(FingerprintFile.file_id == file_id)
    )

    # Create new association
    new_association = FingerprintFile(fingerprint_id=new_fingerprint.id, file_id=file_id)
    db.add(new_association)
    await db.commit()

    return "Fingerprint updated."

# DELETE
async def remove_association_fingerprint_and_file(db: AsyncSession, fingerprint_id: int, file_id: int):
    association = await db.execute(
        select(FingerprintFile).where(FingerprintFile.fingerprint_id == fingerprint_id, FingerprintFile.file_id == file_id)
    )

    association_to_remove = association.scalars().first()

    if association_to_remove:
        await db.delete(association_to_remove)
        await db.commit()
        return "Association removed."
    
    return "Association not found."
```

Think about it in a step-by-step way to make the best, modern and standard python and database practices, thinking about SQLite and SQLAlchemy with asyncio, and how I can fix the locking issue while keeping things async.