Python+Postgres


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.


In [1]:
%load_ext sql


/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/IPython/utils/traitlets.py:5: UserWarning: IPython.utils.traitlets has moved to a top-level traitlets package.
  warn("IPython.utils.traitlets has moved to a top-level traitlets package.")

In [2]:
%sql postgresql:///decibel


Out[2]:
u'Connected: None@decibel'

In [3]:
%sql SELECT now()


1 rows affected.
Out[3]:
now
2016-11-12 01:09:03.282118+00:00

In [4]:
import psycopg2
db = psycopg2.connect('')

In [5]:
db


Out[5]:
<connection object at 0x1087ef8a0; dsn: '', closed: 0>

In [6]:
%sql SET application_name = 'ipython-sql'


Done.
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


4 rows affected.
Out[9]:
pid datname usename application_name state_change state query
46931 decibel decibel psql 2016-11-12 01:08:56.806783+00:00 idle SELECT pg_catalog.pg_backend_pid() AS backend_pid
46976 decibel decibel ipython-sql 2016-11-12 01:10:54.847484+00:00 active SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity
47259 decibel decibel psycopg2 2016-11-12 01:10:53.172094+00:00 idle in transaction CREATE TEMP TABLE i(i int)
47620 catalog postgres None None

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;


Done.
3 rows affected.
Out[10]:
pid datname usename application_name state_change state query
46931 decibel decibel psql 2016-11-12 01:08:56.806783+00:00 idle SELECT pg_catalog.pg_backend_pid() AS backend_pid
46976 decibel decibel 2016-11-12 01:11:17.722069+00:00 active SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity;
47259 decibel decibel psycopg2 2016-11-12 01:10:53.172094+00:00 idle in transaction CREATE TEMP TABLE i(i int)

%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'


0 rows affected.
Out[11]:
schemaname tablename tableowner tablespace hasindexes hasrules hastriggers rowsecurity

In [12]:
db.commit()

In [13]:
%sql SELECT * FROM pg_tables WHERE tableowner='decibel'


1 rows affected.
Out[13]:
schemaname tablename tableowner tablespace hasindexes hasrules hastriggers rowsecurity
pg_temp_4 i decibel None False False False False

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]:
('pg_temp_4', 'i', 'decibel', None, False, False, False, False)

In [18]:
for result in c: # Note that c is our cursor
    print result


('pg_catalog', 'pg_statistic', 'postgres', None, True, False, False, False)
('pg_catalog', 'pg_type', 'postgres', None, True, False, False, False)
('pg_catalog', 'pg_authid', 'postgres', 'pg_global', True, False, False, False)
('pg_catalog', 'pg_user_mapping', 'postgres', None, True, False, False, False)

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]:
[(46931,
  'decibel',
  'decibel',
  'psql',
  datetime.datetime(2016, 11, 12, 1, 8, 56, 806783, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),
  'idle',
  'SELECT pg_catalog.pg_backend_pid() AS backend_pid '),
 (46976,
  'decibel',
  'decibel',
  '',
  datetime.datetime(2016, 11, 12, 1, 11, 28, 104789, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),
  'idle',
  "SELECT * FROM pg_tables WHERE tableowner='decibel'")]

In [21]:
c.statusmessage


Out[21]:
'SELECT 3'

In [22]:
c.rownumber


Out[22]:
2

In [23]:
c.rowcount


Out[23]:
3

In [24]:
len(c.fetchall())


Out[24]:
1

%sql can return a result object


In [25]:
r = %sql SELECT schemaname,count(*) FROM pg_tables GROUP BY schemaname


3 rows affected.

We can haz DataFrames


In [29]:
import pandas as pd

df = r.DataFrame()

df


Out[29]:
schemaname count
0 information_schema 7
1 pg_catalog 54
2 pg_temp_4 1

In [30]:
r


Out[30]:
schemaname count
information_schema 7
pg_catalog 54
pg_temp_4 1

and plots/graphs


In [33]:
%matplotlib inline

r.pie()


Out[33]:
([<matplotlib.patches.Wedge at 0x1095af7d0>,
  <matplotlib.patches.Wedge at 0x1095bc210>,
  <matplotlib.patches.Wedge at 0x1095bcb90>],
 [<matplotlib.text.Text at 0x1095afdd0>,
  <matplotlib.text.Text at 0x1095bc810>,
  <matplotlib.text.Text at 0x1095c81d0>])

