date:20160719

Re: [sqlalchemy] Combining yield_per and eager loading

2016-07-19 Thread Mike Bayer




On 07/19/2016 05:20 PM, Martijn van Oosterhout wrote:


On 19 July 2016 at 18:42, Mike Bayer > wrote:



On 07/19/2016 12:33 PM, Martijn van Oosterhout wrote:

On Sunday, July 17, 2016 at 8:47:11 AM UTC+2, Martijn van
Oosterhout wrote:

But in any case, even this improves performance greatly.


this is adequate for a new recipe if you are interested in adding
it. Also I think sqlalchemy-utils provides a feature along these
lines but I'm not sure how well it works or handles those harder
cases like chained relationships.


Thanks. On the way home though I had a thought: wouldn't it be simpler
to run the original query with yield_from(), and then after each block
run the query with a filter on the primary keys returned, and add all
the joinedload/subqueryload/etc options to this query, run it and rely
on the identity map to fix it for the objects returned the first time.
Or is that something we cannot rely on?


it works for the loading you're doing, where the primary keys of what's 
been fetched are fed into the subsequent query.  But it doesnt work for 
current subquery loading which does not make use of those identifiers, 
nor for joined loading which does OUTER JOIN onto the original query at 
once (doing "joinedload" as a separate query is essentially what 
subquery loading already is).






Have a nice day,
--
Martijn van Oosterhout >
http://svana.org/kleptog/

--
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] Combining yield_per and eager loading

2016-07-19 Thread Martijn van Oosterhout

On 19 July 2016 at 18:42, Mike Bayer  wrote:

>
>
> On 07/19/2016 12:33 PM, Martijn van Oosterhout wrote:
>
>> On Sunday, July 17, 2016 at 8:47:11 AM UTC+2, Martijn van Oosterhout
>> wrote:
>>
>> But in any case, even this improves performance greatly.
>>
>
> this is adequate for a new recipe if you are interested in adding it. Also
> I think sqlalchemy-utils provides a feature along these lines but I'm not
> sure how well it works or handles those harder cases like chained
> relationships.
>
>
Thanks. On the way home though I had a thought: wouldn't it be simpler to
run the original query with yield_from(), and then after each block run the
query with a filter on the primary keys returned, and add all the
joinedload/subqueryload/etc options to this query, run it and rely on the
identity map to fix it for the objects returned the first time. Or is that
something we cannot rely on?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/

-- 
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] gevent.monkey.patch_all() breaks my Sqlalchemy connection...

2016-07-19 Thread Paul Becotte

Hi!  So, I have a traditional Flask app using flask-sqlalchemy and 
sqlalchemy ORM, served with uwsgi.  Uwsgi is set with processes=4 and 
threads=4.  Yesterday I added grequests to improve a single area of the app 
a bit (I had to do a couple hundred external API calls to build a single 
data structure).  No problem- except that I started getting errors like 

/usr/local/lib/python2.7/dist-packages/flask_sqlalchemy/__init__.py", line 
423, in first_or_404 rv = self.first() File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 
2634, in first ret = list(self[0:1]) File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 
2457, in __getitem__ return list(res) File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 
2736, in __iter__ return self._execute_and_instances(context) File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 
2751, in _execute_and_instances result = 
conn.execute(querycontext.statement, self._params) File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 
914, in execute return meth(self, multiparams, params) File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 
323, in _execute_on_connection return 
connection._execute_clauseelement(self, multiparams, params) File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 
1010, in _execute_clauseelement compiled_sql, distilled_params File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 
1078, in _execute_context None, None) File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 
1341, in _handle_dbapi_exception exc_info File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 
200, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, 
cause=cause) File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 
1073, in _execute_context context = constructor(dialect, self, conn, *args) 
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", 
line 558, in _init_compiled self.cursor = self.create_cursor() File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 
748, in create_cursor return self._dbapi_connection.cursor() File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 852, in 
cursor return self.connection.cursor(*args, **kwargs) File 
"/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", 
line 807, in cursor raise errors.OperationalError("MySQL Connection not 
available.") OperationalError: (mysql.connector.errors.OperationalError) 
MySQL Connection not available. 


>From playing around with it, importing grequests was the line of code that 
made the error appear/dis-appear.  Further experimenting shows that `import 
gevent.monkey; gevent.monkey.patch_all()` anywhere in the code breaks the 
app as well.  This doesn't happen on every request, and I am not actually 
using greenlets or the gevent loop anywhere in the endpoints I am 
accessing.  For now this is easy enough for me to just rip out grequests, 
but I was wondering if anyone had any ideas as to why this is happening?

-- 
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] Detecting relationship conflicts

