Re: [sqlalchemy] issue with joineload and multiple relationships to the same table

[Jonathan Vanasco]

Thanks. That's exactly it.  It makes perfect sense why it just happens to 
work when I join one bar onto this, but not two.

FYI, I'm not seeing a comma on the simple join - but it's still a bad query 
that illustrates the problem as Bar doesn't get joined in:

   SELECT foo.id AS foo_id  FROM foo JOIN foo2bar ON foo.id = 
foo2bar.foo_id AND foo2bar.bar_id = bar.id AND bar.is_special = true LIMIT 
%(param_1)s

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] issue with joineload and multiple relationships to the same table

[mike bayer]

On 04/20/2017 09:12 PM, Jonathan Vanasco wrote:
i have roughly the following model: Widget > Widget2Foo > Foo >  Foo2Bar 
 > Bar wherein `Foo` has multiple relationships to `Bar` (via a filter )


the setup works for lazyloading and subqueryloading, but I can't do 
multiple joinedloads:


eg:

lazyloading and this subquery works:

 query = s.query(Widget)\
 
.options(joinedload('to_foos').joinedload('foo').joinedload('to_bars').joinedload('bar'))\

 .options(subqueryload('to_foos.foo.to_bars_special.bar'))

but this won't work, because sqlalchemy has issues with the final segment

 query = s.query(Widget)\
 
.options(joinedload('to_foos').joinedload('foo').joinedload('to_bars').joinedload('bar'))\
 
.options(joinedload('to_foos').joinedload('foo').joinedload('to_bars_special').joinedload('bar'))


i included the relevant bits below and can make a repeatable if needed .

i've traced the issue to the orm having issues with the the multiple 
joins to the same table -- so I figure i'm either pushing the limits or 
implemented that last relationship wrong and this will be obvious to 
someone with more experience.




---

class Foo(Base):
 __tablename__ = 'foo'
 id = Column(Integer, primary_key=True)

 to_bars = relationship("Foo2Bar",
primaryjoin="""Foo.id==Foo2Bar.foo_id""",
)

 to_bars_special = relationship("Foo2Bar",
   
  primaryjoin="""and_(Foo.id==Foo2Bar.foo_id,

   Foo2Bar.bar_id==Bar.id,
   Bar.is_special==True,
   )""",



oh, wait.  how is that supposed to work w/ "Bar"?   that will definitely 
fail.  the biggest reason would be that "Bar" is not part of the FROMs 
it expects so you are likely getting something like "FROM foo JOIN 
foo2bar ON , bar".


Take a look at very simple query(Foo).join(Foo.to_bars_special) to see 
if it's relying on that.   if you see a comma in the FROM clause, you're 
out.


to get that into a joinedload you'd need to encapsulate that join a 
little better, this is discussed at 
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper




"when we seek to join from A to B, making use of any number of C, D, 
etc. in between, however there are also join conditions between A and B 
directly."


seems like it describes this case !





)

class Bar(Base):
 __tablename__ = 'bar'
 id = Column(Integer, primary_key=True)
 is_special = Column(Boolean, nullable=True)

class Foo2Bar(Base):
 __tablename__ = 'foo2bar'
 __primarykey__ = ['foo_id', 'bar_id']
 foo_id = Column(Integer, ForeignKey("Foo.id"), primary_key=True)
 bar_id = Column(Integer, ForeignKey("Bar.id"), primary_key=True)

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and 
Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
description.

---
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 - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Apparent bug when combining not_() and label() in expression language

[mike bayer]

On 04/20/2017 03:39 PM, George London wrote:
I noticed some strange behavior while using the SQLAlchemy expression 
language. I'm using Python 3.5 and SQLAlchemy 1.1.19 against MySQL 
5.7.13 via the mysqlclient  v1.3.10 connector.


The problem happens when I build up a boolean expression using and_() / 
or_(), then give that expression a name using .label(), and then negate 
the expression using not_().


For example,

disjunction = or_(user.c.user_name == "jack", user.c.user_name == "james")
disjunction_with_label = disjunction.label("banned_users")
negated_disjunction = not_(disjunction_with_label)

This is causing two problems:

1) If I use this expression with .where to filter a select query, the 
SQL that SQLAlchemy emits doesn't wrap the negated clause in 
parentheses, meaning that only the first clause in the disjunction will 
be negated (instead of the whole inner expression, as I'd expect). E.g.:


SELECT "user".user_id, "user".user_name, "user".email_address, "user".password
FROM "user"
WHERE NOT "user".user_name = :user_name_1 OR "user".user_name = :user_name_2


OK so, this could be improved, but also, you shouldn't be putting a 
label() expression into the WHERE clause.  That's the part here that the 
API does not expect.







However, if I don't .label() the disjunction, SQLAlchemy emits a query 
that *does* have parentheses:



SQL: SELECT "user".user_id, "user".user_name, "user".email_address, 
"user".password
FROM "user"
WHERE NOT ("user".user_name = :user_name_1 OR "user".user_name = :user_name_2)


right because, negation works in the absense of label().  a negation of 
a label in SQL doesn't make sense.   Label means this:


"some_sql_expression AS some_label"

how do you "negate" that in SQL?   "not (some_sq_expression AS 
some_label)"?   that's invalid SQL. I know you expect it to "dig in" 
to the element, e.g. modify the thing that is labeled, but this would be 
an exception to the usual structural semantics of the SQL expression 
language.  We probably make exceptions like this in some places but they 
are special cases that need to be added and tested.





