Getting 'stuck' while querying on Crate

Question

I ran loops and intensively queried a lot of data in each loop. My database is built using Crate. Sometimes, the loops paused since Crate didn't respond back my query results. (This is not always happening though) The pseudo code is as follows

from crate import client

class data_access(object):
    def __init__(self, IP):
        conn = client.connect(IP)
        self.cursor = conn.cursor()

    def get_report(self, event_id):
        self.cursor.execute('''
            select schema.events."Info", schema.events."Time"
            from schema.events
            where schema.events."Id"='%s' ''' % event_id)

        event = []
        for row in self.cursor:
           event.append((row[0], row[1]))

        return event


dal = data_access("server IP")
all_events = []
for event_id in event_ids:
    events = dal.get_report(event_id)
    if len(events) >0: all_event += events

The length of event_ids could be millions and in each loop the query is intensive. Did any DB experts who use Crate, experience this problems? If yes, how did you fix this? It seems rebooting the DB doesn't work. A ton of thanks for solution!

Are you aware that your query is open to SQL injection attack? — alroc, Mar 07 '17 at 03:34

score 1 · Answer 1 · answered Mar 28 '17 at 08:43

I was unable to reproduce this, but I suspect it may be caused by the fact you create the connection inside the loop every time. Instead of recreating the connection millions of times, I would rewrite this to create the connection once, outside the loop, and then pass it into get_report, like:

from crate import client

def get_report(event_id, conn):
    cursor = conn.cursor()
    cursor.execute('''
       select schema.events."Info", schema.events."Time"
       from schema.events
       where schema.events."Id"='%s' ''' % event_id)

    event = []
    for row in cursor:
       event.append((row[0], row[1]))

    return event


all_events = []
conn = client.connect("server IP")
for event_id in event_ids:
    events = get_report(event_id, conn)
    if len(events) >0: all_event += events

Thank you very much for the solution! But actually we set up a class and only called the connection once. I have re-edited the post with the more accurate code. Thank you. — TripleH, Apr 22 '17 at 01:08

Getting 'stuck' while querying on Crate

1 Answers1