Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > python > insights

DB Connection Manager now supports mxODBC, win32ODBC and PyODBC

bartonc's Avatar
Moderator
  
Join Date: Sep 2006
Location: Minden, Nevada, USA
Posts: 6,400
#1   Oct 18 '07
This is a work in progress (current and active).
There are some issues to resolve in supporting multiple connection types and I plan to add PySQLite to the mix. The this update is due to my suggesting the accompanying helper functions in a recent post and noticing that the ones posted here are a bit out of date. I'll post those helpers in a separate thread.

I've also been working with the following database engines:
MySQL 5.x
SQLite3
JET engine 4.0

Here is the latest dbConection container which was originally called dbServer, but I'd love suggestions for a better name:
Expand|Select|Wrap|Line Numbers
  1. #-----------------------------------------------------------------------------
  2. # Name:        SQLConMan.py  ### This needs a better name ###
  3. # Purpose:     database connection handling. The DBServer() traps all errors and
  4. #              writes status to its master (which can be stdout). If an error does
  5. #              occure, Execute() returns None. This might be confusing since pyodbc
  6. #              returns None from execute() on quries that don't return a result set.
  7. #              It's interesting to not that db interface creator have begun add an
  8. #              execute() funtion to their connection objects.
  9. #
  10. # Author:      <your name>
  11. #
  12. # Created:     2007/10/13
  13. # RCS-ID:      $Id: wxdbtools.py $
  14. # Copyright:   (c) 2007
  15. # Licence:     <your licence>
  16. #-----------------------------------------------------------------------------
  17.  
  18. from time import time
  19.  
  20. ## MySQL is the default db here:
  21. ## MySQL differs from MSSQL and SQLite regarding TRANSACTION statemets  ##
  22. TRANSACTIONS = ('START TRANSACTION', 'BEGIN TRANSACTION')     # MySQL, SQLite & MSSQL
  23. TransactionQuery = TRANSACTIONS[0]
  24. ## and these have a different matrix of support
  25. LASTINSERTQUERIES = ('SELECT Last_Insert_Id()', 'SELECT @@IDENTITY') # MySQL & SQLite, MSSQL
  26. LastInsertQuery = LASTINSERTQUERIES[0]
  27. ## One idea for detecting the DB type is to try each one until it works ##
  28.  
  29. PYODBC = 'pyodbc'
  30. MXODBC = 'mxodbc'
  31. WIN23ODBC = 'win32odbc'
  32.  
  33. class SQLConMan(object):
  34.     global TransactionQuery
  35.     def __init__(self, master):
  36.         self.master = master      # a place to call write() on
  37.         self.dbConnection = None  # the API 2.0 connection object
  38.         self.dbCursor = None      # the API 2.0 cursor object
  39.  
  40.     def __getattribute__(self, name):
  41.         """Redirect method calls to the connection 'object'."""
  42.         try:
  43.             return object.__getattribute__(self, name)
  44.         except AttributeError:
  45.             # __getattribute__() only work for subtypes of object.
  46. ##            return object.__getattribute__(self.dbConnection, name)
  47.             return eval("self.dbConnection.%s" %(name))
  48.  
  49.     def Login(self, servername, username, password, autocommit=1, database="", conType=PYODBC):
  50.         """Attempt to create a database login. If successful, return
  51.            an open connection. Otherwise, return None."""
  52.         global OperationalError, ProgrammingError, DatabaseError, DriverError
  53.  
  54.         odbcstring = "DSN=%s;UID=%s;PWD=%s" %(servername, username, password)
  55.  
  56.         if conType == PYODBC:
  57.             from pyodbc import connect
  58.             from pyodbc import OperationalError
  59.             from pyodbc import ProgrammingError
  60.             from pyodbc import DatabaseError
  61.             from pyodbc import Error as DriverError
  62.             cac = int(not autocommit)
  63.             try:
  64.                 self.dbConnection = connect(odbcstring, autocommit=autocommit)
  65.                 self.dbCursor = self.dbConnection.cursor()
  66.             except (DatabaseError, OperationalError, DriverError), message:
  67.                 self.NoLogin(servername, username, message)
  68.                 return
  69.         elif conType == MXODBC:
  70.             from mx.ODBC.Windows import Connect
  71.             from mx.ODBC.Windows import OperationalError
  72.             from mx.ODBC.Windows import ProgrammingError
  73.             from mx.ODBC.Windows import DatabaseError
  74.             from mx.ODBC.Windows import error as DriverError
  75.             cac = int(not autocommit)
  76.             try:
  77.                 self.dbConnection = Connect(servername, user=username, password=password,
  78.                                             clear_auto_commit=cac)
  79.                 self.dbCursor = self.dbConnection.cursor()
  80.             except (DatabaseError, OperationalError), message:
  81.                 self.NoLogin(servername, username, message)
  82.                 return
  83.         elif conType == WIN32ODBC:
  84.             import odbc
  85.             from dbi import opError as OperationalError
  86.             from dbi import progError as ProgrammingError
  87.             from dbi import dataError as DatabaseError
  88.             from odbc import OdbcError as DriverError
  89.             try:
  90.                 self.dbConnection = odbc.odbc(odbcstring)
  91.                 self.dbCursor = self.dbConnection.cursor()
  92.             except (DatabaseError, OperationalError), message:
  93.                 self.NoLogin(servername, username, message)
  94.                 return
  95.  
  96.         self.master.write("%s has been logged onto %s\n" %(username, servername))
  97.         if database:
  98.             try:
  99.                 self.dbCursor('USE %s' %database)
  100.             except (DatabaseError, OperationalError):
  101.                 pass
  102.         return self.dbConnection
  103.  
  104.     def NoLogin(self, servername, username, message):
  105.         self.master.write('Couldn\'t log on to the server `%s` as `%s`\n' %(servername, username))
  106.         self.DBError("", message)
  107.  
  108.     def DBError(self, query, message):
  109.         """Format the current message and display it.
  110.            Report the query and error to the master."""
  111.         self.master.write('ODBC Error: %s\n' %message)
  112. ##        raise
  113. # all raised by PyODBC from various DBs #
  114. # """('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver]
  115. # Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (-3500)")"""
  116. # """ ('42S02', "[42S02] [Microsoft][ODBC Microsoft Access Driver] 
  117. # The Microsoft Jet database engine cannot find the input table or query 'main'.  
  118. # Make sure it exists and that its name is spelled correctly. (-1305)")"""
  119.  
  120.  
  121. # """('42S02', "[42S02] [MySQL][ODBC 3.51 Driver][mysqld-5.0.22-community-max-nt]
  122. # Table 'hetap_setup.main' doesn't exist (1146)")"""
  123.  
  124. # """('HY000', '[HY000] near "USE": syntax error (1) (1)')"""
  125. # """('HY000', '[HY000] no such table: main (1) (1)')"""
  126.  
  127. ##        self.master.write('%s\nODBC Error %s #%d:  %s #%d\n' %(query, message[0], message[1],
  128. ##                          message[-2].split(']')[-1], message[-1]))
  129.  
  130.     def Execute(self, query, *params):
  131.         """Execution method reports on the number of rows affected and duration
  132.            of the database query execution and catches errors. Return a reference
  133.            to the cursor if no error ocurred, otherwise, None."""
  134.         cursor = self.dbCursor
  135.         if cursor:
  136.             try:
  137.                 now = time()
  138.                 if params:
  139.                     cursor.execute(query, *params)
  140.                 else:
  141.                     cursor.execute(query)
  142. ##                print cursor
  143. ##                nRows = cursor.rowcount
  144. ##                self.master.write("%s   " % query)
  145. ##                self.master.write("%d rows affected: %.2f sec.\n" %(nRows, time() - now))
  146.             except (DatabaseError, OperationalError, DriverError), message:
  147.                 self.DBError(query, message)
  148.                 return
  149.             return cursor
  150.  
  151.     def ExecuteScript(self, query):
  152.         """"""
  153.         for singlequery in query.split(';'):
  154.             if singlequery:
  155.                 self.Execute(singlequery + ';')
  156.  
  157.     def DBExists(self, database):
  158.         """Return True if database exists"""
  159.         cursor = self.Execute("show databases")
  160.         if cursor:
  161.             rows = cursor.fetchall()
  162.             return (database.strip('`').lower(),) in rows
  163.  
  164.     def TableExists(self, table):
  165.         """Return True if database exists"""
  166.         cursor = self.Execute("show tables")
  167.         if cursor:
  168.             rows = cursor.fetchall()
  169.             return (table.strip('`').lower(),) in rows
  170.  
  171.     def ConnectionIsOpen(self):
  172. ##        """Return 1 if the connection has been established and is not closed.
  173. ##           Return 0 if it is closed; None, if it has not been established."""
  174.         return self.dbConnection is not None
  175.  
  176.     def CloseConnection(self):
  177.         """Close the connection if it exists and is open."""
  178.         if self.dbConnection is not None:
  179.             try:
  180.                 self.dbConnection.close()
  181.                 self.master.write("Closed connection.\n")
  182.             except ProgrammingError:
  183.                 self.master.write("Connection is already closed!\n")
  184.         else:
  185.             self.master.write("Connection does not exist!\n")
  186.  
  187.     def SetMaster(self, master):
  188.         """Allow the master to be reset."""
  189.         self.master = master
  190.  
  191.   ## May want to rethink these ones ##
  192.  
  193.     def GetMaster(self):
  194.         return self.master
  195.  
  196.     def GetDbConnection(self):
  197.         return self.dbConnection
Last edited by bartonc; Oct 18 '07 at 07:19 PM.



Reply

« previous insight | next insight »

Similar Python bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,382 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved