GiveCamp

I really enjoyed Southwest Ohio GiveCamp this year. There are lots of community events where I learn stuff and have fun, but it's especially nice when we can also look back on something good done for a good cause.

I was on a small team that redid URS Dayton's website into a WordPress site. (The new version isn't online yet, so don't go clicking today and saying, "Ew, you're proud of that?") I'll say this for WordPress: despite having no genuinely experienced WordPress users on the team, we made a very presentable site in just an evening and a day. There was even enough time left last night for me to jump into another project for an hour - and being able to contribute a little bit within an hour of jumping into an unknown project says something about just how easy GitHub makes collaboration.

It's interesting: nothing stops any of us from volunteering for any of those nonprofits anytime, but something about gathering for an intense push generates enthusiasm and momentum in a way that scattered efforts rarely do. I guess that's why conference sprints exist, too. This being Sunday, it occurs to me that most churches have pitiable websites; I wonder if churchgoing geeks could do a gathering to fix some of those up. Call it a "Sprint to Emmaus" or something.

Anyway, the broadening is nice and all, but someday I'd like to be on a Pythonista team at GiveCamp. I think it would need to be preceded by an effort to convince a requesting nonprofit to ask for a Python-based project. Nonprofits always come to SWOGC requesting a specific technology, almost always something from the PHP or Microsoft universes, simply because that's what they know about. Hmm, I wonder if it would be unethical to say, "For your project, we've used a special cutting-edge version of WordPress called 'Django'..."

A great weekend. Thanks to Andrew Douglas, my team leader, and to the GiveCamp organizers!

Columbus Python Workshop, Jan 18-19

Happy Ada Lovelace Day!

Announcing the first

Columbus Python Workshop

for women and their friends

Jan. 18-19, 2013

The Columbus Python Workshop for women and their friends is a free hands-on introduction to computer programming that's fun, accessible, and practical even to those who've never programmed at all before. We empower women of all ages and backgrounds to learn programming in a beginner-friendly environment.

Thanks to Pillar Technologies for hosting the workshop in their brand-new office in Columbus' Short North!

The workshop is the latest in a series based on the famous Boston Python Workshop; they've already introduced hundreds of beginners to programming in Boston, Indianapolis, Portland, Chicago, and Kansas City. Now it's Ohio's turn, so spread the word!

Get more details and sign up now: https://openhatch.org/wiki/Columbus_Python_Workshop

Traditionally, Ada Lovelace Day is celebrated by highlighting the achievements of present and past women in science and technology. Today, instead, I'm saluting the contributors of the future!

IPython Notebook tutorial with doctest feedback

I'm increasingly amazed at IPython Notebook, and want to use it for an interactive tutorial. I'd like the notebook to be full of exercises for the student to fill out, and to get feedback from the notebook itself - a lot like CodingBat but in IPython Notebook format.

Here's the code: ipython_doctester

Next step - maybe - would be to finish with a scorecard cell summarizing the student's overall progress.

And then... automatically push data on a student's progress up to a webserver on the instructor's machine? Which presents a dashboard showing her where everybody is and what everybody is struggling with...

Like every open-source author, I'm eager for feedback!

This dip into IPython Notebook reminds me of people who suggested years ago that I should integrate SQLPython into IPy. Indeed, writing a SQL-handling extension for IPy sounds really attractive... I wonder how many insomniac nights it would take...

doctesting just one function

There's no such function as doctest.testfunc. Or doctest.testclass, or, for that matter, doctest.testobj. However, you can run the doctests on a single function or class with doctest.run_docstring_examples, like so:

doctest.run_docstring_examples(myfunction, globals=globals())

"That's OK", I thought, "My function doesn't call any globals, so I'll just pass in an empty dictionary." Wrong. run_doctstring_examples actually needs your globals to find the object you're testing; you'll get

    NameError: name 'myfunction' is not defined

The docs say the function is "rarely useful", but I'm finding it very useful for an IPython Notebook-based interactive tutorial I'm hoping to work up shortly!

Indianapolis Python Workshop for women and their friends - the results

This April 13-14, Mel Chua, Jessica McKellar, and I ran the first annual Indianapolis Python Workshop for women and their friends... and I loved it!

