Re: [sqlalchemy] Re: Adding 'where' to query

[Jonathan Vanasco]

If you're not doing it already, It may help to setup a playground/testing 
environment as you get familiar with SqlAlchemy.

You can create a directory that has a copy-of (or can import) your model.

Then create a series of scripts that create a new engine with echo turned 
on.

This way you can play with the syntax directly over short scripts, and see 
the results + compiled sql, without the overhead of whatever app you're 
trying to build SqlAlchemy support into.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Adding 'where' to query

[Alex Hall]

Thanks for the clarification. I'm suddenly getting no results at all
when I add this filter, but at least now I know I'm doing the syntax
right. Never a dull moment. :)

On 3/14/16, Jonathan Vanasco  wrote:
>
>>
>> .filter(t1.c1=='hello', and_(t3.c1=='world'))
>>
>
> The and_ Is wrong in this context.  Everything in `filter` is joined by
> "and" by default.  You just want:
>
> .filter(t1.c1=='hello', t3.c1=='world')
>
> `and_` is usually used in a nested condition, often under an `or_`.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Adding 'where' to query

[Jonathan Vanasco]

>
> .filter(t1.c1=='hello', and_(t3.c1=='world')) 
>

The and_ Is wrong in this context.  Everything in `filter` is joined by 
"and" by default.  You just want: 

.filter(t1.c1=='hello', t3.c1=='world')

`and_` is usually used in a nested condition, often under an `or_`.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Adding 'where' to query

[Alex Hall]

I think I got it. I've been using .filter() for only joins thus far,
so somehow had it in my head that it was only for joining. Of course,
.filter(t1.c1=='hello')
will work. I believe I'm using and_ correctly if I say:
.filter(t1.c1=='hello', and_(t3.c1=='world'))
I may have that and_ part wrong, but filter is the obvious solution to
most of my question.

On 3/14/16, Alex Hall  wrote:
> Hi all,
> I had a link that was a great intro to querying, but of course, I
> can't find it now. I need to add a couple conditions to my query. In
> SQL, it might look like this:
>
> select *
>  from t1 join t2 on t1.c1==t2.c1
> join t3 on t3.c1==t1.c1
> where t1.c1 = 'hello' and t3.c3 = 'world'
>
> The joins I have, through query.filter(). It's the 'where' at the end
> that I'm not certain about. I know I've read how to do this, but I
> can't find that page anywhere. I also don't want to make it more
> complex than it needs to be. For instance, using "select" and putting
> that back into "query" when I don't need to. I've tried adding this
> after the last call to filter():
> .where(item.itm_webflag != 'N', and_(item.itm_suspflag != 'Y'))\
> But of course, SA says that query has no attribute 'where'.
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Adding 'where' to query

[Alex Hall]

Hi all,
I had a link that was a great intro to querying, but of course, I
can't find it now. I need to add a couple conditions to my query. In
SQL, it might look like this:

select *
 from t1 join t2 on t1.c1==t2.c1
join t3 on t3.c1==t1.c1
where t1.c1 = 'hello' and t3.c3 = 'world'

The joins I have, through query.filter(). It's the 'where' at the end
that I'm not certain about. I know I've read how to do this, but I
can't find that page anywhere. I also don't want to make it more
complex than it needs to be. For instance, using "select" and putting
that back into "query" when I don't need to. I've tried adding this
after the last call to filter():
.where(item.itm_webflag != 'N', and_(item.itm_suspflag != 'Y'))\
But of course, SA says that query has no attribute 'where'.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] reflection fails on table with name in all caps

[Alex Hall]

That worked! Thank you so much for your patience. Part of it was the
code, and part of it turned out to be that I was still using
vendorTable = base.classes.VENDR

It didn't occur to me that my VENDR class had taken over that part, so
base.classes would no longer contain VENDR. When I saw your asserts,
it struck me that the last piece of the puzzle might be to set
ventorDable = VENDR, and that seems to be doing the job beautifully.
Thanks again!

