source: mauRepo/MolesManager/trunk/src/libs/commons_db.py @ 8358

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/mauRepo/MolesManager/trunk/src/libs/commons_db.py@8358
Revision 8358, 5.2 KB checked in by mnagni, 7 years ago (diff)

Major refactoring about migration (now handles better create/update, even if the single updates have to be quite fully implemented)
Added the connection pool from SQLAlchemy

Line 
1'''
2BSD Licence
3Copyright (c) 2012, Science & Technology Facilities Council (STFC)
4All rights reserved.
5
6Redistribution and use in source and binary forms, with or without modification,
7are permitted provided that the following conditions are met:
8
9    * Redistributions of source code must retain the above copyright notice,
10        this list of conditions and the following disclaimer.
11    * Redistributions in binary form must reproduce the above copyright notice,
12        this list of conditions and the following disclaimer in the documentation
13        and/or other materials provided with the distribution.
14    * Neither the name of the Science & Technology Facilities Council (STFC)
15        nor the names of its contributors may be used to endorse or promote
16        products derived from this software without specific prior written permission.
17
18THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
20THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
21PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS
22BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,
23OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
24SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
25HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
26OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
27OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
28
29Created on 17 Nov 2011
30
31@author: Maurizio Nagni
32'''
33from sqlalchemy import MetaData, create_engine
34from sqlalchemy.orm import sessionmaker
35from sqlalchemy.sql.expression import text
36class DbManagerCollection(object):
37    """
38        Represents a collection of DbManagers. The key of the dictionary passed as argument to the constructor
39        will be used to retrieve a specific db instance.         
40    """
41    def __init__(self, dbManagers = {}): 
42        self._dbManagers = dbManagers
43       
44    def createSession(self, name):
45        if self._dbManagers.has_key(name):
46            return self._dbManagers[name].createDbSession()
47
48class DbManager(object):
49   
50
51    """
52        Represents a database instance managed by an SQLAlchemy engine.       
53    """
54    def __init__(self, connection, script = None, sql_echo=False, session_event_manager = None, ipool_size = 10):
55        """
56          @param connection: the URL used to open a connection to the database
57          @param script: a python module defining the classes mapping
58          @param sql_echo: sets the SQLalchemy create_engine.echo param. Default is False
59          @param session_event_manager: a python method defining session events \
60          (see http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=session%20events#sqlalchemy.orm.events.SessionEvents)
61          @param ipool_size: sets the connections pool size (note that this is the maximum size not the number of the pre-allocated ones)     
62        """
63        self._connection = connection       
64        self._script = script
65        self.engine = create_engine(self._connection, pool_size = ipool_size, pool_recycle=3600, echo=sql_echo)
66        self.metadata = MetaData(bind=self.engine)       
67        self._session = None
68        self._session_event_manager = session_event_manager
69        if self._script:
70            self._script(self.metadata)
71            self.metadata.create_all(bind=self.engine)
72        if self.engine:
73            self.engine.connect()
74           
75    def createDbSession(self):
76        """
77           Returns a brand new connection to the DB.       
78           @return: a sqlalchemy.orm.session.Session instance if the ORM is fully configured, otherwise a sqlalchemy.engine.base.Connection instance
79        """
80        session = sessionmaker(bind=self.engine)()       
81        if self._session_event_manager:
82            self._session_event_manager(session)
83        return session       
84
85    def closeDbSession(self, dbSession):
86        """
87          Commits and closes the given session/connection instance
88          :param dbSession: a Session/Connection instance
89        """
90        try:
91            dbSession.commit()
92            dbSession.close()
93        except:
94            dbSession.rollback()
95            dbSession.close()
96            raise
97       
98    def executeNative(self, sqlNative):
99        t = text(sqlNative)
100        return self._connection.execute(t)
101
102class NoDBManager(Exception):
103    def __init__(self, value):
104        self.value = value
105    def __str__(self):
106        return "No DBManager has been set for %s" % (self.value) 
107
108def doInsertOrUpdate(items, session, update = True):
109    if update:
110        session.commit()
111    else:
112        session.add_all(items)
113
114def getObjectId(clazz, obj_id, session): 
115    '''
116        Retrieves an entity
117        @param clazz: the entity type
118        @param obj_id: the entity id 
119    '''       
120    res = session.query(clazz).get(obj_id)
121    if res is None:
122        return None
123    return res
124
125def getAllObjects(clazz, session):     
126    res = session.query(clazz)
127    if res is None:
128        return None
129    return res
Note: See TracBrowser for help on using the repository browser.