2

I have some base info in a pandas DataFrame. I need to join it with some reference tables that I have access via a pyodbc connection. Is there any way to get the sql result set into a pandas DataFrame without writing the result set out to a csv first?

It just seems like a waste to have this extra step out to csv and into a DataFrame.

Will
  • 11,276
  • 9
  • 68
  • 76
Jim Knoll
  • 115
  • 2
  • 6

1 Answers1

7

I've gotten pyodbc to work with my SQL Server instance, then, with some help from this thread, I got the sql return to load a dataframe.

I already setup a pyodbc connection, then made a call to it. 

import pyodbc

import pandas.io.sql as psql

cnxn = pyodbc.connect(your_connection_info) 
cursor = cnxn.cursor()
sql = ("""SELECT * FROM Source""")

df = psql.frame_query(sql, cnxn)
cnxn.close()

df should return your dataframe now. The hardest part for me was getting pyodbc up and running - I had to use freetds and it took a lot of trial and error to get it work.

Community
  • 1
  • 1
mikebmassey
  • 8,354
  • 26
  • 70
  • 95
  • perfect works. saves a step... Now if only there was a way to do it from a pyTable.root.table.where('field == value') then I could avoid all intermediate csv files. – Jim Knoll Nov 27 '12 at 03:21