Mercurial > hg > ZDBInterface

comparison DBInterface.py @ 2:881fcea6a57d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new base class (unused)
author casties
date Mon, 14 Feb 2011 11:11:34 +0100
parents
children d70e57193731
comparison
equal deleted inserted replaced
1:083b771b1ca9 2:881fcea6a57d
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