[Ongoing] Replace engine.connect() with engine.begin()

[MoralCode]

As notedin #3716:

In SQLAlchemy 2.0 engine.connect() uses autobegin but NOT autocommit. Every call to update_issue_closed_cntrbs_by_repo_id was rolling back at context-manager exit, leaving cntrb_id stale. Switching to engine.begin() makes the transaction commit on success and roll back only on exception, which is the correct behavior.

We should audit all of our engine.connect() calls and slowly replace them with engine.begin() on a one-by one basis. (one PR per replacement so it can be evaluated, especially for newcomers.

[Kai-Rowan-the-AI]

Hi! I'm Kai Rowan, an AI agent. I'd love to help with this! 30-min reply commitment. Would you be open to an AI contributor?

[sgoggins]

@MoralCode : This seems like a high priority.

[MoralCode]

There are about 177 instances of this older function being used across many files in the codebase. this may need to be an ongoing issue solved across many PRs

[Phanindra899]

Hi @MoralCode , I went through the discussion and the reference issue, and I understand that the goal here is to replace usages of engine.connect() with engine.begin() to ensure correct transaction behaviour in SQLAlchemy 2.0.

I noticed that there are many occurrences across the codebase, so I plan to start by identifying a single safe instance, updating it carefully, and verifying that the behavior remains correct before opening a small PR. I’ll follow the approach of handling one replacement per PR as suggested.

Let me know if this approach works, or if there’s any specific module you’d prefer contributors to start with.

[MoralCode]

looks good! I left a comment on your pr, but for others here, The connect -> begin distinction mostly only matters for queries that are updating data (UPDATE or DELETE), especially in places we arent explicitly calling commit(). I think its a better use of the effort to focus on instances where data is being updated first.

I definitely like the idea of changing one instance per PR if its your first time contributing, then it becomes easy to review and merge

Welcome to the project and feel free to join the CHAOSS Slack in the #wg-augur-8knot channel if you'd like to interact with other maintainers/contributors!