Lesson 9

Export data from a microdost sql database to cvs, excel, and txt.

In [1]:
# Import libraries
import pandas as pd
import sys
from sqlalchemy import create_engine, MetaData, Table, select
In [2]:
print 'Python version ' + sys.version
print 'Pandas version: ' + pd.__version__
Python version 2.7.5 |Anaconda 2.0.1 (64-bit)| (default, Jul  1 2013, 12:37:52) [MSC v.1500 64 bit (AMD64)]
Pandas version: 0.14.1

Grab Data from SQL

In this section we use the sqlalchemy library to grab data from a sql database. Note that the parameter section will need to be modified.

In [3]:
# Parameters
ServerName = "RepSer2"
Database = "BizIntel"
TableName = "DimDate"

# Create the connection
engine = create_engine('mssql+pyodbc://' + ServerName + '/' + Database)
conn = engine.connect()

# Required for querying tables
metadata = MetaData(conn)

# Table to query
tbl = Table(TableName, metadata, autoload=True, schema="dbo")
#tbl.create(checkfirst=True)

# Select all
sql = tbl.select()

# run sql code
result = conn.execute(sql)

# Insert to a dataframe
df = pd.DataFrame(data=list(result), columns=result.keys())

# Close connection
conn.close()

print 'Done'
Done

All the files below will be saved to the same folder the notebook resides in.

Export to CSV

In [4]:
df.to_csv('DimDate.csv', index=False)
print 'Done'
Done

Export to EXCEL

In [5]:
df.to_excel('DimDate.xls', index=False)
print 'Done'
Done

Export to TXT

In [6]:
df.to_csv('DimDate.txt', index=False)
print 'Done'
Done

Author: David Rojas LLC