class Employee(Base):
    __tablename__ = 'employee'

    emp_id = Column('id', Integer(), primary_key=True)
    emp_name = Column('name', String())
    emp_dob = Column('dob', DATE())
    gender = Column('gender', String())
    join_date = Column('join_date', DATE())
    emp_dept = Column('deportment', String())
    designation = Column('designation', String())
Ejemplo n.º 2
0
class ProfessorBorrow(TimestampMixin, BASE):
    """This class stores the information pertaining to the ProfessorBorrow, which reflects the many
    to many relationship between professor's transaction and book-items.

    Attributes
    ----------
    professor_trans_id : int
        Primary Key of ProfessorBorrow table, also a foreign key
        denoting the professor's transaction.

    book_bar_code_id : int
        Primary Key of StudentBorrow table, also a foreign key denoting the book_item.

    dd : DATE
        Due date.
    rd : DATE
        Date of return.

    professor_activity : StudentActivity
        professor activity in which this borrow with a specific book occurred.

    book_item : BookItem
        book item which occurred during this borrow.

    """

    __tablename__ = 'professor_borrow'
    professor_trans_id = Column(Integer,
                                ForeignKey("professor_activity.trans_id"),
                                primary_key=True)
    book_bar_code_id = Column(Integer,
                              ForeignKey("book_item.bar_code"),
                              primary_key=True)
    # Due Date
    due_date = Column(DATE(),
                      nullable=False,
                      default=date.today() + timedelta(15))
    # Return Date
    return_date = Column(DATE())

    professor_activity = relationship(
        "ProfessorActivity",
        backref=backref("book_item_associations",
                        cascade="all, delete, delete-orphan"))
    book_item = relationship("BookItem",
                             backref=backref(
                                 "professor_activity_associations",
                                 cascade="all, delete, delete-orphan"))
Ejemplo n.º 3
0
class Update(Base):
    """
    Update class that represents a device update
    """
    __tablename__ = 'updates'
    id: int = Column(INT(), primary_key=True, autoincrement=True)
    codename: str = Column(VARCHAR(30), nullable=False)
    version: str = Column(VARCHAR(20), nullable=False)
    android: str = Column(VARCHAR(5), nullable=False)
    branch: str = Column(VARCHAR(15), nullable=False)
    type: str = Column(VARCHAR(11), nullable=False)
    method: str = Column(VARCHAR(8), nullable=False)
    size: str = Column(BIGINT(), nullable=True)
    md5: str = Column(CHAR(32), unique=True, nullable=True)
    filename: str = Column(TEXT(), unique=True, nullable=True)
    link: str = Column(TEXT(), nullable=False)
    changelog: str = Column(TEXT(), nullable=True, default='Bug fixes and system optimizations.')
    date: str = Column(DATE(), nullable=True)
    inserted_on: str = Column(TIMESTAMP(), default=current_timestamp())

    def __repr__(self):
        return f"<Update(codename={self.codename}, version={self.version}, branch={self.branch}, method={self.method})>"

    def __str__(self):
        return str({k: v for k, v in self.__dict__.items() if not k.startswith("_")})
Ejemplo n.º 4
0
class Update(Base):
    """
    Update class that represents a device update
    """
    __tablename__ = 'firmware'
    id: int = Column(INT(), primary_key=True, autoincrement=True)
    codename: str = Column(VARCHAR(30), nullable=False)
    version: str = Column(VARCHAR(20), nullable=False)
    android: str = Column(VARCHAR(5), nullable=False)
    branch: str = Column(VARCHAR(15), nullable=False)
    size: str = Column(BIGINT(), nullable=True)
    md5: str = Column(CHAR(32), unique=True, nullable=True)
    filename: str = Column(TEXT(), unique=True, nullable=True)
    github_link: str = Column(TEXT(), nullable=False)
    osdn_link: str = Column(TEXT(), nullable=True)
    date: str = Column(DATE(), nullable=True)
    inserted_on: str = Column(TIMESTAMP(), default=current_timestamp())

    def __repr__(self):
        return f"<Update(codename={self.codename}, version={self.version}, branch={self.branch})>"

    def __str__(self):
        return str(
            {k: v
             for k, v in self.__dict__.items() if not k.startswith("_")})
Ejemplo n.º 5
0
class Students(TimestampMixin, BASE):
    """This class stores the information pertaining to the Students.

    Attributes
    ----------
    reg_id : int
        Primary Key of Students table.

    name : str
        Name of student.

    doj : DATE
        Date of joining of student

    dept_id : int
        Foreign key referring to Department Table, denoting the department
        to which the student belongs to

    activities : list
        List of activities belonging to this student.

    """
    __tablename__ = 'students'
    reg_id = Column(Integer(), primary_key=True)
    name = Column(String(255), index=True, nullable=False)
    doj = Column(DATE(), nullable=False)
    dept_id = Column(Integer(),
                     ForeignKey('department.dept_id'),
                     nullable=False)

    activities = relationship("StudentActivity",
                              backref=backref('student'),
                              order_by="StudentActivity.trans_id",
                              cascade="all, delete, delete-orphan")