On 3/14/16, Mike Bayer  wrote:
> metadata.reflect(..., extend_existing=True), here's a complete example
>
> from sqlalchemy import create_engine
>
> e = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True)
>
> with e.begin() as conn:
>
>  conn.execute("""
>  if not exists (select * from sysobjects where name='sometable'
> and xtype='U')
>  create table sometable (
>  id integer,
>  data varchar(20),
>  primary key (id)
>  )
>  """)
>
>  conn.execute("""
>  if not exists (select * from sysobjects where
> name='someothertable' and xtype='U')
>  create table someothertable (
>  id integer,
>  data varchar(20),
>  primary key (id)
>  )
>  """)
>
>  conn.execute("""
>  if not exists (select * from sysobjects where name='VENDR' and
> xtype='U')
>  create table [VENDR] (
>  [PVVNNO] integer,
>  [DATA] varchar(20)
>  )
>  """)
>
> from sqlalchemy.ext.automap import automap_base
> from sqlalchemy import MetaData, Column, String
> from sqlalchemy.orm import Session
>
> metadata = MetaData()
>
> desiredTables = ["sometable", "someothertable", "VENDR"]
> base = automap_base(metadata=metadata)
>
>
> class VENDR(base):
>  __tablename__ = "VENDR"
>  PVVNNO = Column(String, primary_key=True)
>
> metadata.reflect(e, only=desiredTables, extend_existing=True)
> assert 'VENDR' in metadata.tables
>
> base.prepare()
>
> assert VENDR.DATA
>
> sess = Session(e)
> print sess.query(VENDR).all()
>
>
>
>
> On 03/14/2016 10:21 AM, Alex Hall wrote:
>> I hate to say it, but... AttributeError: VENDR. I've moved different
>> lines all around, above and below the class definition, but nothing
>> I've tried works. The only change was when I put my declaration of
>> base below the class, and Python naturally said it didn't know what my
>> table class was inheriting from. I don't know why this is being such a
>> problem.
>>
>> On 3/14/16, Mike Bayer  wrote:
>>> oh.  try it like this:
>>>
>>> class VENDR(base):
>>>  __tablename__ = "VENDR"
>>>  PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
>>>
>>>  __table_args__ = {"extend_existing": True}
>>>
>>> that tells reflection to add new data to this Table object even though
>>> it already exists.
>>>
>>>
>>> On 03/14/2016 09:24 AM, Alex Hall wrote:
 Thanks for that. Somehow, I'm getting the same error as before--the
 VENDR table isn't being reflected. Here's the entire snippet, from
 engine to trying to get the table.

 engine = sqlalchemy.create_engine("mssql+pyodbc://%s:%s@%s"
 %(username, password, dsn))
 session = Session(engine)
 metadata = sqlalchemy.MetaData()
 desiredTables = ["item", "assignment", "attachment", "attach_text",
 "attribute", "attributevalue", "VENDR", "attributevalueassign"]
 base = automap_base(metadata=metadata)
 #pause here to make a table, since VENDR lacks a PK
 class VENDR(base):
__tablename__ = "VENDR"
PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
 #done. Anyway...
 metadata.reflect(engine, only=desiredTables)
 base.prepare()

 itemTable = base.classes.item
 assignmentTable = base.classes.assignment
 attachmentTable = base.classes.attachment
 attachmentTextTable = base.classes.attach_text
 attributeTable = base.classes.attribute
 attributeValueTable = base.classes.attributevalue
 attributeValueAssignmentTable = base.classes.attributevalueassign
 vendorTable = base.classes.VENDR #AttributeError: VENDR

 I still don't quite see how base, metadata, and session all interact
 to do what SA does, or I'd have a much easier time troubleshooting
 this. I'm sure I just have something out of order, or some other
 simple mistake.

 On 3/11/16, Mike Bayer  wrote:
> like this:
>
> class VENDR(MyAutomapBase):
>__tablename__ = 'VENDR'
>
>id = Column(Integer, primary_key=True)
>
> Above, the 'id' column name should match the column in the table that
> you'd like to consider as the primary key (and so should the type) -
> the
> "id" / "Integer" combination above is just an example.
>
> Then do the automap as you've done.   At the end, if it worked,
> Base.classes.VENDR should be the same class as the 

Re: [sqlalchemy] Multiple many to one relationships to same table

[Mike Bayer]

On 03/14/2016 11:15 AM, 'Chris Norman' via sqlalchemy wrote:

Hi all,
I've tried googling for this, and I get nothing. I have a table to store
data about objects. Each object should have a location property which
links back to the same table. Conversely, each object should have a
contents property which shows all objects which have their location set
to this object.


documentation for multiple relationships to the same table is at:

http://docs.sqlalchemy.org/en/rel_1_0/orm/join_conditions.html#handling-multiple-join-paths

hope this helps





This is the code I have so far:

