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()