Ejemplo n.º 6
0
class Users(Base):
    __tablename__ = "maps_users"

    id = Column(Integer, primary_key=True)
    Username = Column(String(25), primary_key=True, unique=True)
    FullName = Column(String(40))
    Alamat = Column(String(60))
    Password = Column(String(30))
    Email = Column(String(30), unique=True)
    Phone = Column(String(16))
    Work = Column(String(16))
    Profile_Image_Location = Column(String(200))
    Profile_Description = Column(TEXT())
    Website = Column(String(30))
    Date_Joined = Column(DATE())
    Last_Log = Column(String(100))

    def __init__(self, Username, FullName, Password, Email, Phone, Work,
                 Last_Log):
        self.Username = Username
        self.FullName = FullName
        self.Password = Password
        self.Email = Email
        self.Phone = Phone
        self.Work = Work
        self.Last_Log = Last_Log
Ejemplo n.º 7
0
class ProfessorActivity(TimestampMixin, BASE):
    """This class stores the information pertaining to the ProfessorActivity.

    Attributes
    ----------
    trans_id : int
        Primary Key of ProfessorActivity table.

    doi : DATE
        Date of issue.

    student_id : int
        Foreign key denoting the professors's Id, from Professors table.

    staff_id : list
        Foreign key denoting the staff's Id, from staff table who handled this transaction.

    """
    __tablename__ = 'professor_activity'
    trans_id = Column(Integer(), primary_key=True)
    # Date of issue
    doi = Column(DATE(), nullable=False)
    professor_id = Column(Integer(),
                          ForeignKey('professors.employee_code'),
                          nullable=False)
    staff_id = Column(Integer(), ForeignKey('staffs.staff_id'), nullable=False)

    book_items = relationship("BookItem",
                              backref="professor_activities",
                              secondary="professor_borrow")
Ejemplo n.º 8
0
class BookItem(TimestampMixin, BASE):
    """This class stores the information pertaining to the book items.

    Attributes
    ----------
    bar_code : int
        Primary Key of BookItem table.

    dopur : DATE
        date of purchase.

    dopub : DATE
        date of publication.

    price : float
         price of book

    edition : float
        Edition of book

    status : ["Available", "Unavailable", "Lost"]
        Status of bookitem.

    tampered : Boolean
        set to true if while returning the book is tampered.

    isbn_id : int
        Foreign key referring to the Books table.

    """

    __tablename__ = 'book_item'

    bar_code = Column(Integer(), primary_key=True)
    # Date of Purchase
    dopur = Column(DATE(), nullable=False)
    # Date of Publication
    dopub = Column(DATE(), nullable=False)
    price = Column(Float(2), CheckConstraint('price >= 0.00'), nullable=False)
    edition = Column(Float(2))
    status = Column(Enum(BookStatus),
                    default=BookStatus.AVAILABLE,
                    nullable=False)
    tampered = Column(Boolean(), default=False)
    isbn_id = Column(Integer(), ForeignKey('books.isbn_id'), nullable=False)
Ejemplo n.º 9
0
class Express(base):
    # 电话号码和入库时间为联合主键
    __tablename__ = 'express'
    # query = mysql_dbsession.query_property()
    id = Column(Integer, autoincrement=True, nullable=False, primary_key=True)
    express_number = Column(String(64), nullable=False)
    phone_number = Column(String(64), primary_key=True, nullable=False)  # 主键
    name = Column(String(64))
    date = Column(DATE(), default=datetime.datetime.now().date())