class DBObject(Base):
  __tablename__ = 'db_objects'
  id = Column(Integer, primary_key = True)
  name = Column(String)
  description = Column(String)
  location_id = Column(Integer, ForeignKey('db_objects.id'))
  owner_id = Column(Integer, ForeignKey('db_objects.id'))
  contents = relationship('DBObject', remote_side = location_id, backref
= backref('location', remote_side = [location_id]))
  owned_objects = relationship('DBObject', remote_side = owner_id,
backref = backref('owner', remote_side = [owner_id]))
  x = Column(Float)
  y = Column(Float)
  z = Column(Float)
  max_hp = Column(Float)
  damage = Column(Float)
  properties = Column(LargeBinary)

When I issue Base.metadata.create_all I get this:

Traceback (most recent call last):
   File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py",
line 2055, in _determine_joins
 consider_as_foreign_keys=consider_as_foreign_keys
   File "", line 2, in join_condition
   File "C:\python35\lib\site-packages\sqlalchemy\sql\selectable.py",
line 828, in _join_condition
 a, b, constraints, consider_as_foreign_keys)
   File "C:\python35\lib\site-packages\sqlalchemy\sql\selectable.py",
line 918, in _joincond_trim_constraints
 "join explicitly." % (a.description, b.description))
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between
'db_objects' and 'db_objects'; tables have more than one foreign key
constraint relationship between them. Please specify the 'onclause' of
this join explicitly.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
   File "main.py", line 30, in 
 start()
   File "C:\Users\Chrisn Norman\Dropbox\SRC\mindspace_server\server.py",
line 15, in start
 db.initialise()
   File "C:\Users\Chrisn Norman\Dropbox\SRC\mindspace_server\db.py",
line 57, in initialise
 for row in session.query(DBObject):
   File "C:\python35\lib\site-packages\sqlalchemy\orm\session.py", line
1272, in query
 return self._query_cls(entities, self, **kwargs)
   File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line
110, in __init__
 self._set_entities(entities)
   File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line
120, in _set_entities
 self._set_entity_selectables(self._entities)
   File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line
150, in _set_entity_selectables
 ent.setup_entity(*d[entity])
   File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line
3421, in setup_entity
 self._with_polymorphic = ext_info.with_polymorphic_mappers
   File "C:\python35\lib\site-packages\sqlalchemy\util\langhelpers.py",
line 747, in __get__
 obj.__dict__[self.__name__] = result = self.fget(obj)
   File "C:\python35\lib\site-packages\sqlalchemy\orm\mapper.py", line
1893, in _with_polymorphic_mappers
 configure_mappers()
   File "C:\python35\lib\site-packages\sqlalchemy\orm\mapper.py", line
2768, in configure_mappers
 mapper._post_configure_properties()
   File "C:\python35\lib\site-packages\sqlalchemy\orm\mapper.py", line
1710, in _post_configure_properties
 prop.init()
   File "C:\python35\lib\site-packages\sqlalchemy\orm\interfaces.py",
line 183, in init
 self.do_init()
   File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py",
line 1629, in do_init
 self._setup_join_conditions()
   File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py",
line 1704, in _setup_join_conditions
 can_be_synced_fn=self._columns_are_mapped
   File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py",
line 1972, in __init__
 self._determine_joins()
   File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py",
line 2099, in _determine_joins
 % self.prop)
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
condition between parent/child tables on relationship DBObject.contents
- there are multiple foreign key paths linking the tables.  Specify the
'foreign_keys' argument, providing a list of those columns which should
be counted as containing a foreign key reference to the parent table.




I'm using latest sqlalchemy from pip and Python 3.5.

Any ideas on how to fix this?

Cheers,

--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to 

Re: [sqlalchemy] Does dynamic loading effect speed?

[Mike Bayer]

On 03/13/2016 04:46 PM, Kate Boelhauf wrote:

I just learned about dynamic loading and was able to implement that so
that I could filter on a relationship

matched_objects = foo.relationship.filter(RelationshipClass.property
=="mustmatch").all()
if len(matched_objects) > 0:
 continue

Currently I typically loop and check to see if the property matches, I'd
prefer to do the dynamic loading but was wondering if I would take a
penalty on speed if I used it a lot.


the dynamic loader is all about tradeoffs.  It emits SQL every time you 
access it, which has a speed penalty vs. having the whole collection 
loaded into memory at once.  However, if the collection itself is 
millions of rows, that's a non-starter as well, so it depends on what 
kind of data you are accessing what approach is more performant in the 
aggregate.







--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] reflection fails on table with name in all caps

[Mike Bayer]

metadata.reflect(..., extend_existing=True), here's a complete example

from sqlalchemy import create_engine

e = create_engine("mssql+pyodbc://scott:tiger@ms_2008", echo=True)

