Python Database connection Close

Question

Using the code below leaves me with an open connection  how do I close       import pyodbc conn   pyodbc connect  DRIVER MySQL ODBC 5 1 driver SERVER localhost DATABASE spt UID who PWD testest     csr   conn cursor     csr close   del csr

User · Answer

According to pyodbc documentation  connections to the SQL server are not closed by default  Some database drivers do not close connections when close   is called in order to save round-trips to the server   To close your connection when you call close   you should set pooling to False   import pyodbc  pyodbc pooling   False

User · Answer

You can wrap the whole connection in a context manager  like the following   from contextlib import contextmanager import pyodbc import sys   contextmanager def open db connection connection string  commit False       connection   pyodbc connect connection string      cursor   connection cursor       try          yield cursor     except pyodbc DatabaseError as err          error    err args         sys stderr write error message          cursor execute  ROLLBACK           raise err     else          if commit              cursor execute  COMMIT           else              cursor execute  ROLLBACK       finally          connection close     Then do something like this where ever you need a database connection   with open db connection        as cursor        Your code here   The connection will close when you leave the with block  This will also rollback the transaction if an exception occurs or if you didn t open the block using with open db connection        commit True

User · Answer

Connections have a close method as specified in PEP-249  Python Database API Specification v2 0   import pyodbc conn   pyodbc connect  DRIVER MySQL ODBC 5 1 driver SERVER localhost DATABASE spt UID who PWD testest     csr   conn cursor     csr close   conn close         lt --- Close the connection   Since the pyodbc connection and cursor are both context managers  nowadays it would be more convenient  and preferable  to write this as  import pyodbc conn   pyodbc connect  DRIVER MySQL ODBC 5 1 driver SERVER localhost DATABASE spt UID who PWD testest    with conn      crs   conn cursor       do stuff       conn commit   will automatically be called when Python leaves the outer  with  statement       Neither crs close   nor conn close   will be called upon leaving the  with  statement     See https   github com mkleehammer pyodbc issues 43 for an explanation for why conn close   is not called  Note that unlike the original code  this causes conn commit   to be called  Use the outer with statement to control when you want commit to be called   Also note that regardless of whether or not you use the with statements  per the docs   Connections are automatically closed when they are deleted  typically when they go out of scope  so you should not normally need to call  conn close     but you can explicitly close the connection if you wish   and similarly for cursors  my emphasis    Cursors are closed automatically when they are deleted  typically when they go out of scope   so calling  csr close    is not usually necessary

User · Answer

You might try turning off pooling  which is enabled by default  See this discussion for more information   import pyodbc pyodbc pooling   False conn   pyodbc connect  DRIVER MySQL ODBC 5 1 driver SERVER localhost DATABASE spt UID who PWD testest     csr   conn cursor     csr close   del csr

User · Answer

You can define a DB class as below  Also  as andrewf suggested  use a context manager for cursor access I d define it as a member function   This way it keeps the connection open across multiple transactions from the app code and saves unnecessary reconnections to the server    import pyodbc  class MS DB            Collection of helper methods to query the MS SQL Server database               def   init   self  username  password  host  port 1433  initial db  dev db            self username   username         self  password   password         self host   host         self port   str port          self db   initial db         conn str    DRIVER DRIVER ODBC Driver 13 for SQL Server SERVER                          self host     PORT    self port    DATABASE                          self db    UID    self username    PWD                           self  password              print  Connected to DB    conn str          self  connection   pyodbc connect conn str                  pyodbc pooling   False      def   repr   self           return f MS-SQLServer   self username     lt password hidden gt     self host      self port      self db          def   str   self           return f MS-SQLServer Module for STP on  self host        def   del   self           self  connection close           print  Connection closed          contextmanager     def cursor self  commit  bool   False                       A context manager style of using a DB cursor for database operations           This function should be used for any database queries or operations that          need to be done             param commit          A boolean value that says whether to commit any database changes to the database  Defaults to False           type commit  bool                     cursor   self  connection cursor           try              yield cursor         except pyodbc DatabaseError as err              print  DatabaseError      format err               cursor rollback               raise err         else              if commit                  cursor commit           finally              cursor close    ms db   MS DB username  my user   password  my secret   host  hostname   with ms db cursor   as cursor          cursor execute  SELECT   version            print cur fetchall

[python] Python Database connection Close

Examples related to python

Examples related to database-connection