Ejemplo n.º 10
0
    def test_type_reflection(self):
        metadata = self.metadata

        # (ask_for, roundtripped_as_if_different)

        specs = [
            (String(), String()),
            (String(1), String(1)),
            (String(3), String(3)),
            (Text(), Text()),
            (Unicode(), String()),
            (Unicode(1), String(1)),
            (Unicode(3), String(3)),
            (UnicodeText(), Text()),
            (CHAR(1), ),
            (CHAR(3), CHAR(3)),
            (NUMERIC, NUMERIC()),
            (NUMERIC(10, 2), NUMERIC(10, 2)),
            (Numeric, NUMERIC()),
            (Numeric(10, 2), NUMERIC(10, 2)),
            (DECIMAL, DECIMAL()),
            (DECIMAL(10, 2), DECIMAL(10, 2)),
            (INTEGER, INTEGER()),
            (BIGINT, BIGINT()),
            (Float, Float()),
            (NUMERIC(), ),
            (TIMESTAMP, TIMESTAMP()),
            (DATETIME, DATETIME()),
            (DateTime, DateTime()),
            (DateTime(), ),
            (DATE, DATE()),
            (Date, Date()),
            (TIME, TIME()),
            (Time, Time()),
            (BOOLEAN, BOOLEAN()),
            (Boolean, Boolean()),
            ]
        columns = [Column('c%i' % (i + 1), t[0]) for (i, t) in
                   enumerate(specs)]
        db = testing.db
        t_table = Table('types', metadata, *columns)
        metadata.create_all()
        m2 = MetaData(db)
        rt = Table('types', m2, autoload=True)
        try:
            db.execute('CREATE VIEW types_v AS SELECT * from types')
            rv = Table('types_v', m2, autoload=True)
            expected = [len(c) > 1 and c[1] or c[0] for c in specs]
            for table in rt, rv:
                for i, reflected in enumerate(table.c):
                    assert isinstance(reflected.type,
                            type(expected[i])), '%d: %r' % (i,
                            type(expected[i]))
        finally:
            db.execute('DROP VIEW types_v')
Ejemplo n.º 11
0
class Policy(Base):
    __tablename__ = "policies"

    __table_args__ = {}

    # column definitions
    id = Column(u"id", INTEGER(), primary_key=True, nullable=False)
    policy_number = Column(u"policy_number",
                           VARCHAR(length=128),
                           nullable=False)
    effective_date = Column(u"effective_date", DATE(), nullable=False)
    status = Column(
        u"status",
        Enum(u"Active", u"Canceled", u"Expired"),
        default=u"Active",
        nullable=False,
    )
    billing_schedule = Column(
        u"billing_schedule",
        Enum(u"Annual", u"Two-Pay", u"Quarterly", u"Monthly"),
        default=u"Annual",
        nullable=False,
    )
    annual_premium = Column(u"annual_premium", INTEGER(), nullable=False)
    named_insured = Column(u"named_insured", INTEGER(),
                           ForeignKey("contacts.id"))
    agent = Column(u"agent", INTEGER(), ForeignKey("contacts.id"))
    cancel_date = Column(u"cancel_date", DATE(), nullable=True, default=None)
    cancel_reason = Column(u"cancel_reason",
                           VARCHAR(length=128),
                           nullable=True,
                           default=None)

    def __init__(self, policy_number, effective_date, annual_premium):
        self.policy_number = policy_number
        self.effective_date = effective_date
        self.annual_premium = annual_premium

    invoices = relation(
        "Invoice",
        primaryjoin="and_(Invoice.policy_id==Policy.id, Invoice.deleted == 0)",
    )
Ejemplo n.º 12
0
def get_twitter_table(table_name, metadata):
    twitter_table = Table(table_name, metadata,
                          Column('t_id', BIGINT(), primary_key=True),
                          Column('t_tweets', String(), nullable=False),
                          Column('t_id', BIGINT(), primary_key=True),
                          Column('t_tweets', String(), nullable=False),
                          Column('t_len', SMALLINT(), nullable=False),
                          Column('t_date', DATE(), nullable=False),
                          Column('t_source', String(100), nullable=False),
                          Column('t_likes', SMALLINT(), nullable=False),
                          Column('t_retweet', SMALLINT(), nullable=False),
                          Column('t_sentiment', SMALLINT(), nullable=False))
    return twitter_table
Ejemplo n.º 13
0
class AppResult(Base):
    __tablename__ = "app_searchspider_results"
    __table_args__ = (
        UniqueConstraint('videoUrl'),
    )

    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    type = Column(String(20), nullable=False)
    platform = Column(String(20), nullable=False)
    keyword = Column(String(50), nullable=False)
    targetUrl = Column(String(500))
    coverUrl = Column(String(500))
    videoUrl = Column(String(500), unique=True, nullable=False)
    targetTitle = Column(String(500))
    createDate = Column(DATE(), nullable=False)
    status = Column(Integer, default=0)
    timeSpan = Column(Integer)
    publishDate = Column(DATE())
    author = Column(String(50))
    authorId = Column(String(100))
    lookCount = Column(Integer)
    searchTaskId = Column(Integer)
    tortStatus = Column(Integer)
Ejemplo n.º 14
0
def get_share_table(table_name, metadata):
    # define a table with all its field
    shares_table = Table(table_name, metadata,
                         Column('tradedate', DATE(), nullable=False),
                         Column('ticker', String(50), nullable=False),
                         Column('open', Float(), nullable=False),
                         Column('high', Float(), nullable=False),
                         Column('low', Float(), nullable=False),
                         Column('close', Float(), nullable=False),
                         Column('adjclose', Float(), nullable=False),
                         Column('volume', Integer(), nullable=False),
                         Column('dividend', Float(), nullable=False),
                         Column('coef', Float(), nullable=False))
    return shares_table
