Loading SQL data into iPython

Question

I'm trying to load a database into iPython so that I can run loops over the data with Python. So far I have the below query that will load the data, which can be printed to iPython:

> sql = %sql SELECT * FROM products

> print sql

+----+--------------+---------------+-------+
| id |    Product   |      Make     | Price |
+----+--------------+---------------+-------+
| 0  |    Product1  |      Make1    |   5   |
| 1  |    Product2  |      Make2    |   1   |
| 2  |    Product3  |      Make2    |   8   |

However I'm having trouble imputing these results into Python. Is there a good way to store data like this into python variables so that I can run loops over the data?

Thanks in advance!

Andrey · Accepted Answer · 2015-10-03 20:07:35Z

1

Check examples here:

You can do sql[ROW][COLUMN], sql[0][1] will return Product1. If you iterate over sql it will iterate over rows.

answered Oct 3, 2015 at 20:07

Andrey

60.3k12 gold badges127 silver badges169 bronze badges

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

Comments

jgloves · Accepted Answer · 2015-10-03 21:11:11Z

1

Definitely check out the Python package Pandas. You can import data from a SQL query into a Pandas dataframe with the read_sql_query function.

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html

import pandas as pd
import pyodbc

cnxn = pyodbc.connect(connection_info) 
sql = "SELECT * FROM TABLE"
df = pd.read_sql_query(sql, cnxn)
cnxn.close()

Then you can perform functions on columns. You can even plot your data easily. This may be a more powerful tool than you're looking for, but if you want to do more than just loop over your data, it's worth checking out.

10 minutes to Pandas: http://pandas.pydata.org/pandas-docs/stable/10min.html

answered Oct 3, 2015 at 21:11

jgloves

7194 silver badges14 bronze badges

Collectives™ on Stack Overflow

Loading SQL data into iPython

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related