Python-sql In some cases this results in hundreds of thousands of calls to the __str__ loop

Support Developer
1

Hai, I’m using python-sql in python2.7 windows now，In some cases, parsing SQL is getting slow.

  • I generated pickle objects from SQL objects, and I noticed that the str method parsing SQL seems to be called in a loop. An SQL parse will call the function str hundreds of thousands of times, which will take several seconds
  • python 2.7 python-sql==1.2.1
  • Here is my test file
    http://www.chaoyue.red/static/select.pkl 
import pickle
import sql
select = pickle.load(open('select.pkl','rb'))
%time tuple(select)

test
test1112×730 25.3 KB


2

Depending of the size, number of parameters etc. of the query, some methods may be called multiple times. I do not understand the problem.

3

I found that this function was called hundreds of thousands of times,The generated SQL is duplicated,Then I changed this part of the code

# sql line 1053
    def __str__(self):
        join = '%s %s JOIN %s' % (From([self.left]), self.type_,
            From([self.right]))
        if self.condition:
            condition = ' ON %s' % self.condition
        else:
            condition = ''
        return join + condition

import sql
from sql import From
from threading import Lock
lock = Lock()
_only = {}

sql.lock = lock
sql._only = _only


def __iter__(self):
    with lock:
        _only.clear()
        yield str(self)
        yield self.params


def __str__(self):
    query = (self.left, self.type_, self.right)
    if (query) in _only:
        return _only[query]
    else:
        join = '%s %s JOIN %s' % (From([self.left]), self.type_,
        From([self.right]))
        if self.condition:
            condition = ' ON %s' % self.condition
        else:
            condition = ''
        _only[query] = join + condition
        return join + condition


sql.Query.__iter__ = __iter__
sql.Join.__str__ = __str__
  • Performed again

test3
test31253×612 43 KB

The number of loops is reduced, and the results are consistent

Confused as to why repeated join SQL calls multiple times？

4

I do not see why there should be multiple call to __str__ of Join. I’m wondering if it is not your Python shell that triggers those calls.