2016-07-19 Thread Burak Arslan



On 07/19/16 20:37, Burak Arslan wrote:
>
> On 07/19/16 20:19, Burak Arslan wrote:
>> On 07/19/16 19:38, Mike Bayer wrote:
>>> this warning will be removed in 1.1, see
>>> https://bitbucket.org/zzzeek/sqlalchemy/issues/3749 
>> That's nice but am I to understand there is no easy way to test this? I
>> still wouldn't prefer generating redundant properties.
>>
>>
>
> never mind, this seems to work:
> https://github.com/plq/spyne/blob/9c66b7f98ba24595f574a7c4974402a45f4cdcc8/spyne/test/test_sqlalchemy.py#L347
>


Sorry, it should actually be this one:
https://github.com/plq/spyne/blob/960a48317bc4355f3d35656ac4cedf636aae60c5/spyne/test/test_sqlalchemy.py#L344

In English, I'm comparing relationships identities from parent and child
classes.

FWIW, I had this error in production for years and it seems to never
have caused a problem.

Thanks a lot for your answers.

Best regards,
Burak

-- 
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] Detecting relationship conflicts

2016-07-19 Thread Mike Bayer




On 07/19/2016 01:37 PM, Burak Arslan wrote:



On 07/19/16 20:19, Burak Arslan wrote:


On 07/19/16 19:38, Mike Bayer wrote:

this warning will be removed in 1.1, see
https://bitbucket.org/zzzeek/sqlalchemy/issues/3749

That's nice but am I to understand there is no easy way to test this? I
still wouldn't prefer generating redundant properties.





never mind, this seems to work:
https://github.com/plq/spyne/blob/9c66b7f98ba24595f574a7c4974402a45f4cdcc8/spyne/test/test_sqlalchemy.py#L347


Didn't realize you actually wanted to guard against this.





burak



--
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] Detecting relationship conflicts

2016-07-19 Thread Burak Arslan



On 07/19/16 20:19, Burak Arslan wrote:
>
> On 07/19/16 19:38, Mike Bayer wrote:
>> this warning will be removed in 1.1, see
>> https://bitbucket.org/zzzeek/sqlalchemy/issues/3749 
> That's nice but am I to understand there is no easy way to test this? I
> still wouldn't prefer generating redundant properties.
>
>


never mind, this seems to work:
https://github.com/plq/spyne/blob/9c66b7f98ba24595f574a7c4974402a45f4cdcc8/spyne/test/test_sqlalchemy.py#L347

burak

-- 
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] Detecting relationship conflicts

2016-07-19 Thread Burak Arslan



On 07/19/16 19:38, Mike Bayer wrote:
> this warning will be removed in 1.1, see
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3749 

That's nice but am I to understand there is no easy way to test this? I
still wouldn't prefer generating redundant properties.

Best,
Burak

-- 
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] Combining yield_per and eager loading

2016-07-19 Thread Mike Bayer




On 07/19/2016 12:33 PM, Martijn van Oosterhout wrote:

On Sunday, July 17, 2016 at 8:47:11 AM UTC+2, Martijn van Oosterhout wrote:


I'll play a bit and see what I can get to work. Thanks again.


So, I have a chance to play and got something that actually works quite
nicely, see below. Two things:

- I switched to referencing the primary key of the original object
directly, because some of our relationships are a bit more complex.
- Chained relationships don't work.

But in any case, even this improves performance greatly.


this is adequate for a new recipe if you are interested in adding it. 
Also I think sqlalchemy-utils provides a feature along these lines but 
I'm not sure how well it works or handles those harder cases like 
chained relationships.







