12

Just wondering what happens if a connection is not properly closed in pyodbc.

Also, do i need to close the cursor before the connection?

In my particular use case I included a call to close the connection in a custom DB Class in the .__del__() method, but do not explicitly call close.

What's best practice?

mwoods
  • 317
  • 1
  • 3
  • 9

1 Answers1

11

Connections (and their associated cursors) are automatically closed when they are deleted, so it cleans up behind itself. However, if you're connecting in more than one place, you'll want to close explicitly. Also, to be more Pythonic, it is always better to be explicit.

Also note: closing a connection without committing your changes will result in an automatic implicit rollback.

For more, and reference:

https://github.com/mkleehammer/pyodbc/wiki/Connection#close

eksortso
  • 1,273
  • 3
  • 12
  • 21
FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • GAH, you're absolutely right, my fingers got crossed. This is what I get for answering questions first thing in the morning. Can't forget PEP-20! https://www.python.org/dev/peps/pep-0020/ Fixing now. – FlipperPA Jun 18 '15 at 17:29
  • 1
    In other questions I've seen it recommended to use `with` blocks to automatically close and clean up things like file handles. Is that considered good ("best"?) practice for database connections as well? – Gord Thompson Jun 19 '15 at 09:02
  • 5
    Responding to my own comment, it is worth noting that using a pyodbc `connection` object in a `with ...` block ("context manager") does **not** automatically close the connection at the end of that block. I just discovered that while testing some code involving SQL Server connections. For more discussion of pyodbc and context management, see [this issue](https://github.com/mkleehammer/pyodbc/issues/43) on GutHub. – Gord Thompson Sep 01 '16 at 19:45
  • When is a connection deleted? Does this happen automatically when a Python module stops running, for example? – Dustin Michels Aug 20 '18 at 23:48
  • 1
    per https://github.com/mkleehammer/pyodbc/issues/48, "When the function exits, either by completing or via an exception, the cursor will be deleted. Since the cursor is the only reference to the connection, it will be deleted also - immediately" – johnDanger Sep 05 '19 at 16:36
  • what does it mean by "you're connecting in more than one place" or "the connection is referenced in more than one place" per the document... If I only created one connection object, but I passed it into a lot of other object and their method, so it mean i'm "referencing" it in more than one place? – Jing He Jun 28 '20 at 02:34
  • 1
    @JingHe I've found the best way to manage connections is with a `with` statement, which will automatically close the connection once you're out of the block. `with connection.cursor() as cursor:` Then you don't have to worry about it. – FlipperPA Jun 28 '20 at 16:15
  • @FlipperPA but my program read / write database several times every seconds, and the I pass the cursor into a lot of different objects and methods... If I open connection and close it every time in every method... Will it slow down my program? – Jing He Jul 16 '20 at 04:47
  • Yes it will, in that case, you'll want to explicitly open it and close it. – FlipperPA Jul 17 '20 at 01:48