def _teardown_db(): import transaction transaction.abort() import sqlahelper sqlahelper.get_session().remove() from . import models as m m.Base.metadata.drop_all()
def test_add_engine_twice(self): db1 = sa.create_engine(self.db1.url) db2 = sa.create_engine(self.db2.url) sqlahelper.add_engine(db1) self.assertIs(sqlahelper.get_session().bind, db1) sqlahelper.add_engine(db2) self.assertIs(sqlahelper.get_session().bind, db2) self.assertIs(sqlahelper.get_session().bind, sqlahelper.sessions.default.registry.registry.value.bind)
def test_member(self): import sqlahelper from . import models as m p = m.Project(project_name=u"test-project") u = m.User(user_name=u'test-user') m = m.Member(project=p, user=u) sqlahelper.get_session().add(m) request = testing.DummyRequest(matchdict={'ticket_no': '10'}, authenticated_user=u) target = self._makeOne(request) target.project = p self.assertEqual(target.member, m)
def setUp(self): self.config = testing.setUp() from sqlalchemy import create_engine from sqlahelper import add_engine, get_session engine = create_engine('sqlite://') add_engine(engine) from .models import ( Base, MyModel, ) self.session = get_session() Base.metadata.create_all(engine) self.session = get_session() self.model = MyModel(name='one', value=55) self.session.add(self.model)
def find_object(request): session = get_session() try: one = session.query(MyModel).filter(MyModel.name=='one').first() except DBAPIError: raise HTTPInternalServerError(explanation=conn_err_msg) return one
def test1(self): import transaction Base = sqlahelper.get_base() class Person(Base): __tablename__ = "people" id = sa.Column(sa.Integer, primary_key=True) first_name = sa.Column(sa.Unicode(100), nullable=False) last_name = sa.Column(sa.Unicode(100), nullable=False) engine = sa.create_engine(self.db1.url) sqlahelper.add_engine(engine) Base.metadata.create_all() fred = Person(id=1, first_name=u"Fred", last_name=u"Flintstone") wilma = Person(id=2, first_name=u"Wilma", last_name=u"Flintstone") barney = Person(id=3, first_name=u"Barney", last_name=u"Rubble") betty = Person(id=4, first_name=u"Betty", last_name=u"Rubble") Session = sqlahelper.get_session() sess = Session() sess.add_all([fred, wilma, barney, betty]) transaction.commit() sess.expunge_all() del fred, wilma, barney, betty # Can we get back a record? barney2 = sess.query(Person).get(3) self.assertEqual(barney2.id, 3) self.assertEqual(barney2.first_name, u"Barney") self.assertEqual(barney2.last_name, u"Rubble") sa.select([Person.first_name]) # Can we iterate the first names in reverse alphabetical order? q = sess.query(Person.first_name).order_by(Person.first_name.desc()) result = [x.first_name for x in q] control = [u"Wilma", u"Fred", u"Betty", u"Barney"] self.assertEqual(result, control)
def app_settings(key, mod='core'): import sqlahelper from columns.models import Setting Session = sqlahelper.get_session() module = Session.query(Setting).get(mod) setting_dict = getattr(module, 'config', {}) return setting_dict.get(key)
def __getitem__(self, key): if key in self.collection_views: raise KeyError(key) if isinstance(key, slice): def is_negative(value): return value is not None and value < 0 def offset_negative(length, value): try: return length + value if int(value) <= 0 else value except TypeError: return None if is_negative(key.start) or is_negative(key.stop): length = len(self) key = slice(offset_negative(length, key.start), offset_negative(length, key.stop)) return self.index(offset=key.start, limit=key.stop) id_ = self._decode_key(key) db_session = sqlahelper.get_session() resource = db_session.query( self.__model__ ).get(id_) if resource is None: raise KeyError(key) resource.__parent__ = self resource.__name__ = key return resource
def stream_view(request): tag = request.GET.get('tag') user = request.GET.get('user') page_number = request.GET.get('page') stories_per_page = 10 try: page_number = max([int(page_number), 0]) except (TypeError, ValueError): page_number = 0 Session = sqlahelper.get_session() Session.rollback() query = Session.query(Article) if tag: query = query.filter(Article.tags.any(Tag.slug == tag)) if user: query = query.filter(Article.author.has(User.name == user)) stream = query.\ filter(Article.published != None).\ order_by(Article.published.desc()).\ limit(stories_per_page).\ offset(page_number * stories_per_page).\ all() return render_to_response( 'columns:templates/blog/stream.jinja', {'stream': stream, 'request': request, 'page': page_number} )
def main(): # pragma: nocover env = bootstrap("development.ini") from geoportailv3.models import LuxGetfeatureDefinition package = env["registry"].settings["package"] directory = "%s/locale/" % package destination = path.join(directory, "%s-tooltips.pot" % package) w = codecs.open(destination, "wt", encoding="utf-8") w.write( u'''#, fuzzy msgid "" msgstr "" "MIME-Version: 1.0\\n" "Content-Type: text/plain; charset=utf-8\\n" "Content-Transfer-Encoding: 8bit\\n" ''' ) dbsession = sqlahelper.get_session() results = dbsession.query(LuxGetfeatureDefinition).\ filter(LuxGetfeatureDefinition.remote_template == False).\ filter(LuxGetfeatureDefinition.template == 'default_gisgr.html').all() # noqa fields = [] for result in results: engine = None first_row = None if result.query is not None and len(result.query) > 0: engine = sqlahelper.get_engine(result.engine) first_row = engine.execute("SELECT * FROM " + result.query).first() if result.rest_url is not None and len(result.rest_url) > 0: first_row = _get_external_data( result.rest_url, '96958.90059551848,61965.61097091329,' + '97454.77280739773,62463.21618929457', result.layer) if first_row is not None: attributes = dict(first_row) attributes = remove_attributes( attributes, result.attributes_to_remove, result.geometry_column) for attribute in attributes: if attribute not in fields: fields.append(attribute) w.write( u'''#: engine:%(engine)s Layer:%(layer)s Role:%(role)s msgid "f_%(name)s" msgstr "" ''' % { "engine": result.engine_gfi, "layer": result.layer, "role": result.role, "name": attribute, } ) print("tooltips Pot file updated: %s" % destination)
def tearDown(self): config.cleanup_system() sm = self.p_config sm.__init__('base') testing.tearDown() Session = sqlahelper.get_session() Session.expunge_all()
def get_author_data_from_user(): user_id = get_author_id() session = sqlahelper.get_session() try: user = session.query(User).get(user_id) return {'id':user_id, 'name':user.name, 'uri':user.profile} except: # pragma: no cover return {}
def clear(self): db_session = sqlahelper.get_session() try: db_session.query(self.__model__).delete() db_session.commit() except: # pragma: no cover db_session.rollback() raise
def db_session_request(event): session = sqlahelper.get_session() def cleanup(_): try: session.rollback() except: # pragma: no cover pass event.request.add_finished_callback(cleanup) return session
def main_pages(): import sqlahelper from columns.models import Page Session = sqlahelper.get_session() pages = Session.query(Page.slug, Page.title).\ filter(Page.in_menu == True).\ filter(Page.visible == True).\ order_by(Page.in_main.desc(), Page.title.asc()) return [(slug, title) for slug, title in pages]
def __init__(self): self.client = \ Client('https://titan.etat.lu/xxpfoWS/ParcelDetailVer1' + 'Service/META-INF/wsdl/ParcelDetailVer1Service.wsdl') self.client_mesurage = \ Client('https://titan.etat.lu/xxpfoWS/Measure' + 'mentVer1Service/META-INF/wsdl/MeasurementVer1Service.wsdl') self.log = logging.getLogger(__name__) self.dbsession = sqlahelper.get_session()
def add(self, resource): db_session = sqlahelper.get_session() try: saved_resource = db_session.merge(resource) db_session.commit() except: # pragma: no cover db_session.rollback() raise else: saved_resource.__name__ = saved_resource.get_key() return saved_resource
def test_multiple_engines(self): default = sa.create_engine(self.db1.url) stats = sa.create_engine(self.db2.url) sqlahelper.add_engine(default) sqlahelper.add_engine(stats, "stats") # Can we retrieve the engines? self.assertIs(sqlahelper.get_engine(), default) self.assertIs(sqlahelper.get_engine("default"), default) self.assertIs(sqlahelper.get_engine("stats"), stats) # Are the session binding and base binding set correctly? self.assertIs(sqlahelper.get_session().bind, default) self.assertIs(sqlahelper.get_base().metadata.bind, default)
def get_users(self): if self.request.matchdict.get("pathparams"): offset, size = map(int, self.request.matchdict["pathparams"]) else: offset = self.request.GET["offset"] size = self.request.GET["size"] db_session = get_session() q = db_session.query(model.DKUser).offset(offset).limit(size) l = [(u.username, u.master_email) for u in q.all()] return pack_result(RS_SUCCESS, data=l)
def test_multiple_engines_without_default(self): db1 = sa.create_engine(self.db1.url) db2 = sa.create_engine(self.db2.url) sqlahelper.add_engine(db1, "db1") sqlahelper.add_engine(db2, "db2") # Can we retrieve the engines? self.assertIs(sqlahelper.get_engine("db1"), db1) self.assertIs(sqlahelper.get_engine("db2"), db2) # There should be no default engine self.assertIsNone(sqlahelper.get_session().bind) self.assertIsNone(sqlahelper.get_base().metadata.bind) self.assertIsNone(sqlahelper.get_engine())
def __setitem__(self, key, value): db_session = sqlahelper.get_session() value.set_key(key) try: saved_resource = db_session.merge(value) db_session.commit() except: # pragma: no cover db_session.rollback() raise else: saved_resource.__name__ = saved_resource.get_key() return saved_resource
def login(self): username = self.request.POST["username"] passwd = md5(self.request.POST["passwd"]).hexdigest() db_session = get_session() q = db_session.query(model.DKUser) user = q.get(username) if user.passwd == passwd: #cache user in session self.request.session["user"] = user self.request.session.save() return pack_result(RS_SUCCESS) else: return pack_result(RS_FAILURE)
def __delitem__(self, key): id_ = self._decode_key(key) Session = sqlahelper.get_session() resource = Session.query( self.__model__ ).get(id_) if resource is None: raise KeyError(key) basepath = self.request.registry.settings.get('upload_basepath') resource_path = os.path.join(basepath, resource.filepath) Session.delete(resource) Session.commit() os.remove(resource_path)
def main(argv=sys.argv): if len(argv) != 2: usage(argv) config_uri = argv[1] setup_logging(config_uri) settings = get_appsettings(config_uri) engine = engine_from_config(settings, 'sqlalchemy.') add_engine(engine) session = get_session() Base.metadata.create_all(engine) with transaction.manager: model = MyModel(name='one', value=1) session.add(model)
def __delitem__(self, key): id_ = self._decode_key(key) db_session = sqlahelper.get_session() resource = db_session.query( self.__model__ ).get(id_) if resource is None: raise KeyError(key) try: db_session.delete(resource) db_session.commit() except: # pragma: no cover db_session.rollback() raise
def settings_save(request): module = request.matchdict.get('module') Session = sqlahelper.get_session() setting = Session.query(Setting).get(module) for k, v in request.POST.items(): if k == 'save': continue setting.config[k] = v Session.merge(setting) Session.commit() raise exception_response( 302, location=request.route_url('settings') )
def page_view(request): Session = sqlahelper.get_session() try: page = Session.query(Page).\ filter(Page.slug == request.matchdict.get('page')).\ filter(Page.visible == True).\ one() except SQLAlchemyError: Session.rollback() raise exception_response(404) else: return render_to_response( 'columns:templates/blog/page.jinja', {'page': page} )
def story_view(request): Session = sqlahelper.get_session() try: story = Session.query(Article).\ filter( Article.permalink==request.matchdict.get('permalink') ).\ filter(Article.published != None).\ one() except SQLAlchemyError: Session.rollback() raise exception_response(404) else: return render_to_response( 'columns:templates/blog/story.jinja', {'story': story} )
def callback(self, userid, request): DBSession = sqlahelper.get_session() principals = [userid] auth_type = request.session.get('auth.type') #load user into cache if not auth_type: request.session[self.userid_key] = userid user = DBSession.query(User).get(userid) if user is None: return principals request.session['auth.type'] = auth_type = user.type # add in principles according to session stored variables inv_permission = get_permissions() principals.append(inv_permission.get(request.session['auth.type'], DEFAULT_USER_TYPE)) LOG.debug('User principals: %r', principals) return principals
def register(self): user = model.DKUser() user.username = request.POST["username"] user.passwd = md5(request.POST["passwd"]).hexdigest() user.master_email = request.POST["email"] user.phone_mobile = request.POST["phone_mobile"] user.phone_office = request.POST["phone_office"] user.phone_home = request.POST["phone_home"] user.org = request.POST["org"] user.title = request.POST["title"] user.addr = request.POST["addr"] #roles is stored as ":" seperated string user.roles = ":".join((meta.ROLE_USER, )) db_session = get_session() db_session.add(user) return pack_result(RS_SUCCESS)
def setUp(self): reload_logging('./logging.ini') load_database({'url':'sqlite:///:memory:'}) sqlahelper.get_session().configure(extension=[]) transaction.begin()
def setUp(self): from pyramid.config import Configurator self.engine = sqlahelper.add_engine(url="sqlite://") self.session = sqlahelper.get_session()() self.config = Configurator(autocommit=True) self.config.begin()
for fname in data.keys(): if fname not in fields: continue field = fields[fname] value = self[fname] if value == field.default: continue result.append({ 'name': '{0}.{1}'.format(name, fname), 'value': field.dumps(value) }) Session.add( SettingRecord(name='{0}.{1}'.format(name, fname), value=field.dumps(value))) Session.flush() self.__registry__.notify(ptah.events.SettingsGroupModified(self)) Session = sqlh.get_session() class SettingRecord(sqlh.get_base()): __tablename__ = 'ptah_settings' name = sqla.Column(sqla.String, primary_key=True) value = sqla.Column(sqla.String)
def get_user_count(self): db_session = get_session() count = db_session.query(model.DKUser).count() return pack_result(RS_SUCCESS, data=count)
def get_meta(request): meta_id = request.GET.get('meta_id') session = sqlahelper.get_session() json = session.query(models.Meta).get(meta_id).json meta_widget = widgets.JsonWidget(json) return HttpResponse(meta_widget.render())
class Serializer(View): """ Base Class for methods that return a the result of a SQL query in a non-proprietary file format. Provides a the func. to get data (sql or api are main data sources) Mainly returns a query result containing a full record from OEP table as GEOJSON featureCollection. All related tables are joined and the values are included as property within the GEOJSON. :return: dict - geojson featureCollection """ # pylint: disable=unnecessary-pass, no-self-use, no-self-argument # ToDO: after testing done change to input pram Session = sah.get_session() session = Session() ############################################## # list that stores all query results that are defined as feature object myfeatures = [] # load the regions with open( 'WAM_APP_FRED/static/WAM_APP_FRED/geodata/germany_nuts_1.geojson', encoding='UTF-8') as g: ger_regions = geojson.load(g) regions_wkbs = {} regions_nuts = {} for f in ger_regions['features']: region_id = f['properties']['region'] region_boundary = f['geometry']['coordinates'] boundary_geometry = geojson.MultiPolygon(region_boundary) # create shapely geometry from geojson feature _geom = shape(boundary_geometry) # store this information in a dict regions_wkbs[region_id] = from_shape(_geom, srid=4326) regions_nuts[region_id] = f['properties']['nuts_1'] # load the landkreis with open( 'WAM_APP_FRED/static/WAM_APP_FRED/geodata/germany_nuts_3.geojson', encoding='UTF-8') as g: ger_landkreis = geojson.load(g) landkreis_wkbs = {} landkreis_names = {} regions_to_landkreis = {} for f in ger_landkreis['features']: lk_id = f['properties']['nuts'] lk_boundary = f['geometry']['coordinates'] boundary_geometry = geojson.MultiPolygon(lk_boundary) # create shapely geometry from geojson feature _geom = shape(boundary_geometry) # store this information in a dict landkreis_wkbs[lk_id] = from_shape(_geom, srid=4326) # store the region index in a list landkreis_names[lk_id] = f['properties']['gen'] # create a mapping between the region nuts and the lankreis included in it # region is is always the first 3 letters of the nuts code region_id = lk_id[0:3] # add the region id as a key if region_id not in regions_to_landkreis.keys(): regions_to_landkreis[region_id] = [] # append the landkreis id to the list under region id if lk_id not in regions_to_landkreis[region_id]: regions_to_landkreis[region_id].append(lk_id) # load the powerplant allocation per resion with open( 'WAM_APP_FRED/static/WAM_APP_FRED/geodata/bundeslaender_pp_count.json', encoding='UTF-8') as g: ger_pp_count = json.load(g) def ger_boundaries_view(self): germany_boundaries = Serializer.ger_regions return HttpResponse(dumps(germany_boundaries), content_type="application/json") def ger_landkreis_view(self): germany_landkreis = Serializer.ger_landkreis return HttpResponse(dumps(germany_landkreis), content_type="application/json") def ger_powerplant_count_view(self): germany_powerplants_count = Serializer.ger_pp_count return HttpResponse(dumps(germany_powerplants_count), content_type="application/json") def district_feedin_series_view(self): """ This function will return a json/geojson with pre calculated data for a single or multiple district. The data will include a feedin time series for each district. :return: """ pass
def __init__(self, request): self.request = request self.dbsession = sqlahelper.get_session()
def main(): # pragma: nocover env = bootstrap("development.ini") from geoportailv3.models import LuxGetfeatureDefinition package = env["registry"].settings["package"] directory = "%s/locale/" % package destination = path.join(directory, "%s-tooltips.pot" % package) w = codecs.open(destination, "wt", encoding="utf-8") w.write(u'''#, fuzzy msgid "" msgstr "" "MIME-Version: 1.0\\n" "Content-Type: text/plain; charset=utf-8\\n" "Content-Transfer-Encoding: 8bit\\n" ''') dbsession = sqlahelper.get_session() results = dbsession.query(LuxGetfeatureDefinition).\ filter(LuxGetfeatureDefinition.remote_template == False).filter( LuxGetfeatureDefinition.template.in_ (['default.html', 'default_table.html'])).all() # noqa fields = [] for result in results: engine = sqlahelper.get_engine(result.engine_gfi) first_row = None if result.query is not None and len(result.query) > 0: if "SELECT" in result.query.upper(): first_row = engine.execute(result.query).first() else: first_row =\ engine.execute("SELECT * FROM " + result.query).first() if result.rest_url is not None and len(result.rest_url) > 0: first_row = _get_external_data( result.rest_url, '96958.90059551848,61965.61097091329,' + '97454.77280739773,62463.21618929457', result.layer) attributes = None if first_row is not None: attributes = dict(first_row) attributes = remove_attributes(attributes, result.attributes_to_remove, result.geometry_column) if first_row is None and result.columns_order is not None and\ len(result.columns_order) > 0: attributes = result.columns_order.split(",") if attributes is not None: for attribute in attributes: if attribute not in fields: fields.append(attribute) w.write( u'''#: engine:%(engine)s Layer:%(layer)s Role:%(role)s msgid "f_%(name)s" msgstr "" ''' % { "engine": result.engine_gfi, "layer": result.layer, "role": result.role, "name": attribute, }) print("tooltips Pot file updated: %s" % destination)
from raggregate.models.stat import Stat from datetime import datetime from datetime import timedelta import calendar import pytz import time import sqlahelper import sqlalchemy import json dbsession = sqlahelper.get_session() def get_from_post(post, key): if key in post and post[key] != '': return post[key] else: return None def realize_timedelta_constructor(con_str): """ Converts a timedelta constructor parameter list into a real timedelta. @param con_str: the constructor parameters to convert""" return eval("timedelta({0})".format(con_str)) def now_in_utc(): return datetime.utcnow().replace(tzinfo=pytz.utc)
def main(): if len(sys.argv) != 2: sys.exit("Usage: python -m drkpr.scripts.create_db INI_FILE") ini_file = sys.argv[1] logging.config.fileConfig(ini_file) log = logging.getLogger(__name__) app = get_app(ini_file, "myapp") settings = app.registry.settings engine = sqlalchemy.engine_from_config(settings, prefix="sqlalchemy.", pool_recycle=3600, convert_unicode=True) sqlahelper.add_engine(engine) Base = sqlahelper.get_base() Session = sqlahelper.get_session() # Create the tables if they don't already exist log.info("Initialize database ...") Base.metadata.create_all(bind=Session.bind, checkfirst=True) #create default privileges log.info("Populate default privileges ...") log.info("nothing here...") log.info("Populate default privileges done.") #create default roles log.info("Populate default roles ...") q = Session.query(model.DKRole) if not q.all(): records = [ model.DKRole(model.ROLE_SYSADMIN), model.DKRole(model.ROLE_USER), ] Session.add_all(records) log.info("Populate default roles done.") else: log.info("Roles already exist.") log.info("Populate default roles done.") #create default system parameters log.info("Populate default system parameters ...") q = Session.query(model.DKSystem) if not q.all(): records = [ model.DKSystem("master_key_status", model.SS_SERVICE_NO_KEY), model.DKSystem("service_key_gen_status", model.SS_SERVICE_NOT_AVAIL), model.DKSystem("service_key_revoke_status", model.SS_SERVICE_NOT_AVAIL), ] Session.add_all(records) log.info("Populate default system parameters done.") else: log.info("System parameters exists.") #create default admin account log.info("Create default admin account ...") q = Session.query(model.DKUser) r = q.get("sysadmin") if not r: user = model.DKUser() user.username = "******" user.roles = ":".join(["", model.ROLE_SYSADMIN, ""]) user.passwd = hashlib.md5("sysadmin").hexdigest() user.master_email = "*****@*****.**" user.actived = True Session.add(user) log.info("Admin account setup complete.") else: log.info("Admin account already setup.") transaction.commit()
""" sqla module """ import urllib import sqlahelper as psa from sqlalchemy.orm.mapper import _mapper_registry from pyramid.view import view_config from pyramid.compat import url_quote_plus from pyramid.decorator import reify from pyramid.httpexceptions import HTTPFound import ptah from ptah import form Session = psa.get_session() metadata = psa.get_base().metadata @ptah.manage.module('sqla') class SQLAModule(ptah.manage.PtahModule): __doc__ = 'A listing of all tables with ability to view and edit records' title = 'SQLAlchemy' metadata = {} def __getitem__(self, key): try: id, table = key.split('-', 1) except: raise KeyError(key) md = self.metadata[id][0]
def tearDown(self): Session = sqlahelper.get_session() Session.remove()