%load_ext sql

%sql postgresql:///decibel

%sql SELECT now()

import psycopg2
db = psycopg2.connect('')

db

%sql SET application_name = 'ipython-sql'

db.cursor().execute("SET application_name = 'psycopg2'")
db.commit()

db.cursor().execute('CREATE TEMP TABLE i(i int)')

%sql SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity

"""
Explanation: This notebook demonstrates two methods for connecting to Postgres.
The first is iPython-sql a "magic" for Jupyter Notebook (what this document is written in).
The second is Psycopg, which is the fundamental Postgres connector for Python.
End of explanation
"""


%%sql
ROLLBACK;
SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity;

"""
Explanation: Because the sql magic uses SQLalchemy under the hood, it can end up in a transaction, which means you might get stale data. If that happens, you want to issue either a ROLLBACK; or COMMIT;.
End of explanation
"""


%sql SELECT * FROM pg_tables WHERE tableowner='decibel'

db.commit()

%sql SELECT * FROM pg_tables WHERE tableowner='decibel'

c = db.cursor()

c.execute("SELECT * FROM pg_tables LIMIT 5")

r = c.fetchone()

r

for result in c: # Note that c is our cursor
    print result

SQLactivity="SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity"
c.execute(SQLactivity)

c.fetchmany(2)

c.statusmessage

c.rownumber

c.rowcount

len(c.fetchall())

"""
Explanation: %sql does use it's own, separate connection though, so it can't see anything that's not yet committed.
End of explanation
"""


r = %sql SELECT schemaname,count(*) FROM pg_tables GROUP BY schemaname

"""
Explanation: %sql can return a result object
End of explanation
"""


import pandas as pd

df = r.DataFrame()

df

r

"""
Explanation: We can haz DataFrames
End of explanation
"""


%matplotlib inline

r.pie()

"""
Explanation: and plots/graphs
End of explanation
"""


%sql PERSIST df

%sql SELECT * FROM df

r

"""
Explanation: %sql also makes it easy to create new tables (but CREATE TABLE ... AS SELECT ... would be far more efficmient)
End of explanation
"""


%sql PERSIST r

c.execute('SELECT * FROM df')
df_psy_result = c.fetchall()
df_psy_result

type(df_psy_result)

type(df_psy_result[0])

c.description

import psycopg2.extras
c2 = db.cursor(cursor_factory=psycopg2.extras.DictCursor)
c2.execute('SELECT * FROM df')
c2_df_result = c2.fetchall()
c2_df_result

type(c2_df_result[0])

row = c2_df_result[0]

row.keys()

row

row[0]

row['index']

row['index'] is row[0]

c2.execute('UPDATE df SET index=index*10')

c2_df_result

c2.fetchall()

c2.execute('SELECT * FROM df')

c2.fetchall()

"""
Explanation: You can only persist a DataFrame though...
End of explanation
"""


db.autocommit

db.rollback()
c.execute('UPDATE df SET index=index+10')
c2.fetchall()

"""
Explanation: What if that's not what I wanted to do?
End of explanation
"""


c2.execute('SELECT * FROM df')
c2.fetchall()

"""
Explanation: Why didn't that fetchall() bomb like the other one?
End of explanation
"""


%sql SELECT * FROM df

c2.commit()

c2.connection.commit()
c2.connection is db

%sql SELECT * FROM df

"""
Explanation: %sql can't see these updates yet though...
End of explanation
"""


c2_df_result

"""
Explanation: c2_df_result is unchanged though...
End of explanation
"""


c.execute(SQLactivity)
pd.DataFrame(c.fetchall())

"""
Explanation: We can easily turn tuple results into a dataframe, but the dataframe won't have any names.
End of explanation
"""


c2.execute(SQLactivity)
pd.DataFrame([row for row in c2])

c2.execute(SQLactivity)
pd.DataFrame([dict(row) for row in c2])

new_df=pd.read_sql(SQLactivity, db)
new_df

%sql SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity

c2_df_result

%sql SELECT * FROM df

# Lets create a new table...
c2.execute('CREATE TABLE df2(s serial, index int, schemaname name, count bigint)')

# And insert a row into it
SQLdf2_insert='INSERT INTO df2 VALUES(DEFAULT, %(index)s, %(schemaname)s, %(count)s)'
c2.execute(SQLdf2_insert, c2_df_result[0])

%sql SELECT * FROM df2

db.commit()

%sql SELECT * FROM df2

c.executemany(SQLdf2_insert, c2_df_result) # Note executemany(), not just execute()
pd.read_sql('SELECT * FROM df2', db)

c.execute('SELECT * FROM df2')
c.fetchall()

c3=db.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)
c3.execute('SELECT * FROM df2')
r=c3.fetchall()
r

r[0]

r[0][0]

r[0].s

r[0][1] is r[0].index

c3.execute(SQLdf2_insert + ' RETURNING *', r[0]._asdict())
c3.fetchall()

%sql SELECT * FROM df2

with db:
    c3.execute(SQLdf2_insert + ' RETURNING *', r[0]._asdict())
    r=c3.fetchall()

r

%sql SELECT * FROM df2

c.execute('BAD SQL')

c.execute('SELECT 1').fetchall()

db.rollback()

with db:
    c.executemany(SQLdf2_insert, c2_df_result)
    c.execute('BAD SQL')
    c.execute('SELECT 1')
    c.fetchall()

c.execute('SELECT 1')
c.fetchall()

with db.cursor() as new_c:
    new_c.execute('SELECT * FROM df2')
    r=new_c.fetchall()

r

new_c

new_c.execute('SELECT 1')

"""
Explanation: Results from c2 would have names associated with them, but...
End of explanation
"""


c.execute("CREATE TYPE card AS (value text, suit text);")

c.execute("SELECT ('Ace', 'Spades')::card")
r=c.fetchone()[0]
r

type(r)

psycopg2.extras.register_composite('card', c)
c.execute("SELECT ('Ace', 'Spades')::card")
r=c.fetchone()[0]
r

type(r)

%sql CREATE TYPE complex AS(r float, i float)

c3.execute('SELECT row(.1,.2)::complex')
r=c3.fetchone()
r

type(r)

type(r.row)

z=complex(2,3)
z

c2.execute('SELECT %s',(z,))
r=c2.fetchone()
r

import psycopg2.extensions as exten

def adapt_complex(c):
    return exten.AsIs("(%s,%s)" % (c.real, c.imag))

exten.register_adapter(complex,adapt_complex)

c.execute('CREATE TABLE c AS SELECT %s::complex AS c', (z,))

c.execute('INSERT INTO c VALUES(%s),(%s)',(z * 2, z.conjugate()))
c2.execute('SELECT * FROM c')
r=c2.fetchall()
db.commit()
r

type(r[0][0])

%sql SELECT * FROM c

%sql SELECT (c).* FROM c

import re
def cast_complex(value, cursor):
    if value is None:
        return None
    
    # Match a value that looks like (foo,bar)
    m = re.match(r"\(([^)]+),([^)]+)\)", value)
    if m:
        return complex(float(m.group(1)),float(m.group(2)))
    else:
        raise InterfaceError("bad representation for type complex: %r" % value)

c2.description[0]

c2.description[0].type_code

complex_caster = exten.new_type((c2.description[0].type_code,), 'complex', cast_complex)

complex_caster

exten.register_type(complex_caster)

c2.execute('SELECT * FROM c')
r=c2.fetchall()
r

type(r[0][0])

%sql SELECT * FROM c

"""
Explanation: psycopg2 supports non-standard types
End of explanation
"""