|
fromitertools importgroupby,islice
fromsqlalchemy.orm importattributes,object_session
fromsqlalchemy importtuple_


defyielded_load(query,attrs,N=1000):
# Note: query must return only a single object (for now anyway)
main_query =query.yield_per(N)

main_res =iter(main_query)

whileTrue:
# Fetch block of results from query
objs =list(islice(main_res,N))

ifnotobjs:
break

forattr inattrs:
target =attr.prop.mapper
pk =attr.prop.parent.primary_key

# Generate query that joins against original table
child_q =object_session(objs[0]).query(target,*pk).order_by(*pk)
ifattr.prop.order_by:
child_q =child_q.order_by(*attr.prop.order_by)

keys =[[getattr(obj,col.key)forcol inpk]forobj inobjs]

child_q =child_q.join(attr).filter(tuple_(*pk).in_(keys))

collections =dict((k,[r[0]forr inv])fork,v ingroupby(
child_q,
lambdax:tuple([getattr(x,c.key)forc inpk])
))

forobj inobjs:
attributes.set_committed_value(
obj,
attr.key,
collections.get(
tuple(getattr(obj,c.key)forc inpk),
())
)

forobj inobjs:
yieldobj

|


--
Martijn

--
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] SAWarning shows not immediately, but after some time of app execution

2016-07-19 Thread Mike Bayer




On 07/19/2016 11:51 AM, TomS. wrote:

Hi,

We have Flask app which uses SQLAlchemy. Weird error started to happen
recently. The difficulty is that we can't reproduce the error (/figure
out conditions causing issue) - maybe someone could help. Any hints/tips
would be appreciated.

There is a part in the code which constructs IN in SQL:

MyModel.id.in_(my_ids)

For some cases my_ids is an empty list. It works without any problems,
but after some time the same query (using empty list) starts to raise an
exception:

SAWarning: The IN-predicate on "MyModel.id" was invoked with an empty
sequence. This results in a contradiction, which nonetheless can be
expensive to evaluate.  Consider alternative strategies for improved
performance.

After restarting app, everything works again.

The question is - why this exception is not risen always (although we
tried to run app with empty list directly), but after some time of app
execution (~1 day)?


It's not an exception, it's a warning.  Python warnings by default emit 
only once, see: 
https://docs.python.org/2/library/warnings.html#the-warnings-filter






Details:
Flask==0.10.1
Flask-SQLAlchemy==2.1
SQLAlchemy==1.0.14
MySQL DB

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] Detecting relationship conflicts

2016-07-19 Thread Mike Bayer

On 07/19/2016 08:52 AM, Burak Arslan wrote:

Hi,

Behold this test:

https://github.com/plq/spyne/blob/be189784b02e204b031f25bc748d9236ef7dfc59/spyne/test/test_sqlalchemy.py#L314

This is for Spyne => SQLAlchemy object bridge so please ignore
Spyne-specific stuff in there.

I noticed that this produced warning like this:

/home/plq/.local/lib64/python2.7/site-packages/sqlalchemy/orm/relationships.py:1741:
SAWarning: Warning: relationship 'foos' on mapper 'Mapper|SubBar|bar'
supersedes the same relationship on inherited mapper 'Mapper|Bar|bar';
this can cause dependency issues during flush

So now I wrote a patch that omits relationships from parent classes in
subclasses and that warning is gone. However, my test at the end keeps
failing.

1. Is there any way I can properly test this?

this warning will be removed in 1.1, see
https://bitbucket.org/zzzeek/sqlalchemy/issues/3749

2. Is there a way to explicitly compile mappers? That lonely
instantiation looks quite weird there.

http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html?highlight=configure_mappers#sqlalchemy.orm.configure_mappers

Best regards,
Burak

--
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] Combining yield_per and eager loading

2016-07-19 Thread Martijn van Oosterhout

On Sunday, July 17, 2016 at 8:47:11 AM UTC+2, Martijn van Oosterhout wrote:
>
>
> I'll play a bit and see what I can get to work. Thanks again.
>
>
So, I have a chance to play and got something that actually works quite 
nicely, see below. Two things:

