Mercurial > hg > ZDBInterface
annotate DBInterface.py @ 8:17b19345d011
added autocommit option.
| author | casties |
|---|---|
| date | Thu, 17 Feb 2011 19:25:16 +0100 |
| parents | 1b25a85a2165 |
| children | 22c16a632909 |
| rev | line source |
|---|---|
| 2 | 1 ''' |
| 2 Created on 14.2.2011 | |
| 3 | |
| 4 @author: casties | |
| 5 ''' | |
| 6 | |
| 7 import logging | |
|
6
1b25a85a2165
fixed bug and improved sqlName
root@xserve09.mpiwg-berlin.mpg.de
parents:
4
diff
changeset
|
8 import re |
| 2 | 9 import psycopg2 |
| 10 # make psycopg use unicode objects | |
| 11 import psycopg2.extensions | |
| 12 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE) | |
| 13 psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY) | |
| 14 | |
| 15 from Products.ZSQLMethods.SQL import SQLConnectionIDs | |
| 3 | 16 from Shared.DC.ZRDB.Results import Results |
| 2 | 17 |
| 18 | |
| 19 def unicodify(s,alternate='latin-1'): | |
| 20 """decode str (utf-8 or latin-1 representation) into unicode object""" | |
| 21 if not s: | |
| 22 return u"" | |
| 23 if isinstance(s, str): | |
| 24 try: | |
| 25 return s.decode('utf-8') | |
| 26 except: | |
| 27 return s.decode(alternate) | |
| 28 else: | |
| 29 return s | |
| 30 | |
| 31 def utf8ify(s): | |
| 32 """encode unicode object or string into byte string in utf-8 representation. | |
| 33 assumes string objects to be utf-8""" | |
| 34 if not s: | |
| 35 return "" | |
| 36 if isinstance(s, str): | |
| 37 return s | |
| 38 else: | |
| 39 return s.encode('utf-8') | |
| 40 | |
| 41 def getTextFromNode(node): | |
| 42 """get the cdata content of a XML node""" | |
| 43 if node is None: | |
| 44 return "" | |
| 45 | |
| 46 if isinstance(node, list): | |
| 47 nodelist = node | |
| 48 else: | |
| 49 nodelist=node.childNodes | |
| 50 | |
| 51 rc = "" | |
| 52 for node in nodelist: | |
| 53 if node.nodeType == node.TEXT_NODE: | |
| 54 rc = rc + node.data | |
| 55 return rc | |
| 56 | |
| 4 | 57 def sqlName(s, lc=True, more=''): |
| 2 | 58 """returns restricted ASCII-only version of string""" |
| 59 if s is None: | |
| 60 return "" | |
| 61 | |
| 62 # all else -> "_" | |
| 4 | 63 s = re.sub('[^A-Za-z0-9_'+more+']','_',s) |
| 2 | 64 if lc: |
| 65 return s.lower() | |
| 66 | |
| 67 return s | |
| 68 | |
| 69 | |
| 3 | 70 class DBInterface: |
| 71 """Object for database queries""" | |
| 72 | |
| 8 | 73 def __init__(self, connection_id=None, autocommit=False): |
| 2 | 74 """init""" |
| 75 # database connection id | |
| 8 | 76 self.connection_id = connection_id |
| 77 self.autocommit = autocommit | |
| 2 | 78 |
| 3 | 79 def getConnectionIDs(self): |
| 80 """return list of available connection ids""" | |
| 81 return SQLConnectionIDs(self) | |
| 82 | |
| 83 def getDB(self): | |
| 84 """returns DB object""" | |
| 85 # TODO: can we cache and reuse a DB object? | |
| 86 if self.connection_id is None: | |
| 87 # try to take the first existing ID | |
| 88 connids = self.getConnectionIDs() | |
| 89 if len(connids) > 0: | |
| 90 connection_id = connids[0][1] | |
| 91 self.connection_id = connection_id | |
| 92 logging.debug("connection_id: %s"%repr(connection_id)) | |
| 93 | |
| 94 # get Connection instance | |
| 95 con = getattr(self, self.connection_id) | |
| 96 # call to get db object | |
| 97 db = con() | |
| 8 | 98 if self.autocommit: |
| 99 # force our transaction isolation level | |
| 100 db.tilevel = psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT | |
| 101 | |
| 3 | 102 return db |
| 103 | |
| 104 def executeZSQL(self, query, args=None, max_rows=None): | |
| 105 """execute query with args on the database and return all results as Result object.""" | |
| 106 logging.debug("executeZSQL query=%s args=%s"%(query,args)) | |
| 107 dbc = self.getDB() | |
| 108 res = dbc.query(query, max_rows=max_rows, query_data=args) | |
| 109 # return result set as Result object with Brains | |
| 110 return Results(res) | |
| 111 | |
| 112 | |
| 113 # | |
| 114 # Old way using cursor from DA | |
| 115 # | |
| 116 | |
| 2 | 117 def getCursor(self,autocommit=True): |
| 118 """returns fresh DB cursor""" | |
| 3 | 119 conn = getattr(self,"_v_database_connection", None) |
| 2 | 120 if conn is None: |
| 121 # create a new connection object | |
| 122 try: | |
| 123 if self.connection_id is None: | |
| 124 # try to take the first existing ID | |
| 3 | 125 connids = self.getConnectionIDs() |
| 2 | 126 if len(connids) > 0: |
| 3 | 127 connection_id = connids[0][1] |
| 2 | 128 self.connection_id = connection_id |
| 129 logging.debug("connection_id: %s"%repr(connection_id)) | |
| 130 | |
| 131 da = getattr(self, self.connection_id) | |
| 3 | 132 logging.debug('da=%s'%da) |
| 2 | 133 da.connect('') |
| 134 # we copy the DAs database connection | |
| 135 conn = da._v_database_connection | |
| 3 | 136 #conn._register() # register with the Zope transaction system(?) |
| 2 | 137 self._v_database_connection = conn |
| 3 | 138 |
| 2 | 139 except Exception, e: |
| 140 raise IOError("No database connection! (%s)"%str(e)) | |
| 141 | |
| 142 cursor = conn.getcursor() | |
| 143 if autocommit: | |
| 3 | 144 # TODO: is there a better version to get to the connection? |
| 2 | 145 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) |
| 146 | |
| 147 return cursor | |
| 3 | 148 |
| 2 | 149 def getFieldNameMap(self,fields): |
| 150 """returns a dict mapping field names to row indexes""" | |
| 151 map = {} | |
| 152 i = 0 | |
| 153 for f in fields: | |
| 154 map[f[0]] = i | |
| 155 i += 1 | |
| 156 | |
| 157 return map | |
| 158 | |
| 159 def executeSQL(self, query, args=None, hasResult=True, autocommit=True): | |
| 160 """execute query with args on database and return all results. | |
| 161 result format: {"fields":fields, "rows":data}""" | |
| 162 logging.debug("executeSQL query=%s args=%s"%(query,args)) | |
| 163 cur = self.getCursor(autocommit=autocommit) | |
| 164 if args is not None: | |
| 165 # make sure args is a list | |
| 166 if isinstance(args,basestring): | |
| 167 args = (args,) | |
| 168 | |
| 169 cur.execute(query, args) | |
| 170 # description of returned fields | |
| 171 fields = cur.description | |
| 172 if hasResult: | |
| 173 # get all data in an array | |
| 174 data = cur.fetchall() | |
| 175 cur.close() | |
| 176 #logging.debug("fields: %s"%repr(fields)) | |
| 177 #logging.debug("rows: %s"%repr(data)) | |
| 178 return {"fields":fields, "rows":data} | |
| 179 else: | |
| 180 cur.close() | |
| 181 return None | |
| 182 |