Python dataclasses and sqlite3 adapters

Question

What is the cleanest way to commit data stored in instances of a dataclass contained in a list to SQLite with sqlite3's executemany?

For example:

@dataclass
class Person:
    name: str
    age: int

a = Person("Alice", 21)
b = Person("Bob", 22)
c = Person("Charlie", 23)

people = [a, b, c] 

# ...

cursor.executemany(f"INSERT INTO {table} ({headers}) ({placeholders})", people) # <-- fails, obviously

Is there a built-in mechanism that does the work for me (goes over each attribute of the dataclass), or an idiomatic way, or, if not, how do I implement an adapter for this without explicitly listing the attributes one by one?

I could convert it to a list of tuples, that works out of the box, yet feels redundant.

Answer 1

Use the astuple() function to convert the dataclass instances to tuples.

from dataclasses import dataclass, astuple

headers = ','.join(f.name for f in fields(Person))
people = map(astuple, [a, b, c])
placeholders = ','.join(['?'] * len(headers))
table = 'persons'

cursor.executemany(f"INSERT INTO {table} ({headers}) ({placeholders})", people)