Faster way to execute multiple queries to postgresql in python

Question

I'm writing a script in python where I will need to access a postgresql database multiple times and execute multiple select queries and insert queries. I am trying to reduce the time it takes for this script to run.

Currently I have written a secondary function which I pass a qry string, a boolean indicating if I am inserting or recieving data, and a list of parameters and then execute the query:

    def sql_call(qry, insert, inputlist):
        params = config_np()
        with psycopg2.connect(**params) as conn:
            cur = conn.cursor()
            try:
                cur.execute(qry, inputlist)
                if insert:
                    conn.commit()
                    sqlrtn = True
                else:
                    sqlrtn = cur.fetchall()
            except (Exception, psycopg2.DatabaseError) as error:
                print(error)
                quit()
    conn.close()
    return sqlrtn

I'm working with a few hundred thousand entries and this takes forever to run. Is there a faster way to do it?

Are you ware that your results may depend on the execution order of the queries? In any case, the select queries can be run in parallel, e.g. with multiprocessing. — norok2
– norok2, Commented Jun 24, 2019 at 21:22
Some ways not related to python is to index the column in database which you filter the query on and also you can optimize the query. One other way is to load the database in memory using pandas psql and then do operations on it , but this option is only viable is database is small and fits in memory. — arajshree
– arajshree, Commented Jun 24, 2019 at 21:23
Consider also re-using the same connection between function calls. — progmatico
– progmatico, Commented Jun 24, 2019 at 21:37

Deepstop · Accepted Answer · 2019-06-24 21:43:17Z

1

A few things you can do. First, don't reestablish the connection with each query. This can be used over multiple queries so you will not need to recreate it with each query. If you still want to have the flexibility of having a function to execute the query, create a class where the __init__ method opens the connection and keeps it in self.conn, and a __del__ method that closes the connection.

For INSERT operations, you can insert multiple rows if you're using VALUES with,

INSERT INTO table (fld1, fld2, fld3)
    (VALUES ('some', 'data', 'here'),
            ('more', 'data', 'here'));

psycopg2 will take quite a lot rows in a query like this. For the SELECT queries, it would depend on what your program is doing. Depending on requirements, you could cache quite of lot a data in memory given the amount available on computers these days.

answered Jun 24, 2019 at 21:43

Deepstop

3,8372 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Faster way to execute multiple queries to postgresql in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related