[sqlalchemy] joinedload(), contains_eager(), ...
Hello,
I'm busy to update an application to the last SQLAlchemy version.
I have to following mapped object, with a relation:
orm.mapper(Image, table.images, properties = {
'owner' : orm.relationship(
Participant,
uselist = False
)
})
In previous version of SQLAlchemy I used something like:
# Image.query.options(orm.joinedload('owner')).\
order_by(Participant.name)
Now it seems that I have to use contains_eager() too to be able to
.order_by() on the relationship, is it correct ?
# Image.query.outerjoin(Participant).\
options(orm.contains_eager('owner')).\
order_by(Participant.name)
If I understand well joinedload() always generates an anonymous alias
for the joined relationship ?
Thanks,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
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.
attachment: jcigar.vcf
Re: [sqlalchemy] Char encoding..
Simple enough Blob is a binary type, not a character type. You need to
put binary data in there, so in python 2x that's str, ie encode it. Direct
Unicode support in sqla is via the string/Unicode types.
Sent from my iPhone
On Nov 29, 2010, at 8:43 PM, Warwick Prince warwi...@mushroomsys.com wrote:
Hi Michael
Thanks for your thoughts and comments to date.
I can replicate the problem with ease, so perhaps this will help;
# -*- coding: utf-8 -*-
e =
create_engine('mysql+mysqlconnector://user:passw...@127.0.0.1/testdb?use_unicode=0',
encoding='utf8', echo=False)
m = MetaData(e)
t = Table('test_table', m, autoload=True)
#test_table is;
Table('test_table',
MetaData(Engine(mysql+mysqlconnector://user:passw...@127.0.0.1/testdb?use_unicode=0)),
Column(u'ID', INTEGER(display_width=11), table=test_table,
primary_key=True, nullable=False), Column(u'SourceType', VARCHAR(length=10),
table=test_table), Column(u'SourceID', VARCHAR(length=128),
table=test_table), Column(u'Date', DATE(), table=test_table),
Column(u'Time', TIME(timezone=False), table=test_table), Column(u'UserID',
VARCHAR(length=10), table=test_table), Column(u'Note', BLOB(length=None),
table=test_table), Column(u'Division', VARCHAR(length=3),
table=test_table), schema=None)
# Set some row data in a dict
columns = dict(ID=1, SourceType='TEST', SourceID='WAP', Note=u'Aligot\xe9')
# The Note column is set to a unicode value for a French word with accents.
Column type is BLOB
# insert it
t.insert(values=columns).execute()
get this;
Traceback (most recent call last):
File interactive input, line 1, in module
File C:\Python26\lib\site-packages\sqlalchemy\sql\expression.py, line
1217, in execute
return e._execute_clauseelement(self, multiparams, params)
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1722,
in _execute_clauseelement
return connection._execute_clauseelement(elem, multiparams, params)
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1235,
in _execute_clauseelement
parameters=params
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1343,
in __create_execution_context
connection=self, **kwargs)
File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line 384,
in __init__
self.parameters = self.__convert_compiled_params(self.compiled_parameters)
File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line 513,
in __convert_compiled_params
param[key] = processors[key](compiled_params[key])
File C:\Python26\lib\site-packages\sqlalchemy\types.py, line 1209, in
process
return DBAPIBinary(value)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position
6: ordinal not in range(128)
It appears to be in the processing of the Binary type that something is going
wrong.
Further testing showed something interesting. I changed around the data
above and set the unicode value to the VARCHAR column SourceID. That
worked.. Therefore, the issue is related to storing a unicode value into a
BLOB. Surely I can store anything in a BLOB, or am I missing something?
Cheers
Warwick
Warwick Prince
Managing Director
mobile: +61 411 026 992
skype: warwickprince
phone: +61 7 3102 3730
fax: +61 7 3319 6734
web: www.mushroomsys.com
On 30/11/2010, at 1:29 AM, Michael Bayer wrote:
we've got unicode round trips down very well for years now with plenty of
tests, so would need a specific series of steps to reproduce what you're
doing here. Note that the recommended connect string for MySQL + Mysqldb
looks like mysql://scott:ti...@localhost/test?charset=utf8use_unicode=0 .
On Nov 29, 2010, at 2:37 AM, Warwick Prince wrote:
Hi All
I thought I had Character Encoding licked, but I've hit something I can't
work through. Any help appreciated.
I have a legacy non SQL database that I read legacy data from (using cool
Python code that emulates the old ISDB binary comms) and it reads a str
which has Foreign language chars in it. (French for example).
So, firstly, I have myStr = ''Aligot\xc3\xa9 which when printed is
Aligoté. So far so good.
I then convert that to unicode by myUnicode = unicode(myStr, 'utf-8',
errors='ignore') and get u'Aligot\xe9'. This printed is also Aligoté,
therefore all is good.
I have a MySQL database, InnoDB table, charset utf-8.
I set up my values in a dict called setValues with all the columns and
their respective unicode'd values ready to go
I then do a table.insert(values=setValues).execute() and get this error.
Traceback (most recent call last):
File C:\Documents and Settings\wprince\Desktop\PY CODE
DEVELOPMENT\CESyncSQL\TEST_Sync.py, line 148, in SYNC_IT
SyncFunction(ceDB, session, meta)
File C:\Documents and Settings\wprince\Desktop\PY CODE
DEVELOPMENT\CESyncSQL\TEST_Sync.py, line 840, in SYNC_VarietiesOUT
[sqlalchemy] Problem with one to many relationship and composite primary key
Hi.
I'm trying to relate two tables with a one to many relationship (the
parent table has a composite primary key) but I'm getting a mapper
error. I found a recent message about this same problem but with
declarative base (which I don't use) and not sure why the suggestion
there didn't apply to my problem.
Find below the error and the table creation code.
TIA,
Mariano
Error:
ArgumentError: Could not locate any equated, locally mapped column pairs
for primaryjoin condition 'regevent.id =
regevent_who.regevent_id AND regevent.author =
regevent_who.regevent_author' on relationship RegEvent.who. For more
relaxed rules on join conditions, the relationship may be marked as
viewonly=True.
Code:
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime),
useexisting=True)
Index('regevent_cal_ix', *(regevent.c.calname,))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id, regevent_author),
(regevent.id, regevent.author),
regevent_fk),
useexisting=True)
Index(regevent_who_fk_ix, *(regevent_who.c.regevent_id,
regevent_who.c.regevent_author))
class RegEventWho(object):
pass
mapper(RegEvent, regevent_who, properties={
'who': relationship(RegEventWho,
primaryjoin=and_(
regevent.c.id==regevent_who.c.regevent_id,
regevent.c.author==regevent_who.c.regevent_author))
})
mapper(RegEventWho, regevent_who)
--
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.
[sqlalchemy] finding if a table is already join in a query
Hi all,
We have a small function that helps us create a simple search query by
automatically joining on required relations if needed.
For example, consider an employee ORM that has a 1:M relationship with
addresses (for postal/physical). We can do something like:
query = Employee().search('streetname', [Employee.name,
Address.street1])
We have that working, but when we add a second search field on Address:
query = Employee.search('streetname', [Employee.name, Address.street1,
Address.street2])
our method fails with: table name address specified more than once
We need to be able to identify if the query already has a join on
'address'
I've tried getting details on the query object (it
has ._from, ._from_obj, ._from_alias and .from_statement) that looked
interesting, but they don't appear to give us what we need.
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
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.
Re: [sqlalchemy] Problem with one to many relationship and composite primary key
Nothing wrong with the mapping, except the primaryjoin is not needed. The
cause is certainly the usage of useexisting, which implies that these tables
have already been created, and everything you are specifying in the Table() is
ignored. I wouldn't use that flag.
On Nov 30, 2010, at 10:22 AM, Mariano Mara wrote:
Hi.
I'm trying to relate two tables with a one to many relationship (the
parent table has a composite primary key) but I'm getting a mapper
error. I found a recent message about this same problem but with
declarative base (which I don't use) and not sure why the suggestion
there didn't apply to my problem.
Find below the error and the table creation code.
TIA,
Mariano
Error:
ArgumentError: Could not locate any equated, locally mapped column pairs
for primaryjoin condition 'regevent.id =
regevent_who.regevent_id AND regevent.author =
regevent_who.regevent_author' on relationship RegEvent.who. For more
relaxed rules on join conditions, the relationship may be marked as
viewonly=True.
Code:
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime),
useexisting=True)
Index('regevent_cal_ix', *(regevent.c.calname,))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id, regevent_author),
(regevent.id, regevent.author),
regevent_fk),
useexisting=True)
Index(regevent_who_fk_ix, *(regevent_who.c.regevent_id,
regevent_who.c.regevent_author))
class RegEventWho(object):
pass
mapper(RegEvent, regevent_who, properties={
'who': relationship(RegEventWho,
primaryjoin=and_(
regevent.c.id==regevent_who.c.regevent_id,
regevent.c.author==regevent_who.c.regevent_author))
})
mapper(RegEventWho, regevent_who)
--
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.
--
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.
Re: [sqlalchemy] finding if a table is already join in a query
On Nov 30, 2010, at 11:13 AM, James Neethling wrote:
Hi all,
We have a small function that helps us create a simple search query by
automatically joining on required relations if needed.
For example, consider an employee ORM that has a 1:M relationship with
addresses (for postal/physical). We can do something like:
query = Employee().search('streetname', [Employee.name,
Address.street1])
We have that working, but when we add a second search field on Address:
query = Employee.search('streetname', [Employee.name, Address.street1,
Address.street2])
our method fails with: table name address specified more than once
We need to be able to identify if the query already has a join on
'address'
I've tried getting details on the query object (it
has ._from, ._from_obj, ._from_alias and .from_statement) that looked
interesting, but they don't appear to give us what we need.
if you would like multiple references to Address to all work from the same
join, your routine needs to track which entities have already been joined as a
destination in a separate collection:
def search(columns):
already_joined = set()
...
if class_ not in already_joined:
q = q.join(destination)
already_joined.add(class_)
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
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.
--
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.
Re: [sqlalchemy] Problem with one to many relationship and composite primary key
Excerpts from Michael Bayer's message of Tue Nov 30 13:50:26 -0300 2010:
Nothing wrong with the mapping, except the primaryjoin is not needed. The
cause is certainly the usage of useexisting, which implies that these
tables have already been created, and everything you are specifying in the
Table() is ignored. I wouldn't use that flag.
Thanks Michael as always.
Removing the useexisting=True, if I also remove the primaryjoin I get:
ArgumentError: Could not determine join condition between parent/child
tables on relationship RegEvent.who. Specify a 'primaryjoin' expression. If
'secondary' is
present, 'secondaryjoin' is needed as well.
If I leave the primaryjoin I still get the same error as reported.
TIA for any extra ideas you can suggest to fix this.
Mariano
On Nov 30, 2010, at 10:22 AM, Mariano Mara wrote:
Hi.
I'm trying to relate two tables with a one to many relationship (the
parent table has a composite primary key) but I'm getting a mapper
error. I found a recent message about this same problem but with
declarative base (which I don't use) and not sure why the suggestion
there didn't apply to my problem.
Find below the error and the table creation code.
TIA,
Mariano
Error:
ArgumentError: Could not locate any equated, locally mapped column pairs
for primaryjoin condition 'regevent.id =
regevent_who.regevent_id AND regevent.author =
regevent_who.regevent_author' on relationship RegEvent.who. For more
relaxed rules on join conditions, the relationship may be marked as
viewonly=True.
Code:
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime),
useexisting=True)
Index('regevent_cal_ix', *(regevent.c.calname,))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id, regevent_author),
(regevent.id, regevent.author),
regevent_fk),
useexisting=True)
Index(regevent_who_fk_ix, *(regevent_who.c.regevent_id,
regevent_who.c.regevent_author))
class RegEventWho(object):
pass
mapper(RegEvent, regevent_who, properties={
'who': relationship(RegEventWho,
primaryjoin=and_(
regevent.c.id==regevent_who.c.regevent_id,
regevent.c.author==regevent_who.c.regevent_author))
})
mapper(RegEventWho, regevent_who)
--
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.
--
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.
Re: [sqlalchemy] Problem with one to many relationship and composite primary key
your RegEvent mapper is against the wrong table, here is the correct code:
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id, regevent_author),
(regevent.id, regevent.author),
regevent_fk))
class RegEventWho(object):
pass
mapper(RegEvent, regevent, properties={
'who': relationship(RegEventWho)
})
mapper(RegEventWho, regevent_who)
print Session().query(RegEvent).join(RegEvent.who)
On Nov 30, 2010, at 12:22 PM, Mariano Mara wrote:
Excerpts from Michael Bayer's message of Tue Nov 30 13:50:26 -0300 2010:
Nothing wrong with the mapping, except the primaryjoin is not needed.
The cause is certainly the usage of useexisting, which implies that these
tables have already been created, and everything you are specifying in the
Table() is ignored. I wouldn't use that flag.
Thanks Michael as always.
Removing the useexisting=True, if I also remove the primaryjoin I get:
ArgumentError: Could not determine join condition between parent/child
tables on relationship RegEvent.who. Specify a 'primaryjoin' expression. If
'secondary' is
present, 'secondaryjoin' is needed as well.
If I leave the primaryjoin I still get the same error as reported.
TIA for any extra ideas you can suggest to fix this.
Mariano
On Nov 30, 2010, at 10:22 AM, Mariano Mara wrote:
Hi.
I'm trying to relate two tables with a one to many relationship (the
parent table has a composite primary key) but I'm getting a mapper
error. I found a recent message about this same problem but with
declarative base (which I don't use) and not sure why the suggestion
there didn't apply to my problem.
Find below the error and the table creation code.
TIA,
Mariano
Error:
ArgumentError: Could not locate any equated, locally mapped column pairs
for primaryjoin condition 'regevent.id =
regevent_who.regevent_id AND regevent.author =
regevent_who.regevent_author' on relationship RegEvent.who. For more
relaxed rules on join conditions, the relationship may be marked as
viewonly=True.
Code:
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime),
useexisting=True)
Index('regevent_cal_ix', *(regevent.c.calname,))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id, regevent_author),
(regevent.id, regevent.author),
regevent_fk),
useexisting=True)
Index(regevent_who_fk_ix, *(regevent_who.c.regevent_id,
regevent_who.c.regevent_author))
class RegEventWho(object):
pass
mapper(RegEvent, regevent_who, properties={
'who': relationship(RegEventWho,
primaryjoin=and_(
regevent.c.id==regevent_who.c.regevent_id,
regevent.c.author==regevent_who.c.regevent_author))
})
mapper(RegEventWho, regevent_who)
--
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
Re: [sqlalchemy] Problem with one to many relationship and composite primary key
ohhh, I'm out of words other than thank you for spotting it. I can't
believe how stupid I feel right now.
Mariano
Excerpts from Michael Bayer's message of Tue Nov 30 14:27:42 -0300 2010:
your RegEvent mapper is against the wrong table, here is the correct code:
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id, regevent_author),
(regevent.id, regevent.author),
regevent_fk))
class RegEventWho(object):
pass
mapper(RegEvent, regevent, properties={
'who': relationship(RegEventWho)
})
mapper(RegEventWho, regevent_who)
print Session().query(RegEvent).join(RegEvent.who)
On Nov 30, 2010, at 12:22 PM, Mariano Mara wrote:
Excerpts from Michael Bayer's message of Tue Nov 30 13:50:26 -0300 2010:
Nothing wrong with the mapping, except the primaryjoin is not needed.
The cause is certainly the usage of useexisting, which implies that
these tables have already been created, and everything you are specifying
in the Table() is ignored. I wouldn't use that flag.
Thanks Michael as always.
Removing the useexisting=True, if I also remove the primaryjoin I get:
ArgumentError: Could not determine join condition between parent/child
tables on relationship RegEvent.who. Specify a 'primaryjoin' expression.
If 'secondary' is
present, 'secondaryjoin' is needed as well.
If I leave the primaryjoin I still get the same error as reported.
TIA for any extra ideas you can suggest to fix this.
Mariano
On Nov 30, 2010, at 10:22 AM, Mariano Mara wrote:
Hi.
I'm trying to relate two tables with a one to many relationship (the
parent table has a composite primary key) but I'm getting a mapper
error. I found a recent message about this same problem but with
declarative base (which I don't use) and not sure why the suggestion
there didn't apply to my problem.
Find below the error and the table creation code.
TIA,
Mariano
Error:
ArgumentError: Could not locate any equated, locally mapped column pairs
for primaryjoin condition 'regevent.id =
regevent_who.regevent_id AND regevent.author =
regevent_who.regevent_author' on relationship RegEvent.who. For more
relaxed rules on join conditions, the relationship may be marked as
viewonly=True.
Code:
regevent = Table('regevent', metadata,
Column('id', Unicode(200), primary_key=True),
Column('author', Unicode(200), primary_key=True),
Column('since', DateTime),
Column('until', DateTime),
Column('title', Unicode(100)),
Column('content', Unicode(600)),
Column('status', Unicode(200)),
Column('published', DateTime),
useexisting=True)
Index('regevent_cal_ix', *(regevent.c.calname,))
class RegEvent(object):
pass
regevent_who = Table('regevent_who', metadata,
Column('id', Integer, primary_key=True,
autoincrement=True),
Column('regevent_id', Unicode(200)),
Column('regevent_author', Unicode(200)),
Column('email', Unicode(200)),
Column('status', Unicode(200)),
Column('role', Unicode(200)),
ForeignKeyConstraint((regevent_id,
regevent_author),
(regevent.id,
regevent.author),
regevent_fk),
useexisting=True)
Index(regevent_who_fk_ix, *(regevent_who.c.regevent_id,
regevent_who.c.regevent_author))
class RegEventWho(object):
pass
mapper(RegEvent, regevent_who, properties={
'who': relationship(RegEventWho,
primaryjoin=and_(
[sqlalchemy] How can I do this relation?
Hello, I have this table: class Region(rdb.Model): Represents one region in the layout rdb.metadata(metadata) rdb.tablename(regions) id = Column(id, Integer, primary_key=True) title = Column(title, String(50)) .. channelId = Column(channel_id, Integer, ForeignKey(channel.id)) channel = relationship(Channel, uselist=False, backref=regions) One region can just contain one channel, but one channel could be in many regions. A region will never be accessed by a channel. However, Channel could be accessed by a region, so I need that relationship in region. Is that relationship OK? If not, how can I make it? Thanks! -- 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.
Re: [sqlalchemy] finding if a table is already join in a query
On Tue, 2010-11-30 at 11:52 -0500, Michael Bayer wrote:
On Nov 30, 2010, at 11:13 AM, James Neethling wrote:
Hi all,
We have a small function that helps us create a simple search query by
automatically joining on required relations if needed.
For example, consider an employee ORM that has a 1:M relationship with
addresses (for postal/physical). We can do something like:
query = Employee().search('streetname', [Employee.name,
Address.street1])
We have that working, but when we add a second search field on Address:
query = Employee.search('streetname', [Employee.name, Address.street1,
Address.street2])
our method fails with: table name address specified more than once
We need to be able to identify if the query already has a join on
'address'
I've tried getting details on the query object (it
has ._from, ._from_obj, ._from_alias and .from_statement) that looked
interesting, but they don't appear to give us what we need.
if you would like multiple references to Address to all work from the same
join, your routine needs to track which entities have already been joined as
a destination in a separate collection:
def search(columns):
already_joined = set()
...
if class_ not in already_joined:
q = q.join(destination)
already_joined.add(class_)
Hi Michael,
Thank you for the quick response.
Unfortunately we don't always know where this query comes from (my
example was a little contrived :( )
Is there any way to get the tables that are currently in the join for a
query?
Here is a cut down sample implementation that will hopefully remove any
confusion... Note the TODO: in Employee.search()
---8---8---8
from sqlalchemy import create_engine, Column, ForeignKey, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
joinedload
from sqlalchemy.types import Integer, String, Text
from sqlalchemy.sql.expression import cast
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base(bind=engine)
Session = scoped_session(sessionmaker(bind=engine))
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
def search(self, value, columns):
query = Session.query(Employee)
for i, column in enumerate(columns):
model = column.parententity.class_
if Employee is not model:
#TODO: Are we already joined from Employee onto model?
query = query.outerjoin(model)
args = [cast(c, Text).ilike('%%%s%%' % value) for c in columns]
return query.filter(or_(*args))
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
employee_id = Column(Integer, ForeignKey(Employee.id))
street1 = Column(String(50))
street2 = Column(String(50))
employee = relationship(Employee)
Base.metadata.create_all()
#e = Employee(name='Bob')
#a = Address(employee=e, street1='street1', street2='street2')
#Session.add(a)
#Session.commit()
q = Employee().search('stree', [Employee.name, Address.street1,
Address.street2])
print q
SELECT employee.id AS employee_id, employee.name AS employee_name
FROM employee LEFT OUTER JOIN address ON employee.id =
address.employee_id LEFT OUTER JOIN address ON employee.id =
address.employee_id
WHERE lower(CAST(employee.name AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street1 AS TEXT)) LIKE lower(?) OR
lower(CAST(address.street2 AS TEXT)) LIKE lower(?)
---8---8---8
TIA
Jim
--
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.
--
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.
--
James Neethling
Development Manager
XO Africa Safari
(t) +27 21 486 2700 (ext. 127)
(e) jam...@xoafrica.com
--
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
Re: [sqlalchemy] session.execute(sql_statement) does not flush the session in a autoflush=True session ?
On Fri, 2010-11-26 at 15:41 -0500, Michael Bayer wrote: I wouldn't say its a bug since its intentional. But I'll grant the intention is up for debate. I've always considered usage of execute() to mean, you're going below the level of the ORM and would like to control the SQL interaction directly, not to mention with as minimal overhead as possible, which is why it works that way currently. It might be just as surprising to many users if execute() issued a whole series of insert/update statements as much as it was apparently surprising to you that it did not. I agree with the current behaviour. I've never actually thought of execute as doing anything other than *exactly* what I want it to do - and only that. Hi, In a session which has some dirty objects, doing session.execute(sql_statement) doesn't not flush the dirty objects to the database before executing the sql_statement query. The session was initialized with autoflush=True. Is it the expected behaviour ? Is it a bug ? Thanks -- 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.
