Add documentation that pymssql requires an explicit commit #68
Description
I'm testing this with pymssql against a MSSQL instance. Following the examples given by the documentation you'd assume this was the way forward
from pydapper import connect
import os
from dataclasses import dataclass
@dataclass
class Table:
Name: str
Description: str
test_model = Table("Test", "Test")
conn_string = os.getenv("connection_string")
with connect(conn_string) as commands:
rows = commands.execute("INSERT INTO dbo.Table (Name, Description) VALUES (?Name?, ?Description?)", param=test_model.__dict__)
assert rows == 1 # This is truerows will always be 1 here, but the results are never committed to the database. If, however, you do:
with connect(conn_string) as commands:
rows = commands.execute("INSERT INTO dbo.Table (Name, Description) VALUES (?Name?, ?Description?)", param=test_model.__dict__)
assert rows == 1 # This is true
commands.connection.commit()Now the item will be written to the database.
If memory serves, I've seen the first approach work with SQLite in some earlier testing I did, so I'm not sure what the way forwards is:
Either this should be added to Commands.execute like so:
def execute(self, sql: str, param: Union["ParamType", "ListParamType"] = None) -> int:
handler = self.SqlParamHandler(sql, param)
with self.cursor() as cursor:
rowcount = handler.execute(cursor)
self.connection.commit()
return rowcountOr the documentation should be updated to reflect the necessity of committing yourself. I'll happily submit a PR either way, but I'd be interested in your opinion on this first.