SQLAlchemy How to map a single column of a one-to-one relationship using declarative

Question

This is due to this issue of conversion to a declarative method and a property of a column that has never been answered.

We are trying to create a Flask-SQLAlchemy project in an existing schema (one of which we cannot change) and decided to use declarative syntax so that we can organize classes into several files in a reasonable way for maintenance. This works for most of our relationships, with the exception of what we call, in the absence of a better term, attribute tables. These are separate sheet tables from some main object and usually contain some kind of controlled dictionary for the attribute. The goal in ORM is to map all of these (of which there are many) table types, as if they were properties of the primary table.

Here is an example of an SQA with two tables:

class MarkerType(db.Model): __tablename__="mrk_types" _marker_type_key = db.Column(db.Integer,primary_key=True) name = db.Column(db.String()) class Marker(db.Model): __tablename__="mrk_marker" _marker_key=db.Column(db.Integer,primary_key=True) _marker_type_key=db.Column(db.Integer()) 

We want to access MarkerType.name as if we were saying Marker.markertype, or in a query like Marker.markertype == 'thing'. The only way I could handle the column_property class in the Marker class is as follows:

 markertype = db.column_property( db.select([MarkerType.name]). where(MarkerType._marker_type_key==_marker_type_key) ) 

However, I cannot find a way to do this in a declarative way, and perhaps this does not exist. Is there any reasonable way to achieve this without worrying about my import, or, even worse, about the way I practice? Since we have hundreds of tables to display, I see that this is a nightmare for maintenance if we need to worry about the class and import order.

If all this is completely impossible, wishful thinking, what is better suited for comparing these tables?

Answer 1

This sounds like a great precedent for the Association Proxy . This proxies the field of the related model. In this case, the implementation will be as follows:

 from sqlalchemy.orm import relationship from sqlalchemy.ext.associationproxy import association_proxy class MarkerType(db.Model): __tablename__="mrk_types" _marker_type_key = db.Column(db.Integer, primary_key=True) name = db.Column(db.String()) class Marker(db.Model): __tablename__="mrk_marker" _marker_key=db.Column(db.Integer,primary_key=True) _marker_type_key=db.Column(db.Integer, ForeignKey('mrk_types._marker_type_key') mt = relationship(MarkerType, uselist=False) marker_type = association_proxy('mt', 'name') 

This allows you to query as session.query(Marker).filter_by(marker_type='my_marker_type')

The marker_type field is a proxy server for the name field of the MarkerType object. The mt field (relation field) may reference this object.

Pay attention to uselist=False . This means that each marker has 1 type of marker. The link automatically detects the ForeignKey and uses it.

Answer 2

So, from what I collect, you are stuck in two tables. One with the integer col, one with the string col.

 Class Marker _marker_key_ primary_key # _ = Integer ## This can be ignored as its unnecessary. 

Another has

 Class MarkerType _marker_type_key = primary_key name = String 

When I read it, you want the Class Marker to have many lines of Class MarkerType that you can easily manipulate or invoke. Although, I'm not quite sure what you want.

If so, you can achieve this by believing that you control the seeding of the database. You can create a flag at the beginning of each name that points to the Markers primary key.

Example: MarkerType.name = 10324_Orange

I am not familiar with the use of SQLAlchemy without sessions and do not really like doing research, so I will just write my answer, assuming you are using SQLAlchemy sessions, so you can get the concept and configure it necessary.

 ### !!! ASSUME 'create_session' method exists that #### creates a sqlalchemy session instance Class Marker: # ... initialize and such # ... then add these helper methods ## Get all properties linked to this primary table row def marker_types(): return db.query(MarkerType). filter(MarkerType.name.like(str(self._marker_key_)+"_%")).all() ## Get specific property linked to this primary table row def marker_type(marker_type_name): db = create_session() marker_type_list = db.query(MarkerType). filter(MarkerType.name.like(str(self._marker_key_)+"_%") AND marker_type_name == MarkerType.name ).first() db.close() return marker_type_list def update_marker_type(old_val, new_val) db = create_session() updated_marker_type = marker_type(old_val) updated_marker_type.name = str(self._marker_key_)+" "+new_val db.close() return True def create_marker_type(val) marker_type = MarkerType(name = str(self._marker_key_)+" "+val) db = create_session() db.add(marker_type) db.commit() db.close() return marker_type._marker_type_key 

Here you can add additional flags to the name bar. Things like attribute type.

 Marker.id = 193 MarkerType.id = 1 MarkerType.name = "193_color_Black" MarkerType.id = 2 MarkerType.name = "193_style_Fine" 

This optional flag allows you to search for common attributes, specific names associated with your particular string, and is much more useful, albeit a bit more complicated. It really depends on your use case.

Answer 3

Use relationship to allow access to marker_type from the Marker table and specify a ForeignKey constraint, so SQLAlchemy understands the relationship between tables.

This allows you to easily access the MarkerType attribute from a marker entry, as well as request MarkerType.name . The following shows the nesting of two entries, and then filtering based on the name attribute.

 >>> db.session.add(Marker(marker_type=MarkerType(name="blue"))) >>> db.session.add(Marker(marker_type=MarkerType(name="red"))) >>> db.session.commit() >>> markers = Marker.query.all() >>> print({m._marker_key: m.marker_type.name for m in markers}) {1: 'blue', 2: 'red'} >>> result = Marker.query.filter(Marker._marker_type_key==MarkerType._marker_type_key) \ ... .filter(MarkerType.name=='blue').all() >>> print({m._marker_key: m.marker_type.name for m in result}) {1: 'blue'} 

The order in which classes are declared does not matter, and classes do not have to be declared together. However, the schema must be registered in the same db instance, and when querying the tables, the required imported table classes must be imported.

After adding the relationship and ForeignKey to Marker, the declarative scheme will be as follows:

 class MarkerType(db.Model): __tablename__="mrk_types" _marker_type_key = db.Column(db.Integer, primary_key=True) name = db.Column(db.String()) class Marker(db.Model): __tablename__="mrk_marker" _marker_key=db.Column(db.Integer, primary_key=True) _marker_type_key=db.Column(db.Integer(), db.ForeignKey('mrk_types._marker_type_key')) marker_type=db.relationship('MarkerType')