Hi Mariano, I tried your code and it worked great. Now I have to look
a way of linking the "union" query with a mapper to obtain a rather
elaborate object than a raw tuple, I mean, when you execute:
q = session.query(Computer).all()
you get Computer objects, but when you execute the "union" query you
get tuples.
When looking at the resulting tuples, I noticed that those tuples that
represent Computer objects contains a field equal to "None" due to the
extra column added in the query. I guess that field could be used
somehow to distinguish between Computer-like tuples and
CompSetComputer-like ones. I'm telling you this b/c I would like to
know if there is a mechanism to link a method to a mapper. This way,
the method would process the "union" query, and would create a list of
Computer and CompSetComputer objects. Do I make myself clear?
Thanks for your help,
Sandor
PS: this is the code I tried. I made some modifications, but it's the
same as yours.
#------------------------------------------------------------------------------------------------------------------------------------------
from sqlalchemy import create_engine, Table, Column, Integer, String,
MetaData, select, union_all, schema
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy.sql.expression import null
# Engine & Session
engine = create_engine('sqlite:///:memory:', echo=False) # set to True
if you want to activate logging
Session = sessionmaker(bind=engine)
# Current session
session = Session()
# Metadata & Tables & Mappers
metadata = MetaData()
# Tables
comp_table = Table('comp', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('description', String),
Column('ip', String),
# This table has many-to-many relation with the rules table,
# so I don't have a rule_id here, instead I have
# a table rule_computers_table to store rule_ids and computer_ids,
# you know :)
# I included it in my messy-model to specify a relation but it is not
# actually present in the table definition. I appologize.
# That's why I'm including my code, to clarify my situation.
)
compset_comp_table = Table('compset_comp', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('description', String),
Column('ip', String),
Column('compset_id', Integer), # This is actually a ForeinKey to the
Computer_set table
)
# (...)
# Classes
class Computer(object): pass
class CompSetComputer(object): pass
# Mappers
mapper(Computer, comp_table)
mapper(CompSetComputer, compset_comp_table)
# Create the tables
metadata.create_all(engine)
# Testing...
# Insert
c = Computer()
c.name = "comp-1"
c.ip = "1.2.3.4"
c.description = "test comp 1"
session.add(c)
c = Computer()
c.name = "comp-2"
c.ip = "10.20.30.40"
c.description = "test comp 2"
session.add(c)
csc = CompSetComputer()
csc.name = "csc-1"
csc.ip = "11.22.33.44"
csc.description = "test compset-comp 1"
csc.compset_id = "12"
session.add(csc)
csc = CompSetComputer()
csc.name = "csc-2"
csc.ip = "101.202.303.404" # invalid ip address, i know, it's just
for testing :)
csc.description = "test compset-comp 2"
csc.compset_id = "91"
session.add(csc)
session.commit()
# Multi-select
q1 = session.query(Computer.id, Computer.name, Computer.description,
Computer.ip, null())
q2 = session.query(CompSetComputer.id, CompSetComputer.name,
CompSetComputer.description,
CompSetComputer.ip,
CompSetComputer.compset_id)
q = q2.union(q1)
print 80*"-",
print q
print 80*"-"
for x in q:
print x
# Clean up
session.close()
#
------------------------------------------------------------------------------------------------------------------------------------------
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
