|
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
|
|
|
15
|
|
|
16
|
|
|
17 def unicodify(s,alternate='latin-1'):
|
|
|
18 """decode str (utf-8 or latin-1 representation) into unicode object"""
|
|
|
19 if not s:
|
|
|
20 return u""
|
|
|
21 if isinstance(s, str):
|
|
|
22 try:
|
|
|
23 return s.decode('utf-8')
|
|
|
24 except:
|
|
|
25 return s.decode(alternate)
|
|
|
26 else:
|
|
|
27 return s
|
|
|
28
|
|
|
29 def utf8ify(s):
|
|
|
30 """encode unicode object or string into byte string in utf-8 representation.
|
|
|
31 assumes string objects to be utf-8"""
|
|
|
32 if not s:
|
|
|
33 return ""
|
|
|
34 if isinstance(s, str):
|
|
|
35 return s
|
|
|
36 else:
|
|
|
37 return s.encode('utf-8')
|
|
|
38
|
|
|
39 def getTextFromNode(node):
|
|
|
40 """get the cdata content of a XML node"""
|
|
|
41 if node is None:
|
|
|
42 return ""
|
|
|
43
|
|
|
44 if isinstance(node, list):
|
|
|
45 nodelist = node
|
|
|
46 else:
|
|
|
47 nodelist=node.childNodes
|
|
|
48
|
|
|
49 rc = ""
|
|
|
50 for node in nodelist:
|
|
|
51 if node.nodeType == node.TEXT_NODE:
|
|
|
52 rc = rc + node.data
|
|
|
53 return rc
|
|
|
54
|
|
|
55 def sqlName(s,lc=True):
|
|
|
56 """returns restricted ASCII-only version of string"""
|
|
|
57 if s is None:
|
|
|
58 return ""
|
|
|
59
|
|
|
60 # all else -> "_"
|
|
|
61 s = re.sub(r'[^A-Za-z0-9_]','_',s)
|
|
|
62 if lc:
|
|
|
63 return s.lower()
|
|
|
64
|
|
|
65 return s
|
|
|
66
|
|
|
67
|
|
|
68 class DbInterface:
|
|
|
69 """Object for database queries
|
|
|
70 """
|
|
|
71 def __init__(self, id, title, connection_id=None):
|
|
|
72 """init"""
|
|
|
73 self.id = id
|
|
|
74 self.title = title
|
|
|
75 # database connection id
|
|
|
76 self.connection_id = connection_id
|
|
|
77
|
|
|
78 def getCursor(self,autocommit=True):
|
|
|
79 """returns fresh DB cursor"""
|
|
|
80 conn = getattr(self,"_v_database_connection",None)
|
|
|
81 if conn is None:
|
|
|
82 # create a new connection object
|
|
|
83 try:
|
|
|
84 if self.connection_id is None:
|
|
|
85 # try to take the first existing ID
|
|
|
86 connids = SQLConnectionIDs(self)
|
|
|
87 if len(connids) > 0:
|
|
|
88 connection_id = connids[0][0]
|
|
|
89 self.connection_id = connection_id
|
|
|
90 logging.debug("connection_id: %s"%repr(connection_id))
|
|
|
91
|
|
|
92 da = getattr(self, self.connection_id)
|
|
|
93 da.connect('')
|
|
|
94 # we copy the DAs database connection
|
|
|
95 conn = da._v_database_connection
|
|
|
96 #conn._register() # register with the Zope transaction system
|
|
|
97 self._v_database_connection = conn
|
|
|
98 except Exception, e:
|
|
|
99 raise IOError("No database connection! (%s)"%str(e))
|
|
|
100
|
|
|
101 cursor = conn.getcursor()
|
|
|
102 if autocommit:
|
|
|
103 # is there a better version to get to the connection?
|
|
|
104 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
|
|
|
105
|
|
|
106 return cursor
|
|
|
107
|
|
|
108 def getFieldNameMap(self,fields):
|
|
|
109 """returns a dict mapping field names to row indexes"""
|
|
|
110 map = {}
|
|
|
111 i = 0
|
|
|
112 for f in fields:
|
|
|
113 map[f[0]] = i
|
|
|
114 i += 1
|
|
|
115
|
|
|
116 return map
|
|
|
117
|
|
|
118 def executeSQL(self, query, args=None, hasResult=True, autocommit=True):
|
|
|
119 """execute query with args on database and return all results.
|
|
|
120 result format: {"fields":fields, "rows":data}"""
|
|
|
121 logging.debug("executeSQL query=%s args=%s"%(query,args))
|
|
|
122 cur = self.getCursor(autocommit=autocommit)
|
|
|
123 if args is not None:
|
|
|
124 # make sure args is a list
|
|
|
125 if isinstance(args,basestring):
|
|
|
126 args = (args,)
|
|
|
127
|
|
|
128 cur.execute(query, args)
|
|
|
129 # description of returned fields
|
|
|
130 fields = cur.description
|
|
|
131 if hasResult:
|
|
|
132 # get all data in an array
|
|
|
133 data = cur.fetchall()
|
|
|
134 cur.close()
|
|
|
135 #logging.debug("fields: %s"%repr(fields))
|
|
|
136 #logging.debug("rows: %s"%repr(data))
|
|
|
137 return {"fields":fields, "rows":data}
|
|
|
138 else:
|
|
|
139 cur.close()
|
|
|
140 return None
|
|
|
141
|
