Mercurial > hg > ZDBInterface
annotate DBInterface.py @ 19:132ae1c0255a
V1.7: new connection caching. some cleanup.
| author | casties |
|---|---|
| date | Fri, 11 Jan 2013 17:58:56 +0100 |
| parents | 60fea3a6c695 |
| children | 5f3d6623b71e |
| 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 |
| 4 | 18 def sqlName(s, lc=True, more=''): |
| 2 | 19 """returns restricted ASCII-only version of string""" |
| 20 if s is None: | |
| 21 return "" | |
| 22 | |
| 11 | 23 # remove ' |
| 24 s = s.replace("'","") | |
| 2 | 25 # all else -> "_" |
| 4 | 26 s = re.sub('[^A-Za-z0-9_'+more+']','_',s) |
| 2 | 27 if lc: |
| 28 return s.lower() | |
| 29 | |
| 30 return s | |
| 31 | |
| 32 | |
| 3 | 33 class DBInterface: |
| 34 """Object for database queries""" | |
| 35 | |
| 8 | 36 def __init__(self, connection_id=None, autocommit=False): |
| 2 | 37 """init""" |
| 38 # database connection id | |
| 8 | 39 self.connection_id = connection_id |
| 40 self.autocommit = autocommit | |
| 2 | 41 |
| 3 | 42 def getConnectionIDs(self): |
| 43 """return list of available connection ids""" | |
| 44 return SQLConnectionIDs(self) | |
| 45 | |
| 46 def getDB(self): | |
| 47 """returns DB object""" | |
| 19 | 48 db = None |
| 49 | |
| 50 # connection caching according to http://pypi.python.org/pypi/alm.solrindex | |
| 51 jar = self._p_jar | |
| 52 oid = self._p_oid | |
| 53 fc = None | |
| 54 if jar is not None and oid is not None: | |
| 55 fc = getattr(jar, 'foreign_connections', None) | |
| 56 if fc is None: | |
| 57 jar.foreign_connections = fc = {} | |
| 58 | |
| 59 db = fc.get(oid, None) | |
| 60 if db is not None: | |
| 61 logging.debug("getDb: using cached db=%s"%repr(db)) | |
| 62 return db | |
| 63 | |
| 3 | 64 if self.connection_id is None: |
| 65 # try to take the first existing ID | |
| 66 connids = self.getConnectionIDs() | |
| 67 if len(connids) > 0: | |
| 68 connection_id = connids[0][1] | |
| 69 self.connection_id = connection_id | |
| 70 logging.debug("connection_id: %s"%repr(connection_id)) | |
| 71 | |
| 19 | 72 # get Connection/DA instance |
| 3 | 73 con = getattr(self, self.connection_id) |
| 74 # call to get db object | |
| 75 db = con() | |
| 8 | 76 if self.autocommit: |
| 77 # force our transaction isolation level | |
| 78 db.tilevel = psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT | |
| 79 | |
| 19 | 80 if fc is not None and oid is not None: |
| 81 # cache db | |
| 82 fc[oid] = db | |
| 83 | |
| 3 | 84 return db |
| 85 | |
| 86 def executeZSQL(self, query, args=None, max_rows=None): | |
| 87 """execute query with args on the database and return all results as Result object.""" | |
| 88 logging.debug("executeZSQL query=%s args=%s"%(query,args)) | |
| 89 dbc = self.getDB() | |
| 90 res = dbc.query(query, max_rows=max_rows, query_data=args) | |
| 91 # return result set as Result object with Brains | |
| 92 return Results(res) | |
| 93 | |
| 94 | |
| 95 # | |
| 96 # Old way using cursor from DA | |
| 97 # | |
| 98 | |
| 2 | 99 def getCursor(self,autocommit=True): |
| 100 """returns fresh DB cursor""" | |
| 3 | 101 conn = getattr(self,"_v_database_connection", None) |
| 2 | 102 if conn is None: |
| 103 # create a new connection object | |
| 104 try: | |
| 105 if self.connection_id is None: | |
| 106 # try to take the first existing ID | |
| 3 | 107 connids = self.getConnectionIDs() |
| 2 | 108 if len(connids) > 0: |
| 3 | 109 connection_id = connids[0][1] |
| 2 | 110 self.connection_id = connection_id |
| 111 logging.debug("connection_id: %s"%repr(connection_id)) | |
| 112 | |
| 113 da = getattr(self, self.connection_id) | |
| 3 | 114 logging.debug('da=%s'%da) |
| 2 | 115 da.connect('') |
| 116 # we copy the DAs database connection | |
| 117 conn = da._v_database_connection | |
| 3 | 118 #conn._register() # register with the Zope transaction system(?) |
| 2 | 119 self._v_database_connection = conn |
| 3 | 120 |
| 2 | 121 except Exception, e: |
| 122 raise IOError("No database connection! (%s)"%str(e)) | |
| 123 | |
| 124 cursor = conn.getcursor() | |
| 125 if autocommit: | |
| 3 | 126 # TODO: is there a better version to get to the connection? |
| 2 | 127 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) |
| 128 | |
| 129 return cursor | |
| 3 | 130 |
| 2 | 131 def getFieldNameMap(self,fields): |
| 132 """returns a dict mapping field names to row indexes""" | |
| 133 map = {} | |
| 134 i = 0 | |
| 135 for f in fields: | |
| 136 map[f[0]] = i | |
| 137 i += 1 | |
| 138 | |
| 139 return map | |
| 140 | |
| 141 def executeSQL(self, query, args=None, hasResult=True, autocommit=True): | |
| 142 """execute query with args on database and return all results. | |
| 143 result format: {"fields":fields, "rows":data}""" | |
| 144 logging.debug("executeSQL query=%s args=%s"%(query,args)) | |
| 145 cur = self.getCursor(autocommit=autocommit) | |
| 146 if args is not None: | |
| 147 # make sure args is a list | |
| 148 if isinstance(args,basestring): | |
| 149 args = (args,) | |
| 150 | |
| 151 cur.execute(query, args) | |
| 152 # description of returned fields | |
| 153 fields = cur.description | |
|
18
60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
casties
parents:
16
diff
changeset
|
154 #logging.debug("fields: %s"%repr(fields)) |
| 16 | 155 if len(fields) > 0: |
| 156 # re-pack Column object in tuple | |
|
18
60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
casties
parents:
16
diff
changeset
|
157 fs = [] |
|
60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
casties
parents:
16
diff
changeset
|
158 for f in fields: |
|
60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
casties
parents:
16
diff
changeset
|
159 fs.append(f[0:]) |
|
60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
casties
parents:
16
diff
changeset
|
160 |
|
60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
casties
parents:
16
diff
changeset
|
161 fields = fs |
|
60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
casties
parents:
16
diff
changeset
|
162 #logging.debug("re-packed fields: %s"%repr(fields)) |
| 16 | 163 |
| 2 | 164 if hasResult: |
| 165 # get all data in an array | |
| 166 data = cur.fetchall() | |
| 167 cur.close() | |
| 168 #logging.debug("fields: %s"%repr(fields)) | |
| 169 #logging.debug("rows: %s"%repr(data)) | |
| 170 return {"fields":fields, "rows":data} | |
| 171 else: | |
| 172 cur.close() | |
| 173 return None | |
| 174 |