Pandas/mysql example for windows

Download installer from Python Windows binaries site - Python 3.4 has already installed apparently.

Then run the following command from the download directory:

!pip install MySQL_python-1.2.5-cp27-none-win_amd64.whl

To start a mysql server run:

cd "Program Files\MySQL\MySQL Server 5.6\bin"
mysqld

To start up the service, run from the same directory:

mysql -u root

Ignore password/other users for basic usage.



In [1]:

    
import MySQLdb #was installed via pip above
import pandas as pd
import numpy as np
import pandas.io.sql as psql

Create a test data frame:



In [4]:

    
df = pd.DataFrame(np.array([[3,4,5,6], [234,5,678,65], list('abdc')]).T)
df.columns = ['col1', 'col2', 'col3']
df.head()



In [5]:

    
def create_conn():
    conn = MySQLdb.connect(host = "localhost"
                     , user = "root"
                     , db = "scratch")
    return conn



In [7]:

    
conn = create_conn()
cursor = conn.cursor()

#can replace, append or ignore in if_exists
psql.to_sql(df, con=conn, name='test', flavor='mysql', if_exists='replace', index=False)
cursor.close()
conn.close()









    



C:\Users\andrewd\AppData\Local\Continuum\Anaconda\lib\site-packages\pandas\io\sql.py:588: FutureWarning: The 'mysql' flavor with DBAPI connection is deprecated and will be removed in future versions. MySQL will be further supported with SQLAlchemy engines.
  warnings.warn(_MYSQL_WARNING, FutureWarning)

Check that data has been inserted:



In [8]:

    
conn = create_conn()
cursor = conn.cursor()
cursor.execute("SELECT * FROM test;")
rows = cursor.fetchall()
for row in rows:
    print row
cursor.close()
conn.close()









    



('3', '234', 'a')
('4', '5', 'b')
('5', '678', 'd')
('6', '65', 'c')

Return back in data frame form:



In [19]:

    
conn = create_conn()
sql = "SELECT * FROM test ORDER BY col2 ASC LIMIT 2 OFFSET 1;"
newdf = psql.read_sql(sql, conn)
newdf.head()