with e.begin() as conn:

conn.execute("""
if not exists (select * from sysobjects where name='sometable' 
and xtype='U')

create table sometable (
id integer,
data varchar(20),
primary key (id)
)
""")

conn.execute("""
if not exists (select * from sysobjects where 
name='someothertable' and xtype='U')

create table someothertable (
id integer,
data varchar(20),
primary key (id)
)
""")

conn.execute("""
if not exists (select * from sysobjects where name='VENDR' and 
xtype='U')

create table [VENDR] (
[PVVNNO] integer,
[DATA] varchar(20)
)
""")

from sqlalchemy.ext.automap import automap_base
from sqlalchemy import MetaData, Column, String
from sqlalchemy.orm import Session

metadata = MetaData()

desiredTables = ["sometable", "someothertable", "VENDR"]
base = automap_base(metadata=metadata)


class VENDR(base):
__tablename__ = "VENDR"
PVVNNO = Column(String, primary_key=True)

metadata.reflect(e, only=desiredTables, extend_existing=True)
assert 'VENDR' in metadata.tables

base.prepare()

assert VENDR.DATA

sess = Session(e)
print sess.query(VENDR).all()




On 03/14/2016 10:21 AM, Alex Hall wrote:

I hate to say it, but... AttributeError: VENDR. I've moved different
lines all around, above and below the class definition, but nothing
I've tried works. The only change was when I put my declaration of
base below the class, and Python naturally said it didn't know what my
table class was inheriting from. I don't know why this is being such a
problem.

On 3/14/16, Mike Bayer  wrote:

oh.  try it like this:

class VENDR(base):
 __tablename__ = "VENDR"
 PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)

 __table_args__ = {"extend_existing": True}

that tells reflection to add new data to this Table object even though
it already exists.


On 03/14/2016 09:24 AM, Alex Hall wrote:

Thanks for that. Somehow, I'm getting the same error as before--the
VENDR table isn't being reflected. Here's the entire snippet, from
engine to trying to get the table.

engine = sqlalchemy.create_engine("mssql+pyodbc://%s:%s@%s"
%(username, password, dsn))
session = Session(engine)
metadata = sqlalchemy.MetaData()
desiredTables = ["item", "assignment", "attachment", "attach_text",
"attribute", "attributevalue", "VENDR", "attributevalueassign"]
base = automap_base(metadata=metadata)
#pause here to make a table, since VENDR lacks a PK
class VENDR(base):
   __tablename__ = "VENDR"
   PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
#done. Anyway...
metadata.reflect(engine, only=desiredTables)
base.prepare()

itemTable = base.classes.item
assignmentTable = base.classes.assignment
attachmentTable = base.classes.attachment
attachmentTextTable = base.classes.attach_text
attributeTable = base.classes.attribute
attributeValueTable = base.classes.attributevalue
attributeValueAssignmentTable = base.classes.attributevalueassign
vendorTable = base.classes.VENDR #AttributeError: VENDR

I still don't quite see how base, metadata, and session all interact
to do what SA does, or I'd have a much easier time troubleshooting
this. I'm sure I just have something out of order, or some other
simple mistake.

On 3/11/16, Mike Bayer  wrote:

like this:

class VENDR(MyAutomapBase):
   __tablename__ = 'VENDR'

   id = Column(Integer, primary_key=True)

Above, the 'id' column name should match the column in the table that
you'd like to consider as the primary key (and so should the type) - the
"id" / "Integer" combination above is just an example.

Then do the automap as you've done.   At the end, if it worked,
Base.classes.VENDR should be the same class as the VENDR class above.


On 03/11/2016 05:09 PM, Alex Hall wrote:

Sorry, do you mean the base subclass, or a new table class? In either
case, I'm not sure I see how this will fit into my automapping code. I
know this is all fairly basic, I just can't quite picture what goes
where and what inherits from/gets passed to what to make it automap
this VENDR table. If I could, I'd just add a PK column to the table
itself. Sadly, I can't change that kind of thing, only query it.

On 3/11/16, Mike Bayer  wrote:

just make the class and include the PK column, then automap.  the rest
of the columns should be filled in.


On 03/11/2016 04:14 PM, Alex Hall wrote:

Ah, you're right. Every other table I've used in this database has
had
a key, and I didn't even notice that this VENDR table lacks one. That
explains the mystery! Thanks.

Now to map this table. I've read the section of the docs on doing
this, and I get that I 

[sqlalchemy] Multiple many to one relationships to same table

