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))