Return a mapped dictionary based on multiple queries

Question

Issue: I can't figure out how to run a query in the correct way so that it returns a mapped dictionary. The query will use counts from multiple tables.

I am using psycopg2 for a postgresql database, and I will be using the results to create a report on day to day deltas on these counts.

Given that, can someone provide an example on how to execute multiple queries and return a dictionary that I can use for comparison purposes? Thanks! I image in a for loop is needed somewhere in here.

tables = ['table1', 'table2']
def db_query():
    query = "select count(*) from (a_table) where error_string != '';"
    conn = psycopg2.connect(database=db, user=user, password=password, host=host)
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute(query, tables)
    output = cur.fetchall()
    conn.close()
    return output

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

I haven't used postgresql, so you might want to also check this out as a reference: How to store count values in python.

That being said, rearrange your code into something like this. Be sure to make conn global so you don't have to make more than one connection, and make sure you're also closing cur:

conn = None    

def driverFunc():
    global conn
    try:
        conn = psycopg2.connect(database=db, user=user, password=password, host=host)
        tables = ['table1', 'table2']
        countDict = {}
        for thisTable in tables:
            db_query(thisTable, countDict)
    finally:
        if not conn == None:
            conn.close()

def db_query(tableName, countDict):
    # Beware of SQL injection with the following line:
    query = "select count(*) from " + tableName + " where error_string != '';"
    cur = None

    try:
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cur.execute(query)
        countDict[tableName] = int(cur.fetchone())
    finally:
        if not cur == None:
            cur.close()

edited May 23 '17 at 11:50

Community

1
1

answered Sep 24 '13 at 04:30

musical_coder

3,886
3
15
18

Thanks for this. One note, the psycopg2 module documentation strongly advises against string concatenation when running a query. [Psycopg2 Documentation](http://initd.org/psycopg/docs/usage.html) – Victor Torres Sep 24 '13 at 23:43
I guess you acknowledged this with the comment. – Victor Torres Sep 25 '13 at 00:19
This ended up being super helpful, I wasn't quite to figure out the 'proper' way to replace the string concatenation in the query, but since it won't be public this should suffice. Thanks! – Victor Torres Sep 25 '13 at 01:02
1

Glad it helped! For your own piece of mind, I'd switch to a parameterized query if possible. This might help: http://stackoverflow.com/questions/1466741/parameterized-queries-with-psycopg2-python-db-api-and-postgresql – musical_coder Sep 25 '13 at 03:37
the int(cur.fetchone()) part isn't functioning for me. It registers this error: TypeError: int() argument must be a string or a number, not 'DictRow'. Any thoughts? – Victor Torres Sep 26 '13 at 03:19
How about `int(cur.fetchone()[0])`? – musical_coder Sep 26 '13 at 03:43

Return a mapped dictionary based on multiple queries

1 Answers1