[FIXED] SQLAlchemy Retrieving Different Columns

Issue

Here is my code:

import pandas as pd
from sqlalchemy import create_engine

db_username = "my_username"
db_pw = "my_password"
db_to_use = "my_database"
#####
engine = create_engine(
    "postgresql://" +
    db_username + ":" +
    db_pw +
    "@localhost:5432/" +
    db_to_use
    )
#####
connection = engine.connect()
fac_id_list = connection.execute (
    """
        select distinct
            a.name,
            replace(regexp_replace(regexp_replace(a.logo_url,'.*/logo','','i'),'production.*','','i'),'/','') as new_logo
        from
            sync_locations as a
        inner join
            public.vw_locations as b
            on
                a.name = b.location_name
        order by
             new_logo
        """
)

I want to put the results of fac_id_list into two separate lists. One list will contain all of the values from a.name and the other new_logo.

How can I do this?

sql_results = []
for row in fac_id_list:
     sql_results.append(row)

This puts every column in my SQL query into a list, but I want them separated.

Solution

When you loop over the results, you can spread them into separate variables and append them to the corresponding lists

names = []
logos = []
for name, logo in fac_id_list:
    names.append(name)
    logos.append(log)

Answered By - Barmar