['Chris Norman' via sqlalchemy]

Hi all,
I've tried googling for this, and I get nothing. I have a table to store 
data about objects. Each object should have a location property which links 
back to the same table. Conversely, each object should have a contents 
property which shows all objects which have their location set to this 
object.

This is the code I have so far:

class DBObject(Base):
 __tablename__ = 'db_objects'
 id = Column(Integer, primary_key = True)
 name = Column(String)
 description = Column(String)
 location_id = Column(Integer, ForeignKey('db_objects.id'))
 owner_id = Column(Integer, ForeignKey('db_objects.id'))
 contents = relationship('DBObject', remote_side = location_id, backref = 
backref('location', remote_side = [location_id]))
 owned_objects = relationship('DBObject', remote_side = owner_id, backref = 
backref('owner', remote_side = [owner_id]))
 x = Column(Float)
 y = Column(Float)
 z = Column(Float)
 max_hp = Column(Float)
 damage = Column(Float)
 properties = Column(LargeBinary)

When I issue Base.metadata.create_all I get this:

Traceback (most recent call last):
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", 
line 2055, in _determine_joins
consider_as_foreign_keys=consider_as_foreign_keys
  File "", line 2, in join_condition
  File "C:\python35\lib\site-packages\sqlalchemy\sql\selectable.py", line 
828, in _join_condition
a, b, constraints, consider_as_foreign_keys)
  File "C:\python35\lib\site-packages\sqlalchemy\sql\selectable.py", line 
918, in _joincond_trim_constraints
"join explicitly." % (a.description, b.description))
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 
'db_objects' and 'db_objects'; tables have more than one foreign key 
constraint relationship between them. Please specify the 'onclause' of this 
join explicitly.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "main.py", line 30, in 
start()
  File "C:\Users\Chrisn Norman\Dropbox\SRC\mindspace_server\server.py", 
line 15, in start
db.initialise()
  File "C:\Users\Chrisn Norman\Dropbox\SRC\mindspace_server\db.py", line 
57, in initialise
for row in session.query(DBObject):
  File "C:\python35\lib\site-packages\sqlalchemy\orm\session.py", line 
1272, in query
return self._query_cls(entities, self, **kwargs)
  File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line 110, 
in __init__
self._set_entities(entities)
  File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line 120, 
in _set_entities
self._set_entity_selectables(self._entities)
  File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line 150, 
in _set_entity_selectables
ent.setup_entity(*d[entity])
  File "C:\python35\lib\site-packages\sqlalchemy\orm\query.py", line 3421, 
in setup_entity
self._with_polymorphic = ext_info.with_polymorphic_mappers
  File "C:\python35\lib\site-packages\sqlalchemy\util\langhelpers.py", line 
747, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
  File "C:\python35\lib\site-packages\sqlalchemy\orm\mapper.py", line 1893, 
in _with_polymorphic_mappers
configure_mappers()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\mapper.py", line 2768, 
in configure_mappers
mapper._post_configure_properties()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\mapper.py", line 1710, 
in _post_configure_properties
prop.init()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\interfaces.py", line 
183, in init
self.do_init()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", 
line 1629, in do_init
self._setup_join_conditions()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", 
line 1704, in _setup_join_conditions
can_be_synced_fn=self._columns_are_mapped
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", 
line 1972, in __init__
self._determine_joins()
  File "C:\python35\lib\site-packages\sqlalchemy\orm\relationships.py", 
line 2099, in _determine_joins
% self.prop)
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join 
condition between parent/child tables on relationship DBObject.contents - 
there are multiple foreign key paths linking the tables.  Specify the 
'foreign_keys' argument, providing a list of those columns which should be 
counted as containing a foreign key reference to the parent table.

I'm using latest sqlalchemy from pip and Python 3.5.

Any ideas on how to fix this?

Cheers,

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] reflection fails on table with name in all caps

[Alex Hall]

I hate to say it, but... AttributeError: VENDR. I've moved different
lines all around, above and below the class definition, but nothing
I've tried works. The only change was when I put my declaration of
base below the class, and Python naturally said it didn't know what my
table class was inheriting from. I don't know why this is being such a
problem.

