275 lines
9.8 KiB
Python
275 lines
9.8 KiB
Python
'''
|
|
Tutorial link: https://docs.sqlalchemy.org/en/latest/orm/tutorial.html
|
|
Sqlalchemy version: 1.4.31
|
|
Python version: 3.10
|
|
'''
|
|
#!/usr/bin/python# -*- coding: utf-8 -*-
|
|
|
|
from datetime import datetime
|
|
# from config import db, ma
|
|
|
|
import os
|
|
from sqlalchemy import (Column, Integer, Sequence,
|
|
String, DateTime, ForeignKey, Numeric, SmallInteger, create_engine)
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import session, relationship, sessionmaker
|
|
# from geoalchemy2 import Geometry
|
|
from geoalchemy2.types import Geometry
|
|
#from marshmallow import Schema
|
|
from marshmallow_sqlalchemy import SQLAlchemySchema, SQLAlchemyAutoSchema
|
|
from marshmallow import fields
|
|
from dotenv import load_dotenv, find_dotenv
|
|
# from db.pg_models import create_pg_session
|
|
# import sqlalchemy.orm.session
|
|
|
|
Base = declarative_base()
|
|
|
|
|
|
def create_pg_session() -> sessionmaker:
|
|
""" create postgres db session """
|
|
load_dotenv(find_dotenv())
|
|
dbschema = 'gba'
|
|
db_user = os.environ.get("POSTGIS_DBUSER")
|
|
db_password = os.environ.get("POSTGIS_DBPASSWORD")
|
|
db_url = os.environ.get("POSTGIS_DBURL")
|
|
engine = create_engine(
|
|
"postgresql+psycopg2://" + db_user + ":" + db_password + "@" + db_url,
|
|
# connect_args={'options': f'-csearch_path={dbschema}'},
|
|
isolation_level="READ UNCOMMITTED")
|
|
session_maker = sessionmaker(bind=engine)
|
|
_session = session_maker()
|
|
|
|
# Base.metadata.create_all(engine)
|
|
return _session
|
|
|
|
platform_seq = Sequence('platform_seq', schema="gba") # define sequence explicitly
|
|
class Platform(Base):
|
|
""" Platform class """
|
|
__tablename__ = 'platform'
|
|
__table_args__ = {"schema": "gba"}
|
|
|
|
# id = Column('platform_id', Integer, primary_key=True)
|
|
id = Column('platform_id',
|
|
Integer,
|
|
platform_seq,
|
|
primary_key=True,
|
|
server_default=platform_seq.next_value())
|
|
identifier = Column('identifier', String)
|
|
sta_identifier = Column('sta_identifier', String)
|
|
name = Column('name', String)
|
|
# datasets = relationship('Dataset')
|
|
datasets = relationship('Dataset', back_populates="platform", lazy=True)
|
|
|
|
def __repr__(self):
|
|
return f'Platform {self.name}'
|
|
|
|
|
|
class Phenomenon(Base):
|
|
""" phenomenon class """
|
|
__tablename__ = 'phenomenon'
|
|
__table_args__ = {"schema": "gba"}
|
|
|
|
id = Column('phenomenon_id', Integer, primary_key=True)
|
|
name = Column('name', String)
|
|
sta_identifier = Column('sta_identifier', String)
|
|
# datasets = relationship('Dataset')
|
|
datasets = relationship('Dataset', back_populates="phenomenon", lazy=True)
|
|
|
|
def __repr__(self):
|
|
return f'Phenomenon {self.name}'
|
|
|
|
|
|
class Procedure(Base):
|
|
""" procedure class """
|
|
__tablename__ = 'procedure'
|
|
__table_args__ = {"schema": "gba"}
|
|
|
|
id = Column('procedure_id', Integer, primary_key=True)
|
|
name = Column('name', String)
|
|
sta_identifier = Column('sta_identifier', String)
|
|
# datasets = relationship('Dataset')
|
|
datasets = relationship('Dataset', back_populates="procedure", lazy=True)
|
|
|
|
def __repr__(self):
|
|
return f'Procedure {self.name}'
|
|
|
|
|
|
class Dataset(Base):
|
|
""" dataset class """
|
|
__tablename__ = 'dataset'
|
|
__table_args__ = {"schema": "gba"}
|
|
|
|
id = Column('dataset_id', Integer, primary_key=True)
|
|
name = Column('name', String)
|
|
is_published = Column('is_published', SmallInteger)
|
|
is_hidden = Column('is_hidden', SmallInteger)
|
|
dataset_type = Column('dataset_type', String)
|
|
observation_type = Column('observation_type', String)
|
|
value_type = Column('value_type', String)
|
|
|
|
last_time = Column('last_time', DateTime)
|
|
last_value = Column('last_value', Numeric(20, 10))
|
|
fk_last_observation_id = Column(
|
|
'fk_last_observation_id',
|
|
Integer
|
|
)
|
|
# last_observation = relationship(
|
|
# "Observation", foreign_keys=[fk_last_observation_id])
|
|
|
|
first_time = Column('first_time', DateTime)
|
|
first_value = Column('first_value', Numeric(20, 10))
|
|
fk_first_observation_id = Column(
|
|
'fk_first_observation_id',
|
|
Integer
|
|
)
|
|
# first_observation = relationship("Observation", foreign_keys=[
|
|
# fk_first_observation_id])
|
|
|
|
observations = relationship(
|
|
'Observation', back_populates='dataset', lazy=True)
|
|
|
|
fk_phenomenon_id = Column(
|
|
'fk_phenomenon_id', Integer, ForeignKey('gba.phenomenon.phenomenon_id'), nullable=False)
|
|
# phenomenon = relationship("Phenomenon", lazy="joined", foreign_keys=[fk_phenomenon_id])
|
|
phenomenon = relationship(
|
|
"Phenomenon", back_populates="datasets", lazy="joined")
|
|
|
|
fk_platform_id = Column('fk_platform_id', Integer, ForeignKey(
|
|
'gba.platform.platform_id'), nullable=True)
|
|
platform = relationship(
|
|
"Platform", back_populates="datasets", lazy="joined")
|
|
|
|
fk_format_id = Column('fk_format_id', Integer, ForeignKey(
|
|
'gba.format.format_id'), nullable=True)
|
|
format = relationship(
|
|
"Format", back_populates="datasets", lazy="joined")
|
|
|
|
fk_procedure_id = Column('fk_procedure_id', Integer, ForeignKey(
|
|
'gba.procedure.procedure_id'), nullable=False)
|
|
# procedure = relationship("Procedure", lazy="joined")
|
|
procedure = relationship(
|
|
"Procedure", back_populates="datasets", lazy="joined")
|
|
|
|
def new_id_factory():
|
|
''' test '''
|
|
dbschema = ''
|
|
db_user = os.environ.get("POSTGIS_DBUSER")
|
|
db_password = os.environ.get("POSTGIS_DBPASSWORD")
|
|
db_url = os.environ.get("POSTGIS_DBURL")
|
|
engine = create_engine(
|
|
"postgresql+psycopg2://" + db_user + ":" + db_password + "@" + db_url,
|
|
connect_args={'options': f'-csearch_path={dbschema}'},
|
|
isolation_level="READ UNCOMMITTED")
|
|
result = engine.execute('SELECT MAX(observation_id) FROM gba.observation')
|
|
mytable_max_id = result.first().max
|
|
if mytable_max_id is None:
|
|
mytable_max_id = 0
|
|
mytable_max_id += 1
|
|
return mytable_max_id
|
|
|
|
observation_seq = Sequence('observation_seq', schema="gba") # define sequence explicitly
|
|
class Observation(Base):
|
|
""" observation class """
|
|
__tablename__ = 'observation'
|
|
__table_args__ = {"schema": "gba"}
|
|
|
|
# id = Column('observation_id', Integer, primary_key=True)
|
|
id = Column('observation_id',
|
|
Integer,
|
|
observation_seq,
|
|
primary_key=True,
|
|
server_default=observation_seq.next_value())
|
|
name = Column('name', String)
|
|
value_type = Column('value_type', String, default="quantity")
|
|
# pitch = Column('PITCH', String)
|
|
# roll = Column('ROLL', String)
|
|
sampling_time_start = Column('sampling_time_start', DateTime)
|
|
sampling_time_end = Column('sampling_time_end', DateTime)
|
|
result_time = Column('result_time', DateTime)
|
|
sta_identifier = Column('sta_identifier', String)
|
|
value_identifier = Column('value_identifier', String)
|
|
value_quantity = Column('value_quantity', Numeric(20, 10), nullable=True)
|
|
value_text = Column('value_text', String, nullable=True)
|
|
value_geometry = Column(Geometry(geometry_type='POINT', srid=4326, dimension=3), nullable=True)
|
|
|
|
fk_dataset_id = Column(Integer, ForeignKey(
|
|
'gba.dataset.dataset_id'), nullable=False)
|
|
dataset = relationship("Dataset", back_populates="observations")
|
|
|
|
|
|
class ObservationSchema(SQLAlchemySchema):
|
|
""" Platform class """
|
|
DateTime = fields.DateTime(attribute='result_time') # Or vice-versa
|
|
# value_quantity = fields.Integer(attribute='Value')
|
|
# id = fields.Integer(attribute='id')
|
|
Value = fields.Integer(attribute='value_quantity')
|
|
id = fields.Integer(attribute='value_identifier')
|
|
# sta_identifier= fields.String(default=uuid.uuid4()),
|
|
|
|
class Meta:
|
|
""" Platform class """
|
|
model = Observation
|
|
include_relationships = True
|
|
load_instance = True
|
|
#pg_session: session = create_pg_session()
|
|
sqla_session: session = create_pg_session()
|
|
|
|
class Format(Base):
|
|
""" Format class """
|
|
__tablename__ = 'format'
|
|
__table_args__ = {"schema": "gba"}
|
|
id = Column('format_id', Integer, primary_key=True)
|
|
definition = Column('definition', String(255), index=True)
|
|
|
|
datasets = relationship('Dataset', back_populates="format", lazy=True)
|
|
|
|
class Person(Base):
|
|
""" Platform class """
|
|
__tablename__ = 'accounts'
|
|
__table_args__ = {"schema": "gba"}
|
|
person_id = Column('id', Integer, primary_key=True)
|
|
lname = Column('last_name', String(255), index=True)
|
|
fname = Column('first_name', String(255))
|
|
login = Column(String(255))
|
|
timestamp = Column('updated_at', DateTime, default=datetime.utcnow,
|
|
onupdate=datetime.utcnow)
|
|
|
|
def __repr__(self):
|
|
return f"<User(name='{self.login}', lastname='{self.lname}')>"
|
|
|
|
|
|
class PersonSchema(SQLAlchemyAutoSchema):
|
|
""" Platform class """
|
|
class Meta:
|
|
""" Platform class """
|
|
model = Person
|
|
include_relationships = True
|
|
load_instance = True
|
|
#pg_session: session = create_pg_session()
|
|
sqla_session: session = create_pg_session()
|
|
|
|
|
|
def create_db():
|
|
# db_url = 'sqlite:///db.sqlite'
|
|
# engine = create_engine(db_url, echo = True )
|
|
# Base.metadata.drop_all(bind=engine)
|
|
# Base.metadata.create_all(engine)
|
|
""" create postgres db session """
|
|
load_dotenv("D:\\Software\\geomon\\.env")
|
|
dbschema = ''
|
|
db_user = os.environ.get("POSTGIS_DBUSER")
|
|
db_password = os.environ.get("POSTGIS_DBPASSWORD")
|
|
db_url = os.environ.get("POSTGIS_DBURL")
|
|
engine = create_engine(
|
|
"postgresql+psycopg2://" + db_user + ":" + db_password + "@" + db_url,
|
|
connect_args={'options': f'-csearch_path={dbschema}'},
|
|
isolation_level="READ UNCOMMITTED", echo=True)
|
|
# session_maker = sessionmaker(bind=engine)
|
|
# session = session_maker()
|
|
# Base.metadata.drop_all(bind=engine)
|
|
Base.metadata.create_all(engine)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
create_db()
|