Mercurial > hg > ZDBInterface
annotate DBInterface.py @ 11:22c16a632909
add single-quote to ignored characters in sqlName.
| author | casties |
|---|---|
| date | Thu, 23 Jun 2011 11:16:19 +0200 |
| parents | 17b19345d011 |
| children | 5b99c04c567c |
| 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 | |
| 11 | 62 # remove ' |
| 63 s = s.replace("'","") | |
| 2 | 64 # all else -> "_" |
| 4 | 65 s = re.sub('[^A-Za-z0-9_'+more+']','_',s) |
| 2 | 66 if lc: |
| 67 return s.lower() | |
| 68 | |
| 69 return s | |
| 70 | |
| 71 | |
| 3 | 72 class DBInterface: |
| 73 """Object for database queries""" | |
| 74 | |
| 8 | 75 def __init__(self, connection_id=None, autocommit=False): |
| 2 | 76 """init""" |
| 77 # database connection id | |
| 8 | 78 self.connection_id = connection_id |
| 79 self.autocommit = autocommit | |
| 2 | 80 |
| 3 | 81 def getConnectionIDs(self): |
| 82 """return list of available connection ids""" | |
| 83 return SQLConnectionIDs(self) | |
| 84 | |
| 85 def getDB(self): | |
| 86 """returns DB object""" | |
| 87 # TODO: can we cache and reuse a DB object? | |
| 88 if self.connection_id is None: | |
| 89 # try to take the first existing ID | |
| 90 connids = self.getConnectionIDs() | |
| 91 if len(connids) > 0: | |
| 92 connection_id = connids[0][1] | |
| 93 self.connection_id = connection_id | |
| 94 logging.debug("connection_id: %s"%repr(connection_id)) | |
| 95 | |
| 96 # get Connection instance | |
| 97 con = getattr(self, self.connection_id) | |
| 98 # call to get db object | |
| 99 db = con() | |
| 8 | 100 if self.autocommit: |
| 101 # force our transaction isolation level | |
| 102 db.tilevel = psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT | |
| 103 | |
| 3 | 104 return db |
| 105 | |
| 106 def executeZSQL(self, query, args=None, max_rows=None): | |
| 107 """execute query with args on the database and return all results as Result object.""" | |
| 108 logging.debug("executeZSQL query=%s args=%s"%(query,args)) | |
| 109 dbc = self.getDB() | |
| 110 res = dbc.query(query, max_rows=max_rows, query_data=args) | |
| 111 # return result set as Result object with Brains | |
| 112 return Results(res) | |
| 113 | |
| 114 | |
| 115 # | |
| 116 # Old way using cursor from DA | |
| 117 # | |
| 118 | |
| 2 | 119 def getCursor(self,autocommit=True): |
| 120 """returns fresh DB cursor""" | |
| 3 | 121 conn = getattr(self,"_v_database_connection", None) |
| 2 | 122 if conn is None: |
| 123 # create a new connection object | |
| 124 try: | |
| 125 if self.connection_id is None: | |
| 126 # try to take the first existing ID | |
| 3 | 127 connids = self.getConnectionIDs() |
| 2 | 128 if len(connids) > 0: |
| 3 | 129 connection_id = connids[0][1] |
| 2 | 130 self.connection_id = connection_id |
| 131 logging.debug("connection_id: %s"%repr(connection_id)) | |
| 132 | |
| 133 da = getattr(self, self.connection_id) | |
| 3 | 134 logging.debug('da=%s'%da) |
| 2 | 135 da.connect('') |
| 136 # we copy the DAs database connection | |
| 137 conn = da._v_database_connection | |
| 3 | 138 #conn._register() # register with the Zope transaction system(?) |
| 2 | 139 self._v_database_connection = conn |
| 3 | 140 |
| 2 | 141 except Exception, e: |
| 142 raise IOError("No database connection! (%s)"%str(e)) | |
| 143 | |
| 144 cursor = conn.getcursor() | |
| 145 if autocommit: | |
| 3 | 146 # TODO: is there a better version to get to the connection? |
| 2 | 147 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) |
| 148 | |
| 149 return cursor | |
| 3 | 150 |
| 2 | 151 def getFieldNameMap(self,fields): |
| 152 """returns a dict mapping field names to row indexes""" | |
| 153 map = {} | |
| 154 i = 0 | |
| 155 for f in fields: | |
| 156 map[f[0]] = i | |
| 157 i += 1 | |
| 158 | |
| 159 return map | |
| 160 | |
| 161 def executeSQL(self, query, args=None, hasResult=True, autocommit=True): | |
| 162 """execute query with args on database and return all results. | |
| 163 result format: {"fields":fields, "rows":data}""" | |
| 164 logging.debug("executeSQL query=%s args=%s"%(query,args)) | |
| 165 cur = self.getCursor(autocommit=autocommit) | |
| 166 if args is not None: | |
| 167 # make sure args is a list | |
| 168 if isinstance(args,basestring): | |
| 169 args = (args,) | |
| 170 | |
| 171 cur.execute(query, args) | |
| 172 # description of returned fields | |
| 173 fields = cur.description | |
| 174 if hasResult: | |
| 175 # get all data in an array | |
| 176 data = cur.fetchall() | |
| 177 cur.close() | |
| 178 #logging.debug("fields: %s"%repr(fields)) | |
| 179 #logging.debug("rows: %s"%repr(data)) | |
| 180 return {"fields":fields, "rows":data} | |
| 181 else: | |
| 182 cur.close() | |
| 183 return None | |
| 184 |