On 3/14/16, Mike Bayer  wrote:
> oh.  try it like this:
>
> class VENDR(base):
> __tablename__ = "VENDR"
> PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
>
> __table_args__ = {"extend_existing": True}
>
> that tells reflection to add new data to this Table object even though
> it already exists.
>
>
> On 03/14/2016 09:24 AM, Alex Hall wrote:
>> Thanks for that. Somehow, I'm getting the same error as before--the
>> VENDR table isn't being reflected. Here's the entire snippet, from
>> engine to trying to get the table.
>>
>> engine = sqlalchemy.create_engine("mssql+pyodbc://%s:%s@%s"
>> %(username, password, dsn))
>> session = Session(engine)
>> metadata = sqlalchemy.MetaData()
>> desiredTables = ["item", "assignment", "attachment", "attach_text",
>> "attribute", "attributevalue", "VENDR", "attributevalueassign"]
>> base = automap_base(metadata=metadata)
>> #pause here to make a table, since VENDR lacks a PK
>> class VENDR(base):
>>   __tablename__ = "VENDR"
>>   PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
>> #done. Anyway...
>> metadata.reflect(engine, only=desiredTables)
>> base.prepare()
>>
>> itemTable = base.classes.item
>> assignmentTable = base.classes.assignment
>> attachmentTable = base.classes.attachment
>> attachmentTextTable = base.classes.attach_text
>> attributeTable = base.classes.attribute
>> attributeValueTable = base.classes.attributevalue
>> attributeValueAssignmentTable = base.classes.attributevalueassign
>> vendorTable = base.classes.VENDR #AttributeError: VENDR
>>
>> I still don't quite see how base, metadata, and session all interact
>> to do what SA does, or I'd have a much easier time troubleshooting
>> this. I'm sure I just have something out of order, or some other
>> simple mistake.
>>
>> On 3/11/16, Mike Bayer  wrote:
>>> like this:
>>>
>>> class VENDR(MyAutomapBase):
>>>   __tablename__ = 'VENDR'
>>>
>>>   id = Column(Integer, primary_key=True)
>>>
>>> Above, the 'id' column name should match the column in the table that
>>> you'd like to consider as the primary key (and so should the type) - the
>>> "id" / "Integer" combination above is just an example.
>>>
>>> Then do the automap as you've done.   At the end, if it worked,
>>> Base.classes.VENDR should be the same class as the VENDR class above.
>>>
>>>
>>> On 03/11/2016 05:09 PM, Alex Hall wrote:
 Sorry, do you mean the base subclass, or a new table class? In either
 case, I'm not sure I see how this will fit into my automapping code. I
 know this is all fairly basic, I just can't quite picture what goes
 where and what inherits from/gets passed to what to make it automap
 this VENDR table. If I could, I'd just add a PK column to the table
 itself. Sadly, I can't change that kind of thing, only query it.

 On 3/11/16, Mike Bayer  wrote:
> just make the class and include the PK column, then automap.  the rest
> of the columns should be filled in.
>
>
> On 03/11/2016 04:14 PM, Alex Hall wrote:
>> Ah, you're right. Every other table I've used in this database has
>> had
>> a key, and I didn't even notice that this VENDR table lacks one. That
>> explains the mystery! Thanks.
>>
>> Now to map this table. I've read the section of the docs on doing
>> this, and I get that I subclass base, set __table__ to be my VENDR
>> table, then set the key in my subclass. My question is how I access
>> the table, given that I can't automap it first. That is, if I can't
>> map the table because it has no PK, to what do I set __table__ in the
>> subclass that will let me map the table?
>>
>> One post I found suggested something like this:
>>
>> vendorTable = Table("VENDR", metadata, column("PVVNNO",
>> primary_key=True))
>>
>> I'm guessing I'd have to add the column definitions for the other
>> columns if I did that. I'm further guessing that this replaces the
>> docs' method of subclassing, since the PK is now set. However, I
>> don't
>> know if this would still work with automapping.
>>
>> On 3/11/16, Mike Bayer  wrote:
>>> ah.  does VENDR have a primary key?   it won't be mapped if not.
>>>
>>> what's in base.classes.keys() ?   base.classes['VENDR'] ?
>>>
>>>
>>>
>>>
>>>
>>>
>>> On 03/11/2016 12:47 PM, Alex Hall wrote:
 VENDR is right there, in base.classes and metadata.tables. Yet,
 vendorTable = base.classes.VENDR
 raises an 

Re: [sqlalchemy] reflection fails on table with name in all caps

[Mike Bayer]

oh.  try it like this:

class VENDR(base):
   __tablename__ = "VENDR"
   PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)

   __table_args__ = {"extend_existing": True}

that tells reflection to add new data to this Table object even though 
it already exists.



On 03/14/2016 09:24 AM, Alex Hall wrote:

