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.
In [1]:
%load_ext sql
In [2]:
%sql postgresql:///decibel
Out[2]:
In [3]:
%sql SELECT now()
Out[3]:
In [4]:
import psycopg2
db = psycopg2.connect('')
In [5]:
db
Out[5]:
In [6]:
%sql SET application_name = 'ipython-sql'
Out[6]:
In [7]:
db.cursor().execute("SET application_name = 'psycopg2'")
db.commit()
In [8]:
db.cursor().execute('CREATE TEMP TABLE i(i int)')
In [9]:
%sql SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity
Out[9]:
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;.
In [10]:
%%sql
ROLLBACK;
SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity;
Out[10]:
%sql does use it's own, separate connection though, so it can't see anything that's not yet committed.
In [11]:
%sql SELECT * FROM pg_tables WHERE tableowner='decibel'
Out[11]:
In [12]:
db.commit()
In [13]:
%sql SELECT * FROM pg_tables WHERE tableowner='decibel'
Out[13]:
In [14]:
c = db.cursor()
In [15]:
c.execute("SELECT * FROM pg_tables LIMIT 5")
In [16]:
r = c.fetchone()
In [17]:
r
Out[17]:
In [18]:
for result in c: # Note that c is our cursor
print result
In [19]:
SQLactivity="SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity"
c.execute(SQLactivity)
In [20]:
c.fetchmany(2)
Out[20]:
In [21]:
c.statusmessage
Out[21]:
In [22]:
c.rownumber
Out[22]:
In [23]:
c.rowcount
Out[23]:
In [24]:
len(c.fetchall())
Out[24]:
%sql can return a result object
In [25]:
r = %sql SELECT schemaname,count(*) FROM pg_tables GROUP BY schemaname
We can haz DataFrames
In [29]:
import pandas as pd
df = r.DataFrame()
df
Out[29]:
In [30]:
r
Out[30]:
and plots/graphs
In [33]:
%matplotlib inline
r.pie()
Out[33]:
%sql also makes it easy to create new tables (but CREATE TABLE ... AS SELECT ... would be far more efficmient)
In [34]:
%sql PERSIST df
Out[34]:
In [35]:
%sql SELECT * FROM df
Out[35]:
In [36]:
r
Out[36]:
You can only persist a DataFrame though...
In [37]:
%sql PERSIST r
In [38]:
c.execute('SELECT * FROM df')
df_psy_result = c.fetchall()
df_psy_result
Out[38]:
In [39]:
type(df_psy_result)
Out[39]:
In [40]:
type(df_psy_result[0])
Out[40]:
In [41]:
c.description
Out[41]:
In [42]:
import psycopg2.extras
c2 = db.cursor(cursor_factory=psycopg2.extras.DictCursor)
c2.execute('SELECT * FROM df')
c2_df_result = c2.fetchall()
c2_df_result
Out[42]:
In [43]:
type(c2_df_result[0])
Out[43]:
In [44]:
row = c2_df_result[0]
In [45]:
row.keys()
Out[45]:
In [46]:
row
Out[46]:
In [47]:
row[0]
Out[47]:
In [48]:
row['index']
Out[48]:
In [49]:
row['index'] is row[0]
Out[49]:
In [50]:
c2.execute('UPDATE df SET index=index*10')
In [51]:
c2_df_result
Out[51]:
In [52]:
c2.fetchall()
In [53]:
c2.execute('SELECT * FROM df')
In [54]:
c2.fetchall()
Out[54]:
What if that's not what I wanted to do?
In [55]:
db.autocommit
Out[55]:
In [56]:
db.rollback()
c.execute('UPDATE df SET index=index+10')
c2.fetchall()
Out[56]:
Why didn't that fetchall() bomb like the other one?
In [57]:
c2.execute('SELECT * FROM df')
c2.fetchall()
Out[57]:
%sql can't see these updates yet though...
In [58]:
%sql SELECT * FROM df
Out[58]:
In [59]:
c2.commit()
In [60]:
c2.connection.commit()
c2.connection is db
Out[60]:
In [61]:
%sql SELECT * FROM df
Out[61]:
c2_df_result is unchanged though...
In [62]:
c2_df_result
Out[62]:
We can easily turn tuple results into a dataframe, but the dataframe won't have any names.
In [63]:
c.execute(SQLactivity)
pd.DataFrame(c.fetchall())
Out[63]:
Results from c2 would have names associated with them, but...
In [64]:
c2.execute(SQLactivity)
pd.DataFrame([row for row in c2])
In [65]:
c2.execute(SQLactivity)
pd.DataFrame([dict(row) for row in c2])
Out[65]:
In [66]:
new_df=pd.read_sql(SQLactivity, db)
new_df
Out[66]:
In [67]:
%sql SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity
Out[67]:
In [68]:
c2_df_result
Out[68]:
In [69]:
%sql SELECT * FROM df
Out[69]:
In [70]:
# 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])
In [71]:
%sql SELECT * FROM df2
In [72]:
db.commit()
In [73]:
%sql SELECT * FROM df2
Out[73]:
In [74]:
c.executemany(SQLdf2_insert, c2_df_result) # Note executemany(), not just execute()
pd.read_sql('SELECT * FROM df2', db)
Out[74]:
In [75]:
c.execute('SELECT * FROM df2')
c.fetchall()
Out[75]:
In [76]:
c3=db.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)
c3.execute('SELECT * FROM df2')
r=c3.fetchall()
r
Out[76]:
In [77]:
r[0]
Out[77]:
In [78]:
r[0][0]
Out[78]:
In [79]:
r[0].s
Out[79]:
In [80]:
r[0][1] is r[0].index
Out[80]:
In [81]:
c3.execute(SQLdf2_insert + ' RETURNING *', r[0]._asdict())
c3.fetchall()
Out[81]:
In [82]:
%sql SELECT * FROM df2
Out[82]:
In [83]:
with db:
c3.execute(SQLdf2_insert + ' RETURNING *', r[0]._asdict())
r=c3.fetchall()
In [84]:
r
Out[84]:
In [85]:
%sql SELECT * FROM df2
Out[85]:
In [86]:
c.execute('BAD SQL')
In [87]:
c.execute('SELECT 1').fetchall()
In [88]:
db.rollback()
In [89]:
with db:
c.executemany(SQLdf2_insert, c2_df_result)
c.execute('BAD SQL')
c.execute('SELECT 1')
c.fetchall()
In [90]:
c.execute('SELECT 1')
c.fetchall()
Out[90]:
In [91]:
with db.cursor() as new_c:
new_c.execute('SELECT * FROM df2')
r=new_c.fetchall()
In [92]:
r
Out[92]:
In [93]:
new_c
Out[93]:
In [94]:
new_c.execute('SELECT 1')
psycopg2 supports non-standard types
In [95]:
c.execute("CREATE TYPE card AS (value text, suit text);")
In [96]:
c.execute("SELECT ('Ace', 'Spades')::card")
r=c.fetchone()[0]
r
Out[96]:
In [97]:
type(r)
Out[97]:
In [98]:
psycopg2.extras.register_composite('card', c)
c.execute("SELECT ('Ace', 'Spades')::card")
r=c.fetchone()[0]
r
Out[98]:
In [99]:
type(r)
Out[99]:
In [100]:
%sql CREATE TYPE complex AS(r float, i float)
Out[100]:
In [101]:
c3.execute('SELECT row(.1,.2)::complex')
r=c3.fetchone()
r
Out[101]:
In [102]:
type(r)
Out[102]:
In [103]:
type(r.row)
Out[103]:
In [104]:
z=complex(2,3)
z
Out[104]:
In [105]:
c2.execute('SELECT %s',(z,))
r=c2.fetchone()
r
In [106]:
import psycopg2.extensions as exten
In [107]:
def adapt_complex(c):
return exten.AsIs("(%s,%s)" % (c.real, c.imag))
In [108]:
exten.register_adapter(complex,adapt_complex)
In [109]:
c.execute('CREATE TABLE c AS SELECT %s::complex AS c', (z,))
In [110]:
c.execute('INSERT INTO c VALUES(%s),(%s)',(z * 2, z.conjugate()))
c2.execute('SELECT * FROM c')
r=c2.fetchall()
db.commit()
r
Out[110]:
In [111]:
type(r[0][0])
Out[111]:
In [112]:
%sql SELECT * FROM c
Out[112]:
In [113]:
%sql SELECT (c).* FROM c
Out[113]:
In [116]:
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)
In [117]:
c2.description[0]
Out[117]:
In [118]:
c2.description[0].type_code
Out[118]:
In [119]:
complex_caster = exten.new_type((c2.description[0].type_code,), 'complex', cast_complex)
In [120]:
complex_caster
Out[120]:
In [121]:
exten.register_type(complex_caster)
In [122]:
c2.execute('SELECT * FROM c')
r=c2.fetchall()
r
Out[122]:
In [123]:
type(r[0][0])
Out[123]:
In [124]:
%sql SELECT * FROM c
Out[124]:
In [ ]: