Sending the sqlite3 autocommit parameter renders this PRAGMA command a no-op

Bug report

Bug description:

root issue is here ( discussed w\ @zzzeek ): https://github.com/sqlalchemy/sqlalchemy/discussions/12767

An explicit emit of the autocommit attribute (=False) makes PRAGMA command not work. Example of the code:

engine = create_async_engine(
    config.SQLITE_URL,
    echo=True,
    connect_args={"autocommit": False},
    poolclass=NullPool
)

@event.listens_for(engine.sync_engine, "connect", named=True)
def enable_foreign_keys(dbapi_connection: AsyncAdapt_aiosqlite_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.execute("PRAGMA foreign_keys")
    result = cursor.fetchone() 
    print(f"Foreign keys pragma value: {result}")
    cursor.close()

CPython versions tested on:

3.13

Operating systems tested on:

Windows

Linked PRs

• gh-137505