Thanks for that. Somehow, I'm getting the same error as before--the
VENDR table isn't being reflected. Here's the entire snippet, from
engine to trying to get the table.

engine = sqlalchemy.create_engine("mssql+pyodbc://%s:%s@%s"
%(username, password, dsn))
session = Session(engine)
metadata = sqlalchemy.MetaData()
desiredTables = ["item", "assignment", "attachment", "attach_text",
"attribute", "attributevalue", "VENDR", "attributevalueassign"]
base = automap_base(metadata=metadata)
#pause here to make a table, since VENDR lacks a PK
class VENDR(base):
  __tablename__ = "VENDR"
  PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
#done. Anyway...
metadata.reflect(engine, only=desiredTables)
base.prepare()

itemTable = base.classes.item
assignmentTable = base.classes.assignment
attachmentTable = base.classes.attachment
attachmentTextTable = base.classes.attach_text
attributeTable = base.classes.attribute
attributeValueTable = base.classes.attributevalue
attributeValueAssignmentTable = base.classes.attributevalueassign
vendorTable = base.classes.VENDR #AttributeError: VENDR

I still don't quite see how base, metadata, and session all interact
to do what SA does, or I'd have a much easier time troubleshooting
this. I'm sure I just have something out of order, or some other
simple mistake.

On 3/11/16, Mike Bayer  wrote:

like this:

class VENDR(MyAutomapBase):
  __tablename__ = 'VENDR'

  id = Column(Integer, primary_key=True)

Above, the 'id' column name should match the column in the table that
you'd like to consider as the primary key (and so should the type) - the
"id" / "Integer" combination above is just an example.

Then do the automap as you've done.   At the end, if it worked,
Base.classes.VENDR should be the same class as the VENDR class above.


On 03/11/2016 05:09 PM, Alex Hall wrote:

Sorry, do you mean the base subclass, or a new table class? In either
case, I'm not sure I see how this will fit into my automapping code. I
know this is all fairly basic, I just can't quite picture what goes
where and what inherits from/gets passed to what to make it automap
this VENDR table. If I could, I'd just add a PK column to the table
itself. Sadly, I can't change that kind of thing, only query it.

On 3/11/16, Mike Bayer  wrote:

just make the class and include the PK column, then automap.  the rest
of the columns should be filled in.


On 03/11/2016 04:14 PM, Alex Hall wrote:

Ah, you're right. Every other table I've used in this database has had
a key, and I didn't even notice that this VENDR table lacks one. That
explains the mystery! Thanks.

Now to map this table. I've read the section of the docs on doing
this, and I get that I subclass base, set __table__ to be my VENDR
table, then set the key in my subclass. My question is how I access
the table, given that I can't automap it first. That is, if I can't
map the table because it has no PK, to what do I set __table__ in the
subclass that will let me map the table?

One post I found suggested something like this:

vendorTable = Table("VENDR", metadata, column("PVVNNO",
primary_key=True))

I'm guessing I'd have to add the column definitions for the other
columns if I did that. I'm further guessing that this replaces the
docs' method of subclassing, since the PK is now set. However, I don't
know if this would still work with automapping.

On 3/11/16, Mike Bayer  wrote:

ah.  does VENDR have a primary key?   it won't be mapped if not.

what's in base.classes.keys() ?   base.classes['VENDR'] ?






On 03/11/2016 12:47 PM, Alex Hall wrote:

VENDR is right there, in base.classes and metadata.tables. Yet,
vendorTable = base.classes.VENDR
raises an AttributeError. Odd! There's nothing cap-sensitive about
__hasattr__ that I'm forgetting, is there? Or, could I somehow alias
the name before I try to access it, if that would help at all? This
is
the only table in the CMS to have a name in all caps, but I need to
access it to look up manufacturer details for items.

On 3/11/16, Mike Bayer  wrote:


can you look in metadata.tables to see what it actually reflected ?






On 03/11/2016 12:09 PM, Alex Hall wrote:

That's weird: the name I see is exactly what I've been using,
"VENDR".
All caps and everything. I tried using lowercase, just to see what
it
would do, but it failed.

On 3/11/16, Mike Bayer  wrote:



On 03/11/2016 09:39 AM, Alex Hall wrote:

Hello list,
Finally, a pure SA question from me. I'm using Automap and the
"only"
keyword to automap a subset of the tables in our CMS database.
This

Re: [sqlalchemy] reflection fails on table with name in all caps

[Alex Hall]

Thanks for that. Somehow, I'm getting the same error as before--the
VENDR table isn't being reflected. Here's the entire snippet, from
engine to trying to get the table.