- I switched to referencing the primary key of the original object 
directly, because some of our relationships are a bit more complex.
- Chained relationships don't work.

But in any case, even this improves performance greatly.


from itertools import groupby, islice
from sqlalchemy.orm import attributes, object_session
from sqlalchemy import tuple_


def yielded_load(query, attrs, N=1000):
# Note: query must return only a single object (for now anyway)
main_query = query.yield_per(N)

main_res = iter(main_query)

while True:
# Fetch block of results from query
objs = list(islice(main_res, N))

if not objs:
break

for attr in attrs:
target = attr.prop.mapper
pk = attr.prop.parent.primary_key

# Generate query that joins against original table
child_q = object_session(objs[0]).query(target, *pk).order_by(*
pk)
if attr.prop.order_by:
child_q = child_q.order_by(*attr.prop.order_by)

keys = [[getattr(obj, col.key) for col in pk] for obj in objs]

child_q = child_q.join(attr).filter(tuple_(*pk).in_(keys))

collections = dict((k, [r[0] for r in v]) for k, v in groupby(
child_q, 
lambda x:tuple([getattr(x, c.key) for c in pk])
))

for obj in objs:
attributes.set_committed_value(
obj, 
attr.key, 
collections.get(
tuple(getattr(obj, c.key) for c in pk),
())
)

for obj in objs:
yield obj


 
-- 
Martijn

-- 
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] SAWarning shows not immediately, but after some time of app execution

2016-07-19 Thread TomS.

my_ids = [] is initialized in the body of static method (on the top of 
it) which is run via Flask's route decorator.


But I can't understand why following code:

my_ids = []
MyModel.id.in_(my_ids)

doesn't raise SAWarning if 'an empty sequence' causes warning.

Cheers


On 07/19/2016 06:14 PM, Антонио Антуан wrote:


Looks like `my_ids` become empty 'after some time of app execution'. 
How do you initialize the variable?



вт, 19 июля 2016 г., 18:51 TomS. >:


Hi,

We have Flask app which uses SQLAlchemy. Weird error started to happen
recently. The difficulty is that we can't reproduce the error (/figure
out conditions causing issue) - maybe someone could help. Any
hints/tips
would be appreciated.

There is a part in the code which constructs IN in SQL:

MyModel.id.in_(my_ids)

For some cases my_ids is an empty list. It works without any problems,
but after some time the same query (using empty list) starts to
raise an
exception:

SAWarning: The IN-predicate on "MyModel.id" was invoked with an empty
sequence. This results in a contradiction, which nonetheless can be
expensive to evaluate.  Consider alternative strategies for improved
performance.

After restarting app, everything works again.

The question is - why this exception is not risen always (although we
tried to run app with empty list directly), but after some time of app
execution (~1 day)?

Details:
Flask==0.10.1
Flask-SQLAlchemy==2.1
SQLAlchemy==1.0.14
MySQL DB

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.

--

Антон

--
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] SAWarning shows not immediately, but after some time of app execution

2016-07-19 Thread Антонио Антуан

Looks like `my_ids` become empty 'after some time of app execution'. How do
you initialize the variable?

вт, 19 июля 2016 г., 18:51 TomS. :

> Hi,
>
> We have Flask app which uses SQLAlchemy. Weird error started to happen
> recently. The difficulty is that we can't reproduce the error (/figure
> out conditions causing issue) - maybe someone could help. Any hints/tips
> would be appreciated.
>
> There is a part in the code which constructs IN in SQL:
>
> MyModel.id.in_(my_ids)
>
> For some cases my_ids is an empty list. It works without any problems,
> but after some time the same query (using empty list) starts to raise an
> exception:
>
> SAWarning: The IN-predicate on "MyModel.id" was invoked with an empty
> sequence. This results in a contradiction, which nonetheless can be
> expensive to evaluate.  Consider alternative strategies for improved
> performance.
>
> After restarting app, everything works again.
>
> The question is - why this exception is not risen always (although we
> tried to run app with empty list directly), but after some time of app
> execution (~1 day)?
>
> Details:
> Flask==0.10.1
> Flask-SQLAlchemy==2.1
> SQLAlchemy==1.0.14
> MySQL DB
>
> 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.
>
-- 

