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())
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"))
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("_")})
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("_")})
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")
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
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")
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)
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())
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')
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)", )
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
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)
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
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, }
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)
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)
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)
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']))
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))
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)
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))
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
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)
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)
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)
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' )
) # 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",
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')
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 = [ {