1 from sqlalchemy import create_engine
2 from sqlalchemy.orm import sessionmaker
4 from sqlalchemy import Column, Integer, String
5 from sqlalchemy import ForeignKey
7 from sfa.util.sfalogging import logger
9 # this module is designed to be loaded when the configured db server is reachable
10 # OTOH model can be loaded from anywhere including the client-side
14 def __init__ (self, config):
16 # will be created lazily on-demand
18 # the former PostgreSQL.py used the psycopg2 directly and was doing
19 #self.connection.set_client_encoding("UNICODE")
20 # it's unclear how to achieve this in sqlalchemy, nor if it's needed at all
21 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
22 # we indeed have /var/lib/pgsql/data/postgresql.conf where
23 # this setting is unset, it might be an angle to tweak that if need be
24 # try a unix socket first - omitting the hostname does the trick
25 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"%\
26 (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_PORT,dbname)
27 # the TCP fallback method
28 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"%\
29 (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_HOST,config.SFA_DB_PORT,dbname)
30 for url in [ unix_url, tcp_url ] :
32 logger.debug("Trying db URL %s"%url)
33 self.engine = create_engine (url)
40 raise Exception("Could not connect to database %s as %s with psycopg2"%(dbname,config.SFA_DB_USER))
43 # expects boolean True: debug is ON or False: debug is OFF
44 def debug (self, echo):
48 self.engine.execute ("select 1").scalar()
50 def global_session (self):
51 if self._session is None:
52 Session=sessionmaker ()
53 self._session=Session(bind=self.engine)
54 logger.debug('alchemy.global_session created session %s'%self._session)
57 def close_global_session (self):
58 if self._session is None: return
59 logger.debug('alchemy.close_global_session %s'%self._session)
63 # create a dbsession to be managed separately
65 Session=sessionmaker()
66 session=Session (bind=self.engine)
67 logger.debug('alchemy.session created session %s'%session)
70 def close_session (self, session):
71 logger.debug('alchemy.close_session closed session %s'%session)
75 from sfa.util.config import Config
77 alchemy=Alchemy (Config())
79 global_dbsession=alchemy.global_session()