class RepresentedRepresentor(Entity):
"""Relation from a representing party to the person representing the party"""
using_options(tablename='party_representor')
from_date = Field(Date(),
default=datetime.date.today,
required=True,
index=True)
thru_date = Field(Date(), default=end_of_times, required=True, index=True)
comment = Field(camelot.types.RichText())
established_from = ManyToOne(Person,
required=True,
ondelete='cascade',
onupdate='cascade')
established_to = ManyToOne(DirectedDirector,
required=True,
ondelete='cascade',
onupdate='cascade')
class Admin(EntityAdmin):
verbose_name = _('Represented by')
list_display = ['established_from', 'from_date', 'thru_date']
form_display = [
'established_from', 'from_date', 'thru_date', 'comment'
]
field_attributes = {'established_from': {'name': _('Name')}}
class PartyAddress(Entity, Addressable):
using_options(tablename='party_address')
party = ManyToOne(Party,
required=True,
ondelete='cascade',
onupdate='cascade',
lazy='subquery',
backref=orm.backref(
'addresses',
lazy=True,
cascade='all, delete, delete-orphan'))
address = ManyToOne(Address,
required=True,
backref='party_addresses',
ondelete='cascade',
onupdate='cascade',
lazy='subquery')
from_date = schema.Column(Date(),
default=datetime.date.today,
nullable=False,
index=True)
thru_date = schema.Column(Date(),
default=end_of_times,
nullable=False,
index=True)
comment = schema.Column(Unicode(256))
def party_name(self):
return sql.select([sql.func.coalesce(Party.full_name, '')],
whereclause=(Party.id == self.party_id))
party_name = ColumnProperty(party_name, deferred=True)
def __unicode__(self):
return '%s : %s' % (six.text_type(
self.party), six.text_type(self.address))
class Admin(EntityAdmin):
verbose_name = _('Address')
verbose_name_plural = _('Addresses')
list_search = [
'party_name',
'street1',
'street2',
]
list_display = ['party_name', 'street1', 'street2', 'city', 'comment']
form_display = [
'party', 'street1', 'street2', 'city', 'comment', 'from_date',
'thru_date'
]
form_size = (700, 200)
field_attributes = dict(party_name=dict(
editable=False, name='Party', minimal_column_width=30))
def get_compounding_objects(self, party_address):
if party_address.address != None:
yield party_address.address
class PartyRelationship( Entity ):
using_options( tablename = 'party_relationship' )
from_date = Field( Date(), default = datetime.date.today, required = True, index = True )
thru_date = Field( Date(), default = end_of_times, required = True, index = True )
comment = Field( camelot.types.RichText() )
is_synchronized( 'synchronized', lazy = True )
class Admin( EntityAdmin ):
verbose_name = _('Relationship')
verbose_name_plural = _('Relationships')
list_display = ['established_from', 'established_to', 'from_date', 'thru_date']
class PartyContactMechanism(Entity):
using_options(tablename='party_contact_mechanism')
party = ManyToOne(Party,
required=True,
ondelete='cascade',
onupdate='cascade',
backref=orm.backref(
'contact_mechanisms',
lazy='select',
cascade='all, delete, delete-orphan'))
contact_mechanism = ManyToOne(ContactMechanism,
lazy='joined',
required=True,
ondelete='cascade',
onupdate='cascade')
from_date = schema.Column(Date(),
default=datetime.date.today,
nullable=False,
index=True)
thru_date = schema.Column(Date(), default=end_of_times, index=True)
comment = schema.Column(Unicode(256))
@hybrid.hybrid_property
def mechanism(self):
if self.contact_mechanism != None:
return self.contact_mechanism.mechanism
@mechanism.setter
def mechanism_setter(self, value):
if value != None:
if self.contact_mechanism:
self.contact_mechanism.mechanism = value
else:
self.contact_mechanism = ContactMechanism(mechanism=value)
@mechanism.expression
def mechanism_expression(self):
return sql.select([ContactMechanism.mechanism],
whereclause=ContactMechanism.id ==
self.contact_mechanism_id).as_scalar()
def party_name(self):
return sql.select([Party.full_name],
whereclause=(Party.id == self.party_id))
party_name = ColumnProperty(party_name, deferred=True)
def __unicode__(self):
return six.text_type(self.contact_mechanism)
Admin = PartyContactMechanismAdmin
class Person( Party ):
"""Person represents natural persons
"""
using_options( tablename = 'person', inheritance = 'multi' )
first_name = Field( Unicode( 40 ), required = True )
last_name = Field( Unicode( 40 ), required = True )
# end short person definition
middle_name = Field( Unicode( 40 ) )
personal_title = Field( Unicode( 10 ) )
suffix = Field( Unicode( 3 ) )
sex = Field( Unicode( 1 ), default = u'M' )
birthdate = Field( Date() )
martial_status = Field( Unicode( 1 ) )
social_security_number = Field( Unicode( 12 ) )
passport_number = Field( Unicode( 20 ) )
passport_expiry_date = Field( Date() )
is_staff = Field( Boolean, default = False, index = True )
is_superuser = Field( Boolean, default = False, index = True )
picture = Field( camelot.types.Image( upload_to = 'person-pictures' ), deferred = True )
comment = Field( camelot.types.RichText() )
employers = OneToMany( 'EmployerEmployee', inverse = 'established_to', cascade='all, delete, delete-orphan' )
@property
def note(self):
for person in self.__class__.query.filter_by(first_name=self.first_name, last_name=self.last_name):
if person != self:
return _('A person with the same name allready exists')
@property
def name( self ):
# we don't use full name in here, because for new objects, full name will be None, since
# it needs to be fetched from the db first
return u'%s %s' % ( self.first_name, self.last_name )
def __unicode__( self ):
return self.name or ''
class Admin( Party.Admin ):
verbose_name = _( 'Person' )
verbose_name_plural = _( 'Persons' )
list_display = ['first_name', 'last_name', 'contact_mechanisms_email', 'contact_mechanisms_phone']
form_display = TabForm( [( _('Basic'), Form( [HBoxForm( [Form( [WidgetOnlyForm('note'), 'first_name', 'last_name', 'sex'] ),
Form( ['picture', ] ),
] ),
'contact_mechanisms', 'comment', ], scrollbars = False ) ),
( _('Official'), Form( ['birthdate', 'social_security_number', 'passport_number',
'passport_expiry_date', 'addresses', ], scrollbars = False ) ),
( _('Work'), Form( ['employers', 'directed_organizations', 'shares'], scrollbars = False ) ),
( _('Status'), Form( ['status'] ) ),
] )
field_attributes = dict( Party.Admin.field_attributes )
field_attributes['note'] = {'delegate':delegates.NoteDelegate}
class PartyContactMechanism( Entity ):
using_options( tablename = 'party_contact_mechanism' )
def __new__(cls, *args, **kwargs):
party_contact_mechanism = super(PartyContactMechanism, cls).__new__(cls, *args, **kwargs)
setattr(party_contact_mechanism, '_contact_mechanism_mechanism', None)
return party_contact_mechanism
party = ManyToOne( 'Party', required = True, ondelete = 'cascade', onupdate = 'cascade' )
contact_mechanism = ManyToOne( 'ContactMechanism', required = True, ondelete = 'cascade', onupdate = 'cascade' )
from_date = Field( Date(), default = datetime.date.today, required = True, index = True )
thru_date = Field( Date(), default = end_of_times, index = True )
comment = Field( Unicode( 256 ) )
def _get_contact_mechanism_mechanism(self):
if self._contact_mechanism_mechanism != None:
return self._contact_mechanism_mechanism
return self.mechanism
def _set_contact_mechanism_mechanism(self, mechanism):
self._contact_mechanism_mechanism = mechanism
if mechanism != None:
if self.contact_mechanism:
self.contact_mechanism.mechanism = mechanism
else:
self.contact_mechanism = ContactMechanism( mechanism=mechanism )
#
# A property to get and set the mechanism attribute of the
# related contact mechanism object
#
contact_mechanism_mechanism = property( _get_contact_mechanism_mechanism,
_set_contact_mechanism_mechanism )
@ColumnProperty
def mechanism( self ):
return sql.select( [ContactMechanism.mechanism],
whereclause = (ContactMechanism.id==self.contact_mechanism_id))
@ColumnProperty
def party_name( self ):
return sql.select( [Party.full_name],
whereclause = (Party.id==self.party_id))
def __unicode__( self ):
return unicode( self.contact_mechanism )
Admin = PartyContactMechanismAdmin
def test_validate_column_types_devmode(self):
# when in devmode, strict type checking is enforced
with EnvironmentVarGuard() as env:
env["SQLTASK_DEVELOPER_MODE"] = "1"
validate = BaseEngineSpec.validate_column_value
str10_column = Column("str10_col", String(10), nullable=False)
str_column = Column("str_col", String, nullable=False)
int_column = Column("int_col", Integer())
float_column = Column("float_col", Float(), nullable=False)
date_column = Column("float_col", Date(), nullable=False)
datetime_column = Column("float_col", DateTime(), nullable=False)
self.assertIsNone(validate(date(2019, 12, 31), date_column))
self.assertIsNone(validate(date(2019, 12, 31), datetime_column))
self.assertIsNone(validate("abc", str10_column))
self.assertIsNone(validate("1234567890", str10_column))
self.assertIsNone(validate("123456789012345", str_column))
self.assertIsNone(validate(Decimal("1234.567"), float_column))
self.assertIsNone(validate(1.1, float_column))
self.assertIsNone(validate(1, float_column))
self.assertIsNone(validate(1, int_column))
self.assertIsNone(validate(None, int_column))
self.assertRaises(ValueError, validate, datetime.utcnow(), date_column)
self.assertRaises(ValueError, validate, None, str_column)
self.assertRaises(ValueError, validate, "12345678901", str10_column)
self.assertRaises(ValueError, validate, 12345, str_column)
self.assertRaises(ValueError, validate, 12345.5, int_column)
class PartyRelationship(Entity):
using_options(tablename='party_relationship')
from_date = Field(Date(),
default=datetime.date.today,
required=True,
index=True)
thru_date = Field(Date(), default=end_of_times, required=True, index=True)
comment = Field(camelot.types.RichText())
row_type = schema.Column(Unicode(40), nullable=False)
__mapper_args__ = {'polymorphic_on': row_type}
class Admin(EntityAdmin):
verbose_name = _('Relationship')
verbose_name_plural = _('Relationships')
list_display = ['from_date', 'thru_date']
def table_from_fields(engine,
metadata,
fprefix,
filename,
record_type,
table_fields,
pks=[]):
table_name = '_'.join(filter(None,
[fprefix, filename, record_type])).lower()
if not isinstance(table_fields[0], str) and len(table_fields[0]) == 2:
column_names = [f[0] for f in table_fields]
column_sizes = [f[1] for f in table_fields]
else:
column_names = table_fields
column_sizes = [None] * len(table_fields)
columns = []
for column_size, column_name in zip(column_sizes, column_names):
if column_name in ['RECORD_TYPE', 'UPDATE_MARKER']:
continue
if column_name in DATE_FIELDS:
type_ = Date()
elif column_name.endswith('_TIME'):
type_ = Time()
elif column_name in INT_FIELDS:
type_ = Integer()
elif column_size is None:
type_ = Text()
else:
type_ = String(column_size)
columns.append(
Column(column_name.lower(), type_, primary_key=column_name in pks))
table = Table(table_name, metadata, *columns)
return table
class Rating(BaseModel):
"""
Rating table.
"""
__tablename__ = "ratings"
#{ Columns
rating_id = Column(Integer(), autoincrement=True, primary_key=True)
rater_id = Column(Integer(6), ForeignKey("users.user_id"))
movie_id = Column(Integer(5), ForeignKey("movies.movie_id"))
rating_date = Column(Date(), nullable=True)
rating_value = Column(Integer(length=1))
#{ Relationships
rater = relation(User, backref="rated_movies")
movie = relation(Movie, backref="ratings")
#}
@classmethod
def get_average_global_rating(cls, db_session):
"""Return the average global rating."""
query = db_session.query(func.avg(cls.rating_value)).one()
return query[0]
class ForgetPasswordId(Base_db):
__tablename__ = "forget_password"
id = Column(Integer, primary_key=True, index=True)
email = Column(String)
reset_code = Column(String)
is_active = Column(Boolean, default=True)
expired_in = Column(Date(), default=func.now())
def to_mysql(code, df):
table_name = 't_{0}'.format(code)
db_settings = {
"host": 'localhost',
"database": 'StockPrice_Yahoo_1',
"user": '******',
"password": '******',
"port": '3306'
}
engine = create_engine(
'mysql://{user}:{password}@{host}:{port}/{database}'.format(
**db_settings))
# sqlalchemy.typesで定義されたデータ型を辞書形式で設定
dtype = {
'Date': Date(),
'Open': Integer(),
'High': Integer(),
'Low': Integer(),
'Close': Integer(),
'Volume': Integer(),
'AdjClose': Integer()
}
df.to_sql(table_name, engine, if_exists='replace', dtype=dtype)
# 主キーを設定
# 参考 https://stackoverflow.com/questions/30867390/python-pandas-to-sql-how-to-create-a-table-with-a-primary-key
with engine.connect() as con:
con.execute(
'ALTER TABLE `{0}` ADD PRIMARY KEY (`Date`);'.format(table_name))
class PartyContactMechanism(Entity):
using_options(tablename='party_contact_mechanism')
party = ManyToOne(Party,
required=True,
ondelete='cascade',
onupdate='cascade')
contact_mechanism = ManyToOne(ContactMechanism,
lazy='joined',
required=True,
ondelete='cascade',
onupdate='cascade')
from_date = Field(Date(),
default=datetime.date.today,
required=True,
index=True)
thru_date = Field(Date(), default=end_of_times, index=True)
comment = Field(Unicode(256))
@hybrid.hybrid_property
def mechanism(self):
if self.contact_mechanism != None:
return self.contact_mechanism.mechanism
@mechanism.setter
def mechanism_setter(self, value):
if value != None:
if self.contact_mechanism:
self.contact_mechanism.mechanism = value
else:
self.contact_mechanism = ContactMechanism(mechanism=value)
@mechanism.expression
def mechanism_expression(self):
return ContactMechanism.mechanism
def party_name(self):
return sql.select([Party.full_name],
whereclause=(Party.id == self.party_id))
party_name = ColumnProperty(party_name, deferred=True)
def __unicode__(self):
return unicode(self.contact_mechanism)
Admin = PartyContactMechanismAdmin
class UserInfoHistory(Base):
__tablename__ = 'user_info_history'
user_id = Column(Integer(), primary_key=True)
time = Column(Date())
changed_field = Column(String())
old_value = Column(String())
new_value = Column(String())
class Profile(Base):
__tablename__ = 'profile'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('user.id'))
age = Column(Integer)
gender = Column(Integer)
phone = Column(String(11))
email = Column(String(256))
birthday = Column(Date())
def initialise(metadata):
#===============================================================================
from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy.types import Integer, Float, Unicode, Date, Enum, BLOB
Table(
'Chapter',
metadata,
Column('oid', Integer(), primary_key=True),
Column('title', Unicode(), nullable=False),
)
Table(
'Question',
metadata,
Column('oid', Integer(), primary_key=True),
Column('content', BLOB(), nullable=False),
Column('format', Enum('PNG', 'TIFF', 'GIF', 'JPEG'), nullable=False),
Column('width', Integer(), nullable=False),
Column('height', Integer(), nullable=False),
Column('chapter', ForeignKey('Chapter.oid'), nullable=False),
Column('answers', Unicode(), nullable=False),
Column('correct', Unicode(1), nullable=False),
)
Table(
'Questionnaire',
metadata,
Column('oid', Integer(), primary_key=True),
Column('date', Date(), nullable=False),
Column('title', Unicode(), nullable=False),
)
Table(
'QuestionnaireDefn',
metadata,
Column('oid', Integer(), primary_key=True),
Column('questionnaire',
ForeignKey('Questionnaire.oid'),
nullable=False),
Column('question', ForeignKey('Question.oid'), nullable=False),
Column('rank', Integer(), nullable=False),
Column('page', Integer(), nullable=False),
Column('offset', Float(), nullable=False),
)
Table(
'Paper',
metadata,
Column('oid', Integer(), primary_key=True),
Column('student', Unicode(), nullable=False),
Column('questionnaire',
ForeignKey('Questionnaire.oid'),
nullable=False),
)
Table(
'PaperAnswer',
metadata,
Column('oid', Integer(), primary_key=True),
Column('paper', ForeignKey('Paper.oid'), nullable=False),
Column('answer', Unicode(1), nullable=False),
)
def write_info(self, table_name, info):
# sqlalchemy.typesで定義されたデータ型を辞書形式で設定
dtype = {
'Code': Integer(),
'StockName': Text(),
'Date': Date(),
'Open': Text()
}
info.to_sql(table_name, self.engine, if_exists='replace', dtype=dtype)
class UserInfo(Base):
__tablename__ = 'user_info'
user_id = Column(Integer(), primary_key=True)
first_name = Column(String())
last_name = Column(String())
user_name = Column(String())
bio = Column(String())
trackable_online = Column(Boolean())
last_modified = Column(Date())
def coerce_compared_value(self, op, value):
""""如果query.filter(Model.field==value)
的value是date类型，那么使用Date()展开比较的参数
"""
if isinstance(value, datetime):
return self
elif isinstance(value, date):
return Date().coerce_compared_value(op, value)
else:
return self
class Person(Party):
"""Person represents natural persons
"""
using_options(tablename='person')
party_id = Field(Integer, ForeignKey('party.id'), primary_key=True)
__mapper_args__ = {'polymorphic_identity': u'person'}
first_name = Field(Unicode(40), required=True)
last_name = Field(Unicode(40), required=True)
# end short person definition
middle_name = Field(Unicode(40))
personal_title = Field(Unicode(10))
suffix = Field(Unicode(3))
sex = Field(Unicode(1), default=u'M')
birthdate = Field(Date())
martial_status = Field(Unicode(1))
social_security_number = Field(Unicode(12))
passport_number = Field(Unicode(20))
passport_expiry_date = Field(Date())
is_staff = Field(Boolean, default=False, index=True)
is_superuser = Field(Boolean, default=False, index=True)
picture = Field(camelot.types.Image(upload_to='person-pictures'),
deferred=True)
comment = Field(camelot.types.RichText())
employers = OneToMany('EmployerEmployee',
inverse='established_to',
cascade='all, delete, delete-orphan')
@property
def note(self):
for person in self.__class__.query.filter_by(
first_name=self.first_name, last_name=self.last_name):
if person != self:
return _('A person with the same name already exists')
@property
def name(self):
# we don't use full name in here, because for new objects, full name will be None, since
# it needs to be fetched from the db first
return u'%s %s' % (self.first_name, self.last_name)
def __unicode__(self):
return self.name or ''
class AuthenticationMechanism(Entity):
__tablename__ = 'authentication_mechanism'
authentication_type = Column(camelot.types.Enumeration([
(1, 'operating_system'), (2, 'database')
]),
nullable=False,
index=True,
default='operating_system')
username = Column(Unicode(40), nullable=False, index=True, unique=True)
password = Column(Unicode(200), nullable=True, index=False, default=None)
from_date = Column(Date(),
default=datetime.date.today,
nullable=False,
index=True)
thru_date = Column(Date(),
default=end_of_times,
nullable=False,
index=True)
last_login = Column(DateTime())
@classmethod
def get_or_create(cls, username):
session = Session()
authentication = session.query(cls).filter_by(
username=username).first()
if not authentication:
authentication = cls(username=username)
session.add(authentication)
session.flush()
return authentication
def __unicode__(self):
return self.username
class Admin(EntityAdmin):
verbose_name = _('Authentication mechanism')
list_display = [
'authentication_type', 'username', 'from_date', 'thru_date',
'last_login'
]
def map_types(df):
dtypedict = {}
for i, j in zip(df.columns, df.dtypes):
if "object" in str(j):
dtypedict.update({i: NVARCHAR(length=255)})
if "float" in str(j):
dtypedict.update({i: Float(precision=2, asdecimal=True)})
if "int" in str(j):
dtypedict.update({i: Integer()})
if "datetime" in str(j):
dtypedict.update({i: Date()})
return dtypedict
def load_grex_from_dwh(table_name):
db_engine = get_engine()
dwh_ora_engine = get_ora_engine("oracle_dwh_stadsdelen")
with dwh_ora_engine.connect() as connection:
df = pd.read_sql(
"""
SELECT PLANNR as ID
, PLANNAAM
, STARTDATUM
, PLANSTATUS
, OPPERVLAKTE
, GEOMETRIE_WKT AS GEOMETRY
FROM DMDATA.GREX_GV_PLANNEN_V2
""",
connection,
index_col="id",
coerce_float=True,
params=None,
parse_dates=["startdatum"],
columns=None,
chunksize=None,
)
df["geometry"] = df["geometry"].apply(wkt_loads_wrapped)
grex_rapportage_dtype = {
"id": Integer(),
"plannaam": Text(),
"startdatum": Date(),
"planstatus": Text(),
"oppervlakte": Float(),
"geometry": Geometry(geometry_type="GEOMETRY", srid=4326),
}
df.to_sql(table_name,
db_engine,
if_exists="replace",
dtype=grex_rapportage_dtype)
with db_engine.connect() as connection:
connection.execute(
f"ALTER TABLE {table_name} ADD PRIMARY KEY (id)")
connection.execute(f"""
UPDATE {table_name}
SET geometry = ST_CollectionExtract(ST_Makevalid(geometry), 3)
WHERE ST_IsValid(geometry) = False
OR ST_GeometryType(geometry) != 'ST_MultiPolygon';
COMMIT;
""")
connection.execute(f"""
ALTER TABLE {table_name}
ALTER COLUMN geometry TYPE geometry(MultiPolygon,28992)
USING ST_Transform(geometry,28992);
""")
connection.execute(
f"DELETE FROM {table_name} WHERE geometry is NULL")
class Person(Party):
"""Person represents natural persons
"""
using_options(tablename='person')
party_id = Field(camelot.types.PrimaryKey(),
ForeignKey('party.id'),
primary_key=True)
__mapper_args__ = {'polymorphic_identity': u'person'}
first_name = schema.Column(Unicode(40), nullable=False)
last_name = schema.Column(Unicode(40), nullable=False)
# end short person definition
middle_name = schema.Column(Unicode(40))
personal_title = schema.Column(Unicode(10))
suffix = schema.Column(Unicode(3))
sex = schema.Column(Unicode(1), default=u'M')
birthdate = schema.Column(Date())
martial_status = schema.Column(Unicode(1))
social_security_number = schema.Column(Unicode(12))
passport_number = schema.Column(Unicode(20))
passport_expiry_date = schema.Column(Date())
picture = schema.Column(camelot.types.Image(upload_to='person-pictures'))
comment = schema.Column(camelot.types.RichText())
@property
def note(self):
for person in self.__class__.query.filter_by(
first_name=self.first_name, last_name=self.last_name):
if person != self:
return _('A person with the same name already exists')
@property
def name(self):
# we don't use full name in here, because for new objects, full name will be None, since
# it needs to be fetched from the db first
return ' '.join(
[name for name in [self.first_name, self.last_name] if name])
def __unicode__(self):
return self.name or ''
class Student(Model):
__tablename__ = "student"
id = Column(Integer, primary_key=True)
full_name = Column(String(255), nullable=False, unique=True)
dob = Column(Date(), nullable=True)
current_school_id = Column(Integer,
ForeignKey(School.id),
nullable=False)
current_school = relationship(School, backref=backref('students'))
courses = relationship("Course",
secondary=student_course,
backref=backref("students", lazy='dynamic'))
def test_validate_column_types_non_devmode(self):
# when not in devmode, feeding incorrect types into columns should
# not raise exceptions
validate = BaseEngineSpec.validate_column_value
str10_column = Column("str10_col", String(10), nullable=False)
str_column = Column("str_col", String, nullable=False)
int_column = Column("int_col", Integer())
date_column = Column("float_col", Date(), nullable=False)
self.assertIsNone(validate(datetime.utcnow(), date_column))
self.assertIsNone(validate(None, str_column))
self.assertIsNone(validate("12345678901", str10_column))
self.assertIsNone(validate(12345, str_column))
self.assertIsNone(validate(12345.5, int_column))
class FullMarketDailyTrade(Base):
__tablename__ = 'fullmarketdailytrade'
id = Column(Integer, primary_key = True)
tradeDate = Column(Date(), unique = True)
tradeVolume = Column(Integer)
tradeValue = Column(Integer)
transaction = Column(Integer)
TAIEX = Column(Float())
change = Column(Float())
avg5d = Column(Float())
avg10d = Column(Float())
avg20d = Column(Float())
avg30d = Column(Float())
avg60d = Column(Float())
def test_defaults(self):
assert self._comp(Integer()) == 'INTEGER'
assert self._comp(SmallInteger()) == 'SMALLINT'
assert self._comp(BigInteger()) == 'BIGINT'
assert self._comp(Numeric()) == 'NUMERIC'
assert self._comp(Float()) == 'FLOAT'
assert self._comp(DateTime()) == 'TIMESTAMP(6)'
assert self._comp(Date()) == 'DATE'
assert self._comp(Time()) == 'TIME(6)'
assert self._comp(String()) == 'LONG VARCHAR'
assert self._comp(Text()) == 'CLOB'
assert self._comp(Unicode()) == 'LONG VARCHAR CHAR SET UNICODE'
assert self._comp(UnicodeText()) == 'CLOB CHAR SET UNICODE'
assert self._comp(Boolean()) == 'BYTEINT'
def get_time_type(col_type):
"""Create a time type column.
Args:
col_type (string): Type of the column.
Returns:
sqlalchemy.types.TypeEngine: Time type like date or timestamp
"""
if col_type == 'date':
return Date()
elif col_type == 'datetime':
return DateTime()
elif col_type == 'time':
return Time()
elif col_type == 'timestamp':
return TIMESTAMP()
def write_price(self, code, price):
table_name = 't_{0}'.format(code)
# sqlalchemy.typesで定義されたデータ型を辞書形式で設定
dtype = {
'Date': Date(),
'Open': Integer(),
'High': Integer(),
'Low': Integer(),
'Close': Integer(),
'Volume': Integer(),
'AdjClose': Float()
}
price.to_sql(table_name, self.engine, if_exists='replace', dtype=dtype)
# 主キーを設定
# 参考 https://stackoverflow.com/questions/30867390/python-pandas-to-sql-how-to-create-a-table-with-a-primary-key
with self.engine.connect() as con:
con.execute('ALTER TABLE "{0}" ADD PRIMARY KEY ("Date");'.format(
table_name))