Ejemplo n.º 15
0
class Invoice(Base):
    __tablename__ = "invoices"

    __table_args__ = {}

    # column definitions
    id = Column(u"id", INTEGER(), primary_key=True, nullable=False)
    policy_id = Column(u"policy_id",
                       INTEGER(),
                       ForeignKey("policies.id"),
                       nullable=False)
    bill_date = Column(u"bill_date", DATE(), nullable=False)
    due_date = Column(u"due_date", DATE(), nullable=False)
    cancel_date = Column(u"cancel_date", DATE(), nullable=False)
    amount_due = Column(u"amount_due", INTEGER(), nullable=False)
    deleted = Column(u"deleted",
                     Boolean,
                     default=False,
                     server_default="0",
                     nullable=False)

    def __init__(self, policy_id, bill_date, due_date, cancel_date,
                 amount_due):
        self.policy_id = policy_id
        self.bill_date = bill_date
        self.due_date = due_date
        self.cancel_date = cancel_date
        self.amount_due = amount_due

    def serialize(self):
        return {
            "id": self.id,
            "bill_date": self.bill_date.strftime("%Y-%m-%d"),
            "due_date": self.due_date.strftime("%Y-%m-%d"),
            "amount_due": self.amount_due,
        }
Ejemplo n.º 16
0
class Merchandise(Base):
    """A table that stores the merchandise items each game tour"""
    __tablename__ = 'merchandise'
    id = Column(Integer(), primary_key=True, nullable=False)
    item_name = Column(String(100), nullable=False)
    item_price = Column(Numeric(10, 2), nullable=False)
    total_quantity = Column(Integer(), nullable=False)
    date_added = Column(DATE(), default=datetime.now)

    # sale = relationship('Sales',back_populates='merchandise_item')
    def __str_(self):
        merchandise_record = 'Merchandise item id={} item_name={} item_description={} item_price={}, total_quantity={} date_added={}'
        return merchandise_record.format(self.id, self.item_name,
                                         self.item_price, self.total_quantity,
                                         self.date_added)
Ejemplo n.º 17
0
class Client(IntId, Migrated, Audited, Base):
    name = Column(TextLine, nullable=False)

    reduced_fee = Column(types.String(20))

    # TODO: phase this out.
    note = Column(TEXT())

    address = Column(TextLine)
    city = Column(types.String(24))
    state = Column(types.String(3))
    zip = Column(types.String(12))

    phone = Column(types.String(15))
    DOB = Column(DATE)
    Officer_id = Column(INTEGER(),
                        ForeignKey('Officer.id', ondelete="SET NULL"))
    Officer2_id = Column(INTEGER(),
                         ForeignKey('Officer.id', ondelete="SET NULL"))
    Lawyer_id = Column(INTEGER(),
                       ForeignKey('Officer.id', ondelete="SET NULL"))
    Court_id = Column(INTEGER(),
                      ForeignKey('Office.id', ondelete="SET NULL"))
    file = Column(TextCode)
    file_site = Column(Enum('op', 'kck'))
    file_opened = Column(DATE())
    billing_cutoff = Column(DATE())
    recent = Column(DATE())
    charges = Column(Money)
    client_paid = Column(Money)
    insurance_paid = Column(Money)
    balance = Column(Money)
    balance_cached = Column(TIMESTAMP())
    invoice_note = Column(TEXT())
    voucher = Column(BOOLEAN())
    voucher_note = Column(TextLine)
Ejemplo n.º 18
0
class initGroup_GPJY(Base):
    __tablename__ = "挂牌交易交易详细信息"
    NAME = Column(String(length=100))
    CJ_ENERGY = Column(Float())
    GP_ENERGY = Column(Float())
    FZ = Column(Float())
    YNS = Column(DATE())
    SFM = Column(Time())
    CJ_PRICE = Column(Float())
    ZPF_ENERGY = Column(Float())
    KEY = Column(String(length=100), primary_key=True)
    ZPF_COUNT = Column(Float())
    STATU = Column(Float())
    GP_COUNT = Column(Float())

    updated= Column(Integer(), default=0)