%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]:
u'Persisted df'

In [35]:
%sql SELECT * FROM df


3 rows affected.
Out[35]:
index schemaname count
0 information_schema 7
1 pg_catalog 54
2 pg_temp_4 1

In [36]:
r


Out[36]:
schemaname count
information_schema 7
pg_catalog 54
pg_temp_4 1

You can only persist a DataFrame though...


In [37]:
%sql PERSIST r


---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-37-9106d7b141b4> in <module>()
----> 1 get_ipython().magic(u'sql PERSIST r')

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/IPython/core/interactiveshell.pyc in magic(self, arg_s)
   2161         magic_name, _, magic_arg_s = arg_s.partition(' ')
   2162         magic_name = magic_name.lstrip(prefilter.ESC_MAGIC)
-> 2163         return self.run_line_magic(magic_name, magic_arg_s)
   2164 
   2165     #-------------------------------------------------------------------------

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/IPython/core/interactiveshell.pyc in run_line_magic(self, magic_name, line)
   2082                 kwargs['local_ns'] = sys._getframe(stack_depth).f_locals
   2083             with self.builtin_trap:
-> 2084                 result = fn(*args,**kwargs)
   2085             return result
   2086 

<decorator-gen-130> in execute(self, line, cell, local_ns)

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/IPython/core/magic.pyc in <lambda>(f, *a, **k)
    191     # but it's overkill for just that one bit of state.
    192     def magic_deco(arg):
--> 193         call = lambda f, *a, **k: f(*a, **k)
    194 
    195         if callable(arg):

<decorator-gen-129> in execute(self, line, cell, local_ns)

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/IPython/core/magic.pyc in <lambda>(f, *a, **k)
    191     # but it's overkill for just that one bit of state.
    192     def magic_deco(arg):
--> 193         call = lambda f, *a, **k: f(*a, **k)
    194 
    195         if callable(arg):

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sql/magic.pyc in execute(self, line, cell, local_ns)
     76         first_word = parsed['sql'].split(None, 1)[:1]
     77         if first_word and first_word[0].lower() == 'persist':
---> 78             return self._persist_dataframe(parsed['sql'], conn, user_ns)
     79         try:
     80             result = sql.run.run(conn, parsed['sql'], self, user_ns)

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sql/magic.pyc in _persist_dataframe(self, raw, conn, user_ns)
     97         frame = eval(frame_name, user_ns)
     98         if not isinstance(frame, DataFrame) and not isinstance(frame, Series):
---> 99             raise TypeError('%s is not a Pandas DataFrame or Series' % frame_name)
    100         table_name = frame_name.lower()
    101         table_name = self.legal_sql_identifier.search(table_name).group(0)

TypeError: r is not a Pandas DataFrame or Series

In [38]:
c.execute('SELECT * FROM df')
df_psy_result = c.fetchall()
df_psy_result


Out[38]:
[(0L, 'information_schema', 7L),
 (1L, 'pg_catalog', 54L),
 (2L, 'pg_temp_4', 1L)]

In [39]:
type(df_psy_result)


Out[39]:
list

In [40]:
type(df_psy_result[0])


Out[40]:
tuple

In [41]:
c.description


Out[41]:
(Column(name='index', type_code=20, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None),
 Column(name='schemaname', type_code=25, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None),
 Column(name='count', type_code=20, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None))

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]:
[[0L, 'information_schema', 7L],
 [1L, 'pg_catalog', 54L],
 [2L, 'pg_temp_4', 1L]]

In [43]:
type(c2_df_result[0])


Out[43]:
psycopg2.extras.DictRow

In [44]:
row = c2_df_result[0]

In [45]:
row.keys()


Out[45]:
['count', 'index', 'schemaname']

In [46]:
row


Out[46]:
[0L, 'information_schema', 7L]

In [47]:
row[0]


Out[47]:
0L

In [48]:
row['index']


Out[48]:
0L

In [49]:
row['index'] is row[0]


Out[49]:
True

In [50]:
c2.execute('UPDATE df SET index=index*10')

In [51]:
c2_df_result


