search

nackjicholson / aiosql

Simple SQL in Python
https://nackjicholson.github.io/aiosql
Other
1.32k stars 60 forks source link

Transaction handling for I/U/D #212

Closed birlorg closed 1 month ago

birlorg commented 1 month ago

When using the psycopg driver at least, the way aoisql calls the insert's one has to set autocommit=True or it won't commit the insert's, updates or deletes.

According to the psycopg documentation, if it was switched to using connection context's it would autocommit without having to set it autocommit=True.

psycopg uses the Generic adapter and doesn't use the connection context: https://github.com/nackjicholson/aiosql/blob/f4b78a9acd1b492efa12244e804221a7b221900c/aiosql/adapters/generic.py#L109

It would be nice if there was a way to handle transactions somehow, for instance one has a handful of inserts/queries that they all want to do within a transaction. Right now I don't believe that's possible to do, without grabbing your own cursor object and doing it yourself.

I'm not sure how you would want transactions handled in aiosql. It could be done a number of different ways, if it's something you even want aoisql to handle. I can find no mention of transactions in the documentation or a previous case.

I just started playing with it over the weekend and I really like the workflow so far. Thanks for writing, publishing and maintaining this, I would have never thought of doing SQL like this, but it's awesome so far. Thanks!

zx80 commented 1 month ago

I'm not sure I understand the issue: Aiosql does not care about transactions, it just executes SQL queries through the provided connection. The driver name is provided just to know how to handle parameters. The user actually controls the transactions because it controls the connection…

Set autocommit to whatever you want when creating the connection?

db = aiosql.from_path("queries.sql", "psycopg")
conn = psycopg.connect("…", autocommit=True)
db.insert_new_data(conn, val="whatever")
# transaction was committed

Or simply call conn.commit() whenever you want?

db = aiosql.from_path("queries.sql", "psycopg")
conn = psycopg.connect("…", autocommit=False)
db.insert_new_data(conn, val="whatever")
# not committed yet…
conn.commit()
# ok

AFAIK transactions in psycopg are only linked to the connection and not to the cursor anyway.

You may have a look at tne AnoDB extension to manage connection and queries at once:

db = anodb.DB("psycopg", "…", "queries.sql", autocommit=True)
db.insert_new_data(val="whatever")
# transaction was committed

Does this answer clarifies the issue for you? Or did I miss anything?

birlorg commented 1 month ago

DOH. Sorry, you are right. I'm using anodb and didn't realize the separation there. Sorry!

zx80 commented 1 month ago

This is fine, no problem! BTW, AnoDB DB class also carries the connection methods:

db = anodb.DB("psycopg", "queries.sql", kwags_only=True, autocommit=False)
db.insert_some_data(val="stuff")
# not committed…
db.commit()
# committed!