winpython, ipython, and sql combined together

[stonebig]

Hello,

I completed the winpython 3.3.1.0 default installation to integrate sql in my portable winpython directory.

Procedure : 
- install winpython3.3.1.0 somewhere (with no "$" in the directory path, for "pip" to work correctly),
- launch "WinPython Command Prompt.exe" a first time,
- from the opened DOS window , install "SQLAlchemy" and  "ipython-sql" with this 2 commands :
    pip install SQLAlchemy
    pip install ipython-sql

- then, launch ipython a first time :
   ipython3 notebook --pylab inline
   (your favorite navigator should be recent : firefox, chrome, or internet_explorer10)
  
- from the ipython notebook install from the web the mathjax library :
  from IPython.external import mathjax; mathjax.install_mathjax()

- and now, this notebook should be possible, on every pc you copy you your winpython directory :

#this is from http://catherinedevlin.blogspot.fr examples of using sql-ipython
import pandas as pd

%load_ext sql

%%sql sqlite://
 CREATE TABLE writer (first_name, last_name, year_of_death);
 INSERT INTO writer VALUES ('William', 'Shakespeare', 1616);
 INSERT INTO writer VALUES ('Bertold', 'Brecht', 1956);
 SELECT * FROM writer

#getting those SQL query datas in python
#results[:] = query datas uploaded, results.keys[:] = columns of sql query
results = %sql SELECT * FROM writer
#combining sql query and pandas
df=pd.DataFrame(results, columns=results.keys[:])
df

[stonebig]

Here is the reverse method  to send datas from pandas to a sql server :
#Creation of a new table in the sql server  (if needed)
%sql CREATE TABLE hall_of_fame (name, surname )

#A typical treatment on a pandas Dataframe, using "pandas" syntax
df_final = df.ix[df['year_of_death']>'1518', ['first_name' , 'last_name' ]]

#Sending of df_final datas to the sql server
for recording in [tuple(x) for x in  df_final.to_records(index=False)]:
    %sql INSERT INTO hall_of_fame   VALUES   $recording
%sql select * from hall_of_fame

#step 1 : Load pandasql function in your notebook, and execute it after having checked its safety (75 lines of very simple code)  :
%load https://raw.github.com/yhat/pandasql/master/pandasql/sqldf.py

#step 2 : redo same example but now with SQL "natural" syntax everywhere
df_final = sqldf("select  first_name  ,  last_name || ' the Great' as last_name    from df where  year_of_death  > 1518    ", locals())
#Sending of datas to the sql server
for recording in [tuple(x) for x in  df_final.to_records(index=False)]:
    %sql INSERT INTO hall_of_fame   VALUES   $recording
%sql select * from hall_of_fame

[François Coulombeau]

Thanks for the work : here and on the other UTF problem. Good not to start from scratch !

François

[stonebig]

Hello,

Here is a version that doesn't require any installation of complementary module besides 'basic' winpython.

%load https://gist.github.com/stonebig/8030208/raw/baresql.py

#initial example : creating datas in sql and loading them in python
sql="""
drop table if exists writer;

CREATE TABLE writer (first_name, last_name, year_of_death);
 INSERT INTO writer VALUES ('William', 'Shakespeare', 1616);
 INSERT INTO writer VALUES ('Bertold', 'Brecht', 1956);
 SELECT * FROM writer
 """

#execute all the sql and gets the result of last query in a pandas dataframe
df = bsqldf(sql)
df

#reverse example : creating a table of datas on sql from python datas
sql="""
drop table if exists hall_of_fame;
CREATE TABLE hall_of_fame as select  first_name  ,  last_name || ' the Great' as last_name
   from df$$ where  year_of_death  > 1518;
select * from hall_of_fame
 """
bsqldf(sql)

[stonebig]

Hi,

Here are proper nbviewer notebooks.

I added a "Common Table Expression" (CTE) interpretor over SQLite, as :

- SQLite seems not in the process of including it by default (still happy with SQL-92 standard),

- CTE is a real breakthrough in SQL readability, when it arrived with SQL:1999 standard.


http://nbviewer.ipython.org/github/stonebig/baresql/blob/master/examples/baresql_with_cte.ipynb

http://nbviewer.ipython.org/github/stonebig/baresql/blob/master/examples/baresql_with_cte_code_included.ipynb