<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Catherine Devlin
RDBMS were so last year...
last year
SQLAlchemy
~~object-relational mapper~~
RDBMS toolbox
ddlgenerator
Import data and define tables
Step1:  ipython_sql
Access the data
Step2:  rdbms-subsetter
Get a consistent test database.
<ASSISTANT_TASK:>
Python Code:
!head data/provinces.yaml
!ddlgenerator -i -t postgresql data/provinces.yaml | head -20
# !ddlgenerator -i -t postgresql http://github.com/catherinedevlin/pycon2015_sqla_lightning/data/provinces.yaml
!dropdb pycon
!createdb pycon
!ddlgenerator -i postgresql data/provinces.yaml | psql pycon | head -20
Explanation: Catherine Devlin
RDBMS were so last year...
last year
SQLAlchemy
~~object-relational mapper~~
RDBMS toolbox
ddlgenerator
Import data and define tables
End of explanation
%load_ext sql
%sql postgresql://:@/pycon
%sql SELECT name, bird FROM provinces ORDER BY name
%%sql 
SELECT c.name || ', ' || p.name AS name,    
       c.population                         
FROM   cities c                             
JOIN   provinces p                          
ON    (c.provinces_id = p.provinces_id)     
ORDER BY c.population DESC                  
LIMIT 10
cities = _
cities.DataFrame().plot(kind='bar', x='name')
Explanation: ipython_sql
Access the data
End of explanation
!dropdb pycon_test
!createdb pycon_test
!pg_dump --schema-only pycon | psql pycon_test
!rdbms-subsetter -y postgresql://:@/pycon postgresql://:@/pycon_test 0.1
%%sql postgresql://:@/pycon_test
SELECT c.name || ', ' || p.name AS name,    
       c.population                         
FROM   cities c                             
JOIN   provinces p                          
ON    (c.provinces_id = p.provinces_id)     
ORDER BY c.population DESC   
Explanation: rdbms-subsetter
Get a consistent test database.
End of explanation
<END_TASK>