Mercurial > hg > ZDBInterface

comparison DBInterface.py @ 3:d70e57193731

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new executeZSQL method that returns Zope Results. new ZDBInterfaceFolder that doesn't do much yet.
author casties
date Mon, 14 Feb 2011 23:20:43 +0100
parents 881fcea6a57d
children 0ade331198de
comparison
equal deleted inserted replaced
2:881fcea6a57d 3:d70e57193731
10 import psycopg2.extensions 10 import psycopg2.extensions
11 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE) 11 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
12 psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY) 12 psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
13 13
14 from Products.ZSQLMethods.SQL import SQLConnectionIDs 14 from Products.ZSQLMethods.SQL import SQLConnectionIDs
15 from Shared.DC.ZRDB.Results import Results
15 16
16 17
17 def unicodify(s,alternate='latin-1'): 18 def unicodify(s,alternate='latin-1'):
18 """decode str (utf-8 or latin-1 representation) into unicode object""" 19 """decode str (utf-8 or latin-1 representation) into unicode object"""
19 if not s: 20 if not s:
63 return s.lower() 64 return s.lower()
64 65
65 return s 66 return s
66 67
67 68
68 class DbInterface: 69 class DBInterface:
69 """Object for database queries 70 """Object for database queries"""
70 """ 71
71 def __init__(self, id, title, connection_id=None): 72 def __init__(self, connection_id=None):
72 """init""" 73 """init"""
73 self.id = id
74 self.title = title
75 # database connection id 74 # database connection id
76 self.connection_id = connection_id 75 self.connection_id = connection_id
77 76
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
78 def getCursor(self,autocommit=True): 111 def getCursor(self,autocommit=True):
79 """returns fresh DB cursor""" 112 """returns fresh DB cursor"""
80 conn = getattr(self,"_v_database_connection",None) 113 conn = getattr(self,"_v_database_connection", None)
81 if conn is None: 114 if conn is None:
82 # create a new connection object 115 # create a new connection object
83 try: 116 try:
84 if self.connection_id is None: 117 if self.connection_id is None:
85 # try to take the first existing ID 118 # try to take the first existing ID
86 connids = SQLConnectionIDs(self) 119 connids = self.getConnectionIDs()
87 if len(connids) > 0: 120 if len(connids) > 0:
88 connection_id = connids[0][0] 121 connection_id = connids[0][1]
89 self.connection_id = connection_id 122 self.connection_id = connection_id
90 logging.debug("connection_id: %s"%repr(connection_id)) 123 logging.debug("connection_id: %s"%repr(connection_id))
91 124
92 da = getattr(self, self.connection_id) 125 da = getattr(self, self.connection_id)
126 logging.debug('da=%s'%da)
93 da.connect('') 127 da.connect('')
94 # we copy the DAs database connection 128 # we copy the DAs database connection
95 conn = da._v_database_connection 129 conn = da._v_database_connection
96 #conn._register() # register with the Zope transaction system 130 #conn._register() # register with the Zope transaction system(?)
97 self._v_database_connection = conn 131 self._v_database_connection = conn
132
98 except Exception, e: 133 except Exception, e:
99 raise IOError("No database connection! (%s)"%str(e)) 134 raise IOError("No database connection! (%s)"%str(e))
100 135
101 cursor = conn.getcursor() 136 cursor = conn.getcursor()
102 if autocommit: 137 if autocommit:
103 # is there a better version to get to the connection? 138 # TODO: is there a better version to get to the connection?
104 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) 139 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
105 140
106 return cursor 141 return cursor
107 142
108 def getFieldNameMap(self,fields): 143 def getFieldNameMap(self,fields):
109 """returns a dict mapping field names to row indexes""" 144 """returns a dict mapping field names to row indexes"""
110 map = {} 145 map = {}
111 i = 0 146 i = 0
112 for f in fields: 147 for f in fields: