Home > Net >  How can I build a dictionary from a SQLAlchemy row, using cell data as keys and values instead of co
How can I build a dictionary from a SQLAlchemy row, using cell data as keys and values instead of co

Time:02-22

I'm building a Flask API to retrieve information from a Woocommerce database that I cannot modify.

This is one of the tables schema, from which I only need the columns meta_key and meta_value.

MariaDB [<db_name>] DESCRIBE aus_postmeta;
 ------------ --------------------- ------ ----- --------- ---------------- 
| Field      | Type                | Null | Key | Default | Extra          |
 ------------ --------------------- ------ ----- --------- ---------------- 
| meta_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
 ------------ --------------------- ------ ----- --------- ---------------- 

As the column names suggest, the column meta_key is a key (for example, _billing_address) and then the column meta_value stores the billing address string in itself. What I want to do is to build a dictionary (or a set) in which the keys are not the column name, but the value held by meta_key and, respectively, the dictionary value is the information stored in meta_value.

This is the query I run to retrieve the columns filtered by id and getting only the needed meta_keys.

#columns is an array of strings

rows = db.session.query(t_aus_postmeta).\
    with_entities(t_aus_postmeta.c.meta_key, t_aus_postmeta.c.meta_value, ).\
        filter(t_aus_postmeta.c.post_id == order_id).\
            filter(t_aus_postmeta.c.meta_key.in_(columns)).\
                all()

Using the following code, as advised in this question to build a dictionary from the row object returned by the query:

# Returns a list with a dictionary, but both keys and values are tuples

>>>> as_dict = [dict(zip(row.keys(), row) for row in rows)]
[{('meta_key', '_billing_shipment'): ('meta_value', 'Recupérate pronto'), ('meta_key', '_order_total'): ('meta_value', '134000')}]

But I want to take the value in meta_key and use it as the dictionary key. Respectively, take the value in meta_value and use it as that key's corresponding value, so it would be something like this:

{
    "_billing_shipment": "Recupérate pronto",
    "_order_total": "134000"
    # and so on...
}

I've been struggling with this problem but haven't found a way to build the dictionary as I need it. How can I do it? Looking at the tuples returned by the code above, I feel there must be a way to do something like tuple.meta_key (for the dictionary key) and tuple.meta_value, but I can't think of anything.

CodePudding user response:

dict = {result.meta_key: result.meta_value for result in results}

It was much easier than I thought it was.

  • Related