%sql: To Pandas and Back

A Pandas DataFrame has a nice to_sql(table_name, sqlalchemy_engine) method that saves itself to a database.

The only trouble is that coming up with the SQLAlchemy Engine object is a little bit of a pain, and if you're using the IPython %sql magic, your %sql session already has an SQLAlchemy engine anyway. So I created a bogus PERSIST pseudo-SQL command that simply calls to_sql with the open database connection:

%sql PERSIST mydataframe

The result is that your data can make a very convenient round-trip from your database, to Pandas and whatever transformations you want to apply there, and back to your database:

In [1]: %load_ext sql

In [2]: %sql postgresql://@localhost/
Out[2]: u'Connected: @'

In [3]: ohio = %sql select * from cities_of_ohio;
246 rows affected.

In [4]: df = ohio.DataFrame()

In [5]: montgomery = df[df['county']=='Montgomery County']

In [6]: %sql PERSIST montgomery
Out[6]: u'Persisted montgomery'

In [7]: %sql SELECT * FROM montgomery
11 rows affected.
Out[7]: 
[(27L, u'Brookville', u'5,884', u'Montgomery County'),
 (54L, u'Dayton', u'141,527', u'Montgomery County'),
 (66L, u'Englewood', u'13,465', u'Montgomery County'),
 (81L, u'Germantown', u'6,215', u'Montgomery County'),
 (130L, u'Miamisburg', u'20,181', u'Montgomery County'),
 (136L, u'Moraine', u'6,307', u'Montgomery County'),
 (157L, u'Oakwood', u'9,202', u'Montgomery County'),
 (180L, u'Riverside', u'25,201', u'Montgomery County'),
 (210L, u'Trotwood', u'24,431', u'Montgomery County'),
 (220L, u'Vandalia', u'15,246', u'Montgomery County'),
 (230L, u'West Carrollton', u'13,143', u'Montgomery County')]

Presentation links

I have just had a VERY. Busy. Week. (In a good way!) I've promised the world many talk materials, so:

• At Ohio LinuxFest, IPython for non-Pythonistas
• At APCUG Regional Conference, Python
• At Postgres Open, IPython: your new SQL client

As for Postgres Open, I absolutely loved it! So happy I finally got to go. I am proud to say that I was the very first to buy my admission for 2014! Hope to blog more about that later...

IPython at Ohio LinuxFest 2013

Are you signed up yet for Ohio LinuxFest on Sep. 13-15? I'll be there to present

IPython for non-Pythonistas

Break out of your (bash) shell! IPython and the IPython Notebook have swept over the Python programming community, but they're not just for Python programmers - they make for high-powered shell replacements even with little to no Python knowledge. They'll also let you document your work and collaborate with others like never before. Find out how these beautiful tools can improve your daily Linux work!

At PyOhio, I argued that all Python programmers need IPython. At OLF, I'll make the case that non-Pythonistas need IPython, too. Perhaps my next talk will be "Even Your Cat Needs IPython".

Also at OLF, look for PyOhio's booth for info on next year's PyOhio, other Python events around the region, and general Python love!

IPython %helloworld extension

At Monday's after-PyOhio sprint, I changed ipython-sql from an IPython Plugin to an Extension; this makes it compatible with IPython 1.0. Fortunately, this was really easy; mostly I just deleted Plugin code I didn't understand anyway.

But I do feel like "Writing Extensions" docs are lacking a "Hello World" example. Here's mine.

from IPython.core.magic import Magics, magics_class, line_magic, cell_magic

@magics_class
class HelloWorldMagics(Magics):
    """A simple Hello, <name> magic.
   
    """

    @line_magic  # or ``@line_magic("hi")`` to make ``%hi`` the name of the magic
    @cell_magic  
    def helloworld(self, line='', cell=None):
        """Virtually empty magic for demonstration purposes.

        Example::
        
          In [1]: %load_ext helloworld

          In [2]: %helloworld Catherine
          Out[2]: u'Hello, Catherine'
        

        """
        return "Hello, %s\n%s" % (line, cell or "")
       
def load_ipython_extension(ip):
    ip.register_magics(HelloWorldMagics)

Easy HTML output in IPython Notebook

If any object has a _repr_html_ method, the IPython Notebook will use it to render HTML output. It's really easy to make a simple class that permits general dynamic HTML-rich output with Markdown. Markdown is a superset of HTML, so HTML in your output string will work, too.

import markdown
class MD(str):
    def _repr_html_(self):
        return markdown.markdown(self)

Four little lines, and you can do this!

ipython-sql bind variables

Thanks to Mike Wilson, ipython-sql now supports bind variables!

In [12]: name = 'Countess'

In [13]: %sql select description from character where charname = :name
Out[13]: [(u'mother to Bertram',)]

Review of Learning IPython for Interactive Computing and Data Visualization

valuable but traditional

May 25, 2013 by Catherine Devlinproduct

Learning IPython for Interactive Computing and Data Visualization

4 stars (of 5)

Packt Publishing recently asked if I could review their new title, Learning IPython for Interactive Computing and Data Visualization. (I got the e-book free for doing the review, but they don't put any conditions on what I say about it.) I don't often do reviews like that, but I couldn't pass one this up because I'm so excited about the IPython Notebook.

It's a mini title, but it does contain a lot of information I was very pleased to see. First and foremost, this is the first book to focus on the IPython Notebook. That's huge. Also:

• The installation section is thorough and goes well beyond the obvious, discussing options like using prepackaged all-in-one Python distributions like Anaconda.
• Some of the improvements IPython can make to a programming workflow are nicely introduced, like the ease of debugging, source code inspection, and profiling with the appropriate magics.
• The section on writing new IPython extensions is extremely valuable - it contains more complete examples than the official documentation does and would have saved me lots of time and excess code if I'd had it when I was writing ipython-sql.
• There are introductions to all the classic uses that scientists doing numerical simulations value IPython for: convenience in array handling, Pandas integration, plotting, parallel computing, image processing, Cython for faster CPU-bound operations, etc. The book makes no claim to go deeply into any of these, but it gives introductory examples that at least give an idea of how the problems are approached and why IPython excels at them.

So what don't I like? Well, I wish for more. It's not fair to ask for more bulk in a small book that was brought to market swiftly, but I can wish for a more forward-looking, imaginative treatment. The IPython Notebook is ready to go far beyond IPython's traditional core usership in the SciPy community, but this book doesn't really make that pitch. It only touches lightly on how easily and beautifully IPython can replace shell scripting. It doesn't get much into the unexplored possibilities that IPython Notebook's rich display capabilities open up. (I'm thinking of IPython Blocks as a great example of things we can do with IPython Notebook that we never imagined at first glance). This book is a good introduction to IPython's uses as traditionally understood, but it's not the manifesto for the upcoming IPython Notebook Revolution.

The power of hybrid documentation/programs for learning and individual and group productivity is one more of IPython Notebook's emerging possibilities that this book only mentions in passing, and passes up a great chance to demonstrate. The sample code is downloadable as IPython Notebook .ipynb files, but the bare code is alone in the cells, with no use of Markdown cells to annotate or clarify. Perhaps this is just because Packt was afraid that more complete Notebook files would be pirated, but it's a shame.

Overall, this is a short book that achieves its modest goal: a technical introduction to IPython in its traditional uses. You should get it, because IPython Notebook is too important to sit around waiting for the ultimate book - you should be using the Notebook today. But save space on your bookshelf for future books, because there's much more to be said on the topic, some of which hasn't even been imagined yet.

This hReview brought to you by the hReview Creator.