Ejemplo n.º 19
0
def get_table(metadata):
    return Table('updates', metadata,
                 Column('id', INT(), primary_key=True, autoincrement=True),
                 Column('codename', VARCHAR(30), nullable=False),
                 Column('version', VARCHAR(20), nullable=False),
                 Column('android', VARCHAR(5), nullable=False),
                 Column('branch', VARCHAR(15), nullable=False),
                 Column('type', VARCHAR(11), nullable=False),
                 Column('method', VARCHAR(8), nullable=False),
                 Column('size', BIGINT(), nullable=True),
                 Column('md5', CHAR(32), nullable=True, unique=True),
                 Column('filename', TEXT(), nullable=False, unique=True),
                 Column('link', TEXT(), nullable=False),
                 Column('changelog', TEXT(), nullable=True, default='Bug fixes and system optimizations.'),
                 Column('date', DATE(), nullable=True),
                 Column('inserted_on', TIMESTAMP(), default=current_timestamp()),
                 ForeignKeyConstraint(['codename'], ['devices.codename']))
Ejemplo n.º 20
0
def get_table(metadata):
    return Table(
        'firmware', metadata,
        Column('id', INT(), primary_key=True, autoincrement=True),
        Column('codename', VARCHAR(30), nullable=False),
        Column('version', VARCHAR(20), nullable=False),
        Column('android', VARCHAR(5), nullable=False),
        Column('branch', VARCHAR(15), nullable=False),
        Column('size', BIGINT(), nullable=True),
        Column('md5', CHAR(32), nullable=True, unique=True),
        Column('filename', TEXT(), nullable=False, unique=True),
        Column('github_link', TEXT(), nullable=False),
        Column('osdn_link', TEXT(), nullable=True),
        Column('date', DATE(), nullable=True),
        Column('inserted_on', TIMESTAMP(), default=current_timestamp()),
        ForeignKeyConstraint(['codename'], ['devices.codename'],
                             use_alter=True))
Ejemplo n.º 21
0
class Health(Base):
    # 表的名字
    __tablename__ = 'health'

    # 表的结构
    healthId = Column(Integer(), primary_key=True, autoincrement=True)
    userId = Column(Integer())
    insulin = Column(String())  # 胰岛素用量
    sportTime = Column(String())  # 运动时长
    weight = Column(String())  # 体重
    bloodPressure = Column(String())  # 血压
    healthTime = Column(String())  # 保存记录的时间
    healthDate = Column(DATE())  # 保存记录的日期

    def __repr__(self):
        return "<Health(healthId='%s', userId='%s', insulin='%s', sportTime='%s', weight='%s', bloodPressure='%s', heightTime='%s', healthDate='%s')>" % (
            self.healthId, self.userId, self.insulin, self.sportTime,
            self.weight, self.bloodPressure, self.healthTime, self.healthDate)
Ejemplo n.º 22
0
    def test_type_adapt(self):
        dialect = cx_oracle.dialect()

        for start, test in [
            (Date(), cx_oracle._OracleDate),
            (oracle.OracleRaw(), cx_oracle._OracleRaw),
            (String(), String),
            (VARCHAR(), cx_oracle._OracleString),
            (DATE(), cx_oracle._OracleDate),
            (oracle.DATE(), oracle.DATE),
            (String(50), cx_oracle._OracleString),
            (Unicode(), cx_oracle._OracleNVarChar),
            (Text(), cx_oracle._OracleText),
            (UnicodeText(), cx_oracle._OracleUnicodeText),
            (NCHAR(), cx_oracle._OracleNVarChar),
            (oracle.RAW(50), cx_oracle._OracleRaw),
        ]:
            assert isinstance(start.dialect_impl(dialect), test), \
                    "wanted %r got %r" % (test, start.dialect_impl(dialect))
Ejemplo n.º 23
0
class Payment(Base):
    __tablename__ = "payments"

    __table_args__ = {}

    # column definitions
    id = Column(u"id", INTEGER(), primary_key=True, nullable=False)
    policy_id = Column(u"policy_id",
                       INTEGER(),
                       ForeignKey("policies.id"),
                       nullable=False)
    contact_id = Column(u"contact_id",
                        INTEGER(),
                        ForeignKey("contacts.id"),
                        nullable=False)
    amount_paid = Column(u"amount_paid", INTEGER(), nullable=False)
    transaction_date = Column(u"transaction_date", DATE(), nullable=False)

    def __init__(self, policy_id, contact_id, amount_paid, transaction_date):
        self.policy_id = policy_id
        self.contact_id = contact_id
        self.amount_paid = amount_paid
        self.transaction_date = transaction_date
