CODEWITHSUNDEEP
pythonsqlpython-3.xamazon-redshift
dark horse
dark horse

Reputation: 447

Python - Reading specific column from SQL output stored in a variable

I have a basic question here. I am pulling a SQL output as below:

cur = connection.cursor()
cur.execute("""select store_name,count(*) from stores group by store_name""")
data = cur.fetchall()

The output of the above SQL is as below:

Store_1,23
Store_2,13
Store_3,43
Store_4,2

I am trying to read column 1 (store_name) in the above output.

Expected Output:

Store_1
Store_2
Store_3
Store_4

Could anyone advice on how could I have this done. Thanks..

Upvotes: 0

Views: 1551

Answers (3)

rfii
rfii

Reputation: 593

Harvard's CS50 web class has the following which I think helps you in it's last 3 lines.

  import os    
  from sqlalchemy import create_engine
  from sqlalchemy.orm import scoped_session, sessionmaker

  engine = create_engine(os.getenv("DATABASE_URL")) # database engine object from SQLAlchemy that manages connections to the database
                                                    # DATABASE_URL is an environment variable that indicates where the database lives
  db = scoped_session(sessionmaker(bind=engine))    # create a 'scoped session' that ensures different users' interactions with the
                                                    # database are kept separate

  flights = db.execute("SELECT origin, destination, duration FROM flights").fetchall() # execute this SQL command and return all of the results
  for flight in flights
      print(f"{flight.origin} to {flight.destination}, {flight.duration} minutes.") # for every flight, print out the flight info

So in your case I suppose its:

results = db.execute( <PUT YOUR SQL QUERY HERE> )
for row in results:
    print(row.store_name)

Upvotes: 0

Chirag Dhyani
Chirag Dhyani

Reputation: 1063

In your code, you can simply append the following lines: 

for rows in data:
    print(rows[0])

hope this helps.

BTW: I am not on the computer and have not crosschecked the solution.

Upvotes: 1

Grijesh Chauhan
Grijesh Chauhan

Reputation: 58281

If I correctly understand your question, I guess just correcting your SQL will give you the desired result. Fetch distinct store_name 

select distinct store_name from stores

Edit

Response to comment:

Try following:

from operator import itemgetter
data = cur.fetchall() 
list(map(itemgetter(0), data)) # your answer

Upvotes: 1

Related Questions