We borrowed our curriculum (not to mention Jessica) from the Boston workshop, and the time they've put into fine-tuning it really shows. The participants varied from absolute first-time programmers to computer science graduate students, but the materials did a great job of not leaving anybody behind.

I chatted with a few of the participants about their backgrounds and motivations for coming, and I was fascinated by the diversity among them. One woman was re-entering the programming workforce after a decade away. One was an artist and designer of 3D games who wanted to learn to script her favorite gaming engine. One was the business manager for an IT firm who wanted a deeper understanding of the nuts and bolts of her own company. And, of course, several were students, from high school through graduate school.

We had roughly 20 participants, plus assistants from IndyPy, and the classroom dynamics were great. Usually, when running an IT event, I strain to convince the participants on to work together. Far too many programmers tend to struggle along in private silence, each at their own machine, which ruins the point of having a group learning event in the first place. Not our workshop students, though! They did a wonderful job of grouping up and helping each other out. I loved the sound of students teaching each other and laughing with each other. We had lots to teach and we worked them hard, but they met the challenge with enthusiasm and teamwork. I'm really looking forward to seeing them again at IndyPy's project night on May 8.

We have many thanks to give! Thanks to the Python Software Foundation and FewerHassles for their financial support; to DirectEmployers for their logistical help; to Indiana LinuxFest for providing a locale; and to the Boston Python Workshop for sending Jessica and all her experience. Mel and I are looking forward to keeping the ball rolling with more workshops in Indianapolis and elsewhere in the Midwest. Please contact <mpw-staff at lists.openhatch.org> if you'd like to help make it happen.

Indianapolis Python Workshop for women and their friends

Mel Chua, I, the team from the Boston Python Workshop, and IndyPy are all working together to bring about the Indianapolis Python Workshop for women and their friends in just two weeks!

There are several great things about this style of workshop:

- It's designed for beginners. No, really. I've taught what I thought of as "introductions" before, but like most non-beginners, I'm way too prone to zoom ahead and forget what it's really like for beginners. The BPW staff has worked hard to overcome this, and put together an amazing curriculum that really covers what beginners need in detail.

- The participants will be mostly (but not exclusively) women. There is an awkwardness about being the "only girl in the room" which, when heaped on top of garden-variety beginners' nervousness, can make people decide to just stay home... or make them nervous as heck if they do come. (There's nothing like feeling like the reputation of all women everywhere rest on your n00bish shoulders...) The quickest way to short-circuit that problem is to make sure that there are plenty of other girls in the room!

- It's very hands-on, concentrating on getting people using and enjoying code in a short time.

- It's designed to help participants plug immediately into using and enjoying their local developers' community, moving from the workshop into cooperative Project Nights with their local user group. In fact, this workshop takes place at Indiana LinuxFest, so their first exposure to the user community is right outside the door.

Please tell the Midwesterners you know. And if your company is excited about this, too, talk to me about sponsorship opportunities!

PyCon 2012

Best. PyCon. Ever.

And since PyCon is like my Christmas (in the 5-year-old sense: "I get new toys to play with!"), that's extremely high praise.

This isn't a proper retrospective - don't know if I'll have time to write one - but I just wanted to give thanks and glory to Jesse Noller, the entire volunteer staff (from whom I was notably absent), the proud ranks of the sponsors, and the awesome participants (the word is definitely "participant", not "attendee"). If you were wondering if PyCon's magic could scale, be reassured: the rainbows only proliferate.

PyOhio CFP should be out within a couple weeks, too.

SENDING TO EVERYONE. PASS IT ON.

I think I need to create a script that

1. Scans my gmail inbox for any mail whose subject line uses ALL CAPS and which urges me to forward it to everyone I know;
2. Searches snopes.com for the message body;
3. Replies with a link to the top Snopes hit and its text, plus a preachy little sermon by me on the importance of Truth

... all automatically.

What sort of tools can I use to make such a script?

If you don't know, please forward my question on to everyone you know. It's very important. It probably involves President Obama and/or dead babies.

slides from Saturday class

Hi, Python students from Saturday! Hope you enjoyed the class, and thanks for your patience as we struggled with wireless issues.

Here's the updated class materials I promised - if you can, install Visual Python on your machine and try out some of the suggestions given in the comments at the bottom of move.py and gravity.py.

