sql to dataframe in function-CodePudding

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

CodePudding user response：

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()

CodePudding user response：

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__():
      self.driver_path = ("Driver={SQL Server};"
                          "Server=xxx;"
                          "Database=xxx;"
                          "Trusted_Connection=xxx;")       

  def select(self, query):  # you don't need `con`
      con = pyodbc.connect(self.driver_path)
      
      query = "SELECT  * FROM TABLE "
       
      result = pd.read_sql(query, con)
      return result

EDIT:

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

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

  def select(self, query):  # you don't need `con`
      
      query = "SELECT  * FROM TABLE "
       
      result = pd.read_sql(query, self.con)
      
      return result