def import_engine_automapped_data(app, **kwargs):
global Model, Base
logger.debug("kwargs: %s" % kwargs)
model_class_names = []
engine = kwargs.get('engine', None)
if not engine:
raise Exception("engine isn't set")
bind_name = kwargs.get('bind_name')
if not bind_name:
raise Exception("bind_name isn't set or doesn't exist in app.config")
obj = None
prefix = kwargs.get('class_prefix', '')
#return (None, (None,))
with app.app_context():
#bind_name = 'genesis_eg1'
with not_profiled():
metadata = MetaData(engine)
if kwargs.get('use_no_pk_tables', False):
table_objs = get_no_primary_key_table_names(
app, bind_name=bind_name, engine=engine)
for table_obj in table_objs:
table_name = table_obj['table_name']
inspector = table_obj['inspector']
cols = inspector.get_columns(table_name)
col = cols[0]
c_name = col.pop('name')
c_type = col.pop('type')
col['primary_key'] = True
cls = Table(table_name,
metadata,
Column(c_name, c_type, **col),
autoload=True,
extend_existing=True)
class_name = prefix \
+ camelize_and_dedigitize_genesis_classname(
Base, table_name, None,
)
session = Session(engine)
metadata.reflect(bind=engine)
Model = declarative_base(metadata=metadata,
cls=(db.Model, ),
bind=engine)
Base = automap_base(metadata=metadata, declarative_base=Model)
Base.prepare(
engine,
reflect=True,
classname_for_table=camelize_and_dedigitize_genesis_classname,
name_for_collection_relationship=singular_noun_collection,
generate_relationship=generate_relationship_custom,
)
obj = Base
for cls in Base.classes:
if bind_name:
cls.__table__.info = {'bind_key': bind_name}
setattr(cls, '__bind_key__', bind_name)
make_class_dictable(cls)
class_name = prefix + cls.__name__
model_class_names.append(class_name)
logger.debug("Base class name: %s" % cls.__name__)
if kwargs.get('use_automapped_classes', False):
add_to_automapped_classes(bind_name, class_name, cls)
else:
globals()[class_name] = cls
return (obj, tuple(model_class_names))
def get_nodes_and_make_polygon(drive_time_query_id):
print(f'[{datetime.now()}] get_nodes_and_make_polygon(): Setting up sqlalchemy')
engine = create_engine(db_url_from_env(), echo=False)
session = Session(engine)
metadata = MetaData()
metadata.reflect(engine, only=['routing_drivetimenode', 'routing_drivetimepolygon', 'bridges_newyorkbridge'])
Base = automap_base(metadata=metadata)
Base.prepare()
# Reflecting on the metadata collects the routing_drivetimenode table,
# the routing_drivetimepolygon table, and their related tables
# (routing_drivetimequery and ways_vertices_pgr)
DriveTimeNode = Base.classes.routing_drivetimenode
DriveTimePolygon = Base.classes.routing_drivetimepolygon
DriveTimeQuery = Base.classes.routing_drivetimequery
WaysVerticesPgr = Base.classes.ways_vertices_pgr
NewYorkBridge = Base.classes.bridges_newyorkbridge
# Get the routing_drivetimequery object that matches the message and the
# associated drivetimenodes
drive_time_query = session.query(DriveTimeQuery).get(drive_time_query_id)
drive_time_nodes = session.query(DriveTimeNode).filter(
DriveTimeNode.routing_drivetimequery == drive_time_query
).all()
print(f'[{datetime.now()}] Display name: {drive_time_query.display_name}')
# Make a polygon object from the nodes
print(
f'[{datetime.now()}] get_nodes_and_make_polygon(): ' +
f'Processing {len(drive_time_nodes)} nodes'
)
points = [loads(str(dtn.the_geom), hex=True) for dtn in drive_time_nodes]
polygon = to_polygon(points, alpha=30)
# Commit the results to the database
new_drive_time_polygon = DriveTimePolygon(
the_geom='SRID=4326;'+polygon.buffer(0.005).wkt,
drive_time_query_id=drive_time_query_id,
created_time=datetime.now(),
edited_time=datetime.now(),
)
session.add(new_drive_time_polygon)
session.flush()
session.commit()
print(f'[{datetime.now()}] get_nodes_and_make_polygon(): Committed polygon to db')
print(f'[{datetime.now()}] Running intersect query on NewYorkBridge objects')
bridges = session.query(NewYorkBridge).filter(
NewYorkBridge.the_geom.ST_Intersects('SRID=4326;'+polygon.buffer(0.005).wkt)
).all()
print(f'[{datetime.now()}] Iterating through {len(bridges)} bridges')
for b in bridges:
bridge = session.query(NewYorkBridge).filter(NewYorkBridge.id == b.id).first()
drive_time_queries = bridge.drive_time_queries
bridge.drive_time_queries = list(set(drive_time_queries + [drive_time_query_id]))
session.add(bridge)
drive_time_query.polygon_pending = False
session.add(drive_time_query)
print(
f'[{datetime.now()}] get_nodes_and_make_polygon(): Set DriveTimeQuery' +
f'.polygon_pending to {drive_time_query.polygon_pending}'
)
session.flush()
session.commit()
print(f'[{datetime.now()}] get_nodes_and_make_polygon(): Committed bridges to db')
return True
import datetime as dt
import sqlalchemy
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, func
from flask import Flask, jsonify
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
# reflect an existing database into a new model
Base = automap_base()
# reflect the tables
Base.prepare(engine, reflect=True)
# Save reference to the table
Measurement = Base.classes.measurement
Station = Base.classes.station
session = Session(engine)
app = Flask(__name__)
firstDate = (session.query(Measurement.date)
.order_by(Measurement.date.desc()).first())
firstDate = list(np.ravel(firstDate))[0]
secondDate = dt.datetime.strptime(firstDate, "%Y-%m-%d")
secondDate = secondDate.timetuple()
year = secondDate[0]-1
month = secondDate[1]
day = secondDate[2]
start_date = dt.date(2017,7,20)
document._element = element
def __delete__(self, document):
del document._element
document._nodes = []
# override Document's "element" attribute with the marshaller.
Document.element = ElementTreeMarshal()
# PART V - Basic Persistence Example
line = "\n--------------------------------------------------------"
# save to DB
session = Session(e)
# get ElementTree documents
for file in ("test.xml", "test2.xml", "test3.xml"):
filename = os.path.join(os.path.dirname(__file__), file)
doc = ElementTree.parse(filename)
session.add(Document(file, doc))
print("\nSaving three documents...", line)
session.commit()
print("Done.")
print("\nFull text of document 'text.xml':", line)
document = session.query(Document).filter_by(filename="test.xml").first()
ElementTree.dump(document.element)
def tobs():
session = Session(engine)
tobs_query = session.query(Measurement.date, Measurement.tobs).filter(Measurement.date >= year_ago).\
order_by(Measurement.date).all()
return jsonify(tobs_query)
def test_coerce(self):
sess = Session()
f1 = Foo(data={'a': 'b'})
sess.add(f1)
sess.flush()
eq_(type(f1.data), self._type_fixture())
def insert_data(cls):
A = cls.classes.A
s = Session()
s.add(A(data="d1", deferred_data="d2"))
s.commit()
def test_orm_query(n):
"""test a straight ORM query of the full entity."""
session = Session(bind=engine)
for id_ in random.sample(ids, n):
session.query(Customer).filter(Customer.id == id_).one()
def test_orm_query_cols_only(n):
"""test an ORM query of only the entity columns."""
session = Session(bind=engine)
for id_ in random.sample(ids, n):
session.query(Customer.id, Customer.name,
Customer.description).filter(Customer.id == id_).one()
def main():
Base.metadata.create_all(engine)
session = Session(bind=engine)
def _fixture(self, binds):
return Session(binds=binds)
def test_orm_query_classic_style_cols_only(n):
"""classic ORM query against columns"""
session = Session(bind=engine)
for id_ in random.sample(ids, n):
session.query(Customer.id, Customer.name,
Customer.description).filter(Customer.id == id_).one()
def stations():
session=Session(engine)
stations=session.query(Station.station, Station.name).all()
session.close()
all_stations=list(np.ravel(stations))
return jsonify(all_stations)
def _start_conn(self):
engine = db.create_engine('sqlite:///' + self.sqlitefn,
connect_args={'check_same_thread': False})
self.s = Session(bind=engine)
# Database Setup
#################################################
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///project.sqlite"
db = SQLAlchemy(app)
# reflect an existing database into a new model
Base = automap_base()
# reflect the tables
Base.prepare(db.engine, reflect=True)
# Save references to each table
#Forecast = Base.classes.forecast
DataSet = Base.classes.dataset
#Create a session
session = Session(db.engine)
@app.route("/")
def index():
"""Return the homepage."""
return render_template("index.html")
@app.route("/dashboard")
def comparison():
"""Return dashboard.html."""
return render_template("dashboard.html")
@app.route("/map")
def test_baseline(self):
# as of [ticket:2778], this is at 39025
A = self.classes.A
s = Session()
s.query(A).all()
def test_coerce_none(self):
sess = Session()
f1 = Foo(data=None)
sess.add(f1)
sess.commit()
eq_(f1.data, None)
def insert_data(cls):
A, B, C = cls.classes("A", "B", "C")
s = Session()
s.add(A(bs=[B(cs=[C()]), B(cs=[C()])]))
s.commit()
def qwat_import():
QWAT = get_qwat_model()
WASSER = get_wasser_model()
wasser_session = Session(utils.sqlalchemy.create_engine(), autocommit=False, autoflush=False)
qwat_session = Session(utils.sqlalchemy.create_engine(), autocommit=False, autoflush=False)
logger.info("Importing WASSER.hydraulischer_knoten -> QWAT.node")
for row in wasser_session.query(WASSER.hydraulischer_knoten):
# baseclass --- hydraulischer_knoten.t_type, hydraulischer_knoten.t_ili_tid
# sia405_baseclass --- hydraulischer_knoten.obj_id
# hydraulischer_knoten --- hydraulischer_knoten.t_id, hydraulischer_knoten.name_nummer, hydraulischer_knoten.geometrie, hydraulischer_knoten.knotentyp, hydraulischer_knoten.verbrauch, hydraulischer_knoten.druck, hydraulischer_knoten.bemerkung
# _bwrel_ --- hydraulischer_knoten.sia405_textpos__BWREL_hydraulischer_knotenref, hydraulischer_knoten.textpos__BWREL_t_id, hydraulischer_knoten.symbolpos__BWREL_t_id, hydraulischer_knoten.spezialbauwerk__BWREL_t_id, hydraulischer_knoten.metaattribute__BWREL_sia405_baseclass_metaattribute, hydraulischer_knoten.leitungsknoten__BWREL_knotenref, hydraulischer_knoten.hydraulischer_strang__BWREL_bisknotenref, hydraulischer_knoten.hydraulischer_strang__BWREL_vonknotenref
node = QWAT.node(
# --- node ---
# id=row.REPLACE_ME, # INTEGER
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _printmaps=row.REPLACE_ME, # TEXT
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# _pipe_node_type=row.REPLACE_ME, # qwat_od.pipe_connection
# _pipe_orientation=row.REPLACE_ME, # DOUBLE PRECISION
# _pipe_schema_visible=row.REPLACE_ME, # BOOLEAN
# geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(POINTZ,21781)
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
)
qwat_session.add(node)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.hydraulischer_strang, WASSER.leitung -> QWAT.pipe")
for row, leitung in wasser_session.query(WASSER.hydraulischer_strang, WASSER.leitung).join(WASSER.leitung):
# baseclass --- hydraulischer_strang.t_type, hydraulischer_strang.t_ili_tid
# sia405_baseclass --- hydraulischer_strang.obj_id
# hydraulischer_strang --- hydraulischer_strang.t_id, hydraulischer_strang.name_nummer, hydraulischer_strang.referenz_laenge, hydraulischer_strang.referenz_rauheit, hydraulischer_strang.referenz_durchmesser, hydraulischer_strang.verbrauch, hydraulischer_strang.durchfluss, hydraulischer_strang.fliessgeschwindigkeit, hydraulischer_strang.zustand, hydraulischer_strang.bemerkung, hydraulischer_strang.bisknotenref, hydraulischer_strang.vonknotenref
# _bwrel_ --- hydraulischer_strang.sia405_textpos__BWREL_hydraulischer_strangref, hydraulischer_strang.textpos__BWREL_t_id, hydraulischer_strang.symbolpos__BWREL_t_id, hydraulischer_strang.spezialbauwerk__BWREL_t_id, hydraulischer_strang.metaattribute__BWREL_sia405_baseclass_metaattribute, hydraulischer_strang.leitung__BWREL_strangref
# _rel_ --- hydraulischer_strang.bisknotenref__REL, hydraulischer_strang.vonknotenref__REL
# baseclass --- leitung.t_type, leitung.t_ili_tid
# sia405_baseclass --- leitung.obj_id
# leitung --- leitung.t_id, leitung.name_nummer, leitung.geometrie, leitung.funktion, leitung.material, leitung.durchmesserinnen, leitung.durchmesseraussen, leitung.durchmesser, leitung.nennweite, leitung.wasserqualitaet, leitung.lagebestimmung, leitung.astatus, leitung.baujahr, leitung.verbindungsart, leitung.aussenbeschichtung, leitung.innenbeschichtung, leitung.verlegeart, leitung.schubsicherung, leitung.ueberdeckung, leitung.sanierung_erneuerung, leitung.bettung, leitung.kathodischer_schutz, leitung.druckzone, leitung.zulaessiger_bauteil_betriebsdruck, leitung.betriebsdruck, leitung.hydraulische_rauheit, leitung.laenge, leitung.unterhalt, leitung.zustand, leitung.eigentuemer, leitung.betreiber, leitung.konzessionaer, leitung.unterhaltspflichtiger, leitung.bemerkung, leitung.strangref
# _bwrel_ --- leitung.sia405_textpos__BWREL_leitungref, leitung.textpos__BWREL_t_id, leitung.symbolpos__BWREL_t_id, leitung.spezialbauwerk__BWREL_t_id, leitung.metaattribute__BWREL_sia405_baseclass_metaattribute, leitung.schadenstelle__BWREL_leitungref
# _rel_ --- leitung.strangref__REL
pipe = QWAT.pipe(
# --- pipe ---
# id=row.REPLACE_ME, # INTEGER
# fk_parent=row.REPLACE_ME, # INTEGER
# fk_function=row.REPLACE_ME, # INTEGER
# fk_installmethod=row.REPLACE_ME, # INTEGER
# fk_material=row.REPLACE_ME, # INTEGER
# fk_distributor=row.REPLACE_ME, # INTEGER
# fk_precision=row.REPLACE_ME, # INTEGER
# fk_bedding=row.REPLACE_ME, # INTEGER
# fk_protection=row.REPLACE_ME, # INTEGER
# fk_status=row.REPLACE_ME, # INTEGER
# fk_watertype=row.REPLACE_ME, # INTEGER
# fk_locationtype=row.REPLACE_ME, # INTEGER[]
# fk_folder=row.REPLACE_ME, # INTEGER
# year=row.REPLACE_ME, # SMALLINT
# year_rehabilitation=row.REPLACE_ME, # SMALLINT
# year_end=row.REPLACE_ME, # SMALLINT
# tunnel_or_bridge=row.REPLACE_ME, # BOOLEAN
# pressure_nominal=row.REPLACE_ME, # SMALLINT
# remark=row.REPLACE_ME, # TEXT
# _valve_count=row.REPLACE_ME, # SMALLINT
# _valve_closed=row.REPLACE_ME, # BOOLEAN
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
# fk_node_a=row.REPLACE_ME, # INTEGER
# fk_node_b=row.REPLACE_ME, # INTEGER
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _length2d=row.REPLACE_ME, # NUMERIC(8, 2)
# _length3d=row.REPLACE_ME, # NUMERIC(8, 2)
# _diff_elevation=row.REPLACE_ME, # NUMERIC(8, 2)
# _printmaps=row.REPLACE_ME, # VARCHAR(100)
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
# geometry=row.REPLACE_ME, # geometry(LINESTRINGZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(LINESTRINGZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(LINESTRINGZ,21781)
# schema_force_visible=row.REPLACE_ME, # BOOLEAN
# _schema_visible=row.REPLACE_ME, # BOOLEAN
)
qwat_session.add(pipe)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.schadenstelle -> QWAT.leak")
for row in wasser_session.query(WASSER.schadenstelle):
# baseclass --- schadenstelle.t_type, schadenstelle.t_ili_tid
# sia405_baseclass --- schadenstelle.obj_id
# schadenstelle --- schadenstelle.t_id, schadenstelle.name_nummer, schadenstelle.geometrie, schadenstelle.art, schadenstelle.ursache, schadenstelle.ausloeser, schadenstelle.erhebungsdatum, schadenstelle.behebungsdatum, schadenstelle.zustand, schadenstelle.bemerkung, schadenstelle.leitungref
# _bwrel_ --- schadenstelle.textpos__BWREL_t_id, schadenstelle.symbolpos__BWREL_t_id, schadenstelle.spezialbauwerk__BWREL_t_id, schadenstelle.metaattribute__BWREL_sia405_baseclass_metaattribute
# _rel_ --- schadenstelle.leitungref__REL
leak = QWAT.leak(
# --- leak ---
# id=row.REPLACE_ME, # INTEGER
# fk_cause=row.REPLACE_ME, # INTEGER
# fk_pipe=row.REPLACE_ME, # INTEGER
# widespread_damage=row.REPLACE_ME, # BOOLEAN
# detection_date=row.REPLACE_ME, # DATE
# repair_date=row.REPLACE_ME, # DATE
# _repaired=row.REPLACE_ME, # BOOLEAN
# address=row.REPLACE_ME, # TEXT
# pipe_replaced=row.REPLACE_ME, # BOOLEAN
# description=row.REPLACE_ME, # TEXT
# repair=row.REPLACE_ME, # TEXT
# geometry=row.REPLACE_ME, # geometry(POINT,21781)
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
)
qwat_session.add(leak)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.hydrant -> QWAT.hydrant")
for row in wasser_session.query(WASSER.hydrant):
# baseclass --- hydrant.t_type, hydrant.t_ili_tid
# sia405_baseclass --- hydrant.obj_id
# leitungsknoten --- hydrant.geometrie, hydrant.symbolori, hydrant.lagebestimmung, hydrant.hoehe, hydrant.hoehenbestimmung, hydrant.einbaujahr, hydrant.druckzone, hydrant.eigentuemer, hydrant.bemerkung, hydrant.knotenref
# hydrant --- hydrant.t_id, hydrant.name_nummer, hydrant.art, hydrant.material, hydrant.dimension, hydrant.hersteller, hydrant.versorgungsdruck, hydrant.fliessdruck, hydrant.entnahme, hydrant.typ, hydrant.zustand
# _bwrel_ --- hydrant.sia405_textpos__BWREL_leitungsknotenref, hydrant.sia405_symbolpos__BWREL_objekt, hydrant.rohrleitungsteil__BWREL_t_id, hydrant.muffen__BWREL_t_id, hydrant.textpos__BWREL_t_id, hydrant.uebrige__BWREL_t_id, hydrant.symbolpos__BWREL_t_id, hydrant.spezialbauwerk__BWREL_t_id, hydrant.metaattribute__BWREL_sia405_baseclass_metaattribute
# _rel_ --- hydrant.knotenref__REL
hydrant = QWAT.hydrant(
# --- node ---
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _printmaps=row.REPLACE_ME, # TEXT
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# _pipe_node_type=row.REPLACE_ME, # qwat_od.pipe_connection
# _pipe_orientation=row.REPLACE_ME, # DOUBLE PRECISION
# _pipe_schema_visible=row.REPLACE_ME, # BOOLEAN
# geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(POINTZ,21781)
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
# --- network_element ---
# identification=row.REPLACE_ME, # VARCHAR(50)
# fk_distributor=row.REPLACE_ME, # INTEGER
# fk_status=row.REPLACE_ME, # INTEGER
# fk_folder=row.REPLACE_ME, # INTEGER
# fk_locationtype=row.REPLACE_ME, # INTEGER[]
# fk_precision=row.REPLACE_ME, # INTEGER
# fk_precisionalti=row.REPLACE_ME, # INTEGER
# fk_object_reference=row.REPLACE_ME, # INTEGER
# altitude=row.REPLACE_ME, # NUMERIC(10, 3)
# year=row.REPLACE_ME, # SMALLINT
# year_end=row.REPLACE_ME, # SMALLINT
# orientation=row.REPLACE_ME, # DOUBLE PRECISION
# remark=row.REPLACE_ME, # TEXT
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
# --- hydrant ---
# id=row.REPLACE_ME, # INTEGER
# fk_provider=row.REPLACE_ME, # INTEGER
# fk_model_sup=row.REPLACE_ME, # INTEGER
# fk_model_inf=row.REPLACE_ME, # INTEGER
# fk_material=row.REPLACE_ME, # INTEGER
# fk_output=row.REPLACE_ME, # INTEGER
# underground=row.REPLACE_ME, # BOOLEAN
# marked=row.REPLACE_ME, # BOOLEAN
# pressure_static=row.REPLACE_ME, # NUMERIC(5, 2)
# pressure_dynamic=row.REPLACE_ME, # NUMERIC(5, 2)
# flow=row.REPLACE_ME, # NUMERIC(8, 2)
# observation_date=row.REPLACE_ME, # DATE
# observation_source=row.REPLACE_ME, # VARCHAR(45)
)
qwat_session.add(hydrant)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.wasserbehaelter -> QWAT.tank")
for row in wasser_session.query(WASSER.wasserbehaelter):
# baseclass --- wasserbehaelter.t_type, wasserbehaelter.t_ili_tid
# sia405_baseclass --- wasserbehaelter.obj_id
# leitungsknoten --- wasserbehaelter.geometrie, wasserbehaelter.symbolori, wasserbehaelter.lagebestimmung, wasserbehaelter.hoehe, wasserbehaelter.hoehenbestimmung, wasserbehaelter.einbaujahr, wasserbehaelter.druckzone, wasserbehaelter.eigentuemer, wasserbehaelter.bemerkung, wasserbehaelter.knotenref
# wasserbehaelter --- wasserbehaelter.t_id, wasserbehaelter.name_nummer, wasserbehaelter.art, wasserbehaelter.material, wasserbehaelter.beschichtung, wasserbehaelter.ueberlaufhoehe, wasserbehaelter.fassungsvermoegen, wasserbehaelter.brauchwasserreserve, wasserbehaelter.loeschwasserreserve, wasserbehaelter.leistung, wasserbehaelter.zustand
# _bwrel_ --- wasserbehaelter.sia405_textpos__BWREL_leitungsknotenref, wasserbehaelter.sia405_symbolpos__BWREL_objekt, wasserbehaelter.rohrleitungsteil__BWREL_t_id, wasserbehaelter.muffen__BWREL_t_id, wasserbehaelter.textpos__BWREL_t_id, wasserbehaelter.uebrige__BWREL_t_id, wasserbehaelter.symbolpos__BWREL_t_id, wasserbehaelter.spezialbauwerk__BWREL_t_id, wasserbehaelter.metaattribute__BWREL_sia405_baseclass_metaattribute
# _rel_ --- wasserbehaelter.knotenref__REL
tank = QWAT.tank(
# --- node ---
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _printmaps=row.REPLACE_ME, # TEXT
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# _pipe_node_type=row.REPLACE_ME, # qwat_od.pipe_connection
# _pipe_orientation=row.REPLACE_ME, # DOUBLE PRECISION
# _pipe_schema_visible=row.REPLACE_ME, # BOOLEAN
# geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(POINTZ,21781)
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
# --- network_element ---
# identification=row.REPLACE_ME, # VARCHAR(50)
# fk_distributor=row.REPLACE_ME, # INTEGER
# fk_status=row.REPLACE_ME, # INTEGER
# fk_folder=row.REPLACE_ME, # INTEGER
# fk_locationtype=row.REPLACE_ME, # INTEGER[]
# fk_precision=row.REPLACE_ME, # INTEGER
# fk_precisionalti=row.REPLACE_ME, # INTEGER
# fk_object_reference=row.REPLACE_ME, # INTEGER
# altitude=row.REPLACE_ME, # NUMERIC(10, 3)
# year=row.REPLACE_ME, # SMALLINT
# year_end=row.REPLACE_ME, # SMALLINT
# orientation=row.REPLACE_ME, # DOUBLE PRECISION
# remark=row.REPLACE_ME, # TEXT
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
# --- installation ---
# name=row.REPLACE_ME, # VARCHAR(60)
# fk_parent=row.REPLACE_ME, # INTEGER
# fk_remote=row.REPLACE_ME, # INTEGER
# fk_watertype=row.REPLACE_ME, # INTEGER
# parcel=row.REPLACE_ME, # VARCHAR(30)
# eca=row.REPLACE_ME, # VARCHAR(30)
# open_water_surface=row.REPLACE_ME, # BOOLEAN
# geometry_polygon=row.REPLACE_ME, # geometry(MULTIPOLYGON,21781)
# --- tank ---
# id=row.REPLACE_ME, # INTEGER
# fk_overflow=row.REPLACE_ME, # INTEGER
# fk_tank_firestorage=row.REPLACE_ME, # INTEGER
# storage_total=row.REPLACE_ME, # NUMERIC(10, 1)
# storage_supply=row.REPLACE_ME, # NUMERIC(10, 1)
# storage_fire=row.REPLACE_ME, # NUMERIC(10, 1)
# altitude_overflow=row.REPLACE_ME, # NUMERIC(7, 3)
# altitude_apron=row.REPLACE_ME, # NUMERIC(7, 3)
# height_max=row.REPLACE_ME, # NUMERIC(7, 3)
# fire_valve=row.REPLACE_ME, # BOOLEAN
# fire_remote=row.REPLACE_ME, # BOOLEAN
# _litrepercm=row.REPLACE_ME, # NUMERIC(9, 3)
# cistern1_fk_type=row.REPLACE_ME, # INTEGER
# cistern1_dimension_1=row.REPLACE_ME, # NUMERIC(10, 2)
# cistern1_dimension_2=row.REPLACE_ME, # NUMERIC(10, 2)
# cistern1_storage=row.REPLACE_ME, # NUMERIC(10, 2)
# _cistern1_litrepercm=row.REPLACE_ME, # NUMERIC(9, 3)
# cistern2_fk_type=row.REPLACE_ME, # INTEGER
# cistern2_dimension_1=row.REPLACE_ME, # NUMERIC(10, 2)
# cistern2_dimension_2=row.REPLACE_ME, # NUMERIC(10, 2)
# cistern2_storage=row.REPLACE_ME, # NUMERIC(10, 2)
# _cistern2_litrepercm=row.REPLACE_ME, # NUMERIC(9, 3)
)
qwat_session.add(tank)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.foerderanlage -> QWAT.pump")
for row in wasser_session.query(WASSER.foerderanlage):
# baseclass --- foerderanlage.t_type, foerderanlage.t_ili_tid
# sia405_baseclass --- foerderanlage.obj_id
# leitungsknoten --- foerderanlage.geometrie, foerderanlage.symbolori, foerderanlage.lagebestimmung, foerderanlage.hoehe, foerderanlage.hoehenbestimmung, foerderanlage.einbaujahr, foerderanlage.druckzone, foerderanlage.eigentuemer, foerderanlage.bemerkung, foerderanlage.knotenref
# foerderanlage --- foerderanlage.t_id, foerderanlage.name_nummer, foerderanlage.art, foerderanlage.leistung, foerderanlage.zustand
# _bwrel_ --- foerderanlage.sia405_textpos__BWREL_leitungsknotenref, foerderanlage.sia405_symbolpos__BWREL_objekt, foerderanlage.rohrleitungsteil__BWREL_t_id, foerderanlage.muffen__BWREL_t_id, foerderanlage.textpos__BWREL_t_id, foerderanlage.uebrige__BWREL_t_id, foerderanlage.symbolpos__BWREL_t_id, foerderanlage.spezialbauwerk__BWREL_t_id, foerderanlage.metaattribute__BWREL_sia405_baseclass_metaattribute
# _rel_ --- foerderanlage.knotenref__REL
pump = QWAT.pump(
# --- node ---
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _printmaps=row.REPLACE_ME, # TEXT
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# _pipe_node_type=row.REPLACE_ME, # qwat_od.pipe_connection
# _pipe_orientation=row.REPLACE_ME, # DOUBLE PRECISION
# _pipe_schema_visible=row.REPLACE_ME, # BOOLEAN
# geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(POINTZ,21781)
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
# --- network_element ---
# identification=row.REPLACE_ME, # VARCHAR(50)
# fk_distributor=row.REPLACE_ME, # INTEGER
# fk_status=row.REPLACE_ME, # INTEGER
# fk_folder=row.REPLACE_ME, # INTEGER
# fk_locationtype=row.REPLACE_ME, # INTEGER[]
# fk_precision=row.REPLACE_ME, # INTEGER
# fk_precisionalti=row.REPLACE_ME, # INTEGER
# fk_object_reference=row.REPLACE_ME, # INTEGER
# altitude=row.REPLACE_ME, # NUMERIC(10, 3)
# year=row.REPLACE_ME, # SMALLINT
# year_end=row.REPLACE_ME, # SMALLINT
# orientation=row.REPLACE_ME, # DOUBLE PRECISION
# remark=row.REPLACE_ME, # TEXT
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
# --- installation ---
# name=row.REPLACE_ME, # VARCHAR(60)
# fk_parent=row.REPLACE_ME, # INTEGER
# fk_remote=row.REPLACE_ME, # INTEGER
# fk_watertype=row.REPLACE_ME, # INTEGER
# parcel=row.REPLACE_ME, # VARCHAR(30)
# eca=row.REPLACE_ME, # VARCHAR(30)
# open_water_surface=row.REPLACE_ME, # BOOLEAN
# geometry_polygon=row.REPLACE_ME, # geometry(MULTIPOLYGON,21781)
# --- pump ---
# id=row.REPLACE_ME, # INTEGER
# fk_pump_type=row.REPLACE_ME, # SMALLINT
# fk_pipe_in=row.REPLACE_ME, # INTEGER
# fk_pipe_out=row.REPLACE_ME, # INTEGER
# fk_pump_operating=row.REPLACE_ME, # SMALLINT
# no_pumps=row.REPLACE_ME, # SMALLINT
# rejected_flow=row.REPLACE_ME, # NUMERIC(10, 2)
# manometric_height=row.REPLACE_ME, # NUMERIC(10, 2)
)
qwat_session.add(pump)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.anlage -> QWAT.treatment")
for row in wasser_session.query(WASSER.anlage):
# baseclass --- anlage.t_type, anlage.t_ili_tid
# sia405_baseclass --- anlage.obj_id
# leitungsknoten --- anlage.geometrie, anlage.symbolori, anlage.lagebestimmung, anlage.hoehe, anlage.hoehenbestimmung, anlage.einbaujahr, anlage.druckzone, anlage.eigentuemer, anlage.bemerkung, anlage.knotenref
# anlage --- anlage.t_id, anlage.name_nummer, anlage.art, anlage.material, anlage.leistung, anlage.betreiber, anlage.konzessionaer, anlage.unterhaltspflichtiger, anlage.zustand, anlage.dimension1
# _bwrel_ --- anlage.sia405_textpos__BWREL_leitungsknotenref, anlage.sia405_symbolpos__BWREL_objekt, anlage.rohrleitungsteil__BWREL_t_id, anlage.muffen__BWREL_t_id, anlage.textpos__BWREL_t_id, anlage.uebrige__BWREL_t_id, anlage.symbolpos__BWREL_t_id, anlage.spezialbauwerk__BWREL_t_id, anlage.metaattribute__BWREL_sia405_baseclass_metaattribute
# _rel_ --- anlage.knotenref__REL
treatment = QWAT.treatment(
# --- node ---
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _printmaps=row.REPLACE_ME, # TEXT
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# _pipe_node_type=row.REPLACE_ME, # qwat_od.pipe_connection
# _pipe_orientation=row.REPLACE_ME, # DOUBLE PRECISION
# _pipe_schema_visible=row.REPLACE_ME, # BOOLEAN
# geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(POINTZ,21781)
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
# --- network_element ---
# identification=row.REPLACE_ME, # VARCHAR(50)
# fk_distributor=row.REPLACE_ME, # INTEGER
# fk_status=row.REPLACE_ME, # INTEGER
# fk_folder=row.REPLACE_ME, # INTEGER
# fk_locationtype=row.REPLACE_ME, # INTEGER[]
# fk_precision=row.REPLACE_ME, # INTEGER
# fk_precisionalti=row.REPLACE_ME, # INTEGER
# fk_object_reference=row.REPLACE_ME, # INTEGER
# altitude=row.REPLACE_ME, # NUMERIC(10, 3)
# year=row.REPLACE_ME, # SMALLINT
# year_end=row.REPLACE_ME, # SMALLINT
# orientation=row.REPLACE_ME, # DOUBLE PRECISION
# remark=row.REPLACE_ME, # TEXT
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
# --- installation ---
# name=row.REPLACE_ME, # VARCHAR(60)
# fk_parent=row.REPLACE_ME, # INTEGER
# fk_remote=row.REPLACE_ME, # INTEGER
# fk_watertype=row.REPLACE_ME, # INTEGER
# parcel=row.REPLACE_ME, # VARCHAR(30)
# eca=row.REPLACE_ME, # VARCHAR(30)
# open_water_surface=row.REPLACE_ME, # BOOLEAN
# geometry_polygon=row.REPLACE_ME, # geometry(MULTIPOLYGON,21781)
# --- treatment ---
# id=row.REPLACE_ME, # INTEGER
# sanitization_uv=row.REPLACE_ME, # BOOLEAN
# sanitization_chlorine_liquid=row.REPLACE_ME, # BOOLEAN
# sanitization_chlorine_gas=row.REPLACE_ME, # BOOLEAN
# sanitization_ozone=row.REPLACE_ME, # BOOLEAN
# filtration_membrane=row.REPLACE_ME, # BOOLEAN
# filtration_sandorgravel=row.REPLACE_ME, # BOOLEAN
# flocculation=row.REPLACE_ME, # BOOLEAN
# activatedcharcoal=row.REPLACE_ME, # BOOLEAN
# settling=row.REPLACE_ME, # BOOLEAN
# treatment_capacity=row.REPLACE_ME, # NUMERIC(10, 2)
)
qwat_session.add(treatment)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.hausanschluss -> QWAT.subscriber")
for row in wasser_session.query(WASSER.hausanschluss):
# baseclass --- hausanschluss.t_type, hausanschluss.t_ili_tid
# sia405_baseclass --- hausanschluss.obj_id
# leitungsknoten --- hausanschluss.geometrie, hausanschluss.symbolori, hausanschluss.lagebestimmung, hausanschluss.hoehe, hausanschluss.hoehenbestimmung, hausanschluss.einbaujahr, hausanschluss.druckzone, hausanschluss.eigentuemer, hausanschluss.bemerkung, hausanschluss.knotenref
# hausanschluss --- hausanschluss.t_id, hausanschluss.name_nummer, hausanschluss.standort, hausanschluss.art, hausanschluss.gebaeudeanschluss, hausanschluss.isolierstueck, hausanschluss.typ, hausanschluss.dimension, hausanschluss.zuordnung_hydraulischer_strang, hausanschluss.zuordnung_hydraulischer_knoten, hausanschluss.verbrauch, hausanschluss.zustand
# _bwrel_ --- hausanschluss.sia405_textpos__BWREL_leitungsknotenref, hausanschluss.sia405_symbolpos__BWREL_objekt, hausanschluss.rohrleitungsteil__BWREL_t_id, hausanschluss.muffen__BWREL_t_id, hausanschluss.textpos__BWREL_t_id, hausanschluss.uebrige__BWREL_t_id, hausanschluss.symbolpos__BWREL_t_id, hausanschluss.spezialbauwerk__BWREL_t_id, hausanschluss.metaattribute__BWREL_sia405_baseclass_metaattribute
# _rel_ --- hausanschluss.knotenref__REL
subscriber = QWAT.subscriber(
# --- node ---
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _printmaps=row.REPLACE_ME, # TEXT
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# _pipe_node_type=row.REPLACE_ME, # qwat_od.pipe_connection
# _pipe_orientation=row.REPLACE_ME, # DOUBLE PRECISION
# _pipe_schema_visible=row.REPLACE_ME, # BOOLEAN
# geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(POINTZ,21781)
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
# --- network_element ---
# identification=row.REPLACE_ME, # VARCHAR(50)
# fk_distributor=row.REPLACE_ME, # INTEGER
# fk_status=row.REPLACE_ME, # INTEGER
# fk_folder=row.REPLACE_ME, # INTEGER
# fk_locationtype=row.REPLACE_ME, # INTEGER[]
# fk_precision=row.REPLACE_ME, # INTEGER
# fk_precisionalti=row.REPLACE_ME, # INTEGER
# fk_object_reference=row.REPLACE_ME, # INTEGER
# altitude=row.REPLACE_ME, # NUMERIC(10, 3)
# year=row.REPLACE_ME, # SMALLINT
# year_end=row.REPLACE_ME, # SMALLINT
# orientation=row.REPLACE_ME, # DOUBLE PRECISION
# remark=row.REPLACE_ME, # TEXT
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
# --- subscriber ---
# id=row.REPLACE_ME, # INTEGER
# fk_subscriber_type=row.REPLACE_ME, # INTEGER
# fk_pipe=row.REPLACE_ME, # INTEGER
# parcel=row.REPLACE_ME, # VARCHAR(12)
# flow_current=row.REPLACE_ME, # NUMERIC(8, 2)
# flow_planned=row.REPLACE_ME, # NUMERIC(8, 2)
)
qwat_session.add(subscriber)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.wassergewinnungsanlage -> QWAT.source")
for row in wasser_session.query(WASSER.wassergewinnungsanlage):
# baseclass --- wassergewinnungsanlage.t_type, wassergewinnungsanlage.t_ili_tid
# sia405_baseclass --- wassergewinnungsanlage.obj_id
# leitungsknoten --- wassergewinnungsanlage.geometrie, wassergewinnungsanlage.symbolori, wassergewinnungsanlage.lagebestimmung, wassergewinnungsanlage.hoehe, wassergewinnungsanlage.hoehenbestimmung, wassergewinnungsanlage.einbaujahr, wassergewinnungsanlage.druckzone, wassergewinnungsanlage.eigentuemer, wassergewinnungsanlage.bemerkung, wassergewinnungsanlage.knotenref
# wassergewinnungsanlage --- wassergewinnungsanlage.t_id, wassergewinnungsanlage.name_nummer, wassergewinnungsanlage.art, wassergewinnungsanlage.leistung, wassergewinnungsanlage.betreiber, wassergewinnungsanlage.konzessionaer, wassergewinnungsanlage.unterhaltspflichtiger, wassergewinnungsanlage.zustand
# _bwrel_ --- wassergewinnungsanlage.sia405_textpos__BWREL_leitungsknotenref, wassergewinnungsanlage.sia405_symbolpos__BWREL_objekt, wassergewinnungsanlage.rohrleitungsteil__BWREL_t_id, wassergewinnungsanlage.muffen__BWREL_t_id, wassergewinnungsanlage.textpos__BWREL_t_id, wassergewinnungsanlage.uebrige__BWREL_t_id, wassergewinnungsanlage.symbolpos__BWREL_t_id, wassergewinnungsanlage.spezialbauwerk__BWREL_t_id, wassergewinnungsanlage.metaattribute__BWREL_sia405_baseclass_metaattribute
# _rel_ --- wassergewinnungsanlage.knotenref__REL
source = QWAT.source(
# --- node ---
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _printmaps=row.REPLACE_ME, # TEXT
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# _pipe_node_type=row.REPLACE_ME, # qwat_od.pipe_connection
# _pipe_orientation=row.REPLACE_ME, # DOUBLE PRECISION
# _pipe_schema_visible=row.REPLACE_ME, # BOOLEAN
# geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(POINTZ,21781)
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
# --- network_element ---
# identification=row.REPLACE_ME, # VARCHAR(50)
# fk_distributor=row.REPLACE_ME, # INTEGER
# fk_status=row.REPLACE_ME, # INTEGER
# fk_folder=row.REPLACE_ME, # INTEGER
# fk_locationtype=row.REPLACE_ME, # INTEGER[]
# fk_precision=row.REPLACE_ME, # INTEGER
# fk_precisionalti=row.REPLACE_ME, # INTEGER
# fk_object_reference=row.REPLACE_ME, # INTEGER
# altitude=row.REPLACE_ME, # NUMERIC(10, 3)
# year=row.REPLACE_ME, # SMALLINT
# year_end=row.REPLACE_ME, # SMALLINT
# orientation=row.REPLACE_ME, # DOUBLE PRECISION
# remark=row.REPLACE_ME, # TEXT
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
# --- installation ---
# name=row.REPLACE_ME, # VARCHAR(60)
# fk_parent=row.REPLACE_ME, # INTEGER
# fk_remote=row.REPLACE_ME, # INTEGER
# fk_watertype=row.REPLACE_ME, # INTEGER
# parcel=row.REPLACE_ME, # VARCHAR(30)
# eca=row.REPLACE_ME, # VARCHAR(30)
# open_water_surface=row.REPLACE_ME, # BOOLEAN
# geometry_polygon=row.REPLACE_ME, # geometry(MULTIPOLYGON,21781)
# --- source ---
# id=row.REPLACE_ME, # INTEGER
# fk_source_type=row.REPLACE_ME, # SMALLINT
# fk_source_quality=row.REPLACE_ME, # SMALLINT
# flow_lowest=row.REPLACE_ME, # NUMERIC(10, 3)
# flow_average=row.REPLACE_ME, # NUMERIC(10, 3)
# flow_concession=row.REPLACE_ME, # NUMERIC(10, 2)
# contract_end=row.REPLACE_ME, # DATE
# gathering_chamber=row.REPLACE_ME, # BOOLEAN
)
qwat_session.add(source)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.absperrorgan -> QWAT.chamber")
for row in wasser_session.query(WASSER.absperrorgan):
# baseclass --- absperrorgan.t_type, absperrorgan.t_ili_tid
# sia405_baseclass --- absperrorgan.obj_id
# leitungsknoten --- absperrorgan.geometrie, absperrorgan.symbolori, absperrorgan.lagebestimmung, absperrorgan.hoehe, absperrorgan.hoehenbestimmung, absperrorgan.einbaujahr, absperrorgan.druckzone, absperrorgan.eigentuemer, absperrorgan.bemerkung, absperrorgan.knotenref
# absperrorgan --- absperrorgan.t_id, absperrorgan.name_nummer, absperrorgan.art, absperrorgan.schaltzustand, absperrorgan.schaltantrieb, absperrorgan.material, absperrorgan.zulaessiger_bauteil_betriebsdruck, absperrorgan.nennweite, absperrorgan.hersteller, absperrorgan.typ, absperrorgan.schliessrichtung, absperrorgan.zustand
# _bwrel_ --- absperrorgan.sia405_textpos__BWREL_leitungsknotenref, absperrorgan.sia405_symbolpos__BWREL_objekt, absperrorgan.rohrleitungsteil__BWREL_t_id, absperrorgan.muffen__BWREL_t_id, absperrorgan.textpos__BWREL_t_id, absperrorgan.uebrige__BWREL_t_id, absperrorgan.symbolpos__BWREL_t_id, absperrorgan.spezialbauwerk__BWREL_t_id, absperrorgan.metaattribute__BWREL_sia405_baseclass_metaattribute
# _rel_ --- absperrorgan.knotenref__REL
chamber = QWAT.chamber(
# --- node ---
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _printmaps=row.REPLACE_ME, # TEXT
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# _pipe_node_type=row.REPLACE_ME, # qwat_od.pipe_connection
# _pipe_orientation=row.REPLACE_ME, # DOUBLE PRECISION
# _pipe_schema_visible=row.REPLACE_ME, # BOOLEAN
# geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(POINTZ,21781)
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
# --- network_element ---
# identification=row.REPLACE_ME, # VARCHAR(50)
# fk_distributor=row.REPLACE_ME, # INTEGER
# fk_status=row.REPLACE_ME, # INTEGER
# fk_folder=row.REPLACE_ME, # INTEGER
# fk_locationtype=row.REPLACE_ME, # INTEGER[]
# fk_precision=row.REPLACE_ME, # INTEGER
# fk_precisionalti=row.REPLACE_ME, # INTEGER
# fk_object_reference=row.REPLACE_ME, # INTEGER
# altitude=row.REPLACE_ME, # NUMERIC(10, 3)
# year=row.REPLACE_ME, # SMALLINT
# year_end=row.REPLACE_ME, # SMALLINT
# orientation=row.REPLACE_ME, # DOUBLE PRECISION
# remark=row.REPLACE_ME, # TEXT
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
# --- installation ---
# name=row.REPLACE_ME, # VARCHAR(60)
# fk_parent=row.REPLACE_ME, # INTEGER
# fk_remote=row.REPLACE_ME, # INTEGER
# fk_watertype=row.REPLACE_ME, # INTEGER
# parcel=row.REPLACE_ME, # VARCHAR(30)
# eca=row.REPLACE_ME, # VARCHAR(30)
# open_water_surface=row.REPLACE_ME, # BOOLEAN
# geometry_polygon=row.REPLACE_ME, # geometry(MULTIPOLYGON,21781)
# --- chamber ---
# id=row.REPLACE_ME, # INTEGER
# networkseparation=row.REPLACE_ME, # BOOLEAN
# flow_meter=row.REPLACE_ME, # BOOLEAN
# water_meter=row.REPLACE_ME, # BOOLEAN
# manometer=row.REPLACE_ME, # BOOLEAN
# depth=row.REPLACE_ME, # NUMERIC(10, 3)
# no_valves=row.REPLACE_ME, # SMALLINT
)
qwat_session.add(chamber)
print(".", end="")
logger.info("done")
logger.info("Importing WASSER.absperrorgan -> QWAT.valve")
for row in wasser_session.query(WASSER.absperrorgan):
# baseclass --- absperrorgan.t_type, absperrorgan.t_ili_tid
# sia405_baseclass --- absperrorgan.obj_id
# leitungsknoten --- absperrorgan.geometrie, absperrorgan.symbolori, absperrorgan.lagebestimmung, absperrorgan.hoehe, absperrorgan.hoehenbestimmung, absperrorgan.einbaujahr, absperrorgan.druckzone, absperrorgan.eigentuemer, absperrorgan.bemerkung, absperrorgan.knotenref
# absperrorgan --- absperrorgan.t_id, absperrorgan.name_nummer, absperrorgan.art, absperrorgan.schaltzustand, absperrorgan.schaltantrieb, absperrorgan.material, absperrorgan.zulaessiger_bauteil_betriebsdruck, absperrorgan.nennweite, absperrorgan.hersteller, absperrorgan.typ, absperrorgan.schliessrichtung, absperrorgan.zustand
# _bwrel_ --- absperrorgan.sia405_textpos__BWREL_leitungsknotenref, absperrorgan.sia405_symbolpos__BWREL_objekt, absperrorgan.rohrleitungsteil__BWREL_t_id, absperrorgan.muffen__BWREL_t_id, absperrorgan.textpos__BWREL_t_id, absperrorgan.uebrige__BWREL_t_id, absperrorgan.symbolpos__BWREL_t_id, absperrorgan.spezialbauwerk__BWREL_t_id, absperrorgan.metaattribute__BWREL_sia405_baseclass_metaattribute
# _rel_ --- absperrorgan.knotenref__REL
valve = QWAT.valve(
# --- valve ---
# id=row.REPLACE_ME, # INTEGER
# fk_valve_type=row.REPLACE_ME, # INTEGER
# fk_valve_function=row.REPLACE_ME, # INTEGER
# fk_valve_actuation=row.REPLACE_ME, # INTEGER
# fk_pipe=row.REPLACE_ME, # INTEGER
# fk_handle_precision=row.REPLACE_ME, # INTEGER
# fk_handle_precisionalti=row.REPLACE_ME, # INTEGER
# fk_maintenance=row.REPLACE_ME, # INTEGER[]
# closed=row.REPLACE_ME, # BOOLEAN
# networkseparation=row.REPLACE_ME, # BOOLEAN
# handle_altitude=row.REPLACE_ME, # NUMERIC(10, 3)
# handle_geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# fk_district=row.REPLACE_ME, # INTEGER
# fk_pressurezone=row.REPLACE_ME, # INTEGER
# fk_distributor=row.REPLACE_ME, # INTEGER
# fk_precision=row.REPLACE_ME, # INTEGER
# fk_precisionalti=row.REPLACE_ME, # INTEGER
# fk_status=row.REPLACE_ME, # INTEGER
# fk_object_reference=row.REPLACE_ME, # INTEGER
# fk_folder=row.REPLACE_ME, # INTEGER
# year=row.REPLACE_ME, # SMALLINT
# year_end=row.REPLACE_ME, # SMALLINT
# altitude=row.REPLACE_ME, # NUMERIC(10, 3)
# orientation=row.REPLACE_ME, # DOUBLE PRECISION
# fk_locationtype=row.REPLACE_ME, # INTEGER[]
# identification=row.REPLACE_ME, # VARCHAR(50)
# remark=row.REPLACE_ME, # TEXT
# fk_printmap=row.REPLACE_ME, # INTEGER[]
# _geometry_alt1_used=row.REPLACE_ME, # BOOLEAN
# _geometry_alt2_used=row.REPLACE_ME, # BOOLEAN
# _pipe_node_type=row.REPLACE_ME, # qwat_od.pipe_connection
# _pipe_orientation=row.REPLACE_ME, # DOUBLE PRECISION
# _pipe_schema_visible=row.REPLACE_ME, # BOOLEAN
# _printmaps=row.REPLACE_ME, # TEXT
# geometry=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt1=row.REPLACE_ME, # geometry(POINTZ,21781)
# geometry_alt2=row.REPLACE_ME, # geometry(POINTZ,21781)
# update_geometry_alt1=row.REPLACE_ME, # BOOLEAN
# update_geometry_alt2=row.REPLACE_ME, # BOOLEAN
# label_1_visible=row.REPLACE_ME, # SMALLINT
# label_1_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_1_text=row.REPLACE_ME, # VARCHAR(120)
# label_2_visible=row.REPLACE_ME, # SMALLINT
# label_2_x=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_y=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_rotation=row.REPLACE_ME, # DOUBLE PRECISION
# label_2_text=row.REPLACE_ME, # VARCHAR(120)
# schema_force_visible=row.REPLACE_ME, # BOOLEAN
# _schema_visible=row.REPLACE_ME, # BOOLEAN
# fk_nominal_diameter=row.REPLACE_ME, # INTEGER
)
qwat_session.add(valve)
print(".", end="")
logger.info("done")
qwat_session.commit()
qwat_session.close()
wasser_session.close()
def insert_data(cls):
A = cls.classes.A
s = Session()
s.add_all([A(data="asdf") for i in range(5)])
s.commit()
def _fixture(self):
Foo, version_table = self.classes.Foo, self.tables.version_table
mapper(Foo, version_table, version_id_col=version_table.c.version_id)
s1 = Session()
return s1
def ieplot(period):
session = Session(bind=engine)
inc_key = []
inc_val = []
exp_key = []
exp_val = []
if period == "annual":
income = session.query(func.round(func.avg(IncomeInfo.income_amt)),
func.year(IncomeInfo.income_date),
func.max(IncomeInfo.income_date.cast(Date))).\
group_by(func.year(IncomeInfo.income_date))
expenses = session.query(func.round(func.sum(ExpenseInfo.expense_amt)),
func.year(ExpenseInfo.transaction_date),
func.max(ExpenseInfo.transaction_date.cast(Date))).\
filter(ExpenseInfo.transaction_date > "1900-01-01").\
group_by(func.year(ExpenseInfo.transaction_date))
for i in income.all():
inc_key.append(str(i[2].year))
inc_val.append(str(i[0]))
for e in expenses.all():
exp_key.append(str(e[2].year))
exp_val.append(str(e[0]))
if period == "monthly":
income = session.query(func.round(func.avg(IncomeInfo.income_amt)),
func.year(IncomeInfo.income_date),
func.month(IncomeInfo.income_date),
func.max(IncomeInfo.income_date.cast(Date))).\
group_by(func.year(IncomeInfo.income_date),
func.month(IncomeInfo.income_date)).\
order_by(func.max(IncomeInfo.income_date.cast(Date)))
expenses = session.query(func.round(func.sum(ExpenseInfo.expense_amt)),
func.year(ExpenseInfo.transaction_date),
func.month(ExpenseInfo.transaction_date),
func.max(ExpenseInfo.transaction_date.cast(Date))).\
group_by(func.year(ExpenseInfo.transaction_date),
func.month(ExpenseInfo.transaction_date)).\
order_by(func.max(ExpenseInfo.transaction_date.cast(Date)))
for i in income.all():
inc_key.append(str(i[3]))
inc_val.append(str(i[0]/12))
for e in expenses.all():
exp_key.append(str(e[3]))
exp_val.append(str(e[0]))
data = {
"inc_key": inc_key,
"inc_val": inc_val,
"exp_key": exp_key,
"exp_val": exp_val
}
session.close()
return jsonify(data)
def setUp(self):
self.session = Session(engine)
self.Base = declarative_base()
versioned_session(self.session)
def transactions():
session = Session(bind=engine)
cur = session.execute("select DISTINCT new_category FROM categories ORDER BY new_category")
data = cur.fetchall()
session.close()
return render_template("transactions.html", data=data)
def weather(start_date):
session = Session(engine)
results = session.query(Measurement.date, func.min(Measurement.tobs), func.max(Measurement.tobs), func.avg(Measurement.tobs)).\
filter(Measurement.date >= start_date).group_by(Measurement.date).all()
session.close()
return (jsonify(results))
import unittest
import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import Query, Session
from sqlalchemy_mixins import ActiveRecordMixin
from sqlalchemy_mixins.activerecord import ModelNotFoundError
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=False)
sess = Session(engine)
# sess = scoped_session(sessionmaker(bind=engine))
class BaseModel(Base, ActiveRecordMixin):
__abstract__ = True
pass
class User(BaseModel):
__tablename__ = 'user'
__repr_attrs__ = ['name']
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)
posts = sa.orm.relationship('Post', backref='user')
posts_viewonly = sa.orm.relationship('Post', viewonly=True)
from sqlalchemy.orm import Session
from sqlalchemy import create_engine, func
#Set up database connection
engine = create_engine("sqlite:///Resources/hawaii.sqlite")
#reflect Database
Base = automap_base()
Base.prepare(engine, reflect=True)
#save to table
Measurement = Base.classes.measurement
Station = Base.classes.station
#Link session to DB
session = Session(bind=engine)
#Route /
@app.route('/')
def welcome():
"""List of available api routes."""
return (
f"Welcome<br>"
f"Options Available:<br>"
f"/api/v1.0/precipitation<br>"
f"/api/v1.0/stations<br>"
f"/api/v1.0/tobs<br>"
f"/api/v1.0/start Replace <start> with date in format YYYY,M,D <br>"
f"/api/v1.0/start/end Replace <start> and <end> with date in format YYYY,M,D <br>"
class InvestingVo:
id: int = 0
date: str = ''
ticker: str = ''
link: str = ''
headline: str = ''
neg: float = 0.0
pos: float = 0.0
neu: float = 0.0
compound: float = 0.0
Session = openSession()
session = Session()
class InvestingDao(InvestingDto):
@staticmethod
def count():
return session.query(func.count(InvestingDto.id)).one()
@classmethod
def find_all(cls):
return cls.query.all()
@classmethod
def find_by_date(cls, date):
return cls.query.filer_by(date == date).all()
def stations():
session = Session(engine)
results = session.query(Station.station).all()
session.close()
res=list(np.ravel(results))
return jsonify(res)
def test_update_with_sess(code):
engine = dbu.get_engine()
with Session(engine) as db_sess:
db_sess.query(StockInfo).filter_by(code=code).update(
{"last_update_date": "2021-07-09"})
db_sess.commit()
