How to specify relations using SQLAlchemy declarative syntax?
I can't find any proper documentation on how to specify relations
using the declarative syntax of SQLAlchemy.. Is it unsupported? That is, should I use the "traditional" syntax?
I am looking for a way to specify relations at a higher level, avoiding having to mess with foreign keys etc.. I'd like to just declare "addresses = OneToMany(Address)" and let the framework handle the details.. I know that Elixir can do that, but I was wondering if "plain" SQLA could do it too.
Thanks for your help!
Asked by: Kelvin325 | Posted: 28-01-2022
Answer 1
Assuming you are referring to the declarative plugin, where everything I am about to say is documented with examples:
class User(Base):
__tablename__ = 'users'
id = Column('id', Integer, primary_key=True)
addresses = relation("Address", backref="user")
class Address(Base):
__tablename__ = 'addresses'
id = Column('id', Integer, primary_key=True)
user_id = Column('user_id', Integer, ForeignKey('users.id'))
Answer 2
Look at the "Configuring Relations" section of the Declarative docs. Not quite as high level as "OneToMany" but better than fully specifying the relation.
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email = Column(String(50))
user_id = Column(Integer, ForeignKey('users.id'))
Similar questions
python - Should I create mapper objects or use the declarative syntax in SQLAlchemy?
There are two (three, but I'm not counting Elixir, as its not "official") ways to define a persisting object with SQLAlchemy:
Explicit syntax for mapper objects
from s...
python - Reverse engineer SQLAlchemy declarative class definition from existing MySQL database?
I have a pre-existing mysql database containing around 50 tables.
Rather than hand code a declarative style SqlAlchemy class (as shown here) for each table, is there a tool/script/command I can run against the mysql database that will generate a python class in the dec...
python - Declarative SQLAlchemy CREATE SQLITE in memory tables
This is how I setup my database for an application (in Flask):
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import scoped_session, create_session
from sqlalchemy.ext.declarative import declarative_base
engine = None
db_session = scoped_session(lambda: create_session(bind=engine,
autoflush=False, autocommit=False, expire_on_commit=True))
...
python - Is it possible to unload declarative classes in SQLAlchemy?
I’m working on a library where the user shall be able to simply declare a few classes which are automatically backed by the database. In short, somewhere hidden in the code, there is
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class LibraryBase(Base):
# important library stuff
and the user should then do
class MyStuff(LibraryBase):
...
python - SQLAlchemy Declarative Model with Multiple Database Sharding
I'm trying to shard my database into two: one for my main objects, another for logs. Right now, my code looks something like this:
engine = create_engine('postgresql+psycopg2://postgres:password@localhost:5432/logs')
engine2 = create_engine('postgresql+psycopg2://postgres:password@localhost:5432/logs')
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
binds = {'thing': engine,
...
python - How do I make compound columns with SQLAlchemy declarative?
I frequently make models with Text columns that hold Markdown formatted richtext. My models look like this:
class Document(Base):
id = Column(Integer, primary_key=True)
title = Column(Unicode(250))
description = Column(Text)
description_html = Column(Text)
My edit forms (a) read from and write to description and then (b) write the Markdown form...
python - How do I get a declarative model given a Table object that the model is mapped to in SQLAlchemy?
I am trying to obtain all the classes mapped to tables in
metadata.sorted_tables
I would like to replace these tables, but have to update my model's __table__ because I am using declarative.
How do I get the mapped model given a table when I am using declarative?
Thanks!
python - SQLAlchemy declarative property from join (single attribute, not whole object)
I wish to create a mapped attribute of an object which is populated from another table.
Using the SQLAlchemy documentation example, I wish to make a user_name field exist on the Address class such that it can be both easily queried and easily accessed (without a second round trip to the database)
For example, I wish to be able to query and filter by user_name Address.query.filter(Address...
python - onupdate based on another field with sqlalchemy declarative base
I use sqlalchemy with the pyramid framework, and i want to link a person to his geographical department using his postcode.
So i try to use the onupdate argument when defining the department_id column define the department_id.
see fallowing code:
from datetime import date
from emailing.models import Base, DBSession
from sqlalchemy import Column, Integer, Unicode, Text, DateTime, Sequence, Boolean, Date, Uni...
unit testing - How to properly test a Python Flask system based on SQLAlchemy Declarative
I have a project that I've been working on for a while, which is written in Flask, and uses SQLAlchemy with the Declarative extension http://flask.pocoo.org/docs/patterns/sqlalchemy/. I've recently decided to start unit testing my project, but for the life of me, I can't seem to figure out how to make it work.
I looked at
python - Should I create mapper objects or use the declarative syntax in SQLAlchemy?
There are two (three, but I'm not counting Elixir, as its not "official") ways to define a persisting object with SQLAlchemy:
Explicit syntax for mapper objects
from s...
python - Reverse engineer SQLAlchemy declarative class definition from existing MySQL database?
I have a pre-existing mysql database containing around 50 tables.
Rather than hand code a declarative style SqlAlchemy class (as shown here) for each table, is there a tool/script/command I can run against the mysql database that will generate a python class in the dec...
python - is there need for a more declarative way of expressing regular expressions ? :)
I am trying to create a Python function that can take an plain English description of a regular expression and return the regular expression to the caller.
Currently I am thinking of the description in YAML format.
So, we can store the description as a raw string variable, which is passed on to this another function and output of that function is then passed to the 're' module. Following is a rather simplistic exam...
python - SQLAlchemy declarative one-to-many not defined error
I'm trying to figure how to define a one-to-many relationship using SQLAlchemy's declarative ORM, and trying to get the example to work, but I'm getting an error that my sub-class can't be found (naturally, because it's declared later...)
InvalidRequestError: When initializing mapper Mapper|Parent|parent, expression 'C...
python - Declarative SQLAlchemy CREATE SQLITE in memory tables
This is how I setup my database for an application (in Flask):
from sqlalchemy.engine import create_engine
from sqlalchemy.orm import scoped_session, create_session
from sqlalchemy.ext.declarative import declarative_base
engine = None
db_session = scoped_session(lambda: create_session(bind=engine,
autoflush=False, autocommit=False, expire_on_commit=True))
...
python - Is it possible to unload declarative classes in SQLAlchemy?
I’m working on a library where the user shall be able to simply declare a few classes which are automatically backed by the database. In short, somewhere hidden in the code, there is
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class LibraryBase(Base):
# important library stuff
and the user should then do
class MyStuff(LibraryBase):
...
python - How can I decide which declarative model to instantiate, based on row information
I'm building a webapp that has optional Facebook Login. The users created through the Facebook API are handled differently at several points in my application. I want to encapsulate these differences in a subclass of Person that overrides methods.
class Person(Model):
def get_profile_picture(self):
return profile_pictures.url(self.picture)
class FacebookPerson(Person):
def get_...
python - data validation for SQLAlchemy declarative models
I'm using CherryPy, Mako templates, and SQLAlchemy in a web app. I'm coming from a Ruby on Rails background and I'm trying to set up some data validation for my models. I can't figure out the best way to ensure, say, a 'name' field has a value when some other field has a value. I tried using SAValidation but it allowed me to create new rows where a required column w...
python - Declarative GTK
TL;DR: Is there a library for declarative UI creation using GTK? Preferrably with Python support.
I'm a Python/Django developer, most of my experience about user interfaces is from the web, where declarative, loosely coupled UI designs are standard. Recently I've had to create a GUI app using Java/Swing for a school project and ended up using SwiXML to cre...
python - SQLAlchemy Declarative Model with Multiple Database Sharding
I'm trying to shard my database into two: one for my main objects, another for logs. Right now, my code looks something like this:
engine = create_engine('postgresql+psycopg2://postgres:password@localhost:5432/logs')
engine2 = create_engine('postgresql+psycopg2://postgres:password@localhost:5432/logs')
DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
binds = {'thing': engine,
...
Still can't find your answer? Check out these communities...
PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python