Simple insert statement autogen for sqlalchemy

query.sql

SELECT
	m.name,
	(
		'INSERT INTO ' || m.name || '(' || GROUP_CONCAT (p.name, ', ') || ')VALUES(' || GROUP_CONCAT (
			(
				CASE
					WHEN p.pk
					AND m.sql LIKE "%AUTOINCREMENT%" THEN 'NULL'
					ELSE ':' || p.name
				END
			),
			', '
		) || ');'
	) AS stmt
FROM
	sqlite_master m
	LEFT OUTER JOIN pragma_table_info ((m.name)) p ON m.name <> p.name
WHERE
	m.type = 'table'
	AND m.name NOT LIKE "sqlite_%"
GROUP BY
	m.name;

sample.py

from sqlalchemy import create_engine

SELECT_INSERT_STATEMENTS = '''SELECT m.name, ( 'INSERT INTO ' || m.name || '(' || GROUP_CONCAT (p.name, ', ') || ')VALUES(' || GROUP_CONCAT ( ( CASE WHEN p.pk AND m.sql LIKE "%AUTOINCREMENT%" THEN 'NULL' ELSE ':' || p.name END ), ', ' ) || ');' ) AS stmt FROM sqlite_master m LEFT OUTER JOIN pragma_table_info ((m.name)) p ON m.name <> p.name WHERE m.type = 'table' AND m.name NOT LIKE "sqlite_%" GROUP BY m.name;'''

# GOOD Example file: https://github.com/codecrafters-io/sample-sqlite-databases/blob/master/sample.db
DB_FILEPATH = ... # "~/Downloads/sample.db"

engine = create_engine(DB_FILEPATH, echo=False)

with engine.begin() as conn:
    insert_statements = {el['name']: el['insert'] for el in conn.execute(select_insert_statements).mappings().all()}

print(insert_statements)
# {
#   "apples": "INSERT INTO apples(id, name, color) VALUES(NULL, :name, :color);",
#   "oranges": "INSERT INTO oranges(id, name, description) VALUES(NULL, :name, :description);"
# }

with engine.begin() as conn:
    conn.execute(
        insert_statements["apple"],
        [
            {"name": "a", "color": "red"},
            {"name": "b", "color": "green"},
        ],
    )