!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
"""


