Q&A
sqlalchemyを使い、データベースからデータを取得する時にmapperがhas no property と
エラーを吐いてしまいます。
公式チュートリアルに倣って書いているはずなのですが何が問題なのか理解しかねております。
どこの記述が問題となっているのか教えていていただけ無いでしょうか。
python 3.6.2
sqlalchemy '1.1.13'
エラー発生している部分はこちらになります。
python
1stocks = session.query(StockData) 2print(stocks) 3print(type(stocks))
Traceback はこちらになります。
--------------------------------------------------------------------------- KeyError Traceback (most recent call last) ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\mapper.py in get_property(self, key, _configure_mappers) 1858 try: -> 1859 return self._props[key] 1860 except KeyError: KeyError: 'StockData' During handling of the above exception, another exception occurred: InvalidRequestError Traceback (most recent call last) <ipython-input-17-7e886190d051> in <module>() 3 session = Session() 4 ----> 5 stocks = session.query(StockData) 6 print(stocks) 7 print(type(stocks)) ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\session.py in query(self, *entities, **kwargs) 1360 :class:`.Session`.""" 1361 -> 1362 return self._query_cls(entities, self, **kwargs) 1363 1364 @property ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\query.py in __init__(self, entities, session) 137 self.session = session 138 self._polymorphic_adapters = {} --> 139 self._set_entities(entities) 140 141 def _set_entities(self, entities, entity_wrapper=None): ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\query.py in _set_entities(self, entities, entity_wrapper) 148 entity_wrapper(self, ent) 149 --> 150 self._set_entity_selectables(self._entities) 151 152 def _set_entity_selectables(self, entities): ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\query.py in _set_entity_selectables(self, entities) 178 aliased_adapter 179 ) --> 180 ent.setup_entity(*d[entity]) 181 182 def _mapper_loads_polymorphically_with(self, mapper, adapter): ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\query.py in setup_entity(self, ext_info, aliased_adapter) 3583 self.selectable = ext_info.selectable 3584 self.is_aliased_class = ext_info.is_aliased_class -> 3585 self._with_polymorphic = ext_info.with_polymorphic_mappers 3586 self._polymorphic_discriminator = \ 3587 ext_info.polymorphic_on ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\util\langhelpers.py in __get__(self, obj, cls) 762 if obj is None: 763 return self --> 764 obj.__dict__[self.__name__] = result = self.fget(obj) 765 return result 766 ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\mapper.py in _with_polymorphic_mappers(self) 1946 def _with_polymorphic_mappers(self): 1947 if Mapper._new_mappers: -> 1948 configure_mappers() 1949 if not self.with_polymorphic: 1950 return [] ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\mapper.py in configure_mappers() 2870 if not mapper.configured: 2871 try: -> 2872 mapper._post_configure_properties() 2873 mapper._expire_memoizations() 2874 mapper.dispatch.mapper_configured( ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\mapper.py in _post_configure_properties(self) 1763 1764 if prop.parent is self and not prop._configure_started: -> 1765 prop.init() 1766 1767 if prop._configure_finished: ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\interfaces.py in init(self) 182 183 self._configure_started = True --> 184 self.do_init() 185 self._configure_finished = True 186 ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\relationships.py in do_init(self) 1655 self._check_cascade_settings(self._cascade) 1656 self._post_init() -> 1657 self._generate_backref() 1658 self._join_condition._warn_for_conflicting_sync_targets() 1659 super(RelationshipProperty, self).do_init() ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\relationships.py in _generate_backref(self) 1879 1880 if self.back_populates: -> 1881 self._add_reverse_property(self.back_populates) 1882 1883 def _post_init(self): ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\relationships.py in _add_reverse_property(self, key) 1595 1596 def _add_reverse_property(self, key): -> 1597 other = self.mapper.get_property(key, _configure_mappers=False) 1598 self._reverse_property.add(other) 1599 other._reverse_property.add(self) ~\Anaconda3\envs\untitled\lib\site-packages\sqlalchemy\orm\mapper.py in get_property(self, key, _configure_mappers) 1860 except KeyError: 1861 raise sa_exc.InvalidRequestError( -> 1862 "Mapper '%s' has no property '%s'" % (self, key)) 1863 1864 def get_property_by_column(self, column): InvalidRequestError: Mapper 'Mapper|History|history' has no property 'StockData'
全文はこちらになります。
python
1import sqlalchemy 2from sqlalchemy import create_engine 3engine = create_engine('sqlite:///:memory:', echo = True) 4from sqlalchemy.ext.declarative import declarative_base 5Base = declarative_base() 6from sqlalchemy.orm import sessionmaker 7Session = sessionmaker(bind=engine) 8session = Session() 9from sqlalchemy import Column, Integer, String, Float, Date, DateTime, SmallInteger, Boolean 10from sqlalchemy import ForeignKey 11from sqlalchemy.sql import select, text 12from sqlalchemy.orm import relationship 13import datetime 14import pandas as pd 15import numpy as np 16import os 17 18class StockData(Base): 19 """銘柄の情報を持つクラス""" 20 __tablename__ = 'stock' 21 code = Column(Integer, primary_key=True) # 銘柄コード 22 name = Column(String(64), nullable=False) # 銘柄の名前 23 category_id = Column(Integer, nullable=True, index=True) # 業種 24 activated = Column(Boolean, default=False) # backtest対象か否か 25 created_at = Column(DateTime, default=datetime.now()) # 作成日時 26 updated_at = Column(DateTime, default=datetime.now(), onupdate=datetime.now()) # 更新日時 27 28class History(Base): 29 """価格情報を持つクラス""" 30 __tablename__ = 'history' 31 code = Column(Integer,ForeignKey('stock.code'), primary_key=True, ) # 銘柄コード 32 date = Column(Date, primary_key=True, unique=False) # 日付 33 #raw_close_price = Column(Float, nullable=False) # 終値（株式分割調整前） 34 Open = Column(Float, nullable=False) # 始値 35 High = Column(Float, nullable=False) # 高値 36 Low = Column(Float, nullable=False) # 安値 37 Close = Column(Float, nullable=False) # 終値 38 Volume = Column(Integer, nullable=False) # 出来高 39 40 stock = relationship('StockData', back_populates='history') 41 42 43StockData.code = relationship("History", order_by=History.code, back_populates="StockData") 44Base.metadata.create_all(engine) 45 46# データベースへのデータ挿入 pandas.to_sqlを使用 47files = os.listdir('dd') 48for file in files: 49 if(os.path.isfile('dd/' + file)): 50 hist = pd.read_csv('dd/' + file, 51 names=["date", "Open", "High", "Low", "Close", "Volume"], 52 usecols=range(6)) 53 stk = pd.read_csv('dd/' + 'info' + '/' + file, names=["code", "name", "category_id"]) 54 codes = np.repeat(np.array(stk["code"]), (len(hist["date"]))) 55 hist['code'] = codes 56 lst = hist.columns.tolist() 57 lst.remove('code') 58 lst.insert(0, 'code') 59 hist = hist[lst] 60 # バックテスト対象,作成日時,更新日時のパラメータの追加 61 stk['activated'] = True 62 stk['created_at'] = datetime.now() 63 stk['updated_at'] = datetime.now() 64 65 stk.to_sql('stock', engine, index=False, if_exists='append') 66 hist.to_sql('history', engine, index=False, if_exists='append') 67 68stocks = session.query(StockData) 69print(stocks) 70print(type(stocks))
回答1件
0
ベストアンサー
SQLAlchemyにおいて、子になるテーブル(History)から親になるテーブル(StockData)に対してリレーションを張る場合は、親のテーブル定義にも子になるテーブルからリレーションが張られることを知らせる、以下のコードが必要になります。
python3
1class StockData(Base): 2 """銘柄の情報を持つクラス""" 3 __tablename__ = 'stock' 4 code = Column(Integer, primary_key=True) # 銘柄コード 5 name = Column(String(64), nullable=False) # 銘柄の名前 6 category_id = Column(Integer, nullable=True, index=True) # 業種 7 activated = Column(Boolean, default=False) # backtest対象か否か 8 created_at = Column(DateTime, default=datetime.now()) # 作成日時 9 updated_at = Column(DateTime, default=datetime.now(), onupdate=datetime.now()) # 更新日時 10 11 history = relationship('History') # << 追加
投稿2018/01/27 05:40
総合スコア985
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2018/01/27 06:28 編集
2018/01/27 06:49 編集
2018/01/27 07:05
2018/01/27 07:58 編集
2018/01/27 12:00
2018/01/27 12:10 編集