engine = sqlalchemy.create_engine("mssql+pyodbc://%s:%s@%s"
%(username, password, dsn))
session = Session(engine)
metadata = sqlalchemy.MetaData()
desiredTables = ["item", "assignment", "attachment", "attach_text",
"attribute", "attributevalue", "VENDR", "attributevalueassign"]
base = automap_base(metadata=metadata)
#pause here to make a table, since VENDR lacks a PK
class VENDR(base):
 __tablename__ = "VENDR"
 PVVNNO = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
#done. Anyway...
metadata.reflect(engine, only=desiredTables)
base.prepare()

itemTable = base.classes.item
assignmentTable = base.classes.assignment
attachmentTable = base.classes.attachment
attachmentTextTable = base.classes.attach_text
attributeTable = base.classes.attribute
attributeValueTable = base.classes.attributevalue
attributeValueAssignmentTable = base.classes.attributevalueassign
vendorTable = base.classes.VENDR #AttributeError: VENDR

I still don't quite see how base, metadata, and session all interact
to do what SA does, or I'd have a much easier time troubleshooting
this. I'm sure I just have something out of order, or some other
simple mistake.

On 3/11/16, Mike Bayer  wrote:
> like this:
>
> class VENDR(MyAutomapBase):
>  __tablename__ = 'VENDR'
>
>  id = Column(Integer, primary_key=True)
>
> Above, the 'id' column name should match the column in the table that
> you'd like to consider as the primary key (and so should the type) - the
> "id" / "Integer" combination above is just an example.
>
> Then do the automap as you've done.   At the end, if it worked,
> Base.classes.VENDR should be the same class as the VENDR class above.
>
>
> On 03/11/2016 05:09 PM, Alex Hall wrote:
>> Sorry, do you mean the base subclass, or a new table class? In either
>> case, I'm not sure I see how this will fit into my automapping code. I
>> know this is all fairly basic, I just can't quite picture what goes
>> where and what inherits from/gets passed to what to make it automap
>> this VENDR table. If I could, I'd just add a PK column to the table
>> itself. Sadly, I can't change that kind of thing, only query it.
>>
>> On 3/11/16, Mike Bayer  wrote:
>>> just make the class and include the PK column, then automap.  the rest
>>> of the columns should be filled in.
>>>
>>>
>>> On 03/11/2016 04:14 PM, Alex Hall wrote:
 Ah, you're right. Every other table I've used in this database has had
 a key, and I didn't even notice that this VENDR table lacks one. That
 explains the mystery! Thanks.

 Now to map this table. I've read the section of the docs on doing
 this, and I get that I subclass base, set __table__ to be my VENDR
 table, then set the key in my subclass. My question is how I access
 the table, given that I can't automap it first. That is, if I can't
 map the table because it has no PK, to what do I set __table__ in the
 subclass that will let me map the table?

 One post I found suggested something like this:

 vendorTable = Table("VENDR", metadata, column("PVVNNO",
 primary_key=True))

 I'm guessing I'd have to add the column definitions for the other
 columns if I did that. I'm further guessing that this replaces the
 docs' method of subclassing, since the PK is now set. However, I don't
 know if this would still work with automapping.

 On 3/11/16, Mike Bayer  wrote:
> ah.  does VENDR have a primary key?   it won't be mapped if not.
>
> what's in base.classes.keys() ?   base.classes['VENDR'] ?
>
>
>
>
>
>
> On 03/11/2016 12:47 PM, Alex Hall wrote:
>> VENDR is right there, in base.classes and metadata.tables. Yet,
>> vendorTable = base.classes.VENDR
>> raises an AttributeError. Odd! There's nothing cap-sensitive about
>> __hasattr__ that I'm forgetting, is there? Or, could I somehow alias
>> the name before I try to access it, if that would help at all? This
>> is
>> the only table in the CMS to have a name in all caps, but I need to
>> access it to look up manufacturer details for items.
>>
>> On 3/11/16, Mike Bayer  wrote:
>>>
>>> can you look in metadata.tables to see what it actually reflected ?
>>>
>>>
>>>
>>>
>>>
>>>
>>> On 03/11/2016 12:09 PM, Alex Hall wrote:
 That's weird: the name I see is exactly what I've been using,
 "VENDR".
 All caps and everything. I tried using lowercase, just to see what
 it
 would do, but it failed.

 On 3/11/16, Mike Bayer  wrote:
>
>
> On 03/11/2016 09:39 AM, Alex Hall wrote:
>> Hello list,
>>