Антон

-- 
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] SAWarning shows not immediately, but after some time of app execution

2016-07-19 Thread TomS.


Hi,

We have Flask app which uses SQLAlchemy. Weird error started to happen 
recently. The difficulty is that we can't reproduce the error (/figure 
out conditions causing issue) - maybe someone could help. Any hints/tips 
would be appreciated.


There is a part in the code which constructs IN in SQL:

MyModel.id.in_(my_ids)

For some cases my_ids is an empty list. It works without any problems, 
but after some time the same query (using empty list) starts to raise an 
exception:


SAWarning: The IN-predicate on "MyModel.id" was invoked with an empty 
sequence. This results in a contradiction, which nonetheless can be 
expensive to evaluate.  Consider alternative strategies for improved 
performance.


After restarting app, everything works again.

The question is - why this exception is not risen always (although we 
tried to run app with empty list directly), but after some time of app 
execution (~1 day)?


Details:
Flask==0.10.1
Flask-SQLAlchemy==2.1
SQLAlchemy==1.0.14
MySQL DB

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] Error IM001 reflecting table

2016-07-19 Thread Mike Bayer




On 07/19/2016 04:50 AM, Angelo Bulone wrote:

first of all, sorry if I'm not writing in the right place or I'm not
providing enough info about the issue.

Using SQL Alchemy, with pyodbc. I'm trying to reflect a table. When I
try to do that, i get this message

DBAPIError: (pyodbc.Error) ('IM001', '[IM001] [unixODBC][Driver
Manager]Driver does not support this function (0) (SQLNumParams)')
[SQL: u'SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] \nFROM
[INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] \nWHERE
[COLUMNS_1].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND
[COLUMNS_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) ORDER BY
[COLUMNS_1].[ORDINAL_POSITION]'] [parameters: ('Order', 'dbo')]

Here is the code..

|>>>fromsqlalchemy.orm.session importSession>>>fromsqlalchemy.schema
importMetaData>>>importsqlalchemy asSQLA >>>eng
=SQLA.create_engine(connection_string)>>>session
=Session(eng.connect())>>>classDB:...pass...>>>db =DB()>>>db.session
=session >>>db.engine =eng >>>db.metadata
=MetaData(bind=db.engine,schema='dbo')>>>db.session.execute("select *
from
information_schema.columns")>>>t
=SQLA.Table('Order',db.metadata,autoload=True,extend_existing=True,autoload_with=db.engine)>>>Traceback(most
recent call last):...File"",line 1,in...Seethe error above|

The code is running on a RHEL 7.x with unixODBC, Microsoft SQL Server
Native clinet 11 for Linux. Python 2.7.11


this is likely a side effect of the Linux SQL Server client which is 
very new and for which pyodbc was not originally developed.   I'd 
recommend trying the FreeTDS ODBC driver, and assuming that works, the 
problem has to do with pyodbc and/or the Microsoft driver.







Here are the pip requirements

  * - click (6.6) db-connection-maker (1.2.0)
  * - Flask (0.11.1)
  * - itsdangerous (0.24)
  * - Jinja2 (2.8)
  * - MarkupSafe (0.23)
  * - pip (8.0.2)
  * - pyaml (15.8.2)
  * - pyodbc (3.0.10)
  * - PyYAML (3.11)
  * - setuptools (19.6.2)
  * - simplejson (3.8.2)
  * - SQLAlchemy (1.0.14)
  * - Werkzeug (0.11.10)

Note that the same code works without issues on windows.

--
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] Detecting relationship conflicts

2016-07-19 Thread Burak Arslan

Hi,

Behold this test:

https://github.com/plq/spyne/blob/be189784b02e204b031f25bc748d9236ef7dfc59/spyne/test/test_sqlalchemy.py#L314

This is for Spyne => SQLAlchemy object bridge so please ignore
Spyne-specific stuff in there.

I noticed that this produced warning like this:

/home/plq/.local/lib64/python2.7/site-packages/sqlalchemy/orm/relationships.py:1741:
SAWarning: Warning: relationship 'foos' on mapper 'Mapper|SubBar|bar'
supersedes the same relationship on inherited mapper 'Mapper|Bar|bar';
this can cause dependency issues during flush

So now I wrote a patch that omits relationships from parent classes in
subclasses and that warning is gone. However, my test at the end keeps
failing.

1. Is there any way I can properly test this?

2. Is there a way to explicitly compile mappers? That lonely
instantiation looks quite weird there.

Best regards,
Burak

-- 
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] Error IM001 reflecting table

