Extract 1 Column of data from SQL in Python

Question

I have connected to a SQL database through python and am trying to extract one column of data. However, when I go to print what I extracted, it displays the following:

('A', )
('B', )
('C', )
('D', )

When I extract two columns, it prints:

('A', 'a' )
('B', 'b')
('C', 'c')
('D', 'd')

Is there a way to simply take the data in the first column without the additional , ) or even transform into a dataframe while preserving the column names from SQL? I have found a lot of guides how to extract data from SQL, but none showing ability to operate as a dataframe and preserve column names.

Code to generate:

import pyodbc
conn = pyodbc.connect(driver='{SQL Server}'
                      ,server = 'server'
                      ,database = 'db1'
                      ,trusted_connection=True
                      ,user='user')
cursor=conn.cursor()
cursor.execute(
        '''SELECT
                Column_One
                --,Column_Two
            FROM db1.table''')
for row in cursor.fetchall():
    print(row)

Ahsan aslam · Accepted Answer · 2020-03-05 14:24:05Z

2

Try that

import pyodbc
conn = pyodbc.connect(driver='{SQL Server}'
                      ,server = 'server'
                      ,database = 'db1'
                      ,trusted_connection=True
                      ,user='user')
cursor=conn.cursor()
cursor.execute(
        '''SELECT
                Column_One
                --,Column_Two
            FROM db1.table''')

final_result = [list(i) for i in cursor.fetchall()]

answered Mar 5, 2020 at 14:24

Ahsan aslam

1,1993 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jack Armstrong Over a year ago

This prints a list of lists such that each inner list is 1 element. It works for my simple tasks, but does not preserve the columns. Very simplistic for small stuff like I need now.

Sankar · Accepted Answer · 2020-03-05 14:24:11Z

0

from sqlalchemy import create_engine
import pymysql
def keyword_sql():
    try:
        db_connection_str = 'mysql+pymysql://user:password@server/database'
        db_connection = create_engine(db_connection_str)
        df = pd.read_sql('SELECT keyword, category FROM additional_data_dictionary', con=db_connection)
        return df
    except:
        print("mysql connection issue - ignoring the user input")
        sys.exit(1)

See if this helps, I am not using pyodbc, I am using "sqlalchemy" - this code directly returns the pandas dataframe

answered Mar 5, 2020 at 14:24

Sankar

5861 gold badge4 silver badges17 bronze badges

Collectives™ on Stack Overflow

Extract 1 Column of data from SQL in Python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related