Out[51]:
[[0L, 'information_schema', 7L],
 [1L, 'pg_catalog', 54L],
 [2L, 'pg_temp_4', 1L]]

In [52]:
c2.fetchall()


---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-52-f0b6015ec671> in <module>()
----> 1 c2.fetchall()

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/psycopg2/extras.pyc in fetchall(self)
     79     def fetchall(self):
     80         if self._prefetch:
---> 81             res = super(DictCursorBase, self).fetchall()
     82         if self._query_executed:
     83             self._build_index()

ProgrammingError: no results to fetch

In [53]:
c2.execute('SELECT * FROM df')

In [54]:
c2.fetchall()


Out[54]:
[[0L, 'information_schema', 7L],
 [10L, 'pg_catalog', 54L],
 [20L, 'pg_temp_4', 1L]]

What if that's not what I wanted to do?


In [55]:
db.autocommit


Out[55]:
False

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]:
[[10L, 'information_schema', 7L],
 [11L, 'pg_catalog', 54L],
 [12L, 'pg_temp_4', 1L]]

%sql can't see these updates yet though...


In [58]:
%sql SELECT * FROM df


3 rows affected.
Out[58]:
index schemaname count
0 information_schema 7
1 pg_catalog 54
2 pg_temp_4 1

In [59]:
c2.commit()


---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-59-df3064955405> in <module>()
----> 1 c2.commit()

AttributeError: 'DictCursor' object has no attribute 'commit'

In [60]:
c2.connection.commit()
c2.connection is db


Out[60]:
True

In [61]:
%sql SELECT * FROM df


3 rows affected.
Out[61]:
index schemaname count
10 information_schema 7
11 pg_catalog 54
12 pg_temp_4 1

c2_df_result is unchanged though...


In [62]:
c2_df_result


Out[62]:
[[0L, 'information_schema', 7L],
 [1L, 'pg_catalog', 54L],
 [2L, 'pg_temp_4', 1L]]

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]:
0 1 2 3 4 5 6
0 46931 decibel decibel psql 2016-11-12 01:08:56.806783+00:00 idle SELECT pg_catalog.pg_backend_pid() AS backend_...
1 46976 decibel decibel 2016-11-12 01:25:05.126718+00:00 idle SELECT * FROM df
2 47259 decibel decibel psycopg2 2016-11-12 01:25:14.888732+00:00 active SELECT pid,datname,usename,application_name,st...
3 48932 decibel decibel 2016-11-12 01:14:43.449379+00:00 idle COMMIT

Results from c2 would have names associated with them, but...


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


---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-64-c428a3b4851e> in <module>()
      1 c2.execute(SQLactivity)
----> 2 pd.DataFrame([row for row in c2])

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/pandas/core/frame.pyc in __init__(self, data, index, columns, dtype, copy)
    261                     if com.is_named_tuple(data[0]) and columns is None:
    262                         columns = data[0]._fields
