source: IO_handling/trunk/dbCore.py @ 278

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/IO_handling/trunk/dbCore.py@278
Revision 278, 10.9 KB checked in by selatham, 16 years ago (diff)

* empty log message *

  • Property svn:eol-style set to native
  • Property svn:executable set to *
  • Property svn:keywords set to Author Date Id Revision
Line 
1# ------------------------------------------------------------------------
2# dbCore V0.3
3# BNL June 2003
4# Copyleft and no warranty!
5#
6# Other ways of doing this would be to have a dbBase with most of
7# the methods on that, then have the dbspecific modules superclass
8# that and overload their idiosyncracies, and then have dbCore simply
9# grab whichever it wants ... in the long run that might be more
10# efficient that what we've got here ...
11# ------------------------------------------------------------------------
12#
13# public methods on dbCore are identifield below ...
14# note support for joining not included with this version
15
16
17import time, os, string
18
19def list2dict(lista,listb):
20    #there must be a more pythonic way of doing this ...
21    r={}
22    for i in range(len(lista)): r[lista[i]]=listb[i]
23    return r
24
25class dbCore:
26   
27    def __init__(self,usedb='gadfly',**kw):
28        if usedb not in ('gadfly','postgresql','mysql'):
29            raise "DB not supported"
30        self.debug=1
31        self.logfile=None
32        if self.debug:
33            self.logfile=open("sql.log",'a')
34            self.logfile.write("Opening %s db\n"%usedb)
35        self.dbtype=usedb
36        if usedb=='gadfly':
37            adb=GadflyDB(logfile=self.logfile,**kw)
38        elif usedb=='mysql':
39            adb=MysqlDB(**kw)           
40        elif usedb=='postgresql':
41            adb=PostgresDB(**kw)       
42        self.paramstyle=adb.paramstyle
43        self.connect=adb.connect
44        self.cur=self.connect.cursor()
45
46#---------------------------------------------------------------------------
47#
48# Recommended Public Methods follow
49#
50#---------------------------------------------------------------------------
51
52    def sqlMakeTable(self,table,values,types):
53        """ Create a table in the database with a list of values with
54        a corresponding set of types """
55        # we didn't use a dictionary because order matters
56        # note that the table types are a point of massive lack of
57        # portability *between* database implementations at some point
58        # we should put some real checks on allowed types in this method.
59        cmd="create table "+table+" ("
60        for i in range(len(values)):
61            value,type=values[i],types[i]
62            if type=='varchar' and self.dbtype=='mysql': type='varchar(128)'
63            if self.dbtype=='gadfly' and type=='text': type='varchar'
64            cmd='%s%s%s%s%s' % (cmd,value,' ',type,',')
65        cmd=cmd[:-1]+')'
66        try:
67            self._sql(cmd)
68        except:
69            if self.debug:
70                self.logfile.write(
71                    'Error creating table; probably already exists\n')
72                self.logfile.write(
73                    '... or check db implementation supports proposed types\n')
74            raise NameError
75       
76    def sqlKeyGetCol(self,table,column,value,order=None,constraint=None):
77        """ Almost self explanatory: return a list which contains the rows
78        of the table where column = value. Each row is returned as a dictionary
79        item """
80        cmd="select * from "+table+" where "+column+" = "+value
81        if constraint is not None:
82            cmd+=" and "+constraint
83        if order is not None:
84            cmd+=" order by "+order
85        return self._sqlKeyGet(cmd)
86   
87    def sqlKeyGetTab(self,table,constraint=None):
88        """ Return a list of all rows of a table satisfying constraint """
89        cmd="select * from "+table+" "
90        if constraint is not None: cmd+=constraint
91        return self._sqlKeyGet(cmd)
92
93    def sqlInsert(self,table,values,data):
94        """ insert a list of values,data pairs into the table"""
95        self.sqlKeyInsert(table,list2dict(values,data))
96
97    def sqlKeyInsert(self,table,datadict,many=0):
98        """insert one row defined by datadict into table """
99        keys=datadict.keys()
100        if self.paramstyle=='pyformat':
101            data=datadict
102        else:
103            data=tuple(map(lambda x: datadict[x],keys))
104        cmd='insert into %s (%s) values (%s)'%(
105            table,
106            string.join(keys,','),
107            self._sqlparam(keys)
108            )
109        self._sqla(cmd,data)
110        self.sqlSave()
111
112    def sqlMultiInsert(self,table,listofdicts):
113        keys=listofdicts[0].keys
114        if self.paramstyle=='pyformat':
115            data=listofdicts
116        else:
117            data=[]
118            for item in listofdicts:
119                data.append(tuple(map(lambda x: item[x],keys)))
120        cmd='insert into %s (%s) values (%s)'%(
121            table,
122            string.join(keys,','),
123            self._sqlparam(keys)
124            )
125        self._sqlm(cmd,data)
126   
127    def sqlDelete(self,table,column,value):
128        """ Delete entries from table where column has value """
129        print value
130        cmd='delete from %s where %s = %s'%(
131            table,column,self._sqlparam([column]))
132        if self.paramstyle=='pyformat':
133            self._sqla(cmd,{column:value})
134        else: self._sqla(cmd,(value,))
135   
136    def sqlSave(self):
137        """Used to confirm changes to the DB, note that an insert does this
138        by default as well """
139        self.connect.commit()   
140
141    def sqlDropTable(self,table):
142        """blow away an existing table"""
143        try:
144            self._sql("drop table %s"%table)
145        except NameError:
146            if self.debug:self.logfile.write("Table already non-existent\n")
147            raise NameError       
148       
149#-------------------------------------------------------------------------
150#
151# It is recommended that user mode code doesn't use the following methods
152#
153#-------------------------------------------------------------------------
154
155    def _sql(self,argument):
156        if self.debug:
157            self.logfile.write('%s%s\n'%('sql arg:',argument))
158        self.cur.execute(argument)
159       
160    def _sqla(self,argument,param):
161        if self.debug:
162            self.logfile.write('%s%s,%s\n'%('sqla arg:',argument,param))
163        self.cur.execute(argument,param)
164
165    def _sqlm(self,argument,param):
166        if self.debug:
167            self.logfile.write('%s%s\n'%('sql many ',argument))
168        self.cur.executemany(argument,param)
169
170    def _sqlparam(self,args):
171        """ isolate the argument passing piece of code, make sure
172        that args is a list, even if you only have one member in it"""
173        if self.paramstyle=='pyformat':
174                arg=map( lambda x: '%s%s%s'%('%(',x,')s'),args)
175        else:
176            dbi={'qmark':'?','format':'%s'}[self.paramstyle]
177            arg=map( lambda x: dbi,args)
178        return  ','.join(arg)
179
180    def _sqldoandget(self,argument):
181        try:
182            self._sql(argument)
183            return self._sqlgetall()
184        except NameError:
185            raise NameError
186            return []
187
188    def _sqlgetone(self):
189        return self.cur.fetchone()
190
191    def _sqlgetall(self):
192        return self.cur.fetchall()
193       
194    def _sqlgetcol(self,table,column):
195        cmd="select "+column+" from "+table
196        result=[]
197        try:
198            stuff=self._sqldoandget(cmd)
199            for entry in stuff: result.append(entry[0])
200        except NameError:
201            pass 
202        return result
203               
204    def _sqlshape(self):
205        x=self.cur.description
206        headings=[]
207        for i in range(len(x)): headings.append(string.lower(x[i][0]))
208        return headings
209
210    def _sqlKeyGet(self,cmd):
211        try:
212            entries=self._sqldoandget(cmd)
213            headings=self._sqlshape()
214            ind=range(len(headings))
215        except NameError:
216            entries=[]
217        results=[]
218        for entry in entries:
219           result={}
220           for i in ind: result[headings[i]]=entry[i]
221           results.append(result)
222        return results
223#
224# -----------------------------------------------------------------------
225# db specific setup follows
226# -----------------------------------------------------------------------
227#
228class GadflyDB:
229   
230    """ Connect to a local gadfly database, or create it if necessary """
231    def __init__(self,dbname,dbdir,logfile=None):
232        import gadfly
233        dirsep=os.sep
234        path=os.path.join(dbdir,dbname+'.gfd')
235        if dbdir[0:1] != dirsep:
236            path=os.getcwd()+dirsep+path
237            if logfile!=None:
238                logfile.write(path+'\n')
239                logfile.write('%s%s'% (os.path.exists(path),'\n'))
240        if os.path.exists(path):
241            self.connect=gadfly.gadfly(dbname,dbdir)
242        else:
243            self.connect=gadfly.gadfly()
244            self.connect.startup(dbname,dbdir)
245        self.paramstyle='qmark'
246
247class MysqlDB:
248    """ Connnect to an existing mysql database server """
249    def __init__(self,**kw):
250        import MySQLdb
251        self.connect=MySQLdb.connect(**kw)
252        self.paramstyle=MySQLdb.paramstyle
253
254class PostgresDB:
255    """ not yet implemented """
256    def __init__(self,**kw):
257        import pgdb
258        self.connect=pgdb.connect(**kw)
259        self.paramstyle=pgdb.paramstyle
260#
261# ----------------------------------------------------------------------
262#   
263if __name__=="__main__":
264    # a bunch of tests and examples
265    #usedb='postgresql'
266    usedb='mysql'
267    #usedb='gadfly'
268    if usedb=='gadfly':
269        x=dbCore(dbname="testdb",dbdir="testdir",usedb=usedb)
270    elif usedb=='mysql':
271        x=dbCore(host="ssdwbnl.badc.rl.ac.uk",
272                 db="grapetest",usedb=usedb,user='grape',passwd='graper')
273    elif usedb=='postgresql':
274        x=dbCore(host="ssdwbnl.badc.rl.ac.uk",
275             user="grape",database='grape',password='graper',
276             usedb=usedb)
277    vars=("a","b","c","d","e","f")
278    types=("varchar","varchar","integer","varchar","varchar","varchar")
279    #following three statements may be needed if things go wrong ...
280    #x.sqlDropTable("tabtest")
281    #do the following twice to test the exceptions
282    #x.sqlMakeTable("tabtest",vars,types)
283    try:
284        x.sqlMakeTable("tabtest",vars,types)
285    except NameError:
286        print "Table probably already exists"
287    #dummy data
288    y1,y2,y3,y4,y5,y6="Bryan's test","standard",5,"c4","c5","c6"
289    #biff it in
290    x.sqlInsert("tabtest",vars,(y1,y2,y3,y4,y5,y6))
291    y3=15
292    x.sqlInsert("tabtest",vars,(y1,y2,y3,y4,y5,y6))
293    #biff another one in
294    y2='diff"erent'
295    x.sqlInsert("tabtest",vars,(y1,y2,y3,y4,y5,y6))
296    y2='keyed'
297    x.sqlKeyInsert("tabtest",list2dict(vars,(y1,y2,y3,y4,y5,y6)))
298    print "All:"
299    z=x.sqlKeyGetTab("tabtest")
300    #nb these Key things return a list of dictionaries
301    for i in z: print i
302    print '---'
303    #get one back
304    z=x.sqlKeyGetCol("tabtest","b","'"+y2+"'")
305    for i in z: print i
306    print '---'
307    z=x.sqlKeyGetCol("tabtest","f","'c6'",
308                     constraint="c < 10 and d='c4' and e='c5'")
309    for i in z: print i
310    print '---'
311    #remove one
312    x.sqlDelete("tabtest","b","keyed")
313    #look at the lot again:
314    z=x.sqlKeyGetTab("tabtest")
315    print "All:"
316    for i in z: print i
317    #test delete with a funny character
318    x.sqlDelete("tabtest","a",y1)
319    #clean it up for a restart
320    x.sqlDropTable("tabtest")
321    x.sqlSave()
322
323
324
325
326       
327       
328       
Note: See TracBrowser for help on using the repository browser.