Python SQLAlchemy query distinct returns list of lists instead of dict

user_5806297 0
Viewed 660

I'm using SQLAlchemy to setup some data models and query it. I have the following table class

class Transactions(Base):
    __tablename__ = 'simulation_data'
    sender_account = db.Column('sender_account', db.BigInteger)
    recipient_account = db.Column('recipient_account', db.String)
    sender_name = db.Column('sender_name', db.String)
    recipient_name = db.Column('recipient_name', db.String)
    date = db.Column('date', db.DateTime)
    text = db.Column('text', db.String)
    amount = db.Column('amount', db.Float)
    currency = db.Column('currency', db.String)
    transaction_type = db.Column('transaction_type', db.String)
    fraud = db.Column('fraud', db.BigInteger)
    swift_bic = db.Column('swift_bic', db.String)
    recipient_country = db.Column('recipient_country', db.String)
    internal_external = db.Column('internal_external', db.String)
    ID = Column('ID', db.BigInteger, primary_key=True)

I'm trying to get distinct row values for columns recipient_country and internal_external using the following script

data = db.query(
    Transactions.recipient_country,
    Transactions.internal_external).distinct()

However, this doesn't retrieve all distinct combinations of these two columns (it neglects values for Transactions.internal_external in this case). Example:

 {
"China": "External",
"Croatia": "External",
"Denmark": "Internal",
"England": "External",
"Germany": "External",
"Norway": "External",
"Portugal": "External",
"Sweden": "External",
"Turkey": "External"
}

When I try

data = db.query(
    Transactions.recipient_country,
    Transactions.internal_external).distinct().all()

The correct output is returned, however it comes out as a list of lists, and not a dict. Example:

[["China","External"],["Croatia","External"],["Denmark","External"],["Denmark","Internal"],["England","External"],["Germany","External"],["Norway","External"],["Portugal","External"],["Sweden","External"],["Turkey","External"]]

I'm trying to reproduce the following SQL query:

SELECT DISTINCT 
       [recipient_country], 
       [internal_external]
FROM [somedb].[dbo].[simulation_data];

I want it to return the data as a dict instead. What am I doing wrong?

sqlalchemy python
1 Answers

The key in a dictionary is always unique, so if the country (China) occurs multiple times - once for external and once for external - then setting the value the second time will overwrite the first value:

result = {}
result['China'] = 'internal'
result['China'] = 'external'
print(result) # { 'China': 'external' }

How you should visualise your query more is as a list of objects (or dictionaries), with each object representing one row. Then you can have something like

[dict(country="China", internal="internal"), dict(country="China", internal="external"), ...]

Here, country and internal are the column names. You can also get these from the Query object, using query.column_descriptions before you execute .all().

EDIT: You can also store the values in an array:

query = db.query(
    Transactions.recipient_country,
    func.array_agg(Transactions.internal_external.distinct())
).group_by(Transactions.recipient_country)

data = {country: options for country, options in query}
print(data) # { 'China': ['internal', 'external'] }

Or you can use "both" as an identifier to show that internal and external are both possible:

query = db.query(
    Transactions.recipient_country,
    Transactions.internal_external
).distinct()

data = {}
for country, option in query:
    if country in data:
        option = 'both'
    data[country] = option
print(data) # { 'China': 'both' }
user_50153560
Related