2) actually running the emitted query produces strange results (that are 
different from the results I get by binding the literals and running the 
textual query directly.)



Please see this gist 
https://gist.github.com/rogueleaderr/e28cb1f707b6637421137c0c4bf3c282 
for a full working reproduction of the issue in a Jupyter notebook and 
in an executable Python script.


so when you do this stuff it is very important to turn the echo=True on 
the engine.   Because the SQLite compiler is returning a different 
result than the default one:


compare:

str(query_with_negated_disjunction_with_label)

to:

str(query_with_negated_disjunction_with_label.compile(engine))


Why that is, I have no idea.  Let's see.

from sqlalchemy import *

expr = or_(column('x') == 1, column('y') == 2)

bug = not_(expr.label('foo'))

from sqlalchemy.dialects import sqlite

print bug.compile()
print bug.compile(dialect=sqlite.dialect())

output:

NOT x = :x_1 OR y = :y_1
x = ? OR y = ? = 0

So, this seems very odd at first but this is in fact hitting another 
element in the code that even has a TODO around it 
(https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/elements.py#L656), 
because it doesn't know what to do with your label() (again, unsupported 
/ never anticipated use case) it assumes that you're giving it a true_() 
/ false_() constant, so you see in the case of SQLite it is trying to 
compare the whole thing to zero because SQLite doesn't have true/false 
constants.


I've put this up at 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3969/negation-of-labeled-element 
.   SQL element changes like these are never trivial things because they 
can have far reaching affects so I encourage you do just not use label() 
until the moment you are putting your expression into the top-level 
columns clause.thanks for the clear test case!







So...is there a SQL reason I might not be aware of why negating a 
labelled expression should behave in this way? If not, should I consider 
this a bug in SQLAlchemy and report it?



Thank you!

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and 
Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
description.

---
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] autogenerate migrations with alembic for inherited tables

[mike bayer]

On 04/21/2017 09:16 AM, Антонио Антуан wrote:

Helllo.
I have a model, with specified __tablename__ = 'base_table'.
In postgresql, the table has trigger, which executes before each insert: 
it creates partition for current month (if it not exist yet), specifies 
"INHERITS (base_table)" for new partition and insert data into it.


Is there any way to autodetect table inheritance and generate migration 
script for it?



What's the actual migration you want to generate?   E.g. start with A, 
change to Bare you starting with Table(), and then adding 
"postgresql_inherits" to it after the fact?  it's not clear.


if you can illustrate what this migration would be I'd have some idea 
what you are actually trying to do.The "trigger" you refer to seems 
to be something that emits CREATE TABLE upon INSERT, which would be 
outside the realm of Alembic.





If autodetection not working...
I can get list of inherited tables with such query:
|SELECT child.relname, parent.relname
FROM pg_catalog.pg_inherits
  INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid = 
child.oid)
  INNER JOIN pg_catalog.pg_class as parent ON (pg_inherits.inhparent = 
parent.oid)

WHERE inhparent = 'base_table'::regclass;|

Returned names can be specified as parameter "only" in "reflect()" 
method of MetaData instance. Can I specify target table for each table 
in metadata for migration?

I found just such solution:
|
for table_name in inherit_table_names:
 meta_data.tables[table_name].name = "base_table"


I'm not really following what you're doing here.   Changing the name of 
a Table like that is probably not safe in general, depends on the context.








|

Is my solution safe?






--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and 
Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
description.

---
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 - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] IntegrityError following the tutorial using OracleDB

[mike bayer]

On 04/21/2017 04:30 AM, Simon King wrote:

On Fri, Apr 21, 2017 at 1:52 AM, Matei Micu  wrote:

I'm following the tutorial from
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html , from my
understanding the primary key should be added when
a session flushes the content ( in the tutorial the first flush is triggered
by a query ). When I try to follow the tutorial I get a
IntegrityError

Here is my code.
import sqlalchemy
from sqlalchemy.ext import declarative
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

url = "oracle+cx_oracle://ASCIIPIC:ASCIIPIC@127.0.0.1:58639/xe"
engine = sqlalchemy.create_engine(url, convert_unicode=True, echo=True)
engine.execute("DROP TABLE users")

Base = declarative_base()

