sql to dataframe in function

Question

new to functions so obviously there are lack of knowledge around the basics of this. I want to create a function which fetch data from sql server but I don't know how to pass it to a dataframe. This dataframe will later be used by other functions This what I have done so far.

class Inputdata:
 
  def __init__():
      driver_path = ("Driver={SQL Server};"
                    "Server=xxx;"
                    "Database=xxx;"
                    "Trusted_Connection=xxx;")       
  def select(self, query, con):
      con = pyodbc.connect(driver_path)
      
      query = """   Select  * FROM TABLE """

       
      result = pd.read_sql(query,con)
      return result

so what is exactly your question? you run it and see some error? please specify it — Russel FP, Jun 09 '22 at 12:37
What I want is a dataframe named "result" with the data fetched from sql server. But it does not generat any such dataframe with name "result". And no error either..... — Martinswe, Jun 09 '22 at 12:43
Part from importing packages pandas and pyodbc this all the code I run in one sequence. — Martinswe, Jun 09 '22 at 12:45
However there's red error dot (spider) on the line "con = pyodbc.connect(driver_path)" saying "undefined name "driver_path". — Martinswe, Jun 09 '22 at 12:47
you only created the class. now you need to make some "main" function, put inside something like inp = Inputdata() and then res = inp.select(...) however it's really very basic parts. i would recommend some python beginners course — Russel FP, Jun 09 '22 at 12:47

score 1 · Answer 1 · answered Jun 09 '22 at 12:52

Try this, but adopt to your needs please.

def get_sql_data():
    cnxn = pyodbc.connect(r'Driver=SQL Server;Server=enter_your_server;Database=enter_your_database;Trusted_Connection=yes;')
    cursor = cnxn.cursor()

    df = pd.read_sql_query('''SELECT *
                              FROM table ''', cnxn)

    cnxn.close()
    return df

df = get_sql_data()

furas · Answer 2 · 2022-06-17T12:48:04.500

EDIT:

I added self in __init__(self). And I removed query from def select(self, query) because you define it inside function so sending it as parameter is useless.

Inside __init__ you create local variable driver_path and it doesn't exist in other functions.

You have to use self.driver_path to access it in other functions in class.

class Inputdata:
 
  #def __init__():  # OLD VERSION
  def __init__(self):
      self.driver_path = ("Driver={SQL Server};"
                          "Server=xxx;"
                          "Database=xxx;"
                          "Trusted_Connection=xxx;")       

  #def select(self, query): # OLD VERSION
  def select(self):  # you don't need `con`, and you don't need `query` because you define it inside function
      con = pyodbc.connect(self.driver_path)
      
      query = "SELECT  * FROM TABLE "
       
      result = pd.read_sql(query, con)
      return result

# --- main ---

x = Inputdata()
data = x.select()

EDIT:

Eventually you could create con in __init__ and it would need self. to access it in other functions

class Inputdata:
 
  #def __init__():  # OLD VERSION
  def __init__(self):
      driver_path = ("Driver={SQL Server};"
                     "Server=xxx;"
                     "Database=xxx;"
                     "Trusted_Connection=xxx;")       
      self.con = pyodbc.connect(driver_path)

  #def select(self, query):  # OLD VERSION
  def select(self):  # you don't need `con`, and you don't need `query` because you define it inside function
      
      query = "SELECT  * FROM TABLE "
       
      result = pd.read_sql(query, self.con)
      
      return result

# --- main ---

x = Inputdata()
data = x.select()

Thanks, really appreciate that you took time to look in to it! — Martinswe, Jun 13 '22 at 11:11
Hi again, one question. tried to call the function by a = Inputdat data = a.select() but getting error "self not defined " tried many different solutions but seems not get it.... — Martinswe, Jun 17 '22 at 12:08
you should create new question on new page and show full error message and what code makes this problem. I don't know if I see where is the probleme. As for me you simply forgot `()` in line `a = Inputdata()`. But `select()` also need arguments — furas, Jun 17 '22 at 12:39
when I look at this code now then I see it needs `self` in `__init__`. And it doesn't need `query` in `select()` because it replaced by query inside `function`. — furas, Jun 17 '22 at 12:43

sql to dataframe in function

2 Answers2