--> 263                     arrays, columns = _to_arrays(data, columns, dtype=dtype)
    264                     columns = _ensure_index(columns)
    265 

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/pandas/core/frame.pyc in _to_arrays(data, columns, coerce_float, dtype)
   5350     if isinstance(data[0], (list, tuple)):
   5351         return _list_to_arrays(data, columns, coerce_float=coerce_float,
-> 5352                                dtype=dtype)
   5353     elif isinstance(data[0], collections.Mapping):
   5354         return _list_of_dict_to_arrays(data, columns,

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/pandas/core/frame.pyc in _list_to_arrays(data, columns, coerce_float, dtype)
   5427     else:
   5428         # list of lists
-> 5429         content = list(lib.to_object_array(data).T)
   5430     return _convert_object_array(content, columns, dtype=dtype,
   5431                                  coerce_float=coerce_float)

pandas/src/inference.pyx in pandas.lib.to_object_array (pandas/lib.c:63092)()

TypeError: Expected list, got DictRow

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


Out[65]:
application_name datname pid query state state_change usename
0 psql decibel 46931 SELECT pg_catalog.pg_backend_pid() AS backend_... idle 2016-11-12 01:08:56.806783+00:00 decibel
1 decibel 46976 SELECT * FROM df idle 2016-11-12 01:25:05.126718+00:00 decibel
2 psycopg2 decibel 47259 SELECT pid,datname,usename,application_name,st... active 2016-11-12 01:25:14.888732+00:00 decibel
3 decibel 48932 COMMIT idle 2016-11-12 01:14:43.449379+00:00 decibel

In [66]:
new_df=pd.read_sql(SQLactivity, db)
new_df


Out[66]:
pid datname usename application_name state_change state query
0 46931 decibel decibel psql 2016-11-12 01:08:56.806783+00:00 idle SELECT pg_catalog.pg_backend_pid() AS backend_...
1 46976 decibel decibel 2016-11-12 01:25:05.126718+00:00 idle SELECT * FROM df
2 47259 decibel decibel psycopg2 2016-11-12 01:25:14.888732+00:00 active SELECT pid,datname,usename,application_name,st...
3 48932 decibel decibel 2016-11-12 01:14:43.449379+00:00 idle COMMIT

In [67]:
%sql SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity


4 rows affected.
Out[67]:
pid datname usename application_name state_change state query
46931 decibel decibel psql 2016-11-12 01:08:56.806783+00:00 idle SELECT pg_catalog.pg_backend_pid() AS backend_pid
46976 decibel decibel 2016-11-12 01:25:55.772292+00:00 active SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity
47259 decibel decibel psycopg2 2016-11-12 01:25:36.294069+00:00 idle in transaction SELECT pid,datname,usename,application_name,state_change,state,query FROM pg_stat_activity
48932 decibel decibel 2016-11-12 01:14:43.449379+00:00 idle COMMIT

In [68]:
c2_df_result


Out[68]:
[[0L, 'information_schema', 7L],
 [1L, 'pg_catalog', 54L],
 [2L, 'pg_temp_4', 1L]]

In [69]:
%sql SELECT * FROM df


3 rows affected.
Out[69]:
index schemaname count
10 information_schema 7
11 pg_catalog 54
12 pg_temp_4 1

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


(ProgrammingError) relation "df2" does not exist
LINE 1: SELECT * FROM df2
                      ^
 'SELECT * FROM df2' {}

In [72]:
db.commit()

In [73]:
%sql SELECT * FROM df2


1 rows affected.
Out[73]:
s index schemaname count
1 0 information_schema 7

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


Out[74]:
s index schemaname count
0 1 0 information_schema 7
1 2 0 information_schema 7
2 3 1 pg_catalog 54
3 4 2 pg_temp_4 1

In [75]:
c.execute('SELECT * FROM df2')
c.fetchall()


Out[75]:
[(1, 0, 'information_schema', 7L),
 (2, 0, 'information_schema', 7L),
 (3, 1, 'pg_catalog', 54L),
 (4, 2, 'pg_temp_4', 1L)]

In [76]:
c3=db.cursor(cursor_factory=psycopg2.extras.NamedTupleCursor)
c3.execute('SELECT * FROM df2')
r=c3.fetchall()
r


Out[76]:
[Record(s=1, index=0, schemaname='information_schema', count=7L),
 Record(s=2, index=0, schemaname='information_schema', count=7L),
 Record(s=3, index=1, schemaname='pg_catalog', count=54L),
 Record(s=4, index=2, schemaname='pg_temp_4', count=1L)]

In [77]:
r[0]


Out[77]:
Record(s=1, index=0, schemaname='information_schema', count=7L)

In [78]:
r[0][0]


Out[78]:
1

In [79]:
r[0].s


Out[79]:
1

In [80]:
r[0][1] is r[0].index


Out[80]:
True

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


Out[81]:
[Record(s=5, index=0, schemaname='information_schema', count=7L)]

In [82]:
%sql SELECT * FROM df2


1 rows affected.
Out[82]:
s index schemaname count
1 0 information_schema 7

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

In [84]:
r


Out[84]:
[Record(s=6, index=0, schemaname='information_schema', count=7L)]

In [85]:
%sql SELECT * FROM df2


6 rows affected.
Out[85]:
s index schemaname count
1 0 information_schema 7
2 0 information_schema 7
3 1 pg_catalog 54
4 2 pg_temp_4 1
5 0 information_schema 7
6 0 information_schema 7

In [86]:
c.execute('BAD SQL')


---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-86-37cf8911a624> in <module>()
----> 1 c.execute('BAD SQL')

ProgrammingError: syntax error at or near "BAD"
LINE 1: BAD SQL
        ^

In [87]:
c.execute('SELECT 1').fetchall()


---------------------------------------------------------------------------
InternalError                             Traceback (most recent call last)
<ipython-input-87-c8e0f687956a> in <module>()
----> 1 c.execute('SELECT 1').fetchall()

InternalError: current transaction is aborted, commands ignored until end of transaction block

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()


---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-89-da5fd4e7e6f2> in <module>()
      1 with db:
      2     c.executemany(SQLdf2_insert, c2_df_result)
----> 3     c.execute('BAD SQL')
      4     c.execute('SELECT 1')
      5     c.fetchall()

ProgrammingError: syntax error at or near "BAD"
LINE 1: BAD SQL
        ^

In [90]:
c.execute('SELECT 1')
c.fetchall()


Out[90]:
[(1,)]

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

In [92]:
r


Out[92]:
[(1, 0, 'information_schema', 7L),
 (2, 0, 'information_schema', 7L),
 (3, 1, 'pg_catalog', 54L),
 (4, 2, 'pg_temp_4', 1L),
 (5, 0, 'information_schema', 7L),
 (6, 0, 'information_schema', 7L)]

In [93]:
new_c


Out[93]:
<cursor object at 0x1093cb718; closed: -1>

In [94]:
new_c.execute('SELECT 1')


---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
<ipython-input-94-3928c573d0e0> in <module>()
----> 1 new_c.execute('SELECT 1')

InterfaceError: cursor already closed

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]:
'(Ace,Spades)'

