[79] | 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 | # |
---|
[229] | 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 | # |
---|
[79] | 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() |
---|