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 Person(Base):
__tablename__ = 'person'
# Here we define columns for the table person
# Notice that each column is also a normal Python instance attribute.
id = Column(Integer, primary_key=True, nullable=False)
vorname = Column(String(250), nullable=False)
nachname = Column(String(250), nullable=False)
geburtsdatum = Column(Date(), nullable=True)
telefon = Column(String(250), nullable=True)
email = Column(String(250), nullable=True)
newsletter = Column(Boolean(), nullable=True)
class Tag(Base):
__tablename__ = 'tag'
id = Column(Integer, primary_key=True)
tag = Column(String)
inserted_date = Column(Date(), default=datetime.datetime.now().date())
tag_rank = Column(String)
def __repr__(self):
return "<Artist (tag='%s', inserted_date='%s', tag_rank='%s' )" % (
self.tag, self.inserted_date, self.tag_rank)
class ProjectData(Base):
__tablename__ = 'projects'
id = Column(Integer(), primary_key=True, autoincrement=True)
name = Column(String(50), unique=True, nullable=False)
date_start = Column(Date(), nullable=False)
date_end = Column(Date(), nullable=True)
program_type = Column(String(50), nullable=True)
# Charge Number (One-to-One)
charge_number = relationship('ChargeNumber',
back_populates='project',
uselist=False)
# Program (Many-to-One)
# program_name = Column(String(50), ForeignKey('programs.name', ondelete='CASCADE', onupdate='CASCADE'))
# program = relationship('Program', back_populates='projects')
programs = relationship('Program',
lazy='dynamic',
secondary='program_project_link')
class Model1(Model):
id = Column(Integer, primary_key=True)
field_string = Column(String(50), unique=True, nullable=False)
field_integer = Column(Integer())
field_float = Column(Float())
field_date = Column(Date())
field_file = FileColumn()
field_image = ImageColumn()
def __repr__(self):
return str(self.field_string)
class AssetDataModel(Base):
__tablename__ = 'asset_data'
id = Column(Integer(), primary_key=True)
asset_id = Column(Integer(), nullable=False)
trade_date = Column(Date(), nullable=False)
open_price = Column(Float(), nullable=False)
high_price = Column(Float(), nullable=False)
low_price = Column(Float(), nullable=False)
close_price = Column(Float(), nullable=False)
adjusted_close_price = Column(Float(), nullable=False)
trade_volume = Column(BigInteger(), nullable=False)
dividend_amount = Column(Float(), nullable=True)
split_coefficient = Column(Float(), nullable=True)
maturity_date = Column(Date(), nullable=True)
coupon_percent = Column(Float(), nullable=True)
coupon_value = Column(Float(), nullable=True)
yield_amount = Column(Float(), nullable=True)
accrued_interest = Column(Float(), nullable=True)
class CommonUserMixin(object):
""" Common User information.
"""
username = Column(Unicode(128), unique=True, nullable=False)
first_name = Column(Unicode(64), nullable=False)
last_name = Column(Unicode(64), nullable=False)
email = Column(Unicode(128), unique=True, nullable=False)
password = Column(Unicode(128), nullable=False)
dob = Column(Date(), nullable=False)
identifier_id = Column(Unicode(32), nullable=False)
def define_columns(data_dict, class_name):
"""Dynamically define the class attributes for the ORM
Parameters
----------
data_dict : dict
A dictionary containing the ORM definitions
class_name : str
The name of the class/ORM.
Returns
-------
data_dict : dict
A dictionary containing the ORM definitions, now with header
definitions added.
"""
special_keywords = [
'RULEFILE', 'FWERROR', 'FW2ERROR', 'PROPTTL1', 'TARDESCR', 'QUALCOM2'
]
with open(
os.path.join(
os.path.split(__file__)[0], 'table_definitions',
class_name.lower() + '.txt'), 'r') as f:
data = f.readlines()
keywords = [item.strip().split(', ') for item in data]
for keyword in keywords:
if keyword[0] in special_keywords:
data_dict[keyword[0].lower()] = get_special_column(keyword[0])
elif keyword[1] == 'Integer':
data_dict[keyword[0].lower()] = Column(Integer())
elif keyword[1] == 'String':
data_dict[keyword[0].lower()] = Column(String(50))
elif keyword[1] == 'Float':
data_dict[keyword[0].lower()] = Column(Float(precision=32))
elif keyword[1] == 'Decimal':
data_dict[keyword[0].lower()] = Column(Float(precision='13,8'))
elif keyword[1] == 'Date':
data_dict[keyword[0].lower()] = Column(Date())
elif keyword[1] == 'Time':
data_dict[keyword[0].lower()] = Column(Time())
elif keyword[1] == 'DateTime':
data_dict[keyword[0].lower()] = Column(DateTime)
elif keyword[1] == 'Bool':
data_dict[keyword[0].lower()] = Column(Boolean)
else:
raise ValueError('unrecognized header keyword type: {}:{}'.format(
keyword[0], keyword[1]))
if 'aperture' in data_dict:
data_dict['aperture'] = Column(String(50), index=True)
return data_dict
class Jz_autoinfo_PageSource(Base):
__tablename__ = 'jz_autoinfo_pagesource'
# __table_args__ = {"useexisting": False}
uid = Column(String(), primary_key=True)
url = Column(String())
collection_time = Column(TIMESTAMP(),
nullable=True,
server_default=func.now()) #爬取时间
pagesource = Column(VARCHAR())
flag = Column(String()) #标志位，纪录网站下的各站点
write_date = Column(Date())
class QuestionModel(Base):
__tablename__ = 'questions'
questionID = Column(Integer(), primary_key=True)
creatorID = Column(Integer()) # foreign key to userDim
text = Column(String(255))
publicOrPrivate = Column(Boolean())
createDt = Column(Date())
survey = relationship('SurveysModel',
secondary=assocation_table,
back_populates='question')
class DiaryEntry(Base):
__tablename__ = 'diary_entries'
id = Column(Integer,
Sequence('diary_entry_id_seq'),
primary_key=True,
nullable=False)
user_id = Column(Integer, index=True, nullable=False)
created_on = Column(Date(), index=True, nullable=False)
highlights = Column(Text, nullable=False)
moments = Column(Text, nullable=False)
class DealerMatch(Base):
__tablename__ = 'tbl_dealermatch'
uid = Column(Integer, primary_key=True, autoincrement=True)
dealerid = Column(BigInteger)
date = Column(Date())
matchAID = Column(String(50))
matchAResult = Column(String(10))
matchBID = Column(String(50))
matchBResult = Column(String(10))
winFlag = Column(String(10))
matchdesc = Column(Text)
class AccountRunningBatch(Base):
__tablename__ = 'tbl_account_running_batch'
uid = Column(Integer, primary_key=True, autoincrement=True)
date = Column(Date())
useMoney = Column(Float, default=0.0)
dResult = Column(Float, default=0.0)
riskMoney = Column(Float, default=0.0)
totalResult = Column(Float, default=0.0)
fixTotal = Column(Float, default=0.0)
matchCount = Column(Integer)
status = Column(String(5), default='1')
class Registration(db.Model):
__tablename__ = 'registrations'
id = Column('id', Integer, primary_key=True, autoincrement=True)
regcode = Column('regcode', String(16), unique=True)
registered_at = Column('registered_at', DateTime(timezone=True))
participant_id = Column('participant_id', ForeignKey('participants.id'))
participant = db.relationship('Participant', backref=backref('registers'))
payment_required = Column('payment_required', Boolean(), default=False)
badge = Column('badge', String(32))
pay_status = Column('pay_status', Boolean(), default=False)
paid_on = Column('paid_on', Date(), nullable=True)
class salesOrdersRaw(Base):
__tablename__ = 'sales_order'
__table_args__ = {'extend_existing': True}
id = Column(Integer, primary_key=True)
OrderDate = Column(Date())
Region = Column(String(20))
Rep = Column(String(20))
Item = Column(String(10))
Units = Column(Integer())
UnitCost = Column('Unit Cost', Float())
Total = Column(Float())
class Puppy(Base):
#inside MenuItem class
__tablename__ = 'puppy'
name = Column(String(80), nullable=False)
id = Column(Integer, primary_key=True)
dateOfBirth = Column(Date())
gender = Column(String(25))
weight = Column(Numeric(10))
shelter_id = Column(Integer, ForeignKey('shelter.id'))
shelter = relationship(Shelter)
class Price(Base):
__tablename__ = 'price'
id = Column(Integer, primary_key=True)
id_producto = Column(ForeignKey('product.id'), index=True)
id_marca = Column(String(10), index=True)
marca = Column(String(300), nullable=False)
id_establecimiento = Column(String(10), nullable=False)
establecimiento = Column(String(200), nullable=False)
direccion = Column(String(500), nullable=True)
colonia = Column(String(200), nullable=True)
codigo_postal = Column(String(8), nullable=False, index=True)
estado = Column(String(50), nullable=True)
latitud = Column(String(30), nullable=False)
longitud = Column(String(30), nullable=False)
precio = Column(Numeric, nullable=False)
fecha_observacion = Column(Date(), nullable=False, index=True)
fecha_actualizacion = Column(Date(),
nullable=False,
default=datetime.now().date(),
index=True)
class WeeklyStats(BaseModel, Base):
"""WeeklyStats keeps track of weekly stats for users.
This table has a many to one relationship with the Users table
Attributes:
user_id (str): Foreign key that associates WeeklyStats with a user
week_range (str): Tracks the week range in following format:
yyyy-mm-dd:yyyy-mm-dd
num_applications (str): Number of applications user applied to
within the week range
num_interviews (str): Number of interviews user had (not received)
within the week range.
"""
__tablename__ = 'weekly_stats'
user_id = Column(String(60), ForeignKey('users.id'))
users = relationship('User', back_populates='weekly_stats')
start_date = Column(Date())
end_date = Column(Date())
num_applications = Column(Integer, default=0)
num_interviews = Column(Integer, default=0)
class Timesheet(Base):
__tablename__ = "timesheet"
project_id = Column(Integer, ForeignKey('projects.project_id'), primary_key=True)
datestring = Column(Date(), primary_key=True)
timestring = Column(Integer)
project = relationship('Project', back_populates='cats')
def __repr__(self):
return "<Time Entry: Project ID {p} - Date {d} - Worked {w}>".format(p=self.project_id,
d=self.datestring,
w=self.timestring)
class Act(DATABASES['main']): # pylint: disable=R0903
'''
Model of acts.
'''
__tablename__ = 'acts'
id = Column(Integer, primary_key=True) # pylint: disable=C0103
parent_act = Column(Integer, ForeignKey('acts.id'))
# _parent = relationship('Act', remote_side=[id])
date = Column(String(length=8))
act_date = Column(Date())
storage = Column(Integer, ForeignKey('storages.id'))
receiver_storage = Column(Integer, ForeignKey('storages.id'))
act_type = Column(Integer)
storekeeper = Column(String(length=200))
is_active = Column(Boolean, default=False)
is_upload = Column(Boolean, default=False)
upload_date = Column(Date())
is_download = Column(Boolean, default=False)
download_date = Column(String(length=8))
subdivision = Column(Integer, ForeignKey('subdivisions.id'))
def __init__(self, *, password: str):
"""Create a new :class:`Password` instance.
all attributes passed to this constructor must be kwargs::
password = Password(password="******")
:param password: used to log in to the respective service.
"""
self.password = password
self.creation_date = Date()
def main():
""" starting import """
# get argument
parser = argparse.ArgumentParser()
parser.add_argument("input_json", type=str, help="JSON file to process")
args = parser.parse_args()
# transform data into dataframe
for row in data_iter(args.input_json):
subset_amterdam.append(row)
result = pd.DataFrame(subset_amterdam)
# lower all column names
result.columns = result.columns.str.lower()
# aggregating numbers per group
result = result.groupby(
['date_of_publication', 'municipality_code', 'municipality_name', 'province']).agg(
total_reported = ('total_reported','sum'),
hospital_admission = ('hospital_admission','sum'),
deceased = ('deceased','sum'),
).reset_index()
log.info(f"Starting import {args.input_json}")
engine = get_engine()
result.to_sql(
"corona_gevallen_new",
engine,
dtype={
"index": Integer(),
"date_of_publication": Date(),
"municipality_code": String(),
"municipality_name": String(),
"province": String(),
"total_reported": Integer(),
"hospital_admission": Integer(),
"deceased": Integer(),
},
)
log.info("Data loaded into DB")
ProvenanceRenameOperator(
task_id="provenance_rename",
dataset_name="corona",
prefix_table_name="corona_",
postfix_table_name="_new",
rename_indexes=False,
pg_schema="public",
).execute()
log.info("Renamed columns based on provenance.")
class User_table(Base):
__tablename__ = 'user_orm'
id = Column(Integer(), primary_key=True)
name = Column(String(50), nullable=False, index=True)
age = Column(SmallInteger())
birthday = Column(Date())
gender = Column(String(5))
education = Column(String(20))
created_on = Column(DateTime(), default=datetime.now)
updated_on = Column(DateTime(),
default=datetime.now,
onupdate=datetime.now)
class Games(Base):
__tablename__ = 'games'
__table_args__ = (UniqueConstraint('season_id', 'game_num'), {
"schema": "admin"
})
id = Column(Integer(), primary_key=True)
season_id = Column('season_id', Integer(), ForeignKey('admin.seasons.id'))
game_num = Column(Integer())
budget = Column(Integer())
flex = Column(Boolean)
theme = Column(String(255))
date = Column(Date())
class DBPerformance(Base):
# 表的名字:
__tablename__ = 'daily'
# 表的结构:
id = Column(Integer(), primary_key=True)
dept_id = Column(Integer())
date = Column(Date())
submit_time = Column(DateTime())
submit_user = Column(String(30))
comments = Column(String(200))
extra_fields = Column(JSON())
class Variance(Base):
__tablename__ = 'variance'
id = Column(Integer, primary_key=True, nullable=False)
project_no = Column(String())
bu = Column(String())
project_name = Column(String())
complete = Column(String())
revenue = Column(String())
cost = Column(String())
stage = Column(String())
report_month = Column(Date())
class User_table(Base):
__tablename__ = 'userinfo'
id = Column(Integer(), primary_key=True)
name = Column(String(50), index=True)
age = Column(Integer())
birthday = Column(Date(), nullable=False)
gender = Column(String(10))
degree = Column(String(50))
create_time = Column(DateTime(), default=datetime.now)
update_time = Column(DateTime(),
default=datetime.now,
onupdate=datetime.now)
class Star(Base):
__tablename__ = 'avstar'
id = Column(Integer, primary_key=True)
name = Column(String(64), index=True)
birthday = Column(Date())
image_urls = Column(String(64))
height = Column(Integer())
cup = Column(String(64))
bust = Column(Integer())
waist = Column(Integer())
hips = Column(Integer())
class User(Base):
__tablename__ = 'user'
user_id = Column(Integer(), primary_key=True)
username = Column(String(50), nullable=False, unique=True)
age = Column(Integer(), nullable=False)
birthday = Column(Date(), nullable=False)
gender = Column(String(10), nullable=False)
qualification = Column(String(10), nullable=False)
created_at = Column(DateTime(), default=datetime.now())
updated_at = Column(DateTime(),
default=datetime.now(),
onupdate=datetime.now())
class Pub(Base):
__tablename__ = 'pubs'
id = Column(Integer(), primary_key=True)
doi = Column(String(), unique=True)
title = Column(String(), index=True)
abstract = Column(String())
pub_date = Column(Date())
citation_count = Column(Integer())
detail = Column(JSON())
authors = relationship('Author',
secondary=PubAuthorTable,
backref='publications')
