Wednesday, December 2, 2015

calling programs/REST URLs from Postgres

Postgres already has good JSON support. It can copy to/from external files, which is great for easily exporting a table or three, or importing data from a CSV file.  As of 9.3, it can copy from a program or URL!

Example:

# CREATE TABLE worldbank_json (data json);
CREATE TABLE


# COPY worldbank_json FROM PROGRAM 'curl "http://api.worldbank.org/countries?format=json&per_page=100&page=1"';
COPY 1
# COPY worldbank_json FROM PROGRAM 'curl "http://api.worldbank.org/countries?format=json&per_page=100&page=2"';
COPY 1
# COPY worldbank_json FROM PROGRAM 'curl "http://api.worldbank.org/countries?format=json&per_page=100&page=3"';
COPY 1

WITH je AS (
            SELECT json_array_elements(data->1) AS jd FROM  worldbank_json
)
SELECT jd->>'id' AS id, jd->>'name' As country, 
jd#>>'{adminregion,id}' As region_id
FROM je limit 20;

 id  |       country        | region_id
-----+----------------------+-----------
 ABW | Aruba                |
 AFG | Afghanistan          | SAS
 AFR | Africa               |
 AGO | Angola               | SSA
 ALB | Albania              | ECA
 AND | Andorra              |
 ANR | Andean Region        |
 ARB | Arab World           |
 ARE | United Arab Emirates |
 ARG | Argentina            |
 ARM | Armenia              | ECA
 ASM | American Samoa       | EAP
 ATG | Antigua and Barbuda  |
 AUS | Australia            |
 AUT | Austria              |
 AZE | Azerbaijan           | ECA
 BDI | Burundi              | SSA
 BEL | Belgium              |
 BEN | Benin                | SSA
 BFA | Burkina Faso         | SSA
 (20 rows)

Rows marked with "#" are typed into the Postgres PSQL prompt.

Unsurprisingly, there are limitations:
- you must be superuser

Borrowed from http://www.postgresonline.com/journal/archives/325-Using-wget-directly-from-PostgreSQL-using-COPY-FROM-PROGRAM.html

Friday, October 2, 2015

Learning Python Programming

I give a number of talks on Python and other subjects. The two universal questions I get are:

- how can I... learn Python? get better at Python? learn programming?

- which web framework should I use?

There's a number of resources for learning programming with Python. Here are my favorites:

- Girl Develop It https://www.girldevelopit.com/

This (USA) national organization helps train women (and men) in learning web and software development. In the Los Angeles area they have classes nearly every weekend. My girlfriend took the "CSS" and "Javascript" classes and found them rewarding.

As an experienced dev who wants teaching experience, the organization itself is pretty easy to get in to. You can be a TA (teacher assistant) or work up to being a Teacher.  You can be paid or donate your salary to the national nonprofit.

I've taught a number of people, and found working with beginners to be humbling. They always ask insightful questions. As a teacher, to be clear and patient is a wonderful skill.

- MakerSquare http://www.makersquare.com/

This organization has an intensive "boot camp" in which you work, full time, for three months on training up to become a professional Javascript programmer. They also have "keep your day job" night class sessions.  Reportedly 96% of graduates get a professional Programming job in three months! Locations in Austin, San Francisco, and Los Angeles.

- Hack Night at Carbon Five http://www.meetup.com/Hack-Night-at-Carbon-Five-LA/

It's great fun to bring your laptop and projects to meet other geeks. This event is super chill and it's easy to talk to people and learn new things!

- Learn to Code with Us http://www.learntocodewithus.com/

I've been to the Culver City location (at Crash Space, a great hacker space), and it was a lot of fun. Lots of people work on projects. Michelle is very quiet and open, doing one-on-ones with people to help install Python or other software, or she'll help with general programming questions.


Friday, August 28, 2015

TIP: language-aware Git gives you contextual searches

TIP: if Git is setup correctly, Git knows we're working with Python, so commands are clearer.

setup: echo '*.py filter=python' >> .gitattributes

Example: find the usage/definition of formatted_tax:

$ git grep -p formatted_tax

app/models.py=2083=class Order(CreatedMixin):
app/models.py:2454:            'tax': self.formatted_tax,

The second line says the symbol was found on line 2454.  The first line shows the context, the symbol was found inside the class Order! Very useful

related: 'git grep' and Language-Aware Diffs

slides: Practical Python Testing

The talk at The Black Tux last night went really well!  Here are the slides:

- Practical Python Testing (google docs)

and me:


Sunday, August 23, 2015

Django: speed up Sqlite 1000x!

I'm working on a project analyzing large code bases. For just messing around, I'm using Sqlite. A strange thing happened when updating ~10,000 rows: they were really slow! Sqlite was updating about 10 records a second.

It turns out Sqlite correctly cares about your data, so it makes a transaction around the UPDATE statement, verifying that each and every bit of your data hits disk before returning. For me, if disaster strikes and I lose my tiny database I can recreated in a few seconds, so I decided to live life dangerously, and tell Sqlite to go ahead and try to update data, but don't wait around for it to hit disk.

Here's the magic. Put this before your bulk INSERT/UPDATE statements. Note that it affects the entire session, so you don't want to do this before your valuable data-manipulation commands.

from django.db import connection
if connection.vendor == 'sqlite':
    connection.cursor().execute('PRAGMA synchronous=OFF')

Three commands sped up my program a zillion percent. The entire 10K rows updated in a fraction of a second, vs minutes. Yay!

talk: Practical Python Testing

I'm speaking at this month's SoCal Python Meetup!  I'll post slides and notes here soon.

http://www.meetup.com/socalpython/events/224586741/

Thursday, August 13, 2015

How and why we use DevOps checklists - Server Density Blog

In the health care and airline industries, simple checklists save thousands of lives.  Here are several clear examples how the same technique is used in DevOps:



How and why we use DevOps checklists - Server Density Blog