I am trying to setup a many-to-many relationship for two tables where
I would like to allow more natural access to the data using a
dictionary interface. The exact usage is pretty complex to explain,
but I have come up with a simple example that demonstrates the same
concept. (there is a full code example at the end of the e-mail)
The tables are:
- Script: holds a code script to run
- VarTypes: Type details for variables that can be used as input and
output to the script
The relationship is:
- For each Script, there is a set of named variables.
- Each variable has a type associated with it
- Each variable can be either an input variable or an output variable
What I want to allow in the end is something like this:
script = Script()
script.code = "test code"
var1 = VarType()
var2 = VarType()
var3 = VarType()
var4 = VarType()
script.input_vars["in1"] = var1
script.input_vars["in2"] = var2
script.output_vars["out1"] = var3
script.output_vars["out2"] = var4
Is there some way to setup a many-to-many relationship to do this?
Thanks,
Allen
Here is the more complete code example to play with and see what I am
thinking so far for table definitions.
#-----------------------------------------------------------
from sqlalchemy import (create_engine, Table, Column, Integer, String, Text,
MetaData, ForeignKey)
from sqlalchemy.orm import mapper, backref, relation, create_session
engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData()
metadata.bind = engine
script_table = Table('script', metadata,
Column('id', Integer, primary_key=True),
Column('code', Text)
)
types_table = Table('types', metadata,
Column('id', Integer, primary_key=True),
Column('desc', Text))
script_var_table = Table('script_var_table', metadata,
Column('script_id', Integer, ForeignKey('script.id')),
Column('var_name', Text),
Column('input_output_type', Integer),
Column('type_id', Integer, ForeignKey('types.id')))
print "Creating all tables"
metadata.create_all(engine)
INPUT_VAR = 0
OUTPUT_VAR = 1
class Script(object):
def __init__(self):
pass
class VarType(object):
def __init__(self):
pass
mapper(VarType, types_table)
mapper(Script, script_table, properties = {
'vars':relation(VarType, secondary=script_var_table)
})
session = create_session(bind = engine)
script = Script()
script.code = "test code"
var1 = VarType()
var1.desc = "var type 1"
var2 = VarType()
var2.desc = "var type 2"
script.vars.append(var1)
script.vars.append(var2)
session.save(script)
session.flush()
# ---- WOULD LIKE --------- #
# Can this be done using
# - Custom join condition on input_output_type
# - column_mapped_collection
#
script = Script()
script.code = "test code"
var1 = VarType()
var2 = VarType()
var3 = VarType()
var4 = VarType()
script.input_vars["in1"] = var1
script.input_vars["in2"] = var2
script.output_vars["out1"] = var3
script.output_vars["out2"] = var4
print "Done"
#-----------------------------------------------------------
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
