On 10/17/2016 11:33 PM, Alfred Perlstein wrote:ith the community.

Adding a snippet like this to my app was very helpful, however I'm
wondering if there's something I'm missing that may cause a catastrophe?

I'm looking at maybe making it useful for detecting if a new piece of
code happens to also cause more then "N" queries to suddenly happen,
making the app slow.

Is there maybe a better way?

So especially that you are trying to maximize performance, you should:

1. put imports at the module level. imports inside of functions is usually not necessary and especially inside of multithreaded code can cause race conditions.

2. make a single event listener that you attach just once to the engine. The event registration mechanics are not cheap either.

3. don't use "".format() inside of the log statement, format() is slow and also doesn't take advantage of logging's late string interpolation feature

4. There is a pretty good, generalized way to apply stats to catch changes in execution, SQLAlchemy's own test suite uses this technique underneath test/aaa_profiling/.


        a. apply a decorator to functions you want to monitor:

                 @monitor_sqlcalls
                 def my_app_thing():

b. The decorator sets up a context that is intercepted by the before_cursor_execute() event listener you've set up (this can be keyed to threadid, or session.connection(), or a token in session.connection().info(), etc.)

c. The decorator also makes a key based on the function that is being decorated. The function's physical code position is best, e.g. looking at fn.__code__ you can get the filename, line number, etc. An example of this is in sqlalchemy/ext/baked.py also.

d. Count the SQL call and append a counter that is local to the @monitor_sqlcalls context.

e. at the end, using that code key, you retrieve from a fixed datafile (sqlite database, dbm file, pickled file, etc) what the expected number of SQL calls is. If the current number of calls exceeds it by a certain threshhold, emit a warning.

f. The datafile itself is written out as part of your application's source code. Whenever a new method is added or modified, you run the app in test mode with a flag set up such that the decorator *writes to the file* instead of comparing. SQLAlchemy's file is here: https://github.com/zzzeek/sqlalchemy/blob/master/test/profiles.txt

g. your test suite should also be making sure these methods are called and thresholds aren't passed (turn warnings into exceptions so that tests fail).

This approach can also be made extremely performant by making sure you load up that profile datafile just once, and cache it in memory in a dictionary.



thanks,

-Alfred


+++ b/jetbridge/models/quiz_attempt.py
@@ -84,12 +84,21 @@ class QuizAttempt(JBModel, db.Model):
             query = query.options(jl)
         return query

+    def new_score_before_cursor_execute(self, conn, cursor, statement,
parameters, context, executemany):
+        log.info("quiz_attempt id: {} ".format(self.id))
+        log.info("vvvvvvvvvvvvvvvvvvv\nNEW_SCORE: Received statement:
%s\n^^^^^^^^^^^^^^^^^^\n", statement)
+
+
     def new_score(self, question_id=None, question=None):
         """The new scoring system.

         Returns a triple (total_score, base_score, timer_bonus)
         """
         _log_scores("Called new_score")
+        from sqlalchemy import event
+        listen_obj = db.session.connection()
+        fun = self.new_score_before_cursor_execute
+        event.listen(listen_obj, "before_cursor_execute", fun)

         table = dict()
         """
@@ -143,6 +152,7 @@ class QuizAttempt(JBModel, db.Model):
         log.debug("loops = {}".format(loops))
         log.debug("score: {} (base: {}, timer_bonus:
{})".format(total_score, base_score, timer_bonus))
         """
+        event.remove(listen_obj, "before_cursor_execute", fun)
         return {
             "score_total": total_score,
             "score_base": base_score,



On 10/17/16 10:34 AM, Jonathan Vanasco wrote:
Alfred-

I'm sorry you felt offended and misinterpreted my comment.  I did not
think you were unskilled in the slightest.  Quite the opposite (i did
look you up).

Your concerns and solutions were in-line with to how every highly
experienced lower-level engineer I've worked with has approached an
issue in a Python app.  I was hoping to spark a positive discussion,
not offend 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 a topic in the
Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/W2c5LmCcnww/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto: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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto: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.

Reply via email to