I'm very fortunate to be able to go to PyCon this year, and I plan to come to the Dayton Dynamic Languages meeting the following Wednesday (March 14) bubbling over with stuff I learn there - I'd love to see you there! Bring whatever Python project you've been working on and we'll help push you over any bumps you've hit, or strategize on ideas for new projects.

Growth in PyCon sponsorship

When I teach Python, I usually include a slide or two about Python's importance and growth. At this point, that's begun to feel unnecessary - but I think I will include this illustration, because it's a neat one.

In 2004, I'd just started looking at Python. When I found out that PyCon would be just a few miles from my sister-in-law's house, I thought, "Why not?" - and I fell in love with the Python community forever. PyCon 2004 had nine sponsors.


This year, I barely squeaked my registration in before the blast doors slammed shut. Here are PyCon US 2012's sponsors.

Free Python class in Dayton

This Saturday (Feb 18, 2012), Sinclair Community College in Dayton will host Python: Programming is Fun Again, a two-hour introduction to the language.

It's a hands-on workshop, so bring a laptop if you can.

I'm teaching, so you can expect some planets to collide. However, this time, the aim is to help you get your own feet wet in actual programming. We'll start with simple 3-D graphical scenarios, and students will decide how to tweak and evolve the programs from there.

Contact Dr. Shirley Stallworth (shirley.stallworth@sinclair.edu) to register and for detailed directions and parking information.

Afterward, you can stop by TechFest, also running at Sinclair!

Pass the word along!

February 18, 2012 10 – 12pm
Python: Programming is Fun Again

Sinclair Community College, room 14130

444 W 3rd Street

Dayton OH 45402-1453 USA

A free, hands-on introduction to Python programming

Free Python class in Dayton

Tags: python class programming

This hCalendar event brought to you by the hCalendar Creator.

HTSQL-powered Flask

With seven lines of code, you can plug HTSQL into a web application platform like Flask. Yes, I know that you know SQLAlchemy already; the point is that then your Flask apps can support HTSQL's rich in-the-URL filtering language. (Also, it lets you skip the schema definition step for read-only apps.)

For instance, this app serves reports from HTSQL's sample "University" database. Each report can run as-is

  http://localhost:5000/hardcourses

or can accept arbitrary HTSQL filters

  http://localhost:5000/hardcourses?title~'ogy'

The advantage over plain HTSQL is that you can use full Flask power. In this case, the reports come through a template that includes functioning links in each row, so it's a drill-down report:

<td><a href="/departments?code='{{ row.department_code }}'">
        {{ row.dept }}</td>

Here are the magic seven lines:

htsql = HTSQL('pgsql:uni')   # customize me

def data(qry):
    filters = flask.request.url.replace(flask.request.base_url, '', 1)
    if filters and ('?' in qry):    # this can be fooled by a filtered subquery
        filters = '&%s' % filters.strip()[1:]
    qry = '%s%s' % (qry, filters or '')
    return htsql.produce(qry)

Now each of your report pages asks for data in the form of an HTSQL query, and the ``data`` function appends any user-supplied filters to that:

    return flask.render_template('departments.html', data=data(qry))

full code for the sample app

Movie Night at the user group

At Dayton Dynamic Languages last night, we watched a great video, "Git for Ages 4 and Up". ("WARNING: CHOKING HAZARD - Small Parts. Not For Children Under 4 Years.") We had a great time, and I learned a bunch of things that I literally put to use for work on the bus home that night.

So, what's the difference between watching a video at home and watching it at a user group meeting? The all-important pause key. Every couple minutes, one of us would say, "Wait, what?"; we'd pause and discuss. Discussion is always the best part of a user event; anytime I help organize an event where the attendees don't start talking to each other, I'm disappointed. DDL never has a problem with discussion, but the video acts as a great tool to help seed and drive the conversation.

Now I'm thinking that all sorts of groups may want to add an occasional Movie Night to their regular meeting schedule. There are plenty of great video sources like Python Miro Community, but watching with your friends adds enormously to the experience.

