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
– Russel FP, Commented Jun 9, 2022 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
– Martinswe, Commented Jun 9, 2022 at 12:43
Part from importing packages pandas and pyodbc this all the code I run in one sequence. — Martinswe
– Martinswe, Commented Jun 9, 2022 at 12:45
However there's red error dot (spider) on the line "con = pyodbc.connect(driver_path)" saying "undefined name "driver_path". — Martinswe
– Martinswe, Commented Jun 9, 2022 at 12:47

Gedas Miksenas · Accepted Answer · 2022-06-09 12:52:09Z

1

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

answered Jun 9, 2022 at 12:52

Gedas Miksenas

1,07910 silver badges18 bronze badges

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

Comments

furas · Accepted Answer · 2022-06-17 12:48:04Z

1

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

edited Jun 17, 2022 at 12:48

answered Jun 9, 2022 at 13:58

furas

149k12 gold badges121 silver badges171 bronze badges

5 Comments

Martinswe Over a year ago

Thanks, really appreciate that you took time to look in to it!

Martinswe Over a year ago

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....

furas Over a year ago

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 Over a year ago

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 Over a year ago

I added this in code.

Collectives™ on Stack Overflow

sql to dataframe in function

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related