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