2016-07-19 Thread Angelo Bulone



first of all, sorry if I'm not writing in the right place or I'm not 
providing enough info about the issue.

Using SQL Alchemy, with pyodbc. I'm trying to reflect a table. When I try 
to do that, i get this message

DBAPIError: (pyodbc.Error) ('IM001', '[IM001] [unixODBC][Driver 
Manager]Driver does not support this function (0) (SQLNumParams)') [SQL: 
u'SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], 
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], 
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], 
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], 
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], 
[COLUMNS_1].[COLLATION_NAME] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS 
[COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND 
[COLUMNS_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) ORDER BY 
[COLUMNS_1].[ORDINAL_POSITION]'] [parameters: ('Order', 'dbo')]

Here is the code..

>>> from sqlalchemy.orm.session import Session>>> from sqlalchemy.schema import 
>>> MetaData>>> import sqlalchemy as SQLA>>> eng = 
>>> SQLA.create_engine(connection_string)>>> session = 
>>> Session(eng.connect())>>> class DB:... pass... >>> db = DB()>>> 
>>> db.session = session>>> db.engine = eng>>> db.metadata = 
>>> MetaData(bind=db.engine, schema='dbo')>>> db.session.execute("select * from 
>>> information_schema.columns")>> 0x7f76eb770890 t = SQLA.Table('Order', db.metadata, autoload=True, 
>>> extend_existing=True, autoload_with=db.engine)>>> Traceback (most recent 
>>> call last):...   File "", line 1, in  ... See the error above

The code is running on a RHEL 7.x with unixODBC, Microsoft SQL Server 
Native clinet 11 for Linux. Python 2.7.11

Here are the pip requirements

   - - click (6.6) db-connection-maker (1.2.0)
   - - Flask (0.11.1)
   - - itsdangerous (0.24)
   - - Jinja2 (2.8)
   - - MarkupSafe (0.23)
   - - pip (8.0.2)
   - - pyaml (15.8.2)
   - - pyodbc (3.0.10)
   - - PyYAML (3.11)
   - - setuptools (19.6.2)
   - - simplejson (3.8.2)
   - - SQLAlchemy (1.0.14)
   - - Werkzeug (0.11.10)

Note that the same code works without issues on windows.

-- 
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] Combining yield_per and eager loading

Re: [sqlalchemy] Combining yield_per and eager loading

[sqlalchemy] gevent.monkey.patch_all() breaks my Sqlalchemy connection...

Re: [sqlalchemy] Detecting relationship conflicts

Re: [sqlalchemy] Detecting relationship conflicts

Re: [sqlalchemy] Detecting relationship conflicts

Re: [sqlalchemy] Detecting relationship conflicts

Re: [sqlalchemy] Combining yield_per and eager loading

Re: [sqlalchemy] SAWarning shows not immediately, but after some time of app execution

Re: [sqlalchemy] Detecting relationship conflicts

Re: [sqlalchemy] Combining yield_per and eager loading

Re: [sqlalchemy] SAWarning shows not immediately, but after some time of app execution

Re: [sqlalchemy] SAWarning shows not immediately, but after some time of app execution

[sqlalchemy] SAWarning shows not immediately, but after some time of app execution

Re: [sqlalchemy] Error IM001 reflecting table

[sqlalchemy] Detecting relationship conflicts

[sqlalchemy] Error IM001 reflecting table

17 matches

Site Navigation

Mail list logo

Footer information