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 = [
{
