I am new to SQLAlchemy (ORMs in generally) and I am trying to move an existing application on to SQLAlchemy so that we can shift some of the code complexity from the currently existing (and tedious to update) queries, to Python. Unfortunately, I haven't been able to experiment that much because I am getting errors immediately after database reflection, and I cannot find a solution. Below is an approximately minimal example of what I am trying to do and the stack trace.
Existing postgres table:
dev=> d+ gmt_file
Table "public.gmt_file"
Column | Type | Modifiers | Storage | Stats target | Description
-------------------------+--------------------------+-----------+----------+--------------+-------------
file_id | integer | not null | plain | |
file_name | character varying(255) | | extended | |
mime_type | character varying(255) | not null | extended | |
file | bytea | not null | extended | |
created | timestamp with time zone | not null | plain | |
created_by | integer | not null | plain | |
company_id | integer | not null | plain | |
deleted | timestamp with time zone | | plain | |
deleted_by | integer | | plain | |
status | character varying(1) | not null | extended | |
parent_file_id | integer | | plain | |
Indexes:
"gmt_file_pk" PRIMARY KEY, btree (file_id)
"gmt_file_company_idx" btree (company_id)
Foreign-key constraints:
"gmt_file_company_fk" FOREIGN KEY (company_id) REFERENCES gmt_company(company_id)
"gmt_file_deleted_user_fk" FOREIGN KEY (deleted_by) REFERENCES cmn_user(user_id)
"gmt_file_parent_file_id_fk" FOREIGN KEY (parent_file_id) REFERENCES gmt_file(file_id)
"gmt_file_status_fk" FOREIGN KEY (status) REFERENCES gmt_file_status_code(status)
"gmt_file_user_fk" FOREIGN KEY (created_by) REFERENCES cmn_user(user_id)
Referenced by:
TABLE "gmt_file" CONSTRAINT "gmt_file_parent_file_id_fk" FOREIGN KEY (parent_file_id) REFERENCES gmt_file(file_id)
TABLE "gmt_style" CONSTRAINT "gmt_style_file_fk" FOREIGN KEY (file_id) REFERENCES gmt_file(file_id)
SQLAlchemy Application (minimal example):
from sqlalchemy import create_engine
from sqlalchemy.orm import Session,Mapper
from sqlalchemy.ext.automap import automap_base
engine = create_engine('postgresql://user:pass@localhost:5432/dev')
Base = automap_base()
Base.prepare(engine, reflect=True)
session = Session(engine,autocommit=True)
session.query(Base.classes.gmt_file).all()
Stack Trace:
---------------------------------------------------------------------------
ArgumentError Traceback (most recent call last)
<ipython-input-5-e0284415685d> in <module>()
----> 1 session.query(str_store_defn).all()
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/session.py in query(self, *entities, **kwargs)
1258 :class:`.Session`."""
1259
-> 1260 return self._query_cls(entities, self, **kwargs)
1261
1262 @property
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py in __init__(self, entities, session)
108 self.session = session
109 self._polymorphic_adapters = {}
--> 110 self._set_entities(entities)
111
112 def _set_entities(self, entities, entity_wrapper=None):
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py in _set_entities(self, entities, entity_wrapper)
118 entity_wrapper(self, ent)
119
--> 120 self._set_entity_selectables(self._entities)
121
122 def _set_entity_selectables(self, entities):
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py in _set_entity_selectables(self, entities)
148 aliased_adapter
149 )
--> 150 ent.setup_entity(*d[entity])
151
152 def _mapper_loads_polymorphically_with(self, mapper, adapter):
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py in setup_entity(self, ext_info, aliased_adapter)
3444 self.selectable = ext_info.selectable
3445 self.is_aliased_class = ext_info.is_aliased_class
-> 3446 self._with_polymorphic = ext_info.with_polymorphic_mappers
3447 self._polymorphic_discriminator =
3448 ext_info.polymorphic_on
/usr/local/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py in __get__(self, obj, cls)
752 if obj is None:
753 return self
--> 754 obj.__dict__[self.__name__] = result = self.fget(obj)
755 return result
756
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/mapper.py in _with_polymorphic_mappers(self)
1891 def _with_polymorphic_mappers(self):
1892 if Mapper._new_mappers:
-> 1893 configure_mappers()
1894 if not self.with_polymorphic:
1895 return []
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/mapper.py in configure_mappers()
2768 if not mapper.configured:
2769 try:
-> 2770 mapper._post_configure_properties()
2771 mapper._expire_memoizations()
2772 mapper.dispatch.mapper_configured(
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/mapper.py in _post_configure_properties(self)
1708
1709 if prop.parent is self and not prop._configure_started:
-> 1710 prop.init()
1711
1712 if prop._configure_finished:
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/interfaces.py in init(self)
181 """
182 self._configure_started = True
--> 183 self.do_init()
184 self._configure_finished = True
185
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/relationships.py in do_init(self)
1630 self._check_cascade_settings(self._cascade)
1631 self._post_init()
-> 1632 self._generate_backref()
1633 self._join_condition._warn_for_conflicting_sync_targets()
1634 super(RelationshipProperty, self).do_init()
/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/relationships.py in _generate_backref(self)
1821 "'%s' on relationship '%s': property of that "
1822 "name exists on mapper '%s'" %
-> 1823 (backref_key, self, m))
1824
1825 # determine primaryjoin/secondaryjoin for the
ArgumentError: Error creating backref 'gmt_file' on relationship 'gmt_file.gmt_file': property of that name exists on mapper 'Mapper|gmt_file|gmt_file'
I have tried multiple ways of handling this error, including creating custom naming functions (name_for_scalar_relationship(), and name_for_collection_relationship()), to no avail. The ultimate goal would be to reflect the db in an automated way, and not have to write custom name mappings for the hundreds of tables that exist currently, but I am at a loss for what to do. Any help is appreciated.
Thank you
Aucun commentaire:
Enregistrer un commentaire