Mercurial > pylearn
annotate pylearn/dbdict/sql.py @ 622:d2d582bcf7dc
api0 and sql seem to work with session closing
| author | James Bergstra <bergstrj@iro.umontreal.ca> |
|---|---|
| date | Sun, 18 Jan 2009 21:39:28 -0500 |
| parents | 7041749cf804 |
| children | ae954c27fd11 |
| rev | line source |
|---|---|
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
1 |
|
612
5a7927691d2c
- Account for exception name change in sqlalchemy 0.5.0
lamblinp@ip03.m
parents:
589
diff
changeset
|
2 import sys, os, copy, time |
|
5a7927691d2c
- Account for exception name change in sqlalchemy 0.5.0
lamblinp@ip03.m
parents:
589
diff
changeset
|
3 |
|
5a7927691d2c
- Account for exception name change in sqlalchemy 0.5.0
lamblinp@ip03.m
parents:
589
diff
changeset
|
4 import numpy.random |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
5 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
6 import sqlalchemy |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
7 import sqlalchemy.pool |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
8 from sqlalchemy import create_engine, desc |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
9 from sqlalchemy.orm import eagerload |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
10 import psycopg2, psycopg2.extensions |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
11 |
| 589 | 12 from api0 import db_from_engine, postgres_db, DbHandle |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
13 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
14 |
| 589 | 15 EXPERIMENT = 'dbdict.experiment' |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
16 #using the dictionary to store these is too slow |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
17 STATUS = 'dbdict.status' |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
18 PRIORITY = 'dbdict.sql.priority' |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
19 |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
20 HOST = 'dbdict.sql.hostname' |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
21 HOST_WORKDIR = 'dbdict.sql.host_workdir' |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
22 PUSH_ERROR = 'dbdict.sql.push_error' |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
23 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
24 START = 0 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
25 RUNNING = 1 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
26 DONE = 2 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
27 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
28 _TEST_CONCURRENCY = False |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
29 |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
30 def postgres_serial(user, password, host, database, poolclass=sqlalchemy.pool.NullPool, **kwargs): |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
31 """Return a DbHandle instance that communicates with a postgres database at transaction |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
32 isolation_level 'SERIALIZABLE'. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
33 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
34 :param user: a username in the database |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
35 :param password: the password for the username |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
36 :param host: the network address of the host on which the postgres server is running |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
37 :param database: a database served by the postgres server |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
38 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
39 """ |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
40 this = postgres_serial |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
41 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
42 if not hasattr(this,'engine'): |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
43 def connect(): |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
44 c = psycopg2.connect(user=user, password=password, database=database, host=host) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
45 c.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
46 return c |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
47 this.engine = create_engine('postgres://' |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
48 ,creator=connect |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
49 ,poolclass=poolclass |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
50 ) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
51 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
52 db = db_from_engine(this.engine, **kwargs) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
53 db._is_serialized_session_db = True |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
54 return db |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
55 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
56 def book_dct_postgres_serial(db, retry_max_sleep=10.0, verbose=0): |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
57 """Find a trial in the lisa_db with status START. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
58 |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
59 A trial will be returned with status=RUNNING. |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
60 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
61 Returns None if no such trial exists in DB. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
62 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
63 This function uses a serial access to the lisadb to guarantee that no other |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
64 process will retrieve the same dct. It is designed to facilitate writing |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
65 a "consumer" for a Producer-Consumer pattern based on the database. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
66 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
67 """ |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
68 print >> sys.stderr, """#TODO: use the priority field, not the status.""" |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
69 print >> sys.stderr, """#TODO: ignore entries with key PUSH_ERROR.""" |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
70 |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
71 s = db.session() #open a new session |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
72 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
73 # NB. we need the query and attribute update to be in the same transaction |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
74 assert s.autocommit == False |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
75 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
76 dcts_seen = set([]) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
77 keep_trying = True |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
78 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
79 dct = None |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
80 while (dct is None) and keep_trying: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
81 #build a query |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
82 q = s.query(db._Dict) |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
83 #q = q.options(eagerload('_attrs')) #hard-coded in api0 |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
84 |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
85 #N.B. |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
86 # use dedicated column to retrieve jobs, not the dictionary keyval pair |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
87 # This should be much faster. |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
88 q = q.filter(db._Dict.status==START) |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
89 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
90 #try to reserve a dct |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
91 try: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
92 #first() may raise psycopg2.ProgrammingError |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
93 dct = q.first() |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
94 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
95 if dct is not None: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
96 assert (dct not in dcts_seen) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
97 if verbose: print 'book_unstarted_dct retrieved, ', dct |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
98 dct._set_in_session(STATUS, RUNNING, s) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
99 if 1: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
100 if _TEST_CONCURRENCY: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
101 print >> sys.stderr, 'SLEEPING BEFORE BOOKING' |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
102 time.sleep(10) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
103 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
104 #commit() may raise psycopg2.ProgrammingError |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
105 s.commit() |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
106 else: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
107 print >> sys.stderr, 'DEBUG MODE: NOT RESERVING JOB!', dct |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
108 #if we get this far, the job is ours! |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
109 else: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
110 # no jobs are left |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
111 keep_trying = False |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
112 except (psycopg2.OperationalError, |
|
612
5a7927691d2c
- Account for exception name change in sqlalchemy 0.5.0
lamblinp@ip03.m
parents:
589
diff
changeset
|
113 sqlalchemy.exceptions.ProgrammingError, |
|
5a7927691d2c
- Account for exception name change in sqlalchemy 0.5.0
lamblinp@ip03.m
parents:
589
diff
changeset
|
114 sqlalchemy.exc.DBAPIError), e: |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
115 #either the first() or the commit() raised |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
116 s.rollback() # docs say to do this (or close) after commit raises exception |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
117 if verbose: print 'caught exception', e |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
118 if dct: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
119 # first() succeeded, commit() failed |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
120 dcts_seen.add(dct) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
121 dct = None |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
122 wait = numpy.random.rand(1)*retry_max_sleep |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
123 if verbose: print 'another process stole our dct. Waiting %f secs' % wait |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
124 time.sleep(wait) |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
125 s.close() |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
126 return dct |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
127 |
|
620
04752b23da8d
changed sql to use filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
128 def book_dct_non_postgres(db): |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
129 print >> sys.stderr, """#TODO: use the priority field, not the status.""" |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
130 print >> sys.stderr, """#TODO: ignore entries with key self.push_error.""" |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
131 |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
132 raise NotImplementedError() |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
133 |
| 589 | 134 |
| 135 ########### | |
| 136 # Connect | |
| 137 ########### | |
| 138 | |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
139 def parse_dbstring(dbstring): |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
140 postgres = 'postgres://' |
| 589 | 141 if not dbstring.startswith(postgres): |
| 142 raise ValueError('For now, dbdict dbstrings must start with postgres://', dbstring) | |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
143 dbstring = dbstring[len(postgres):] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
144 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
145 #username_and_password |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
146 colon_pos = dbstring.find('@') |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
147 username_and_password = dbstring[:colon_pos] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
148 dbstring = dbstring[colon_pos+1:] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
149 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
150 colon_pos = username_and_password.find(':') |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
151 if -1 == colon_pos: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
152 username = username_and_password |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
153 password = None |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
154 else: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
155 username = username_and_password[:colon_pos] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
156 password = username_and_password[colon_pos+1:] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
157 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
158 #hostname |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
159 colon_pos = dbstring.find('/') |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
160 hostname = dbstring[:colon_pos] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
161 dbstring = dbstring[colon_pos+1:] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
162 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
163 #dbname |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
164 colon_pos = dbstring.find('/') |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
165 dbname = dbstring[:colon_pos] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
166 dbstring = dbstring[colon_pos+1:] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
167 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
168 #tablename |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
169 tablename = dbstring |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
170 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
171 if password is None: |
| 589 | 172 password = get_password(hostname, dbname) |
| 173 | |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
174 if False: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
175 print 'USERNAME', username |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
176 print 'PASS', password |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
177 print 'HOST', hostname |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
178 print 'DB', dbname |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
179 print 'TABLE', tablename |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
180 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
181 return username, password, hostname, dbname, tablename |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
182 |
| 589 | 183 def get_password(hostname, dbname): |
| 184 """Return the current user's password for a given database | |
| 185 | |
| 186 :TODO: Replace this mechanism with a section in the pylearn configuration file | |
| 187 """ | |
| 188 password_path = os.getenv('HOME')+'/.dbdict_%s'%dbname | |
| 189 try: | |
| 190 password = open(password_path).readline()[:-1] #cut the trailing newline | |
| 191 except: | |
| 192 raise ValueError( 'Failed to read password for db "%s" from %s' % (dbname, password_path)) | |
| 193 return password | |
| 194 | |
| 195 def db(dbstring): | |
| 196 username, password, hostname, dbname, tablename = parse_dbstring(dbstring) | |
| 197 try: | |
| 198 return postgres_db(username, password, hostname, dbname, table_prefix=tablename) | |
| 199 except: | |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
200 print 'Error connecting with password', password |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
612
diff
changeset
|
201 raise |
| 589 | 202 |
| 203 | |
| 204 ########### | |
| 205 # Queue | |
| 206 ########### | |
| 207 | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
208 def insert_dict(jobdict, db, force_dup=False, session=None): |
| 589 | 209 """Insert a new `job` dictionary into database `db`. |
| 210 | |
| 211 :param force_dup: forces insertion even if an identical dictionary is already in the db | |
| 212 | |
| 213 """ | |
| 214 job = copy.copy(jobdict) | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
215 if session is None: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
216 s = db.session() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
217 do_insert = force_dup or (None is db.query(s).filter_eq_dct(job).first()) |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
218 s.close() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
219 else: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
220 do_insert = force_dup or (None is db.query(session).filter_eq_dct(job).first()) |
| 589 | 221 if do_insert: |
| 222 job[STATUS] = START | |
| 223 job[PRIORITY] = 1.0 | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
224 return db.insert(job, session=session) |
| 589 | 225 else: |
| 226 return None | |
| 227 | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
228 def insert_job(experiment_fn, state, db, force_dup=False, session=None): |
| 589 | 229 state = copy.copy(state) |
| 230 state[EXPERIMENT] = experiment_fn.__module__ + '.' + experiment_fn.__name__ | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
231 return insert_dict(state, db, force_dup=force_dup, session=session) |
| 589 | 232 |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
233 |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
234 def add_experiments_to_db(jobs, db, verbose=0, add_dups=False, type_check=None, session=None): |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
235 """Add experiments paramatrized by jobs[i] to database db. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
236 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
237 Default behaviour is to ignore jobs which are already in the database. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
238 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
239 If type_check is a class (instead of None) then it will be used as a type declaration for |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
240 all the elements in each job dictionary. For each key,value pair in the dictionary, there |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
241 must exist an attribute,value pair in the class meeting the following criteria: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
242 the attribute and the key are equal, and the types of the values are equal. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
243 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
244 :param jobs: The parameters of experiments to run. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
245 :type jobs: an iterable object over dictionaries |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
246 :param verbose: print which jobs are added and which are skipped |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
247 :param add_dups: False will ignore a job if it matches (on all items()) with a db entry. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
248 :type add_dups: Bool |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
249 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
250 :returns: list of (Bool,job[i]) in which the flags mean the corresponding job actually was |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
251 inserted. |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
252 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
253 """ |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
254 rval = [] |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
255 for job in jobs: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
256 job = copy.copy(job) |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
257 if session is None: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
258 s = db.session() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
259 do_insert = force_dup or (None is db.query(s).filter_eq_dct(job).first()) |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
260 s.close() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
261 else: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
620
diff
changeset
|
262 do_insert = force_dup or (None is db.query(session).filter_eq_dct(job).first()) |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
263 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
264 if do_insert: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
265 if type_check: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
266 for k,v in job.items(): |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
267 if type(v) != getattr(type_check, k): |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
268 raise TypeError('Experiment contains value with wrong type', |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
269 ((k,v), getattr(type_check, k))) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
270 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
271 job[STATUS] = START |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
272 job[PRIORITY] = 1.0 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
273 if verbose: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
274 print 'ADDING ', job |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
275 db.insert(job) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
276 rval.append((True, job)) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
277 else: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
278 if verbose: |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
279 print 'SKIPPING', job |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
280 rval.append((False, job)) |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
281 |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
diff
changeset
|
282 |