Ejemplo n.º 24
0
class Sales(Base):
    """A table that holds sales information for each each merchandise item and game location"""
    __tablename__ = 'sale'
    id = Column(Integer, primary_key=True, nullable=False)
    venue_id = Column(Integer(), ForeignKey('game_schedule.venue_id'))
    item_id = Column(Integer(), ForeignKey('merchandise.id'))
    quantity_sold = Column(Integer(), nullable=False)
    date_enter = Column(DATE(), default=datetime.now())

    #Establishing of various tables relationship. Sales table referencing game and merchandise table
    game = relationship('Game', backref=backref('sale', order_by=id))
    merchandise = relationship('Merchandise',
                               backref=backref('sale', order_by=id))

    def __str__(self):

        sales_record = "Sale Details: id={}  venue_id={} quantity_sold={} "
        return sales_record.format(self.id, self.venue_id, self.item_id,
                                   self.quantity_sold)

        sales_record = "Sale Details: id={}  venue_id={} quantity_sold={} date_enter={}"
        return sales_record.format(self.id, self.venue_id, self.item_id,
                                   self.quantity_sold, self.date_enter)
Ejemplo n.º 25
0
class DialectTypesTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = oracle.OracleDialect()

    def test_no_clobs_for_string_params(self):
        """test that simple string params get a DBAPI type of
        VARCHAR, not CLOB. This is to prevent setinputsizes
        from setting up cx_oracle.CLOBs on
        string-based bind params [ticket:793]."""

        class FakeDBAPI(object):
            def __getattr__(self, attr):
                return attr

        dialect = oracle.OracleDialect()
        dbapi = FakeDBAPI()

        b = bindparam("foo", "hello world!")
        eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING")

        b = bindparam("foo", "hello world!")
        eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING")

    def test_long(self):
        self.assert_compile(oracle.LONG(), "LONG")

    @testing.combinations(
        (Date(), cx_oracle._OracleDate),
        (oracle.OracleRaw(), cx_oracle._OracleRaw),
        (String(), String),
        (VARCHAR(), cx_oracle._OracleString),
        (DATE(), cx_oracle._OracleDate),
        (oracle.DATE(), oracle.DATE),
        (String(50), cx_oracle._OracleString),
        (Unicode(), cx_oracle._OracleUnicodeStringCHAR),
        (Text(), cx_oracle._OracleText),
        (UnicodeText(), cx_oracle._OracleUnicodeTextCLOB),
        (CHAR(), cx_oracle._OracleChar),
        (NCHAR(), cx_oracle._OracleNChar),
        (NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR),
        (oracle.RAW(50), cx_oracle._OracleRaw),
    )
    def test_type_adapt(self, start, test):
        dialect = cx_oracle.dialect()

        assert isinstance(
            start.dialect_impl(dialect), test
        ), "wanted %r got %r" % (test, start.dialect_impl(dialect))

    @testing.combinations(
        (String(), String),
        (VARCHAR(), cx_oracle._OracleString),
        (String(50), cx_oracle._OracleString),
        (Unicode(), cx_oracle._OracleUnicodeStringNCHAR),
        (Text(), cx_oracle._OracleText),
        (UnicodeText(), cx_oracle._OracleUnicodeTextNCLOB),
        (NCHAR(), cx_oracle._OracleNChar),
        (NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR),
    )
    def test_type_adapt_nchar(self, start, test):
        dialect = cx_oracle.dialect(use_nchar_for_unicode=True)

        assert isinstance(
            start.dialect_impl(dialect), test
        ), "wanted %r got %r" % (test, start.dialect_impl(dialect))

    def test_raw_compile(self):
        self.assert_compile(oracle.RAW(), "RAW")
        self.assert_compile(oracle.RAW(35), "RAW(35)")

    def test_char_length(self):
        self.assert_compile(VARCHAR(50), "VARCHAR(50 CHAR)")

        oracle8dialect = oracle.dialect()
        oracle8dialect.server_version_info = (8, 0)
        self.assert_compile(VARCHAR(50), "VARCHAR(50)", dialect=oracle8dialect)

        self.assert_compile(NVARCHAR(50), "NVARCHAR2(50)")
        self.assert_compile(CHAR(50), "CHAR(50)")

    @testing.combinations(
        (String(50), "VARCHAR2(50 CHAR)"),
        (Unicode(50), "VARCHAR2(50 CHAR)"),
        (NVARCHAR(50), "NVARCHAR2(50)"),
        (VARCHAR(50), "VARCHAR(50 CHAR)"),
        (oracle.NVARCHAR2(50), "NVARCHAR2(50)"),
        (oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"),
        (String(), "VARCHAR2"),
        (Unicode(), "VARCHAR2"),
        (NVARCHAR(), "NVARCHAR2"),
        (VARCHAR(), "VARCHAR"),
        (oracle.NVARCHAR2(), "NVARCHAR2"),
        (oracle.VARCHAR2(), "VARCHAR2"),
    )
    def test_varchar_types(self, typ, exp):
        dialect = oracle.dialect()
        self.assert_compile(typ, exp, dialect=dialect)

    @testing.combinations(
        (String(50), "VARCHAR2(50 CHAR)"),
        (Unicode(50), "NVARCHAR2(50)"),
        (NVARCHAR(50), "NVARCHAR2(50)"),
        (VARCHAR(50), "VARCHAR(50 CHAR)"),
        (oracle.NVARCHAR2(50), "NVARCHAR2(50)"),
        (oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"),
        (String(), "VARCHAR2"),
        (Unicode(), "NVARCHAR2"),
        (NVARCHAR(), "NVARCHAR2"),
        (VARCHAR(), "VARCHAR"),
        (oracle.NVARCHAR2(), "NVARCHAR2"),
        (oracle.VARCHAR2(), "VARCHAR2"),
    )
    def test_varchar_use_nchar_types(self, typ, exp):
        dialect = oracle.dialect(use_nchar_for_unicode=True)
        self.assert_compile(typ, exp, dialect=dialect)

    @testing.combinations(
        (oracle.INTERVAL(), "INTERVAL DAY TO SECOND"),
        (oracle.INTERVAL(day_precision=3), "INTERVAL DAY(3) TO SECOND"),
        (oracle.INTERVAL(second_precision=5), "INTERVAL DAY TO SECOND(5)"),
        (
            oracle.INTERVAL(day_precision=2, second_precision=5),
            "INTERVAL DAY(2) TO SECOND(5)",
        ),
    )
    def test_interval(self, type_, expected):
        self.assert_compile(type_, expected)
Ejemplo n.º 26
0
class ElectricityData(RefModel):
    """
        Model for the electricity data
    """

    __tablename__ = "elec_usage"

    usage_type = Column(String(length=20))
    usage_date = Column(DATE())
    start_time = Column(TIME(timezone=False))
    end_time = Column(TIME(timezone=False))
    usage_in_kwh = Column(Float(precision=5))
    units = Column(String(length=5))
    cost_in_dollor = Column(String(length=5))
    notes = Column(String(length=100))
    cust_id = Column(String(length=10))
    rec_id = Column(Integer(), primary_key=True)

    @hybrid_property
    def date_time(self):
        date_times = dt.datetime.combine(self.usage_date, self.end_time)
        return date_times

    @date_time.expression
    def date_time(cls):
        return cls.usage_date + cls.end_time

    @hybrid_property
    def day_type(self):
        is_week_day = 1
        week_day = self.usage_date.isocalendar()[2]
        print(week_day)

        if week_day > 5:
            is_week_day = 0

        return is_week_day

    @day_type.expression
    def day_type(cls):
        from sqlalchemy import true

        return case([
            (sal.extract('isodow', cls.usage_date).__gt__(5), 1),
        ],
                    else_=0)

    @hybrid_property
    def is_holiday(self):
        is_hday = 0
        cal = calendar()
        holidays = cal.holidays(start=dt.date(2015, 1, 1),
                                end=dt.date(2020, 12, 31))
        if np.datetime64(self.usage_date) in holidays:
            is_hday = 1

        return is_hday

    @is_holiday.expression
    def is_holiday(cls):
        """
            Reference - https://stackoverflow.com/questions/64276059
        """
        is_hday = 0
        cal = calendar()
        holidays = cal.holidays(start=dt.date(2015, 1, 1),
                                end=dt.date(2020, 12, 31))

        is_hday = cls.usage_date.in_(holidays)
        return is_hday

    def __init__(self, **kwargs) -> None:
        super(ElectricityData, self).__init__(**kwargs)
Ejemplo n.º 27
0
                Column(u'email', TextLine),
                Column(u'Office_id', INTEGER(),
                       ForeignKey('Office.id', ondelete="SET NULL")),
                Column(u'id_zoho', TextCode),
                Column(u'id_dabble', TextCode),
                Column('added_time', TIMESTAMP()),
                Column('added_user', TextCode),
                Column('modified_time', TIMESTAMP()),
                Column('modified_user', TextCode),
                mysql_engine='InnoDB'
                )


Session =  Table('Session', metadata,
                 Column(u'id', INTEGER(), primary_key=True, nullable=False),
                 Column(u'session_date', DATE(), nullable=False),
                 Column(u'time', TextCode),
                 Column(u'Group_id', INTEGER(),
                        ForeignKey('Group.id', ondelete="CASCADE"),
                        nullable=False),
                 Column(u'Therapist_id', INTEGER(),
                        ForeignKey('Therapist.id', ondelete="SET NULL")),
                 Column(u'id_zoho', TextCode),
                 Column(u'id_dabble', TextCode),
                 Column('added_time', TIMESTAMP()),
                 Column('added_user', TextCode),
                 Column('modified_time', TIMESTAMP()),
                 Column('modified_user', TextCode),
                 mysql_engine='InnoDB'
                 )
Ejemplo n.º 28
0
)