In [97]:
type(r)


Out[97]:
str

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


Out[98]:
card(value='Ace', suit='Spades')

In [99]:
type(r)


Out[99]:
psycopg2.extras.card

In [100]:
%sql CREATE TYPE complex AS(r float, i float)


Done.
Out[100]:
[]

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


Out[101]:
Record(row='(0.1,0.2)')

In [102]:
type(r)


Out[102]:
psycopg2.extras.Record

In [103]:
type(r.row)


Out[103]:
str

In [104]:
z=complex(2,3)
z


Out[104]:
(2+3j)

In [105]:
c2.execute('SELECT %s',(z,))
r=c2.fetchone()
r


---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-105-1b1f5234c17d> in <module>()
----> 1 c2.execute('SELECT %s',(z,))
      2 r=c2.fetchone()
      3 r

/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/psycopg2/extras.pyc in execute(self, query, vars)
    118         self.index = {}
    119         self._query_executed = 1
--> 120         return super(DictCursor, self).execute(query, vars)
    121 
    122     def callproc(self, procname, vars=None):

ProgrammingError: can't adapt type 'complex'

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]:
[['(2,3)'], ['(4,6)'], ['(2,-3)']]

In [111]:
type(r[0][0])


Out[111]:
str

In [112]:
%sql SELECT * FROM c


3 rows affected.
Out[112]:
c
(2,3)
(4,6)
(2,-3)

In [113]:
%sql SELECT (c).* FROM c


3 rows affected.
Out[113]:
r i
2.0 3.0
4.0 6.0
2.0 -3.0

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]:
Column(name='c', type_code=3150012, display_size=None, internal_size=-1, precision=None, scale=None, null_ok=None)

In [118]:
c2.description[0].type_code


Out[118]:
3150012

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

In [120]:
complex_caster


Out[120]:
<psycopg2._psycopg.type 'complex' at 0x1097d1578>

In [121]:
exten.register_type(complex_caster)

In [122]:
c2.execute('SELECT * FROM c')
r=c2.fetchall()
r


Out[122]:
[[(2+3j)], [(4+6j)], [(2-3j)]]

In [123]:
type(r[0][0])


Out[123]:
complex

In [124]:
%sql SELECT * FROM c


3 rows affected.
Out[124]:
c
(2+3j)
(4+6j)
(2-3j)

Questions?

Jim.Nasby@BlueTreble.com

This notebook can be downloaded from https://github.com/AustinPUG/PGDay2016/blob/master/Python%2BPostgres.ipynb (search for PGDay2016 on Github)


In [ ]: