class Subscription(Base): """Model class to represent ATT subscriptions""" __tablename__ = "subscriptions" id = Column(Integer, primary_key=True) phone_number = Column(String(50)) status = Column(ENUM(SubscriptionStatus), default=SubscriptionStatus.new) activation_date = Column(TIMESTAMP(timezone=True), nullable=True) expiry_date = Column(TIMESTAMP(timezone=True), nullable=True) plan_id = Column(Integer, ForeignKey('plans.id')) plan = relationship("Plan", back_populates="plans") def __repr__(self): # pragma: no cover return (f"<{self.__class__.__name__}: {self.id} ({self.status}), " f"phone_number: {self.phone_number or '[no phone number]'}, ", f"plan: {self.plan_id}>") @classmethod def get_subscriptions(cls, **kwargs): """Gets a list of Subscription objects using given kwargs Generates query filters from kwargs param using base class method Args: kwargs: key value pairs to apply as filters Returns: list: objects returned from query result """ return cls.query.filter(**kwargs).all()
class Payday(db.Model): __tablename__ = 'paydays' __table_args__ = (UniqueConstraint('ts_end', name='paydays_ts_end_key'), ) # TODO: Move this to a different module? EPOCH = datetime.datetime(1970, 1, 1, tzinfo=pytz.utc) id = Column(Integer, nullable=False, primary_key=True) ts_start = Column(TIMESTAMP(timezone=True), nullable=False, default="now()") ts_end = Column(TIMESTAMP(timezone=True), nullable=False, default=EPOCH) nparticipants = Column(Integer, default=0) ntippers = Column(Integer, default=0) ntips = Column(Integer, default=0) ntransfers = Column(Integer, default=0) transfer_volume = Column(Numeric(precision=35, scale=2), default=0.0) ncc_failing = Column(Integer, default=0) ncc_missing = Column(Integer, default=0) ncharges = Column(Integer, default=0) charge_volume = Column(Numeric(precision=35, scale=2), default=0.0) charge_fees_volume = Column(Numeric(precision=35, scale=2), default=0.0) nachs = Column(Integer, default=0) ach_volume = Column(Numeric(precision=35, scale=2), default=0.0) ach_fees_volume = Column(Numeric(precision=35, scale=2), default=0.0) nach_failing = Column(Integer, default=0)
class Tip(db.Model): __tablename__ = 'tips' id = Column(Integer, nullable=False, primary_key=True) ctime = Column(TIMESTAMP(timezone=True), nullable=False) mtime = Column(TIMESTAMP(timezone=True), nullable=False, default="now()") tipper = Column(Text, ForeignKey("participants.id", onupdate="CASCADE", ondelete="RESTRICT"), nullable=False) tippee = Column(Text, ForeignKey("participants.id", onupdate="CASCADE", ondelete="RESTRICT"), nullable=False) amount = Column(Numeric(precision=35, scale=2), nullable=False)
class APIKey(db.Model): __tablename__ = 'api_keys' id = Column(Integer, nullable=False, primary_key=True) ctime = Column(TIMESTAMP(timezone=True), nullable=False) mtime = Column(TIMESTAMP(timezone=True), nullable=False, default="now()") participant = Column(Text, ForeignKey("participants.username", onupdate="CASCADE", ondelete="RESTRICT"), nullable=False) api_key = Column(Text, nullable=True)
class Archive(Base): "Archive" __tablename__ = 'archive' id = Column(BigInteger, primary_key=True) messageid = Column(Unicode(255), index=True) actions = Column(Unicode(255)) clientip = Column(Unicode(128)) date = Column(Date, index=True) from_address = Column(Unicode(255), index=True) from_domain = Column(Unicode(255), index=True) headers = Column(UnicodeText) hostname = Column(UnicodeText) highspam = Column(SmallInteger, default=0, index=True) rblspam = Column(SmallInteger, default=0) saspam = Column(SmallInteger, default=0) spam = Column(SmallInteger, default=0, index=True) nameinfected = Column(SmallInteger, default=0) otherinfected = Column(SmallInteger, default=0) isquarantined = Column(SmallInteger, default=0, index=True) isarchived = Column(SmallInteger, default=0, index=True) sascore = Column(Float) scaned = Column(SmallInteger, default=0, index=True) size = Column(Integer) blacklisted = Column(SmallInteger, default=0, index=True) spamreport = Column(UnicodeText) whitelisted = Column(SmallInteger, default=0, index=True) subject = Column(UnicodeText) time = Column(Time(timezone=True)) timestamp = Column(TIMESTAMP(timezone=True), server_default=utcnow(), index=True) to_address = Column(Unicode(255), index=True) to_domain = Column(Unicode(255), index=True) virusinfected = Column(SmallInteger, default=0) ts = Column(TIMESTAMP(timezone=True), server_default=utcnow()) msgfiles = Column(UnicodeText) def __init__(self, messageid): "init" self.messageid = messageid __mapper_args__ = {'order_by': timestamp} @property def isdangerous(self): "Check if the message is dangerous" if self.virusinfected or self.otherinfected or self.nameinfected: return True return False
class FileLedgerMixin(TableMixin): """Mixin for parsed file ledger (tracking) table, which serves as accounting system for parsers. Designed to work with FileMetadataMixin in the libadfir-parsers library. """ file_name = Column(DialectSpecificText(), nullable=False) file_path = Column(DialectSpecificText(), nullable=False) file_size = Column(Integer, nullable=False) md5hash = Column(DialectSpecificText()) sha1hash = Column(DialectSpecificText()) sha2hash = Column(DialectSpecificText()) modify_time = Column(TIMESTAMP(timezone=True)) access_time = Column(TIMESTAMP(timezone=True)) create_time = Column(TIMESTAMP(timezone=True)) completed = Column(Boolean, index=True)
class FileLedger(BaseTable, ConcreteTableMixin): ''' Parsed $MFT file ledger (tracking) table ''' file_name = Column(String().with_variant(Text, 'postgresql'), nullable=False) file_path = Column(String().with_variant(Text, 'postgresql'), nullable=False) file_size = Column(BigInteger, nullable=False) md5hash = Column(String().with_variant(Text, 'postgresql')) sha1hash = Column(String().with_variant(Text, 'postgresql')) sha2hash = Column(String().with_variant(Text, 'postgresql'), nullable=False) modify_time = Column(TIMESTAMP(timezone=True)) access_time = Column(TIMESTAMP(timezone=True)) create_time = Column(TIMESTAMP(timezone=True)) completed = Column(Boolean, index=True) entry_headers = relationship('EntryHeader', backref='file_ledger')
class Users(Base): __tablename__ = 'forumusers' uid = Column(String(50), primary_key=True) # user id firstName = Column(String(200)) lastName = Column(String(100)) source = Column(String(100)) # URL of the user profile colleagues = Column(Integer) followers = Column(Integer) following = Column(Integer) numPosts = Column(Integer) numVotes = Column(Integer) numAwards = Column(Integer) points = Column(Integer) account = Column(String(50)) # account type: base, plus, pro city = Column(String(100)) state = Column(String(50)) dateJoined = Column(DateTime) # creation date of the user account seeking = Column(Text) # currently seeking experience = Column(Text) # real estate experience occupation = Column(String(767)) goals = Column(Text) # real estate goals crawl_time = Column(TIMESTAMP(timezone=True), server_default=func.now(), onupdate=func.now())
class Metric(Base): """ This is an ORM constructor for the metrics table. Using the ORM makes it easier to create and destroy the table, as well as change to different backends. For example, to use sqlite for local development. The table has the following columns: uid = a unique auto increment row id ts = timestamp supplied by the user via POST request name = metric name value = metric value raw = copy of the original json POST request """ __tablename__ = 'metrics' uid = Column(Integer, primary_key=True) ts = Column(TIMESTAMP(timezone=True), index=True) name = Column(String(length=512), index=True) value = Column(String(length=512)) raw = Column(JSONB) def asdict(self): return dict(id=self.uid, ts=str(self.ts), name=self.name, value=self.value, raw=self.raw)
class TableMixin: """Mixin class for (non-view) tables.""" id = Column(Integer, primary_key=True) created_at = Column(TIMESTAMP(timezone=True), server_default=TimestampDefaultExpression(), index=True) __table_args__ = dict(mysql_engine='InnoDB', mysql_charset='utf8mb4')
class IndexerKillList(Base): "Keyword kill list" __tablename__ = 'indexer_killlist' id = Column(BigInteger, primary_key=True) ts = Column(TIMESTAMP(timezone=True), nullable=False, primary_key=True) tablename = Column(Unicode(255), nullable=False, primary_key=True)
class User(db.Model): __tablename__ = "users" user_id = Column(Integer, primary_key=True) username = Column(String(32)) registration_datetime = Column(TIMESTAMP(), default=datetime.utcnow, nullable=False)
class OrderDetailDAO(BaseModel): __tablename__ = 'order_detail' id = Column(Integer, primary_key=True) order_id = Column(Integer) food_id = Column(Integer) amount = Column(Integer) price = Column(DECIMAL(5, 2)) created_at = Column(TIMESTAMP(timezone=False), nullable=False, server_default=func.current_timestamp()) @hybrid_method def add(self, session): try: self._commit(session, self) return self except Exception as e: session.rollback() raise e @hybrid_method def food(self, session): return FoodDAO.byID(session, self.food_id) @hybrid_method def by_order_id(cls, session, order_id): query = session.query(cls).filter(cls.order_id== order_id) return query.all()
def test_support_sql_tz_naive_datetime_types(self): column_name = "tz_naive_datetime" tz_naive_types = (DateTime(timezone=False), TIMESTAMP(timezone=False)) for sql_type in tz_naive_types: self.assertEqual( GraphQLDateTime, try_get_graphql_scalar_type(column_name, sql_type))
class AuditLog(Base): "Audit Log items" __tablename__ = 'auditlog' id = Column(BigInteger, primary_key=True) username = Column(Unicode(255)) category = Column(SmallInteger, default=1, index=True) info = Column(UnicodeText) hostname = Column(UnicodeText) remoteip = Column(UnicodeText) timestamp = Column(TIMESTAMP(timezone=True), server_default=utcnow()) __mapper_args__ = {'order_by': timestamp} def __init__(self, username, category, info, hostname, remoteip, timestamp=None): "init" self.username = username self.category = category self.info = info self.hostname = hostname self.remoteip = remoteip if timestamp: self.timestamp = timestamp # @property def tojson(self): "JSON friendly format" return dict(username=self.username, category=unicode(CATEGORY_MAP[self.category]), info=self.info, hostname=self.hostname, remoteip=self.remoteip, timestamp=str(self.timestamp.strftime('%Y-%m-%d %H:%M')))
def ingest(self, df, table_name): if 'alertregions' in table_name: comp_cols = ['geohash', 'time', 'expires', 'region'] update_cols = ['geohash', 'time', 'expires'] str_cols = ", ".join(update_cols) update_or_string = self.query_string_interpolation(df, update_cols) update_query = f"SELECT id, {str_cols} FROM api_data_alerts WHERE {update_or_string}" alerts = pd.read_sql(con=self.engine, sql=update_query) for index, item in alerts.iterrows(): idx_filter = df[(df['time'] == item['time']) & (df['geohash'] == item['geohash']) & (df['expires'] == item['expires'])].index df.loc[idx_filter, 'alert_id'] = item['id'] elif 'alerts' in table_name: comp_cols = ['geohash', 'time', 'expires'] else: comp_cols = ['geohash', 'time'] df.drop_duplicates(subset=comp_cols, inplace=True) delete_or_string = self.query_string_interpolation(df, comp_cols) delete_query = f"DELETE FROM {table_name} WHERE id IN (SELECT id FROM {table_name} WHERE {delete_or_string})" self.cursor.execute(delete_query) self.conn.commit() time_cols = [ col for col in df.columns if 'time' in col.lower() or 'expires' in col.lower() ] conv_dict = {col: TIMESTAMP(timezone=True) for col in time_cols} df.to_sql(name=table_name, con=self.engine, if_exists='append', index=False, dtype=conv_dict, chunksize=1000)
class Event(Base): __tablename__ = 'events' id = Column( UUID(as_uuid=True), primary_key=True, # This requires the uuid-ossp extension server_default=text('uuid_generate_v4()')) service_id = Column(UUID(as_uuid=True), ForeignKey('services.id'), nullable=False) # Index on the timestamp, BETWEEN when = Column(TIMESTAMP(timezone=True), nullable=False) status = Column(ENUM('up', 'down', 'limited', name='status_enum', create_type=False), nullable=False) description = Column(TEXT, nullable=False) informational = Column(Boolean, nullable=False) extra = Column(JSONB, nullable=False) ix_events_when = Index(when.desc()) # service = relationship('Service', backref='events', cascade='delete, delete-orphan') def __str__(self): return f"{self.service} -> {self.status.upper()}"
class TrashTvArchillectHistory(Base): """DB Model for 'TRASH_TV_ARCHILLECT_HISTORY' db view.""" __tablename__ = "TRASH_TV_ARCHILLECT_HISTORY" id = Column(AGNOSTIC_UUID(), primary_key=True, default=uuid4) gif_id = Column(String, ForeignKey(TrashTvArchillectData.archillect_id)) timestamp = Column(TIMESTAMP(timezone=True), server_default=func.now())
def test_do_not_support_sql_tz_aware_datetime_types(self): column_name = "tz_aware_datetime" tz_aware_types = (DateTime(timezone=True), TIMESTAMP(timezone=True)) for sql_type in tz_aware_types: with self.assertWarns(Warning): graphql_type = try_get_graphql_scalar_type( column_name, sql_type) self.assertIsNone(graphql_type)
class IndexerCounter(Base): "Indexing counters" __tablename__ = 'indexer_counters' tablename = Column(Unicode(255), primary_key=True) maxts = Column(TIMESTAMP(timezone=True), server_default=utcnow(), index=True, nullable=False)
def test_render_col_with_server_default(self): c = Column('updated_at', TIMESTAMP(), server_default='TIMEZONE("utc", CURRENT_TIMESTAMP)', nullable=False) result = autogenerate._render_column(c, self.autogen_context) eq_( result, 'sa.Column(\'updated_at\', sa.TIMESTAMP(), ' 'server_default=\'TIMEZONE("utc", CURRENT_TIMESTAMP)\', ' 'nullable=False)')
def test_render_server_default_text(self): c = Column('updated_at', TIMESTAMP(), server_default=text('now()'), nullable=False) result = autogenerate.render._render_column(c, self.autogen_context) eq_ignore_whitespace( result, 'sa.Column(\'updated_at\', sa.TIMESTAMP(), ' 'server_default=sa.text(!U\'now()\'), ' 'nullable=False)')
class ATTPlanVersion(Base): """Model class to represent ATT plan version Custom versioning class to keep track of plans enabled ATT side """ __tablename__ = "att_plan_versions" id = Column(Integer, primary_key=True) subscription_id = Column( Integer, ForeignKey("subscriptions.id"), nullable=False ) subscription = relationship("Subscription", back_populates="subscriptions") plan_id = Column(String(30), ForeignKey("plans.id"), nullable=False) plan = relationship("Plan", back_populates="plans") start_effective_date = Column(TIMESTAMP(timezone=True), nullable=False) end_effective_date = Column(TIMESTAMP(timezone=True), nullable=False) mb_available = Column(BigInteger) def __repr__(self): # pragma: no cover return ( f"<{self.__class__.__name__}: {self.subscription_id}, " f"{str(self.plan_id)} ({self.start_effective_date} - " f"{self.end_effective_date}) " ) def getmb(id): # r = s.query(Plan).filter(Plan.id== self.plan_id).first().get('mb_available') # return (r.mb_available) connection = psycopg2.connect(user="******", password="******", host="127.0.0.1", port="5432", database="wingattbilling") cursor = connection.cursor() postgreSQL_select_Query = "select mb_available from plans where id=" + id cursor.execute(postgreSQL_select_Query) mobile_records_one = cursor.fetchone() print(mobile_records_one) print("r") return(1048)
class Identification(db.Model): __tablename__ = 'identifications' id = Column(BigInteger, nullable=False, primary_key=True) ctime = Column(TIMESTAMP(timezone=True), nullable=False) mtime = Column(TIMESTAMP(timezone=True), nullable=False, default="now()") individual = Column(Text, ForeignKey( "participants.username" , onupdate="CASCADE" , ondelete="RESTRICT" ), nullable=False) group = Column(Text, ForeignKey( "participants.username" , onupdate="CASCADE" , ondelete="RESTRICT" ), nullable=False) weight = Column(Numeric(precision=17, scale=16), nullable=False) identified_by = Column(Text, ForeignKey( "participants.username" , onupdate="CASCADE" , ondelete="RESTRICT" ), nullable=False)
class Exchange(db.Model): __tablename__ = 'exchanges' id = Column(Integer, nullable=False, primary_key=True) timestamp = Column(TIMESTAMP(timezone=True), nullable=False, default="now()") amount = Column(Numeric(precision=35, scale=2), nullable=False) fee = Column(Numeric(precision=35, scale=2), nullable=False) participant = Column(Text, ForeignKey("participants.username", onupdate="CASCADE", ondelete="RESTRICT"), nullable=False)
class Post(Base): __tablename__ = 'post' id = Column(Integer, primary_key=True, nullable=False) created = Column(TIMESTAMP(timezone=False), default=datetime.utcnow, nullable=False) link = Column(TEXT, nullable=False) title = Column(TEXT, nullable=False) type = Column(Enum('article', 'ask', 'job', 'show', name='post_type'), nullable=False) username = Column(TEXT) website = Column(TEXT) __table_args__ = (Index('post_index', 'id', 'username'), )
class Activities(Base): __tablename__ = 'activities' id = Column(Integer, primary_key=True, nullable=False) external_id = Column(TEXT, nullable=False) activities = Column(JSON, nullable=False) chart_types = Column(JSON, nullable=False) time_unit = Column(TEXT, nullable=False) month = Column(TEXT, nullable=True) year = Column(Integer, nullable=True) created = Column(TIMESTAMP(timezone=False), default=datetime.utcnow, nullable=False)
class TrashTvArchillectData(Base): """DB Model for 'TRASH_TV_ARCHILLECT_DATA' db view.""" __tablename__ = "TRASH_TV_ARCHILLECT_DATA" id = Column(AGNOSTIC_UUID(), primary_key=True, default=uuid4) archillect_id = Column(String(), unique=True) source_link = Column(VARCHAR()) gif_raw_data = Column(LargeBinary, nullable=True) timestamp = Column(TIMESTAMP(timezone=True), server_default=func.now()) played_on_archillect = relationship("TrashTvArchillectHistory")
class SAAccessKey(Base): """ The SQLAlchemy declarative model class for a camera object. """ __tablename__ = 'access_key' id = Column(BigInteger, nullable=False, primary_key=True, autoincrement=True) key_id = Column(String(length=32, convert_unicode=True), nullable=False, index=True, unique=True) secret = Column(String(length=64, convert_unicode=True), nullable=False, server_default="") desc = Column(String(length=255, convert_unicode=True), nullable=False, server_default="") username = Column(String(length=64, convert_unicode=True), ForeignKey('user.username', onupdate="CASCADE", ondelete="CASCADE"), nullable=False, server_default="") enabled = Column(Boolean(), nullable=False, server_default=text("0")) key_type = Column(SmallInteger, nullable=False, server_default=text("0")) ctime = Column(TIMESTAMP(), nullable=False, server_default=text("0")) user = relationship("SAUser", back_populates="access_keys") def __repr__(self): return "SA AccessKey Object(key_id:%s)" % (self.key_id) def from_access_key(self, access_key): self.key_id = access_key.key_id self.secret = access_key.secret self.key_type = access_key.key_type self.username = access_key.username self.desc = access_key.desc self.enabled = access_key.enabled self.ctime = access_key.ctime def to_access_key(self, access_key_cls): return access_key_cls(key_id=self.key_id, secret=self.secret, username=self.username, key_type=self.key_type, enabled=self.enabled, desc=self.desc, ctime=self.ctime)
def generate_table(schema_name=None, bind=None): metadata = MetaData(schema=schema_name, bind=bind) Table('HISTORY_TABLE', metadata, Column('history_id', BigInteger, primary_key=True, nullable=False), Column('cpu_info', VARCHAR(50), nullable=False), Column('memory_info', VARCHAR(50), nullable=False), Column('last_UDL_run_date', TIMESTAMP(True), nullable=False), Column('UDL_completion_duration', VARCHAR(50), nullable=False), Column('file_size', VARCHAR(50), nullable=False) ) return metadata