# XIgnite stock data.  Partial results from XIgniteGlobalQuota GetBars API
xigniteSecurityBars = Table(
    "xignite_security_bars",
    metadata,
    Column("symbol",
           mysql.VARCHAR(length=_FIN_SECURITY_SYMBOL_MAX_LEN,
                         **_ASCII_TEXT_KWARGS),
           ForeignKey(xigniteSecurity.c.symbol,
                      name="xignite_security_bars_to_security_fk",
                      onupdate="CASCADE",
                      ondelete="CASCADE"),
           primary_key=True,
           nullable=False),
    Column("StartDate", DATE(), primary_key=True, nullable=False),
    Column("StartTime", TIME(), primary_key=True, nullable=False),
    Column("EndDate", DATE(), primary_key=True, nullable=False),
    Column("EndTime", TIME(), primary_key=True, nullable=False),
    Column("UTCOffset", FLOAT(), primary_key=True, nullable=False),
    Column("Open", FLOAT(), nullable=False),
    Column("High", FLOAT(), nullable=False),
    Column("Low", FLOAT(), nullable=False),
    Column("Close", FLOAT(), nullable=False),
    Column("Volume", INTEGER(), nullable=False),
    Column("Trades", INTEGER(), nullable=False),
)

emittedStockPrice = Table(
    "emitted_stock_price", metadata,
    Column("symbol",
Ejemplo n.º 29
0
def protocol_typecast(protocol_choice: str, type: str):
    # customtype = None
    customsize = None
    if 'v_' in type:
        # customtype = 'varchar'
        customsize = int(type.split('_')[1])
    """ dictionary with kv pairs of type-protocol for each field type"""
    text = {
        'sqlalchemy': TEXT(),
        'pandas': "object",
        'pg': "TEXT",
        "custom": VARCHAR(customsize),
        "custompg": f'VARCHAR({customsize})'
    }
    float = {'sqlalchemy': NUMERIC(), 'pandas': 'float64', 'pg': "NUMERIC"}
    integer = {'sqlalchemy': INTEGER(), 'pandas': 'Int64', 'pg': 'INTEGER'}
    date = {'sqlalchemy': DATE(), 'pandas': 'datetime64[ns]', 'pg': 'DATE'}
    geom = {
        'sqlalchemy': Geometry('POINT', srid=4326),
        'pandas': 'object',
        'pg': 'postgis.GEOMETRY(POINT, 4326)'
    }
    """ executed pattern will depend on function parameters """

    if 'sqlalchemy' in protocol_choice:
        if 'text' in type:
            return text['sqlalchemy']
        elif 'float' in type:
            return float['sqlalchemy']
        elif 'integer' in type:
            return integer['sqlalchemy']
        elif 'date' in type:
            return date['sqlalchemy']
        elif 'v_' in type:
            return text['custom']
        elif 'geom' in type:
            return geom['sqlalchemy']
        else:
            print('type not yet implemented')

    elif 'pandas' in protocol_choice:
        if 'text' in type:
            return text['pandas']
        elif 'float' in type:
            return float['pandas']
        elif 'integer' in type:
            return integer['pandas']
        elif 'date' in type:
            return date['pandas']
        elif 'v_' in type:
            return text['pandas']
        elif 'geom' in type:
            return geom['pandas']
        else:
            print('type not yet implemented')

    elif 'pg' in protocol_choice:
        if 'text' in type:
            return text['pg']
        elif 'float' in type:
            return float['pg']
        elif 'integer' in type:
            return integer['pg']
        elif 'date' in type:
            return date['pg']
        elif 'v_' in type:
            return text['custompg']
        elif 'geom' in type:
            return geom['pg']
        else:
            print('type not yet implemented')
Ejemplo n.º 30
0
                Column('cookie_recipe_url', String(255)),
                Column('cookie_sku', String(55)),
                Column('quantity', Integer()),
                Column('unit_cost', Numeric(12, 2))
                )
hotels = Table('hotel',metadata,
              Column('id',Integer(),primary_key=True),
              Column('hotelName',String(32)),
              Column('hotelType',Integer())
        )
prices = Table('price',metadata,
              Column('id',Integer(),primary_key=True),
              Column('hotel_id',Integer(),ForeignKey('hotel.id')),
              Column('room_id',Integer(),ForeignKey('room.id')),
              Column('price',Numeric(10,2)),
              Column('checkInDate',DATE()),
              Column('isValid',Integer(),default=1)
        )
rooms = Table('room',metadata,
              Column('id',Integer(),primary_key=True),
              Column('roomName',String(64)),
              Column('roomType',Integer())
)

metadata.create_all(engine)

ins = cookies.insert()
roomInsert = rooms.insert()

room_list = [
    {