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.