def test_char_length(self, metadata, connection):
t1 = Table(
"t1",
metadata,
Column("c1", VARCHAR(50)),
Column("c2", NVARCHAR(250)),
Column("c3", CHAR(200)),
Column("c4", NCHAR(180)),
)
t1.create(connection)
m2 = MetaData()
t2 = Table("t1", m2, autoload_with=connection)
eq_(t2.c.c1.type.length, 50)
eq_(t2.c.c2.type.length, 250)
eq_(t2.c.c3.type.length, 200)
eq_(t2.c.c4.type.length, 180)
class User(Base):
__tablename__ = 'user'
id = Column(BIGINT(20), primary_key=True, comment='主键')
level_id = Column(BIGINT(20),
nullable=False,
server_default=text("'0'"),
comment='会员等级ID')
password = Column(String(128), nullable=False)
username = Column(String(40), comment='登录名、昵称')
mobile = Column(String(11))
email = Column(String(80))
experience = Column(BIGINT(20),
nullable=False,
server_default=text("'0'"),
comment='经验值')
sex = Column(Enum('hide', 'male', 'female', 'other'),
nullable=False,
server_default=text("'hide'"),
comment='性别(男 male ，女 female 隐藏 hide)')
avatar = Column(String(255),
nullable=False,
server_default=text("''"),
comment='头像')
sign = Column(String(255), server_default=text("''"), comment='会员签名')
login_count = Column(BIGINT(20),
nullable=False,
server_default=text("'0'"),
comment='登陆次数')
last_login_ip = Column(String(40),
nullable=False,
server_default=text("''"),
comment='最后登陆IP')
last_login_at = Column(BIGINT(13), comment='最后登录UTC时间')
ref_user_id = Column(CHAR(32), comment='推荐人ID，空字符串表示为推荐人')
status = Column(TINYINT(1),
nullable=False,
server_default=text("'1'"),
comment='状态:( 0 禁用；1 启用, 默认1)')
deleted = Column(TINYINT(1),
nullable=False,
server_default=text("'0'"),
comment='已删除的 1 是 0 否 默认 0')
created_at = Column(BIGINT(13), comment='创建记录UTC时间')
reg_ip = Column(String(40), comment='注册IP')
reg_client = Column(String(20),
comment='客户端：web wechat android ios mobile')
class UserRoleMap(db.Model):
__tablename__ = 'user_role_map'
__table_args__ = {
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8mb4',
'mysql_collate': 'utf8mb4_unicode_ci'
}
id = Column(Integer, primary_key=True, autoincrement=True)
user = Column(String(255), nullable=True)
role = Column(String(255), nullable=True)
valid = Column(CHAR(1), nullable=False, server_default='1')
created_at = Column(DateTime, nullable=False, server_default=func.now())
updated_at = Column(DateTime,
nullable=False,
server_default=func.now(),
onupdate=func.now())
class KHACHHANG(db.Model): # Khách hàng
ID_KH = Column(Integer, autoincrement=True, primary_key=True)
MA_KH = Column(CHAR(7), nullable=False, unique=True)
TENKH = Column(Text, nullable=False, unique=True)
DIACHI = Column(Text)
SDT = Column(String(11))
NGUOILH = Column(Text) # Người liên hệ
phieuxuat = db.relationship('PHIEUXUAT',
backref='owner_phieuxuat_khachhang',
lazy='dynamic')
def __init__(self, MA_KH, TENKH, DIACHI, SDT, NGUOILH):
self.MA_KH = MA_KH
self.TENKH = TENKH
self.DIACHI = DIACHI
self.SDT = SDT
self.NGUOILH = NGUOILH
class ContentChannelmetadata(Base):
__tablename__ = "content_channelmetadata"
id = Column(CHAR(32), primary_key=True)
name = Column(String(200), nullable=False)
description = Column(String(400), nullable=False)
tagline = Column(String(150))
author = Column(String(400), nullable=False)
version = Column(Integer, nullable=False)
thumbnail = Column(Text, nullable=False)
last_updated = Column(String)
min_schema_version = Column(String(50), nullable=False)
root_id = Column(ForeignKey("content_contentnode.id"),
nullable=False,
index=True)
root = relationship("ContentContentnode")
class EventImage(Base):
__tablename__ = 'EventImages'
event_id = Column(Integer,
ForeignKey('Articles.article_id'),
primary_key=True,
index=True)
image_id = Column(Integer, primary_key=True)
time = Column(VARCHAR(50))
url = Column(CHAR(100))
def to_dict(self):
return {
'event_id': self.event_id,
'image_id': self.image_id,
'time': self.time,
'url': self.url
}
class UserAddress(Base):
__tablename__ = 'user_addresses'
id = Column(Integer, primary_key = True, nullable = False, autoincrement = True)
user_id = Column(CHAR(36), ForeignKey('users.id'), nullable = False, index = True)
address = Column(String(256), nullable = False)
size = Column(Integer, nullable = False)
kind = Column(Integer, nullable = False)
rooms = Column(Integer, nullable = False, default = 0)
baths = Column(Integer, nullable = False, default = 0)
user_addr_index = Column(Integer, nullable = False)
latitude = Column(DOUBLE, nullable = False)
longitude = Column(DOUBLE, nullable = False)
geohash5 = Column(String(5), nullable = False)
geohash6 = Column(String(6), nullable = False)
user = relationship(User)
class OUI_MAS(Base):
__tablename__ = "OUI_MAS"
'''
MA-S (small) 36-bit prefix
'''
id = Column(Integer, primary_key=True)
assignment = Column(CHAR(8))
organization = Column(String)
organization_address = Column(String)
def __init__(self, assignment = None, organization = None, organization_address = None):
self.assignment = assignment
self.organization = organization
self.organization_address = organization_address
class LimPaisA(Base):
__tablename__ = 'lim_pais_a'
__table_args__ = {'schema': 'bcim'}
id_objeto = Column(Integer, primary_key=True)
nome = Column(String(100))
nomeabrev = Column(String(50))
geometriaaproximada = Column(String(3))
sigla = Column(String(3))
codiso3166 = Column(CHAR(3))
geom = Column(Geometry('MULTIPOLYGON', 4674, from_text='ST_GeomFromEWKT', name='geometry'), index=True)
id_produtor = Column(Integer)
id_elementoprodutor = Column(Integer)
cd_insumo_orgao = Column(Integer)
nr_insumo_mes = Column(SmallInteger)
nr_insumo_ano = Column(SmallInteger)
tx_insumo_documento = Column(String(60))
def test_char_length(self):
metadata = self.metadata
t1 = Table(
"t1",
metadata,
Column("c1", VARCHAR(50)),
Column("c2", NVARCHAR(250)),
Column("c3", CHAR(200)),
Column("c4", NCHAR(180)),
)
t1.create()
m2 = MetaData(testing.db)
t2 = Table("t1", m2, autoload=True)
eq_(t2.c.c1.type.length, 50)
eq_(t2.c.c2.type.length, 250)
eq_(t2.c.c3.type.length, 200)
eq_(t2.c.c4.type.length, 180)
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']))
class Order(Base):
__tablename__ = 'orders'
OrderID = Column(SmallInteger, primary_key=True, server_default=text("nextval('orders_orderid_seq'::regclass)"))
CustomerID = Column(CHAR(6))
EmployeeID = Column(SmallInteger)
OrderDate = Column(Date)
RequiredDate = Column(Date)
ShippedDate = Column(Date)
ShipVia = Column(SmallInteger)
Freight = Column(Float)
ShipName = Column(String(40))
ShipAddress = Column(String(60))
ShipCity = Column(String(15))
ShipRegion = Column(String(15))
ShipPostalCode = Column(String(10))
ShipCountry = Column(String(15))
class Order(Base):
__tablename__ = "orders"
OrderID = Column(SmallInteger, primary_key=True)
CustomerID = Column(CHAR(6))
EmployeeID = Column(SmallInteger)
OrderDate = Column(Date)
RequiredDate = Column(Date)
ShippedDate = Column(Date)
ShipVia = Column(SmallInteger)
Freight = Column(Float)
ShipName = Column(String(40))
ShipAddress = Column(String(60))
ShipCity = Column(String(15))
ShipRegion = Column(String(15))
ShipPostalCode = Column(String(10))
ShipCountry = Column(String(15))
class Resident(db.Model):
"""
Defines a Resident as represented in the database. Along with some utility functions.
Args:
user_id (int): Must be the same as the corresponding `User.id`.
Returns:
A Resident Model object.
"""
__tablename__ = 'residents'
user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
room_number = Column(CHAR(255), ForeignKey('rooms.number', onupdate='cascade'))
mealplan_pin = Column(Integer, ForeignKey('mealplan.pin'))
created = Column(DateTime, default=datetime.utcnow)
updated = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
user = relationship('User', uselist=False, single_parent=True,
cascade='delete, delete-orphan')
packages = relationship('Package', cascade='delete, delete-orphan')
meal_plan = relationship('MealPlan', uselist=False, single_parent=True,
cascade='delete, delete-orphan')
def __init__(self, user_id):
super(Resident, self).__init__(user_id=user_id)
def __repr__(self):
"""
Allows Resident to be printed.
Returns:
str: A string representation of this Resident.
"""
return 'Resident([user_id={user_id}, room_number={room_number},' \
'created={created}, updated={updated}])'.format(**self.__dict__)
def json(self):
"""
Get JSON representation of this Resident.
Returns:
A JSON representation of this Resident.
"""
return jsonify(
user_id=self.user_id,
room_number=self.room_number,
created=self.created,
updated=self.updated
)
class DeptManager(Base):
__tablename__ = 'dept_manager'
dept_no = Column(CHAR(4),
ForeignKey('departments.dept_no'),
index=True,
primary_key=True)
emp_no = Column(Integer,
ForeignKey('employees.emp_no'),
index=True,
primary_key=True)
from_date = Column(Date, nullable=False)
to_date = Column(Date, nullable=False)
dept = relationship('Department')
def __str__(self):
return "({}, {})".format(self.dept_no, self.emp_no)
class AccessInfo(Base):
__tablename__ = 'access_info'
id = Column(INTEGER(11), primary_key=True)
name = Column(String(255), nullable=False)
sex = Column(CHAR(2), nullable=False)
phone = Column(String(255), nullable=False)
id_card = Column(String(255), nullable=False)
idcard_sha256 = Column(String(255), nullable=False)
address = Column(String(1000))
people_num = Column(String(1000))
plate_num = Column(String(255))
cause = Column(String(2000), nullable=False)
carry = Column(String(1000))
time_start = Column(String(255), nullable=False)
time_end = Column(String(255), nullable=False)
respondent_name = Column(String(255), nullable=False)
respondent_dept = Column(String(255), nullable=False)
class Charity(Base):
__tablename__ = 'charities'
eid = Column(CHAR(10), primary_key=True)
organization_name = Column(String(200), nullable=False, index=True)
cover_photo = Column(String(200))
mission = Column(Text)
city = Column(String(200), index=True)
state = Column(String(100), index=True)
zip_code = Column(String(10), index=True)
logo_url = Column(String(200))
aka_name = Column(String(200), index=True)
geographic_areas_served = Column(Text)
website_url = Column(String(200))
year_founded = Column(Integer)
codes = relationship("Ntee",
foreign_keys='Ntee.charity_id',
backref="charities")
class Channel(Main):
table: str = 'channel'
_table = Table(
table, metadata,
Column('channel_id', String(24), primary_key=True, unique=True),
Column('title', String(100), nullable=False),
Column('description', Text),
Column('age', TINYINT(1), server_default=text("9")),
Column('effect', TINYINT(1), server_default=text("2")),
Column('language', CHAR(2)), Column('tag', String(900),
nullable=False),
Column('category', TINYINT(1)),
Column('created',
DateTime,
nullable=False,
server_default=text("current_timestamp()")),
Column('updated', DateTime))
class Pegawai(Base):
__tablename__ = "pegawai"
id = Column(String(11), primary_key=True, index=True)
nama_lengkap = Column(String(60))
nama_panggilan = Column(String(60), nullable=True)
tempat_lahir = Column(String(20), nullable=True)
tanggal_lahir = Column(DateTime, nullable=True)
jenis_kelamin = Column(CHAR(1), default='L')
no_hp = Column(String(16), nullable=True)
no_wa = Column(String(16), nullable=True)
created_at = Column(DateTime, default=datetime.now)
update_at = Column(DateTime, default=datetime.now, onupdate=datetime.now)
deleted_at = Column(DateTime, nullable=True)
user = relationship("User", back_populates="pegawai")
class ContentFile(Base):
__tablename__ = "content_file"
id = Column(CHAR(32), primary_key=True)
checksum = Column(String(400), nullable=False)
extension = Column(String(40), nullable=False)
available = Column(Boolean, nullable=False)
file_size = Column(Integer)
preset = Column(String(150), nullable=False)
supplementary = Column(Boolean, nullable=False)
thumbnail = Column(Boolean, nullable=False)
priority = Column(Integer)
contentnode_id = Column(ForeignKey("content_contentnode.id"), index=True)
lang_id = Column(ForeignKey("content_language.id"), index=True)
contentnode = relationship("ContentContentnode")
lang = relationship("ContentLanguage")
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 Viagem(Base):
__tablename__ = 'bagagens_viagens'
ID = Column(BigInteger().with_variant(Integer, 'sqlite'),
primary_key=True,
autoincrement=True)
cpf = Column(CHAR(11), index=True)
data_chegada = Column(DateTime(), index=True)
origem = Column(CHAR(3), index=True)
destino = Column(CHAR(3), index=True)
localizador = Column(CHAR(10), index=True)
voo = Column(CHAR(10), index=True)
codigo_vu = Column(CHAR(17), index=True)
class Article(Base):
__tablename__ = "articles"
id = Column(Integer, primary_key=True)
hash = Column(String(32), unique=True)
slug = Column(String(255), index=True)
title = Column(String(255), index=True)
content = Column(Text, nullable=True, index=True)
status = Column(CHAR(1), index=True)
published = Column(DateTime, nullable=True)
# relationships
authors = relationship(
"Author",
secondary=article_authors,
backref=backref("authors", lazy="dynamic"),
lazy="dynamic",
)
tags = relationship(
"Tag",
secondary=article_tags,
backref=backref("tags", lazy="dynamic"),
lazy="dynamic",
)
images = relationship(
"Image",
secondary=article_images,
backref=backref("images", lazy="dynamic"),
lazy="dynamic",
)
media = relationship(
"Media",
secondary=article_media,
backref=backref("media", lazy="dynamic"),
lazy="dynamic",
)
misc = relationship(
"Misc",
secondary=article_misc,
backref=backref("misc", lazy="dynamic"),
lazy="dynamic",
)
def __repr__(self):
return "<Article(id='{id}', title='{title}')>".format(
id=self.id, title=self.title
)
class WkUserInfo(db.Model):
__tablename__ = 'wk_user_info'
id = Column(String(36),
primary_key=True,
server_default=text("''"),
comment='用户表')
user_name = Column(String(20),
nullable=False,
server_default=text("''"),
comment='姓名')
user_phone = Column(String(11),
nullable=False,
server_default=text("''"),
comment='用户电话号码')
user_pwd = Column(String(50), server_default=text("''"), comment='用户密码')
user_herf = Column(String(255),
nullable=False,
server_default=text("''"),
comment='股票个人链接 (*经济人有两个个人链接）')
user_qr_code = Column(String(255),
nullable=False,
server_default=text("''"),
comment='股票二维码 (*经纪人有两个二维码）')
user_referral_code = Column(String(50),
nullable=False,
index=True,
comment='用户推荐码')
user_up_referral_code = Column(String(50), comment='用户上级推荐码')
user_role = Column(String(20),
comment='用户角色：100003 代理商，100004 经纪人，100005 客户')
register_date_time = Column(DateTime,
nullable=False,
comment='注册时间 / 添加时间')
del_state = Column(CHAR(1),
nullable=False,
server_default=text("'0'"),
comment='删除状态：0 存在，1 已删除')
user_herf_futures = Column(String(255), comment='期货个人链接 (*经济人有两个个人链接）')
user_qr_code_futures = Column(String(255), comment='期货二维码 (*经纪人有两个二维码）')
type = Column(TINYINT(4), comment='区分客户用 (*1 为期货 2 为股票）')
identity_number = Column(String(50), comment='身份证号码')
last_login_time = Column(BIGINT(20), comment='最近登录时间')
wk_company_futures_id = Column(String(36), comment='公司 (*期货公司 id)')
wk_company_securities_id = Column(String(36), comment='公司 (*证券公司 id)')
class Timetable(db.Model):
__tablename__ = 'timetable'
id = Column(BigInteger, primary_key=True)
id_platform = Column(ForeignKey('platforms.id'), nullable=False)
id_line = Column(ForeignKey('lines.id'), nullable=False)
id_direction = Column(ForeignKey('line_directions.id'), nullable=False)
departure_hour = Column(BigInteger, nullable=False)
departure_minute = Column(BigInteger)
type = Column(ForeignKey('timetable_type.indication'), nullable=False)
low_rise = Column('low-rise', Boolean, nullable=False)
request_stop = Column(Boolean)
special_type = Column(CHAR(1))
line_direction = relationship('LineDirection')
line = relationship('Line')
platform = relationship('Platform')
timetable_type = relationship('TimetableType')
class TBscPsopreferstock(Base):
__tablename__ = 't_bsc_psopreferstock'
vc_scode = Column(VARCHAR(16), primary_key=True)
vc_code = Column(VARCHAR(16))
vc_sname = Column(VARCHAR(128))
c_is_list = Column(CHAR(1))
l_market = Column(VARCHAR(50))
vc_kind = Column(VARCHAR(8))
l_listdate = Column(VARCHAR(50))
l_delistdate = Column(VARCHAR(50))
vc_valuecur = Column(VARCHAR(3))
en_nst_volume = Column(VARCHAR(50))
en_nst_scale = Column(VARCHAR(50))
en_couponrate = Column(VARCHAR(50))
l_begin_date = Column(VARCHAR(50))
l_couponrate_adjust = Column(VARCHAR(50))
l_adjust_unit = Column(VARCHAR(50))
vc_adjust_desc = Column(VARCHAR(1024))
vc_repricing_date = Column(VARCHAR(128))
en_premium = Column(VARCHAR(50))
l_day_type = Column(VARCHAR(50))
l_day_mode = Column(VARCHAR(50))
l_interestpay_times = Column(VARCHAR(50))
vc_yearpay_date = Column(VARCHAR(128))
l_nextpay_date = Column(VARCHAR(50))
c_is_accumulate = Column(CHAR(1))
c_is_merge = Column(CHAR(1))
c_is_switch = Column(CHAR(1))
cl_transfershare_memo = Column(Text)
en_current_price = Column(VARCHAR(50))
c_is_call = Column(CHAR(1))
cl_call_desc = Column(Text)
c_is_putclause = Column(CHAR(1))
cl_putclause_desc = Column(Text)
l_repaymode = Column(VARCHAR(50))
vc_chgsecode = Column(VARCHAR(36))
vc_chgsymbol = Column(VARCHAR(36))
c_as_bond_or_equity = Column(CHAR(1), server_default=text("'3'"))
vc_eventcode = Column(VARCHAR(20))
d_updatetime = Column(DateTime)
vc_md5 = Column(VARCHAR(32))
vc_source = Column(VARCHAR(20))
vc_update_operater = Column(VARCHAR(20))
def __str__(self):
return str(self.vc_scode) + '$*' + str(self.vc_md5)
class Pessoa(Base):
__tablename__ = 'pessoa'
__table_args__ = {'schema': 'pessoal'}
nome = Column(String(100), nullable=False, unique=True)
data_nascimento = Column(Date)
id_pessoa = Column(
Integer,
primary_key=True,
server_default=text("nextval('pessoal.s_pessoa'::regclass)"))
cpf = Column(CHAR(11))
is_usuario = Column(Boolean, server_default=text("false"))
senha = Column(String)
email = Column(String, unique=True)
id_local_residencia = Column(
ForeignKey('pessoal.local_residencia.id_local_residencia'))
local_residencia = relationship('LocalResidencia')
class Person(Base):
__tablename__ = 'person'
code = Column(Integer, Sequence('person_code_seq'))
doc = Column(String(20), nullable=False)
name = Column(String(250), nullable=False)
email = Column(String(200), nullable=False)
flag = Column(CHAR(1), nullable=False, default='A')
city_code = Column(String(10), nullable=False)
city_name = Column(String(100), nullable=False)
state = Column(String(50), nullable=False)
zipcode = Column(String(10), nullable=True)
district = Column(String(50), nullable=True)
country = Column(String(50), nullable=True)
address = Column(String(50), nullable=True)
address_nr = Column(String(50), nullable=False, default='S/N')
group_grid = Column(Integer, ForeignKey('person_group.grid'))
person = relationship(PersonGroup)
class Instrument(Base):
__tablename__ = 'instrument'
__table_args__ = (Index('code', 'instrumentId'), )
instrumentName = Column(String(255))
instrumentId = Column(String(255), primary_key=True)
serialNumber = Column(String(255))
abbreviation = Column(String(255))
model = Column(String(255))
manufacturer = Column(String(255))
instrumentUncertainty = Column(Float(11))
installationDatetime = Column(String(50))
deinstallationDatetime = Column(String(50))
height = Column(String(255))
instrumentPicture = Column(CHAR(255))
installedAt = Column(ForeignKey('station.stationId'))
station = relationship('Station')
class Account(db.Model):
accountID = Column(CHAR(6), primary_key=True)
money = Column(Float, nullable=False)
settime = Column(DateTime)
accounttype = Column(Enum('saveacc', 'checkacc'))
saveacc = relationship(Saveacc,
foreign_keys=[Saveacc.accountID],
uselist=False,
passive_deletes=True)
checkacc = relationship(Checkacc,
foreign_keys=[Checkacc.accountID],
uselist=False,
passive_deletes=True)
cusforacc = relationship(
Cusforacc,
foreign_keys=[Cusforacc.accountID, Cusforacc.cusID],
uselist=False,
passive_deletes=True)
