How to Write Python Objects to a Database for use with Tableau

1. Get Some Data

Start by accessing some data. In this example we read some data as a Pandas dataframe.


In [ ]:
import pandas as pd
import os
s3file = 'https://dsclouddata.s3.amazonaws.com/churn.csv'
churnDF = pd.read_csv(s3file, delimiter=',')
churnDF.head(5)

2. Put the Data in a Database

SQLAlechemy supports SQLite, Postgresql, MySQL, Oracle, MS-SQL, Firebird, Sybase and others

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives Python users the full power and flexibility of SQL. This example shows how a Python user can connect to a MySQL database, and write a Pandas dataframe as a table in the DB.


In [ ]:
# Install MySQLdb, the interface to the popular MySQL database server for Python for use with SQLalchemy
!sudo apt-get install python-mysqldb
!pip install sqlalchemy

In [ ]:
from sqlalchemy import create_engine

#Create these environment variables in your DataScience.com project or user profile
mySQL_Username = os.environ['BRETT_MYSQL_USERNAME']
mySQL_Password = os.environ['BRETT_MYSQL_PASSWORD']
mySQL_IP = os.environ['BRETT_MYSQL_IP']

#Create a connection to the MySQL database
engine = create_engine("mysql+mysqldb://"+mySQL_Username+":"+mySQL_Password+"@"+mySQL_IP+"/customers")

Write the dataframe as a table called 'account_info'


In [ ]:
# Use Pandas.to_sql to write the dataframe as a database table called 'account_info'. This is a useful way to make 
# Python data available to external BI tools such as Tableau

churnDF.to_sql(con=engine, name='account_info', if_exists='replace')