1

I'm a complete newbie when it comes to Python. Using pyodbc, I've managed to connect to an instance of SQL Server Express 2008 R2 I have running locally. How do I create an array similar to a dataframe in R?

Using RODBC in R, I can easily fetch directly to a dataframe. For example, if I pull a table with 5 columns and 5 rows, I fetch the data into a dataframe with 5 columns and 5 rows.

With pyodbc, I fetch what appears to be all the cells consecutively like this:

[('2013 Q4', 'An', 'Ta', 'Mono', 'Consumer', 'Europe', '2014 Q3', '2014', 'Q3', 'Transfer', 1.0), ('2013 Q4', 'An', 'Ta', 'Mono', 'Consumer', 'Europe', '2014 Q4', '2014', 'Q4', 'Transfer', 1.0)]

This is the code I used in Python 2.7.3:

    cursor.execute("""select * 
            from table1
            """)
    row = cursor.fetchall()
    print row

Can someone direct me to some reference material online? (I'm having no luck with google.) Is it even possible to do what I'm asking with Python? In R, reading data from SQL tables is trivial. I'm getting the impression that I have to process each fetched line with Python???

user2962397
  • 393
  • 1
  • 5
  • 12
  • 3
    possible duplicate of [How to convert SQL Query result to PANDAS Data Structure?](http://stackoverflow.com/questions/12047193/how-to-convert-sql-query-result-to-pandas-data-structure) – m.wasowski Mar 25 '14 at 19:36
  • 1
    You should probably be using [pandas](http://johnbeieler.org/blog/2013/06/06/using-sql/). – kindall Mar 25 '14 at 19:35
  • Great! This helped. I had no idea what pandas was until you two mentioned it. This will work just fine. Thank you. – user2962397 Mar 26 '14 at 16:00

1 Answers1

0

The best way to do this is using the pandas library, specifically pandas.is.sql.read_sql

import pyodbc
import pandas as ps

SQL_Connection = pyodbc.connect( INSERT SQL CONNECTION SETTINGS HERE)
cursor = SQLconnection.cursor()

SQL_Script = "Select * from table1"

df = ps.io.sql.read_sql(SQL_Script, SQL_Connection)
SQL_Connection.close()
print df
aleph-w
  • 1