1 | #!/usr/bin/env python |
---|
2 | # |
---|
3 | # Orginal from Andre van der Vlies <andre@vandervlies.xs4all.nl> for MySQL. Changed |
---|
4 | # and added some more functions for postgres. |
---|
5 | # |
---|
6 | # |
---|
7 | # Changed by: Bas van der Vlies <basv@sara.nl> |
---|
8 | # |
---|
9 | # SARA API for Postgres Database |
---|
10 | # |
---|
11 | # Changed by: Ramon Bastiaans for Job Monarch |
---|
12 | # |
---|
13 | # |
---|
14 | # This file is part of Jobmonarch |
---|
15 | # |
---|
16 | # Copyright (C) 2006 Ramon Bastiaans |
---|
17 | # |
---|
18 | # Jobmonarch is free software; you can redistribute it and/or modify |
---|
19 | # it under the terms of the GNU General Public License as published by |
---|
20 | # the Free Software Foundation; either version 2 of the License, or |
---|
21 | # (at your option) any later version. |
---|
22 | # |
---|
23 | # Jobmonarch is distributed in the hope that it will be useful, |
---|
24 | # but WITHOUT ANY WARRANTY; without even the implied warranty of |
---|
25 | # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
---|
26 | # GNU General Public License for more details. |
---|
27 | # |
---|
28 | # You should have received a copy of the GNU General Public License |
---|
29 | # along with this program; if not, write to the Free Software |
---|
30 | # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA |
---|
31 | # |
---|
32 | # SVN $Id: DBClass.py 229 2006-03-10 16:29:56Z bastiaans $ |
---|
33 | # |
---|
34 | """ |
---|
35 | This is an generic SARA python class that connects to any postgres database. |
---|
36 | THe default one is uva_cluster_db, but that can specified as argument. |
---|
37 | |
---|
38 | The basic usage is: |
---|
39 | import DBClass |
---|
40 | |
---|
41 | db_vars = DBClass.InitVars(DataBaseName='rc_cluster_db', |
---|
42 | User='root', |
---|
43 | Host='localhost', |
---|
44 | Password='', |
---|
45 | Dictionary='true') |
---|
46 | db = DBClass.DB(db_vars) |
---|
47 | |
---|
48 | # Get valuese from database |
---|
49 | print db.Get("SELECT * FROM clusters") |
---|
50 | |
---|
51 | # Set values into database |
---|
52 | bla=db.Set("DELETE FROM clusters WHERE clustername = 'bas'") |
---|
53 | """ |
---|
54 | from pyPgSQL import PgSQL |
---|
55 | |
---|
56 | # |
---|
57 | # Class to 'simplify' the declaration of 'global' |
---|
58 | # variables.... |
---|
59 | # |
---|
60 | class InitVars: |
---|
61 | Vars = {} |
---|
62 | |
---|
63 | def __init__(self, **key_arg): |
---|
64 | for (key, value) in key_arg.items(): |
---|
65 | if value: |
---|
66 | self.Vars[key] = value |
---|
67 | else: |
---|
68 | self.Vars[key] = None |
---|
69 | |
---|
70 | def __call__(self, *key): |
---|
71 | key = "%s" % key |
---|
72 | return self.Vars[key] |
---|
73 | |
---|
74 | def __getitem__(self, key): |
---|
75 | return self.Vars[key] |
---|
76 | |
---|
77 | def __repr__(self): |
---|
78 | return repr(self.Vars) |
---|
79 | |
---|
80 | def keys(self): |
---|
81 | barf = map(None, self.Vars.keys()) |
---|
82 | return barf |
---|
83 | |
---|
84 | def values(self): |
---|
85 | barf = map(None, self.Vars.values()) |
---|
86 | return barf |
---|
87 | |
---|
88 | def has_key(self, key): |
---|
89 | if self.Vars.has_key(key): |
---|
90 | return 1 |
---|
91 | else: |
---|
92 | return 0 |
---|
93 | |
---|
94 | class DBError(Exception): |
---|
95 | def __init__(self, msg=''): |
---|
96 | self.msg = msg |
---|
97 | Exception.__init__(self, msg) |
---|
98 | def __repr__(self): |
---|
99 | return self.msg |
---|
100 | __str__ = __repr__ |
---|
101 | |
---|
102 | # |
---|
103 | # Class to connect to a database |
---|
104 | # and return the queury in a list or dictionairy. |
---|
105 | # |
---|
106 | class DB: |
---|
107 | def __init__(self, db_vars): |
---|
108 | |
---|
109 | self.dict = db_vars |
---|
110 | |
---|
111 | if self.dict.has_key('User'): |
---|
112 | self.user = self.dict['User'] |
---|
113 | else: |
---|
114 | self.user = 'postgres' |
---|
115 | |
---|
116 | if self.dict.has_key('Host'): |
---|
117 | self.host = self.dict['Host'] |
---|
118 | else: |
---|
119 | self.host = 'localhost' |
---|
120 | |
---|
121 | if self.dict.has_key('Password'): |
---|
122 | self.passwd = self.dict['Password'] |
---|
123 | else: |
---|
124 | self.passwd = '' |
---|
125 | |
---|
126 | if self.dict.has_key('DataBaseName'): |
---|
127 | self.db = self.dict['DataBaseName'] |
---|
128 | else: |
---|
129 | self.db = 'uva_cluster_db' |
---|
130 | |
---|
131 | # connect_string = 'host:port:database:user:password: |
---|
132 | dsn = "%s::%s:%s:%s" %(self.host, self.db, self.user, self.passwd) |
---|
133 | |
---|
134 | try: |
---|
135 | self.SQL = PgSQL.connect(dsn) |
---|
136 | except PgSQL.Error, details: |
---|
137 | str = "%s" %details |
---|
138 | raise DBError(str) |
---|
139 | |
---|
140 | def __repr__(self): |
---|
141 | return repr(self.result) |
---|
142 | |
---|
143 | def __nonzero__(self): |
---|
144 | return not(self.result == None) |
---|
145 | |
---|
146 | def __len__(self): |
---|
147 | return len(self.result) |
---|
148 | |
---|
149 | def __getitem__(self,i): |
---|
150 | return self.result[i] |
---|
151 | |
---|
152 | def __getslice__(self,i,j): |
---|
153 | return self.result[i:j] |
---|
154 | |
---|
155 | def Get(self, q_str): |
---|
156 | c = self.SQL.cursor() |
---|
157 | try: |
---|
158 | c.execute(q_str) |
---|
159 | result = c.fetchall() |
---|
160 | except PgSQL.Error, details: |
---|
161 | c.close() |
---|
162 | str = "%s" %details |
---|
163 | raise DBError(str) |
---|
164 | |
---|
165 | c.close() |
---|
166 | return result |
---|
167 | |
---|
168 | def Set(self, q_str): |
---|
169 | c = self.SQL.cursor() |
---|
170 | try: |
---|
171 | c.execute(q_str) |
---|
172 | result = c.oidValue |
---|
173 | |
---|
174 | except PgSQL.Error, details: |
---|
175 | c.close() |
---|
176 | str = "%s" %details |
---|
177 | raise DBError(str) |
---|
178 | |
---|
179 | c.close() |
---|
180 | return result |
---|
181 | |
---|
182 | def Commit(self): |
---|
183 | self.SQL.commit() |
---|
184 | |
---|
185 | # |
---|
186 | # Some tests.... |
---|
187 | # |
---|
188 | def main(): |
---|
189 | db_vars = InitVars(DataBaseName='rc_cluster_db', |
---|
190 | User='root', |
---|
191 | Host='localhost', |
---|
192 | Password='', |
---|
193 | Dictionary='true') |
---|
194 | print db_vars; |
---|
195 | |
---|
196 | db = DB(db_vars) |
---|
197 | print db.Get("""SELECT * FROM clusters""") |
---|
198 | |
---|
199 | if __name__ == '__main__': |
---|
200 | main() |
---|