class User(Base):
 __tablename__ = 'users'

 id = Column(Integer, primary_key=True)
 name = Column(String(50))
 fullname = Column(String(50))
 password = Column(String(50))

 def __repr__(self):
 return "" %
(self.name, self.fullname, self.password)

Base.metadata.create_all(engine)
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')

Session = sessionmaker(bind=engine)
session = Session()
session.add(ed_user)

# As per tutorial, here the session should flush the content and assign
# an id to the user
our_user = session.query(User).filter_by(name='ed').first()


Here is the outputand traceback:

mmicu@nuc1 $ python -i test.py
2017-04-21 03:47:01,191 INFO sqlalchemy.engine.base.Engine SELECT USER FROM
DUAL
2017-04-21 03:47:01,191 INFO sqlalchemy.engine.base.Engine {}
2017-04-21 03:47:01,193 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
2017-04-21 03:47:01,193 INFO sqlalchemy.engine.base.Engine {}
2017-04-21 03:47:01,194 INFO sqlalchemy.engine.base.Engine SELECT CAST('test
unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
2017-04-21 03:47:01,194 INFO sqlalchemy.engine.base.Engine {}
2017-04-21 03:47:01,196 INFO sqlalchemy.engine.base.Engine DROP TABLE users
2017-04-21 03:47:01,196 INFO sqlalchemy.engine.base.Engine {}
2017-04-21 03:47:01,212 INFO sqlalchemy.engine.base.Engine COMMIT
2017-04-21 03:47:01,216 INFO sqlalchemy.engine.base.Engine SELECT table_name
FROM all_tables WHERE table_name = :name AND owner = :schema_name
2017-04-21 03:47:01,216 INFO sqlalchemy.engine.base.Engine {'name':
u'USERS', 'schema_name': u'ASCIIPIC'}
2017-04-21 03:47:01,268 INFO sqlalchemy.engine.base.Engine
CREATE TABLE users (
 id INTEGER NOT NULL,
 name VARCHAR2(50 CHAR),
 fullname VARCHAR2(50 CHAR),
 password VARCHAR2(50 CHAR),
 PRIMARY KEY (id)
)


2017-04-21 03:47:01,268 INFO sqlalchemy.engine.base.Engine {}
2017-04-21 03:47:01,332 INFO sqlalchemy.engine.base.Engine COMMIT
2017-04-21 03:47:01,334 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-04-21 03:47:01,335 INFO sqlalchemy.engine.base.Engine INSERT INTO users
(name, fullname, password) VALUES (:name, :fullname, :password) RETURNING
users.id INTO :ret_0
2017-04-21 03:47:01,335 INFO sqlalchemy.engine.base.Engine {'fullname': u'Ed
Jones', 'password': u'edspassword', 'name': u'ed', 'ret_0':
}
2017-04-21 03:47:01,337 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
   File "t.py", line 33, in 
 our_user = session.query(User).filter_by(name='ed').first()
   File
"/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line 2755, in first
 ret = list(self[0:1])
   File
"/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line 2547, in __getitem__
 return list(res)
   File
"/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
line 2854, in __iter__
 self.session._autoflush()
   File
"/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 1375, in _autoflush
 util.raise_from_cause(e)
   File
"/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py",
line 203, in raise_from_cause
 reraise(type(exception), exception, tb=exc_tb, cause=cause)
   File
"/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 1365, in _autoflush
 self.flush()
   File
"/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 2139, in flush
 self._flush(objects)
   File
"/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py",
line 2259, in _flush
 transaction.rollback(_capture_exception=True)
   File
"/home/mmicu/Git/asciipic/.venv/test/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py",
line 66, in __exit__
 compat.reraise(exc_type, exc_value, exc_tb)
   File

[sqlalchemy] autogenerate migrations with alembic for inherited tables

[Антонио Антуан]

Helllo.
I have a model, with specified __tablename__ = 'base_table'.
In postgresql, the table has trigger, which executes before each insert: it 
creates partition for current month (if it not exist yet), specifies 
"INHERITS (base_table)" for new partition and insert data into it.

Is there any way to autodetect table inheritance and generate migration 
script for it?

If autodetection not working... 
I can get list of inherited tables with such query:
SELECT child.relname, parent.relname 
FROM pg_catalog.pg_inherits 
 INNER JOIN pg_catalog.pg_class as child ON (pg_inherits.inhrelid = 
child.oid) 
 INNER JOIN pg_catalog.pg_class as parent ON (pg_inherits.inhparent = 
parent.oid) 
WHERE inhparent = 'base_table'::regclass;

Returned names can be specified as parameter "only" in "reflect()" method 
of MetaData instance. Can I specify target table for each table in metadata 
for migration?
I found just such solution:
for table_name in inherit_table_names:
meta_data.tables[table_name].name = "base_table"

Is my solution safe?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.