[Hi to all my friends at CodeMash! Sorry I'm not there... next year in Sanduskalem, right?]

MySQL Bizarro World

Getting used to MySQL has been a real challenge for me. Most everything I know about databases is backward in this MySQL world.

In the REAL WORLD,
table names are case-insensitive.

In MySQL WORLD,
table names are case-sensitive. Maybe. Depending on what platform you're running on.

in the REAL WORLD,
queries against VARCHAR data are case-sensitive.

in the MySQL WORLD,
queries against VARCHAR data are case-insensitive. (So is every other use of the data. Which can cause immeasurable pain w/r/t UNIQUE constraints if you weren't prepared for it.)

in the REAL WORLD,
database connections are expensive, and complex queries are optimized well. If you can connect to the database once and issue a single complex query, you'll get much better performance than if you make repeated connections and issue large numbers of simple queries.

in the MySQL WORLD,
database connections are cheap, and complex queries are optimized badly. If you make repeated connections and issue large numbers of simple queries, you'll get much better performance than if you connect to the database once and issue a single complex query.

I think the conclusion is obvious: MySQL was written by programmers from Superman's Bizarro World.

Incidentally, the Dayton Oracle User Group is planning a MySQL-themed meeting in the mid-term future. If you'd like to get involved - as an attendee or a speaker - let me know!

Hello!

HTSQL answers

HTSQL slides

Thanks to the great audiences at my HTSQL talks at Ohio Linuxfest and Dayton IEEE! (And to the folks who will come see me at Columbus Code Camp on Saturday.) I've promised you answers to some of your questions that stumped me, and (for OLF people) been criminally slow at getting them to you. So here you are! Some of the answers come from my own research, but I've also inserted quotes directly from the creator of HTSQL, Clark Evans... the embedded quotes are from Clark.

1. How do you restrict access via HTSQL?

http://htsql.org/doc/install.html#security

First, consider carefully which database user account you use to run htsql-ctl serve, and assign only the rights that user (representing your HTSQL users) should legitimately have.

Second, you can (and probably should) close down port 8080 (or whatever port you're serving HTSQL on) on your machine's firewall, and route all traffic through a webserver like Apache. (My HTRAF directions tell how to do that.) Then you can apply whatever authentication, IP limits, etc. you need at the webserver level.

If you need multiple groups to access your data with varying levels of permission, it's easy to run multiple instances of HTSQL as multiple
database users, route those instances through Apache, and restrict them at the Apache level appropriately.

This is a great answer. A few more items:

If the database is static (updated periodically), want to put varnish or something on the front. When you make another "data push" you could run though common queries to warm the cache. This is what we do for demo.htsql.org so that queries in our tutorial don't even hit the server.

For PostgreSQL, there is also a ``select timeout`` you can set using the "tweak.timeout" plugin, it can help a little bit with accidental denial of service. Basically, it cancels a query if your query runs over a particular number of seconds. If other databases have this ability, we could add a similar feature.

There is a also a ``autolimit`` that you can apply, this adds a LIMIT X to every query. In the current HTSQL implementation, all the results have to fit into memory: so, you can kill the backend process by creating a large result set. We'll fix this problem sometime next year... if you have a friendly audience, this generally isn't a problem. This plugin helps ensure users don't "accidently" create a big result though.

Even with these two enabled, you can still make queries that bring down either the HTSQL server (via memory exhaustion on big result) or the Database (via memory or cpu denial). So, some caution is advised if you give *direct* HTSQL access since you're letting arbitrary queries be created and such.

One solid way to handle this is separate the "trusted" users who need to create queries from "untrusted" users who are just running canned reports and dashboards. There's a "ssi" demo for doing this and we'll improve on it later. Basically, you have .htsql files server side with canned queries in them. You then limit users to only access .htsql saved queries. It'd be great to have this more automated... the demo code is just that: a proof of concept.

2. How can you paginate results?

You can request a "page" of results with HTSQL's limit() function. The optional second argument is an offset:

http://demo.htsql.org/course.limit(10)
http://demo.htsql.org/course.limit(10,10)
http://demo.htsql.org/course.limit(10,20)

There is a tweak.autolimit available to keep users from killing off their browsers with mistakenly broad queries. For example:

htsql-ctl -E tweak.autolimit:limit=10 serve pgsql://user:pwd@host/database

Users may also want to consider a browser like Chrome, where a runaway tab won't lock up the entire browser.

There's no way currently to have HTML results automatically insert "Next Page"-type links. Keep in mind that users aren't likely to genuinely want to page manually through very long result sets anyway; they'd probably be better off narrowing their queries rather than searching long lists by eyeball.

There are plans for HTRAF to generate automatically paginated tables at some point in the future.

3. Performance-wise, how does HTSQL respond under intense loads?

The best way to perform under intense loads is to not perform at all (see varnish above). The HTSQL server is also stateless: you can load balance as many copies as you need to meet demand.

As far as a single-process goes, the time spent converting HTSQL-to-SQL isn't really that significant compared to the query execution. The result handling isn't bad... although we've not done any load testing.

The SQL generation is probably the most important part. I think HTSQL generates quite clear/clean SQL, although your mileage may vary. To make any real judgments you need test queries and test data and profile it. Often times a 30 line SQL query that looks a bit ugly/repetitive will outperform a "hand-optimized" SQL query that is 12 lines.

We (Prometheus Research) [would] be delighted to help figure out why HTSQL performs badly if you have a specific query and test data set for us!

Anecdotally, for big (1-3 page) queries we have converted from SQL to HTSQL, the HTSQL equivalent is ~40% less source code and an order of magnitude more readable and maintainable. The SQL we then generate is typically bigger, sometimes 2x the size, but so far, the performance in the samples we have has been as-good-if-not-better than the original.

4. What about outer joins in HTSQL?

All HTSQL joins are LEFT OUTER joins - rows from the "driving" table are always included in the results, whether or not there are also records in the joined tables... it's a natural consequence of the driving table always determining the size of the result set. (If you specifically want to exclude rows that don't have counterparts in the joined tables, you can use ?exists(joined-table). More about that here.

Columbus Code Camp

I'll be speaking on HTSQL this Saturday at Columbus Code Camp. It was a great event last year, and I'm expecting even better this year. Check out the schedule - it's an excellent set of "I gotta see that!" topics.

Hope to see you there! (Unless you're at Southwest Ohio GiveCamp, of course - sorry I have to miss my GiveCamp friends this year.)

HTRAF setup, from zero

As I mentioned in my OLF talk, setting up HTSQL is crazy-easy.

HTRAF, however - the library that lets you do the gorgeous graphics - takes a few more steps. The documentation doesn't spell out the webserver-specific aspects of the setup, which may confuse you if you aren't an experienced webserver admin. So, here's my expanded version "getting started with HTRAF". My directions assume an Ubuntu machine.

1.

sudo easy_install htsql

It doesn't have to be on the same machine you use for HTRAF, just a machine that your HTRAF machine can contact.

2. Start up HTSQL:

htsql-ctl serve postgres://username:password@hostname/databasename

3. On your HTRAF machine, install a web server.

sudo apt-get install apache2

4. Get the HTRAF library. The simplest thing is to put it right under your Apache DocumentRoot:

sudo su - www-data
cd /var/www/
wget http://htsql.org/download/HTRAF-latest.zip
unzip HTRAF-latest.zip

5. You need to handle HTSQL requests via your Apache server. (If you try contacting the HTSQL server directly from your webpages, users' browsers are likely to block you, thinking that the site includes a cross-site scripting attack.)

So you'll need to change your Apache server configuration by adding ProxyPass and ProxyPassReverse directives. Apache configuration files are structured differently on different distributions; on mine, I used

gksudo gedit /etc/apache2/sites-enabled/000-default

to add

    ProxyPass /htsql/ http://localhost:8080/
    ProxyPassReverse /htsql/ http://localhost:8080/

just within the <VirtualHost *:80> directive.

6. Next, you need to enable mod_proxy on your Apache, so that it knows what to do with a ProxyPass.

cd /etc/apache2
sudo cp mods-available/proxy_*.* mods-enabled/

7. Now restart Apache so that the new settings will take effect.

sudo service apache2 restart

8. Test it out! Hit this with your web browser:
http://localhost:8080/htsql/a_table_from_your_database

9. Now write a webpage that includes HTRAF elements calling HTSQL! Here's a minimal example.

<html>
<head>
     <script type="text/javascript"
     src="HTRAF-2.0.0b1/htraf/htraf.js"
     data-htsql-version="2"
     data-htsql-prefix="/htsql">
     </script>
    <link rel="stylesheet" type="text/css" 
        href="HTRAF-2.0.0b1/htraf/htraf-02.css"/>
</head>
<body>
<select id="school"
    data-htsql="/school{code, name}?exists(department)">
    </select>
<h3>Departments</h3>
<table id="department" data-hide-column-0="yes"
    data-htsql="/department{code, name,
                     count(course) :as '%23 of courses'}
         ?school_code=$school&name~$department_name"
    data-ref="school department_name">
</table>
</body>
</html>

Save it as /var/www/minimal.html and view it at http://localhost/minimal.html.

8. Hey, that table looks bland! If you preferred the colors I showed in my talk, you can use this CSS, which I copied from HTRAF's demo a few months ago. Save it to /var/www/HTRAF-2.0.0b1/htraf/htraf-02.css and change the stylesheet in your <head> to

    <link rel="stylesheet" type="text/css" 
    href="HTRAF-2.0.0b1/htraf/htraf-02.css"/>

HTSQL slides posted

Thanks to my Ohio Linuxfest audience for your attention and interest! My slides from yesterday are posted:

Your Database, Exposed: HTSQL


Shortly, I'll also post a summary of the questions I was asked that I didn't have firm answers for.

As usual, I had great time at OLF. As usual, I brought my voice to its knees by talking to awesome people in the noisy exhibit hall before my talk... I'm going to ask for a morning speaking slot next time I speak! Thanks, everybody!

Ohio LinuxFest

I feel bad for not blogging after PyOhio. I just have trouble finding words. Along with PyCon, it's a sort of family reunion for me.

But anyway - next conference: Ohio LinuxFest. This weekend, so sign up now now now move move move - I think today is the pre-reg deadline. It's always a great event, draws people from all over the East and sometimes further. Look for our PyOhio table to have a Python chat (or help staff the table, and introduce other attendees to Python joy). OLF is one of the best places in the region for midway mingling.

I'm speaking on HTSQL. I spoke on it at Indiana LinuxFest in the spring, too, only this time it follows several months of using it seriously at work. The experience has only made me more enthusiastic about HTSQL. Check it out... there's still time to be an early adopter and sneer at everybody else after it becomes famous.

See you in Columbus! Register now!

MSSQL to CSV

Searching for ways to dump CSV from a MS SQL Server brings up recommendations to buy various third-party tools.

Excuse me? Buy tools for something any Pythonista can do in seventeen lines?

You'll need the pyodbc module, which you should have anyway because it rocks.

import csv

import pyodbc



cnxn = pyodbc.connect(

       '''DRIVER={SQL Server};

          SERVER=localhost;

          DATABASE=mydb;

          UID=myname;

          PWD=mypwd''')

curs = cnxn.cursor()



def write_table(tblname):

    with open(tblname+'.csv', 'wb') as outfile:

        writer = csv.writer(outfile)

        curs.execute('SELECT * FROM %s' % tblname)

        writer.writerows(curs.fetchall())



curs.execute('SELECT name FROM sys.tables')

table_names = curs.fetchall()

for table_name in table_names:

    write_table(table_name.name)

Go on, get more sophisticated with the hardcoded connect string, etc.

AND A NOTE: If the ultimate destination of your .csv is to be imported into another database, you'd better distinguish between empty strings and NULLS. To do that, replace

        writer.writerows(curs.fetchall())

with

        for row in curs:

            writer.writerow(['NULL' if r is None else r 

                             for r in row])

AND, if you end up importing this .csv into MySQL, you'll want to set ESCAPED BY '' in your LOAD DATA statement, or else backslashes will start mucking up your field boundaries. (Thanks to Shannon -jj Behrens for saving my sanity on that one). Here's my script to consume the files:

for fn in `ls *.csv`

do

  tbl=${fn%.*}

  mysql myinst -e  "SET FOREIGN_KEY_CHECKS=0; 

                    DELETE FROM $tbl; 

                    LOAD DATA LOCAL INFILE '$fn' 

                      INTO TABLE $tbl 

                      COLUMNS TERMINATED BY ',' 

                      OPTIONALLY ENCLOSED BY '\"' 

                      ESCAPED BY '';"

done