class drfOpsClassBase(Base):
__tablename__ = 'drf_ops_class_base' #班次表
base_id = Column(VARCHAR(32), nullable=False, primary_key=True)
class_id = Column(VARCHAR(32))
receive_time = Column(VARCHAR(8))
send_time = Column(VARCHAR(8))
duty_officer = Column(VARCHAR(32))
drf_monitor = Column(VARCHAR(32))
receive_well_depth = Column(DECIMAL(8, 2))
send_well_depth = Column(DECIMAL(8, 2))
backfill_well_depth = Column(DECIMAL(8, 2))
backfill_type = Column(VARCHAR(32))
drilled_footage = Column(DECIMAL(8, 2)) #进尺
fen_well_depth = Column(DECIMAL(8, 2))
drillrig_group_size = Column(VARCHAR(500))
bit_model = Column(VARCHAR(32))
bit_size = Column(VARCHAR(32))
delivery_volume = Column(DECIMAL(32))
pump_pressure = Column(DECIMAL(8, 2))
torque = Column(DECIMAL(8, 2))
side_well_depth = Column(DECIMAL(8, 2))
backfill_start_depth = Column(DECIMAL(8, 2))
backfill_end_depth = Column(DECIMAL(8, 2))
well_side = Column(VARCHAR(35))
position = Column(VARCHAR(35))
fluid_heat = Column(DECIMAL(8, 2))
fluid_volume = Column(DECIMAL(8, 2))
add_water_volume = Column(DECIMAL(8, 2))
sampling_depth = Column(DECIMAL(8, 2))
solidoid_content = Column(DECIMAL(8, 2))
amargosite_content = Column(DECIMAL(8, 2))
layer_lithology = Column(VARCHAR(32))
layer_position = Column(VARCHAR(32))
int_note = Column(VARCHAR(500))
join_note = Column(VARCHAR(500))
spare1 = Column(VARCHAR(32))
spare2 = Column(VARCHAR(32))
spare3 = Column(VARCHAR(32))
spare4 = Column(VARCHAR(32))
create_org_id = Column(VARCHAR(32))
create_date = Column(DATETIME)
modify_date = Column(DATETIME)
creator_id = Column(VARCHAR(32))
rflag = Column(VARCHAR(1))
isondata = Column(VARCHAR(1), default='0')
layer_lithology_name = Column(VARCHAR(100))
layer_position_name = Column(VARCHAR(100))
well_level = Column(VARCHAR(100))
con_well_section = Column(VARCHAR(100))
drf_type_name = Column(VARCHAR(100))
drf_type = Column(VARCHAR(32))
bit_model_name = Column(VARCHAR(100))
bit_size_name = Column(VARCHAR(100))
rig_type = Column(VARCHAR(20))
casing_depth = Column(DECIMAL(8, 2))
class AssetTable(Base):
'''资产表'''
__tablename__ = 'user_asset'
user_id = Column(Integer(), primary_key=True, nullable=False)
asset = Column(DECIMAL(19, 4), nullable=False)
class User(BaseModel):
"""用户表"""
__tablename__ = 'tb_user'
id = Column(Integer, primary_key=True, autoincrement=True)
u_name = Column(String(20), unique=True, nullable=False)
u_address = Column(String(32), default='广州')
u_mobilephone = Column(String(16), unique=True, nullable=False)
u_sex = Column(Integer, default=0)
u_vistors = Column(Integer, default=0)
u_passwd = Column(String(32), nullable=False)
u_paypwd = Column(String(32), default='123456')
u_headurl = Column(String(32), default='')
u_describe = Column(String(256), default='')
u_descimg = Column(String(32), default='')
u_level = Column(Integer, default=1) # 1级，愣头青
u_type = Column(Integer, default=1) # 1 个人号，2平台号，3机构号
u_earn_rate = Column(DECIMAL(8, 2), default=0.0)
u_earn_rate_mon = Column(DECIMAL(8, 2), default=0.0)
u_win_rate = Column(DECIMAL(8, 2), default=0.0)
u_money = Column(DECIMAL(36, 4), default=5000000.0)
u_status = Column(Integer, default=0)
u_regtime = Column(DateTime, default=datetime.datetime.now())
u_lastlogin = Column(DateTime, default=datetime.datetime.now())
u_recom_id = Column(Integer, default=0) # 平台推挤
u_email = Column(String(32), default='')
u_rank = Column(Integer, default=0) # 未排名
def __repr__(self):
return "TABLE <User: %s>" % self.to_json();
def to_dict(self):
return dict(
uid = self.id,
uname = self.u_name,
uaddress = self.u_address,
umobile = self.u_mobilephone,
usex = self.u_sex,
uvisitors = self.u_vistors,
uheadurl = self.u_headurl,
udescibe = self.u_describe,
udescimg = self.u_descimg,
ulevel = self.u_level,
utype = self.u_type,
uearnrate = (float)(self.u_earn_rate),
uearnratemon = (float)(self.u_earn_rate_mon),
uwinrate = (float)(self.u_win_rate),
umoney = (float)(self.u_money),
ulastlogin = (str)(self.u_lastlogin),
uemail = self.u_email,
urank = self.u_rank
)
def to_json(self):
return json.dumps( dict(
uid = self.id,
uname = self.u_name,
uaddress = self.u_address,
umobile = self.u_mobilephone,
usex = self.u_sex,
uvisitors = self.u_vistors,
uheadurl = self.u_headurl,
udescibe = self.u_describe,
udescimg = self.u_descimg,
ulevel = self.u_level,
utype = self.u_type,
uearnrate = (float)(self.u_earn_rate),
uearnratemon = (float)(self.u_earn_rate_mon),
uwinrate = (float)(self.u_win_rate),
umoney = (float)(self.u_money),
ulastlogin = (str)(self.u_lastlogin),
uemail = self.u_email,
urank = self.u_rank
) )
class PmsProduct(Base):
__tablename__ = 'pms_product'
__table_args__ = {'comment': '商品信息'}
id = Column(BIGINT(20), primary_key=True)
brand_id = Column(ForeignKey('pms_brand.id',
ondelete='RESTRICT',
onupdate='RESTRICT'),
index=True)
product_category_id = Column(ForeignKey('pms_product_category.id',
ondelete='RESTRICT',
onupdate='RESTRICT'),
index=True)
feight_template_id = Column(ForeignKey('pms_feight_template.id',
ondelete='RESTRICT',
onupdate='RESTRICT'),
index=True)
product_attribute_category_id = Column(ForeignKey(
'pms_product_attribute_category.id',
ondelete='RESTRICT',
onupdate='RESTRICT'),
index=True)
name = Column(VARCHAR(64), nullable=False)
pic = Column(VARCHAR(255))
product_sn = Column(VARCHAR(64), nullable=False, comment='货号')
delete_status = Column(INTEGER(11), comment='删除状态：0->未删除；1->已删除')
publish_status = Column(INTEGER(11), comment='上架状态：0->下架；1->上架')
new_status = Column(INTEGER(11), comment='新品状态:0->不是新品；1->新品')
recommand_status = Column(INTEGER(11), comment='推荐状态；0->不推荐；1->推荐')
verify_status = Column(INTEGER(11), comment='审核状态：0->未审核；1->审核通过')
sort = Column(INTEGER(11), comment='排序')
sale = Column(INTEGER(11), comment='销量')
price = Column(DECIMAL(10, 2))
sub_title = Column(VARCHAR(255), comment='副标题')
description = Column(TEXT, comment='商品描述')
original_price = Column(DECIMAL(10, 2), comment='市场价')
stock = Column(INTEGER(11), comment='库存')
low_stock = Column(INTEGER(11), comment='库存预警值')
unit = Column(VARCHAR(16), comment='单位')
weight = Column(DECIMAL(10, 2), comment='商品重量，默认为克')
preview_status = Column(INTEGER(11), comment='是否为预告商品：0->不是；1->是')
service_ids = Column(VARCHAR(64),
comment='以逗号分割的产品服务：1->无忧退货；2->快速退款；3->免费包邮')
keywords = Column(VARCHAR(255))
note = Column(VARCHAR(255))
album_pics = Column(VARCHAR(255), comment='画册图片，连产品图片限制为5张，以逗号分割')
detail_title = Column(VARCHAR(255))
detail_desc = Column(TEXT)
detail_html = Column(TEXT, comment='产品详情网页内容')
detail_mobile_html = Column(TEXT, comment='移动端网页详情')
brand_name = Column(VARCHAR(255), comment='品牌名称')
product_category_name = Column(VARCHAR(255), comment='商品分类名称')
shop_id = Column(ForeignKey('pms_shop.id',
ondelete='RESTRICT',
onupdate='RESTRICT'),
index=True)
brand = relationship('PmsBrand')
feight_template = relationship('PmsFeightTemplate')
product_attribute_category = relationship('PmsProductAttributeCategory')
product_category = relationship('PmsProductCategory')
shop = relationship('PmsShop')
class User(Base, UserMixin):
"""Model for user accounts."""
# Defined User Level Ranges
CONST_LEVEL1 = 5.0
CONST_LEVEL2 = 10.0
CONST_LEVEL3 = 15.0
CONST_LEVEL4 = 20.0
# Table name
__tablename__ = 'USER'
# Table Columns
userID = Column(Integer, primary_key=True)
firstname = Column(String(255), nullable=False, unique=False)
lastname = Column(String(255), nullable=False, unique=False)
email = Column(String(40), unique=True, nullable=False)
dob = Column(Date, unique=False, nullable=False)
gender = Column(String(20), unique=False, nullable=False)
username = Column(String(64), index=True, unique=True)
userpass = Column(String(200), unique=False, nullable=False)
verified = Column(Boolean, unique=False, nullable=False)
banned = Column(Boolean, unique=False, nullable=False)
balance = Column(DECIMAL(20, 2, asdecimal=False),
unique=False,
nullable=False)
overallPerc = Column(Float, unique=False, nullable=False)
totalNumSales = Column(Integer, unique=False, nullable=False)
def getRating(self):
"""
Returns overall rating between 1-5 based on Users overall proft/loss
percentage.
Args: None
Returns:
A Star rating between 1-5 for the user currently
"""
numStars = 0
# The overall percent of a user into a defined set of ratings 1-5
if ((self.overallPerc > 0)
and (self.overallPerc <= self.CONST_LEVEL1)):
numStars = 1
if ((self.overallPerc > self.CONST_LEVEL1)
and (self.overallPerc <= self.CONST_LEVEL2)):
numStars = 2
if ((self.overallPerc > self.CONST_LEVEL2)
and (self.overallPerc <= self.CONST_LEVEL3)):
numStars = 3
if ((self.overallPerc > self.CONST_LEVEL3)
and (self.overallPerc <= self.CONST_LEVEL4)):
numStars = 4
if (self.overallPerc > self.CONST_LEVEL4):
numStars = 5
return (numStars)
def getStarRating(self):
"""
Returns the name of the png file to display the correct star rating img
Args: None
Returns:
Returns a png link address
"""
returnval = ""
numStars = self.getRating()
if (numStars > 0):
returnval = 'images/awards' + str(numStars) + '.png'
return (returnval)
def get_id(self):
return self.userID
class adjusted_tx_volume(Base): #float
__tablename__ = "adjusted_tx_volume"
entry_id = Column(Integer,
ForeignKey('coin_date.entry_id'),
primary_key=True)
value = Column(DECIMAL(23, 6), nullable=False)
class MatureMiningRewardCheckpoint(Base):
__tablename__ = "mature_mining_reward_checkpoints"
mining_round = Column(String, primary_key=True)
holder = Column(String, primary_key=True)
block_number = Column(Integer, primary_key=True)
mcb_balance = Column(DECIMAL(78, 18))
{'name': 'InstanceSize', 'type': NVARCHAR(20)},
{'name': 'GuestOS', 'type': NVARCHAR(50)},
{'name': 'GuestSize', 'type': NVARCHAR(50)},
{'name': 'GuestOSType', 'type': NVARCHAR(50)},
{'name': 'GuestDistro', 'type': NVARCHAR(50)},
{'name': 'KernelVersion', 'type': NVARCHAR(50)},
{'name': 'DiskSetup', 'type': NVARCHAR(20)},
{'name': 'TestMode', 'type': NVARCHAR(30)},
{'name': 'FileTestMode', 'type': NVARCHAR(20)},
{'name': 'WebServerVersion', 'type': NVARCHAR(20)},
{'name': 'Driver', 'type': NVARCHAR(10)},
{'name': 'IPVersion', 'type': NVARCHAR(4)},
{'name': 'ProtocolType', 'type': NVARCHAR(3)},
{'name': 'ClusterSetup', 'type': NVARCHAR(25)},
{'name': 'HadoopVersion', 'type': NVARCHAR(12)},
{'name': 'Threads', 'type': DECIMAL(4, 0)},
{'name': 'BufferSize_Bytes', 'type': DECIMAL(5, 0)},
{'name': 'TestConnections', 'type': DECIMAL(4, 0)},
{'name': 'NumberOfConnections', 'type': INT},
{'name': 'TestPipelines', 'type': DECIMAL(4, 0)},
{'name': 'TestConcurrency', 'type': DECIMAL(4, 0)},
{'name': 'NodeSize_bytes', 'type': DECIMAL(4, 0)},
{'name': 'TeragenRecords', 'type': DECIMAL(12, 0)},
{'name': 'SortDuration_sec', 'type': DECIMAL(10, 4)},
{'name': 'TotalCreatedCallsPerSec', 'type': DECIMAL(10, 4)},
{'name': 'TotalSetCallsPerSec', 'type': DECIMAL(10, 4)},
{'name': 'TotalGetCallsPerSec', 'type': DECIMAL(10, 4)},
{'name': 'TotalDeletedCallsPerSec', 'type': DECIMAL(10, 4)},
{'name': 'TotalWatchedCallsPerSec', 'type': DECIMAL(10, 4)},
{'name': 'TotalQueries', 'type': DECIMAL(9, 0)},
{'name': 'TransactionsPerSec', 'type': DECIMAL(8, 3)},
ebay_product = Table(
'ebay_product', metadata,
Column('item_id', String(32), nullable=False, default=''),
Column('site', String(8), nullable=False, default=''),
Column('category_id', String(32), nullable=False, default=''),
Column('img', String(128), nullable=False, default=''),
Column('title', String(512), nullable=False, default=''),
Column('brand', String(64), nullable=False, default=''),
Column('item_location', String(64), nullable=False, default=''),
Column('item_location_country', String(8), nullable=False, default=''),
Column('seller', String(64), nullable=False, default=''),
Column('store', String(64), nullable=False, default=''),
Column('store_location', String(64), nullable=False, default=''),
Column('marketplace', String(16), nullable=False, default=''),
Column('popular', TINYINT, nullable=False, default=0),
Column('price', DECIMAL(10, 2), nullable=False, default=0),
Column('sold', Integer, nullable=False, default=0),
Column('visit', Integer, nullable=False, default=0),
Column('gen_time', DateTime, nullable=True),
Column('data_update_time', DateTime, nullable=True),
Column('update_time', DateTime, nullable=True),
Column('description', TEXT, nullable=True),
PrimaryKeyConstraint('item_id', 'site', name='pk'))
ebay_product_history = Table(
'ebay_product_history',
metadata,
Column('item_id', String(32), nullable=False, default=''),
Column('site', String(8), nullable=False, default=''),
Column('date', DateTime, nullable=False),
# Column('brand', String(64), nullable=False, default=''),
Column('source_area_code', String, nullable=False),
Column('destination_area_code', String, nullable=False),
Column('start_at', Time, nullable=False),
Column('end_at', Time, nullable=False))
tariff_configuration = Table(
'tariff_configuration', meta,
Column('id', BigInteger, autoincrement=True, primary_key=True),
Column('created_date', DateTime, nullable=False),
Column('config_start_date', DateTime, nullable=False),
Column('config_end_date', DateTime),
Column('conditions_id',
BigInteger,
ForeignKey(tariff_conditions.c.id),
nullable=False),
Column('standard_charge', DECIMAL(8, 3), nullable=False),
Column('call_time_charge', DECIMAL(8, 3), nullable=False))
call_records = Table(
'call_records',
meta,
Column('id', BigInteger, autoincrement=True, primary_key=True),
Column('created_date', DateTime, nullable=False),
Column('external_id', BigInteger,
nullable=False), # "id": Record unique identificator
Column('call_id', BigInteger, nullable=False),
Column('type', String, nullable=False),
Column('source_area_code', String, nullable=True),
Column('source', String, nullable=True),
Column('destination_area_code', String, nullable=True),
Column('destination', String, nullable=True),
class Nodes(Base):
__tablename__ = 'nodes'
id = Column(BIGINT, primary_key=True)
osm_id = Column(BIGINT)
longitude = Column(DECIMAL(precision=12, scale=9))
latitude = Column(DECIMAL(precision=12, scale=9))
class ImmatureMiningRewardSummary(Base):
__tablename__ = "immature_mining_reward_summaries"
mining_round = Column(String, primary_key=True)
holder = Column(String, primary_key=True)
mcb_balance = Column(DECIMAL(78, 18))
class RoundPaymentSummary(Base):
__tablename__ = "round_payment_summaries"
mining_round = Column(String, primary_key=True)
holder = Column(String, primary_key=True)
paid_amount = Column(DECIMAL(78, 18))
class PaymentSummary(Base):
__tablename__ = "payment_summaries"
holder = Column(String, primary_key=True)
paid_amount = Column(DECIMAL(78, 18))
class UserCommissionStatistic(db.Model):
__tablename__ = 'user_commission_statistics'
id = Column(String(36), primary_key=True)
user_id = Column(String(36), comment='用户 id')
daily_trading_volume = Column(DECIMAL(16, 2), comment='日交易量')
monthly_transaction_volume = Column(DECIMAL(16, 2), comment='月交易量')
total_transaction_volume = Column(DECIMAL(16, 2), comment='总交易量')
daily_total_commission = Column(DECIMAL(16, 2), comment='日的总佣金')
brokerage_cost_per_day = Column(DECIMAL(16, 2), comment='日的券商成本')
daily_remaining_commission = Column(DECIMAL(16, 2), comment='日的剩余佣金')
total_monthly_commission = Column(DECIMAL(16, 2), comment='月的总佣金')
brokerage_cost_per_month = Column(DECIMAL(16, 2), comment='月的券商成本')
months_remaining_commission = Column(DECIMAL(16, 2), comment='月的剩余佣金')
total_total_commission = Column(DECIMAL(16, 2), comment='总的总佣金')
total_brokerage_cost = Column(DECIMAL(16, 2), comment='总的券商成本')
total_remaining_commission = Column(DECIMAL(16, 2), comment='总的剩余佣金')
create_time = Column(BIGINT(20), comment='创建时间')
creation_time_int = Column(INTEGER(11), comment='创建时间 yyMMdd')
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
)
category = relationship("Category")
film = relationship("Film")
t_film_list = Table(
"film_list",
metadata,
Column("FID", SMALLINT(5), server_default=text("'0'")),
Column("title", String(255)),
Column("description", Text),
Column("category", String(25)),
Column("price", DECIMAL(4, 2), server_default=text("'4.99'")),
Column("length", SMALLINT(5)),
Column("rating",
Enum("G", "PG", "PG-13", "R", "NC-17"),
server_default=text("'G'")),
Column("actors", Text),
)
class FilmText(Base):
__tablename__ = "film_text"
__table_args__ = (Index("idx_title_description", "title", "description"), )
film_id = Column(SMALLINT(6), primary_key=True)
title = Column(String(255), nullable=False)
description = Column(Text)
class avg_difficulty(Base): #float
__tablename__ = "avg_difficulty"
entry_id = Column(Integer,
ForeignKey('coin_date.entry_id'),
primary_key=True)
value = Column(DECIMAL(23, 6), nullable=False)
class Publish(Base):
__table__ = Table(
'publish',
Base.metadata,
Column('id', Integer, primary_key=True),
Column('status', Enum('act', 'dis'), default='act', nullable=False),
Column('root', String(512), nullable=True),
Column('path', String(512), nullable=True),
Column('version', SmallInteger, nullable=False),
Column('project_id', Integer, ForeignKey(Project.id), nullable=False),
Column('publishgroup_id',
Integer,
ForeignKey(PublishGroup.id),
nullable=False),
Column('publishkind_id',
Integer,
ForeignKey(PublishKind.id),
nullable=False),
Column('user_id', Integer, ForeignKey(User.id), nullable=False),
Column('task_id', Integer, ForeignKey(Task.id)),
Column('diskspace', DECIMAL(10, 3)), # TODO: make use of this
Column('created', DateTime(timezone=True), server_default=func.now()),
Column('updated', DateTime(timezone=True), onupdate=func.now()),
Column('description', String(512)),
Index('ix_project_group_kind', 'project_id', 'publishgroup_id',
'publishkind_id'),
Index('ix_project_kind_root', 'project_id', 'publishkind_id', 'root'),
Index('ix_project_task', 'project_id', 'task_id'),
Index('ix_project_user', 'project_id', 'user_id'),
UniqueConstraint('publishgroup_id',
'publishkind_id',
'version',
name="uq_group_kind_version"),
UniqueConstraint('publishgroup_id',
'publishkind_id',
'root',
name="uq_group_kind_root"),
)
_publishmetadata = relationship('PublishMetadata',
backref='publish',
lazy='dynamic',
cascade="all, delete-orphan")
def __repr__(self):
return "{cls}(id={id})".format(cls=self.__class__.__name__, id=self.id)
@property
def parent(self):
'''
Return Publish parent entity.
Parent could be Sequence, Shot, Instance or Asset entity.
'''
return self.publishgroup.parent
@property
def metadata(self):
'''
Return Publish metadata from PublishMetadata entity.
'''
_metas = [m for m in self._publishmetadata]
return _metas[0].metadata if _metas else None
@metadata.setter
def metadata(self, data):
'''
Set Publish metadata into PublishMetadata entity.
'''
from . import PublishMetadata
PublishMetadata.set_metadata(self, data)
@classmethod
def find(cls,
project=None,
publishgroup=None,
publishkind=None,
version=None,
root=None,
path=None,
user=None,
status=None,
id=None):
'''
Return Publish instances by query arguments
Args:
project (Project) : parent Project instance.
publishgroup (PublishGroup) : PublishGroup parent.
publishkind (PublishKind) : PublishKind link.
version (int) : Publish version number.
root (str) : Publish root in filesystem.
path (str) : Publish path in filesystem.
user (User) : Publish user instance.
status (str) : Publish status.
id (int/list) : Publish id(s).
Returns:
A list of Publish instances matching find arguments.
'''
query = cls.query(project=project, id=id, status=status)
if publishgroup:
cls.assert_isinstance(publishgroup, 'PublishGroup')
query = query.filter(cls.publishgroup_id == publishgroup.id)
if publishkind:
cls.assert_isinstance(publishkind, 'PublishKind')
query = query.filter(cls.publishkind_id == publishkind.id)
if user:
cls.assert_isinstance(user, 'User')
query = query.filter(cls.user_id == user.id)
if version:
assert isinstance(
version, int), ('version arg must be int. Given {}'.format(
type(version)))
query = query.filter(cls.version == version)
if root:
assert isinstance(
root,
string_types), ('root arg must be string. Given {}'.format(
type(root)))
query = query.filter(cls.root == root)
if path:
assert isinstance(
path,
string_types), ('path arg must be string. Given {}'.format(
type(path)))
query = query.filter(cls.path == path)
return query.all()
@classmethod
def create(cls,
project,
publishgroup,
publishkind,
user,
version,
root,
path=None,
description=None,
status=None):
'''
Create a Publish instance.
Args:
project (Project) : parent Project instance.
publishgroup (PublishGroup) : PublishGroup parent.
publishkind (PublishKind) : PublishKind link.
user (User) : Publish User instance.
version (int) : Publish version number.
root (str) : Publish root in filesystem.
path (str) : Publish path in filesystem.
status (str) : Publish status.
Returns:
New Publish Instance.
'''
cls.assert_isinstance(project, 'Project')
cls.assert_isinstance(publishgroup, 'PublishGroup')
cls.assert_isinstance(publishkind, 'PublishKind')
cls.assert_isinstance(user, 'User')
assert isinstance(version,
int), ('version arg must be int. Given {}'.format(
type(version)))
assert isinstance(
root, string_types), ('root arg must be string. Given {}'.format(
type(root)))
data = dict(project_id=project.id,
publishgroup_id=publishgroup.id,
publishkind_id=publishkind.id,
user_id=user.id,
version=version,
root=root,
path=path,
description=description,
status=status)
return super(Publish, cls).create(**data)
class market_cap(Base): #float
__tablename__ = "market_cap"
entry_id = Column(Integer,
ForeignKey('coin_date.entry_id'),
primary_key=True)
value = Column(DECIMAL(23, 6), nullable=False)
def __init__(self, city_name, kijiji_url, num_pages, user, password, host,
port, dbname, api_key):
'''
Parameters
----------
city_name : str
The name of the city for which data is being collected. It is used
to determine the table name.
kijiji_url : str
The intial url that is used to initalizes the web scraping tools.
This is manually input and unique to each Kijiji search.
num_pages : str
This is the number of pages the web scraper parses through and runs.
It determines the length of the dataframe that is compiled and also
the runtime of the web secraping application.
user: str
The MySQL user
password : str
The password of the MySQL user. If the user has no password input
variable as 'NO'.
host : str
The host of the MySQL database.
port : str
The port that the MySQL database.
dbname : str
The name of the MySQL database.
api_key : str
This is the api key that will be used to access the geocoder api to perform
the geoprocessing data. For the moment this method uses the geocoder api
OpenCage
Methods
-------
self.table_update()
Writes each unique row of data to the MySQL table based on the built
dataframe.
.
'''
# Declaring instance variables:
self.city_name = city_name
self.kijiji_url = kijiji_url
self.num_pages = num_pages
self.api_key = api_key
# Building the table name:
self.table_name = f'kijiji_{city_name.lower()}_real_estate_listings'
# Conditional that selects if password or no password for db user:
if password == 'NO':
connect_string = f'mysql+mysqldb://{user}@{host}:{port}/{dbname}'
else:
connect_string = f'mysql+mysqldb://{user}:{password}@{host}:{port}/\
{dbname}'
# Creating the sqlalchemy engine:s
self.engine = create_engine(connect_string, echo=True)
if not self.engine.dialect.has_table(self.engine, self.table_name):
metadata = MetaData(self.engine)
# Creating table if not exists:
Table(self.table_name, metadata,
Column('Address', String(250), primary_key=True, unique=True),
Column('Price', Float),
Column('Date', Date),
Column('Bedrooms', Float),
Column('Bathrooms', Float),
Column('Size', Float),
Column('Lat', DECIMAL(38,6)),
Column('Long', DECIMAL(38,6)))
# Commiting changes:
metadata.create_all()
# Initalzing an instance variable of the web_scraped data:
raw_data = Kijiji(kijiji_url, num_pages).data
self.geoprocessed_data = kijiji_geoprocessor(raw_data, api_key).data
# Updating the table:
self.table_update()