Unpacking Python Dictionary of Various Length into sql query

Question

I currently have a function which takes a dictionary as an input and returns all columns in database table as a dict:

import sqlite3

def get_person_dict_from_dict(self):
    database = "/Users/Mary/Documents/Database/{DB}.db".format(DB=self['db'])
    conn = sqlite3.connect(database)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    sql_command = "SELECT * FROM {dbTableIn} WHERE Identifier = '{nameIn}' AND Day = {dateIn};".format(
        dbTableIn=self['my_table'],
        dateIn=self['date'],
        nameIn=self['names'])
    c.execute(sql_command)
    r = c.fetchall()
    result = [dict(row) for row in r]
    dict_out = result[0]
    return dict_out

inputDict = {"date" : '19891229',"names" : 'Mary', "db" :'MyDatabase', "my_table" :'Measurements'}
outputDict = get_person_dict_from_dict(inputDict)

This works fine. However, how do I refine it such that:

1) I can include an additional argument, as a list/tuple/dict of variable length, such that I can pull only the attributes that I am interested in rather than all of the available metrics. For example:

attributesWanted = ['Age', 'Height']
inputDict = {attributesWanted, "date" : '19891229',"names" : 'Mary', "db" :'MyDatabase', "my_table" :'Measurements'}

yet, for example, be able to flexibly use attributesWanted = ['Age', 'Height', 'ShoeSize'] if needed.

2) Be able to do this for multiple individuals. E.g namesWanted = ['Mary', 'Joe']

Preferably would be able to use a single dictionary as an input to the function. I have tried including lists and tuples but run up against errors such as

TypeError: 'tuple' object is not a mapping

score 0 · Answer 1 · answered Dec 24 '19 at 12:51

I would recommend to use external function which parse the clause to the relevant column and pass the args as column_type so you will be able to defer between the value types another thing is using kwargs (keyword args) in the given function

here is a short example which parse the args and concat them.

import sqlite3

def format_arg(key, value):
    ## remove the suffix
    key_without_suffix = "_".join(key.split("_")[:-1])
    if key.endswith("str"):
        return "{key} = '{value}'".format(key=key_without_suffix, value=value)
    if key.endswith("date"):
        return "{key} = {value}".format(key=key_without_suffix, value=value)
    if key.endswith("list"):
        return "{key} IN ({value})".format(key=key_without_suffix, value=(",".join(value)))

def get_person_dict_from_dict(db, table, **kwargs):
    database = "/Users/Mary/Documents/Database/{DB}.db".format(DB=db)
    conn = sqlite3.connect(database)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    where_clause = " AND ".join([format_arg(k,v) for k,v in kwargs.items()])
    sql_command = "SELECT * FROM {table} WHERE {where};".format(
        table=table, where=where_clause)
    c.execute(sql_command)
    r = c.fetchall()
    result = [dict(row) for row in r]
    dict_out = result[0]
    return dict_out

input_dict = {"Date_date": "19891229", "names_list": ["Marry", "Anne"]}
get_person_dict_from_dict(db="MyDatabase", table="Measurements", **input_dict)

Unfortunately this still returns all columns. – Mary Jan 18 '20 at 11:42 — Mary, Jan 18 '20 at 11:42

Unpacking Python Dictionary of Various Length into sql query

1 Answers1