How do you select values from a SQL column in Python

Question

I have a column called REQUIREDCOLUMNS in a SQL database which contains the columns which I need to select in my Python script below.

Excerpt of Current Code:

db = mongo_client.get_database(asqldb_row.SCHEMA_NAME)
coll = db.get_collection(asqldb_row.TABLE_NAME) 
table = list(coll.find())
root = json_normalize(table)

The REQUIREDCOLUMNSin SQL contains values reportId, siteId, price, location

So instead of explicitly typing:

print(root[["reportId","siteId","price","location"]])

Is there a way to do print(root[REQUIREDCOLUMNS])?

Note: (I'm already connected to the SQL database in my python script)

richinrix · Accepted Answer · 2021-03-11 18:27:58Z

2

You will have to use cursors if you are using mysql or pymysql , both the syntax are almost similar below i will mention for mysql

import mysql
import mysql.connector

db = mysql.connector.connect(
   host = "localhost",
    user = "root",
    passwd = "  ",
    database = "  "
)

cursor = db.cursor()
sql="select REQUIREDCOLUMNS from table_name"
cursor.execute(sql)
required_cols = cursor.fetchall()#this wll give ["reportId","siteId","price","location"] 

cols_as_string=','.join(required_cols)
new_sql='select '+cols_as_string+' from table_name'

cursor.execute(new_sql)
result=cursor.fetchall()

This should probably work, i intentionally split many lines into several lines for understanding. syntax could be slightly different for pymysql

answered Mar 11, 2021 at 18:27

richinrix

4921 gold badge4 silver badges13 bronze badges

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

1 Comment

jcoke Over a year ago

so in regards to the question as i'm referring to SQL Server would this method still work? i'm using pyodbc to connect to SQL Server

Collectives™ on Stack Overflow

How do you select values from a SQL column in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related