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
15 def __init__(self, config):
17 # will be created lazily on-demand
19 # the former PostgreSQL.py used the psycopg2 directly and was doing
20 # self.connection.set_client_encoding("UNICODE")
21 # it's unclear how to achieve this in sqlalchemy, nor if it's needed at all
22 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
23 # we indeed have /var/lib/pgsql/data/postgresql.conf where
24 # this setting is unset, it might be an angle to tweak that if need be
25 # try a unix socket first - omitting the hostname does the trick
26 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s" %\
27 (config.SFA_DB_USER, config.SFA_DB_PASSWORD, config.SFA_DB_PORT, dbname)
28 # the TCP fallback method
29 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s" %\
30 (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
31 config.SFA_DB_HOST, config.SFA_DB_PORT, dbname)
32 for url in [unix_url, tcp_url]:
34 logger.debug("Trying db URL %s" % url)
35 self.engine = create_engine(url)
42 raise Exception("Could not connect to database %s as %s with psycopg2" % (
43 dbname, config.SFA_DB_USER))
45 # expects boolean True: debug is ON or False: debug is OFF
46 def debug(self, echo):
47 self.engine.echo = echo
50 self.engine.execute("select 1").scalar()
52 def global_session(self):
53 if self._session is None:
54 Session = sessionmaker()
55 self._session = Session(bind=self.engine)
56 logger.debug('alchemy.global_session created session %s' %
60 def close_global_session(self):
61 if self._session is None:
63 logger.debug('alchemy.close_global_session %s' % self._session)
67 # create a dbsession to be managed separately
69 Session = sessionmaker()
70 session = Session(bind=self.engine)
71 logger.debug('alchemy.session created session %s' % session)
74 def close_session(self, session):
75 logger.debug('alchemy.close_session closed session %s' % session)
79 from sfa.util.config import Config
81 alchemy = Alchemy(Config())
82 engine = alchemy.engine
83 global_dbsession = alchemy.global_session()