In [1]:

    

%reload_ext sql


    

In [2]:

    

%%sql postgresql://superuser:superuserpass@localhost/app
        select * from api.todos;


    

    




3 rows affected.






    
Out[2]:





    

        
id
        
todo
        
private
        
mine
    
    

        
1
        
up1
        
False
        
None
    
    

        
3
        
item_3
        
False
        
None
    
    

        
6
        
item_6
        
False
        
None
    

In [3]:

    

%sql select 1;


    

    




1 rows affected.






    
Out[3]:





    

        
?column?
    
    

        
1
    

In [16]:

    

import os
os.getcwd()


    

    
Out[16]:





'/Users/mertnuhoglu/projects/study/py/jupyter'

In [4]:

    

import anosql
import psycopg2
import sqlite3

# PostgreSQL
conn = psycopg2.connect('postgresql://superuser:superuserpass@localhost/app')
queries = anosql.load_queries('postgres', 'sql/study01.sql')
queries.select1(conn)


    

    
Out[4]:





[(1,)]

In [5]:

    

queries.select2(conn)


    

    
Out[5]:





[(2,)]

In [1]:

    

!cat sql/study01.sql


    

    




-- name: select1
SELECT 1;

-- name: select2
SELECT 2;

In [3]:

    

!echo "hello"


    

    




hello

In [44]:

    

%config SqlMagic.displaylimit = 5
%sql select * from pg_catalog.pg_tables pt where pt.schemaname = 'pg_catalog';


    

    




55 rows affected.






    
Out[44]:





    

        
schemaname
        
tablename
        
tableowner
        
tablespace
        
hasindexes
        
hasrules
        
hastriggers
        
rowsecurity
    
    

        
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
    
    

        
pg_catalog
        
pg_attribute
        
postgres
        
None
        
True
        
False
        
False
        
False
    

55 rows, truncated to displaylimit of 5

In [45]:

    

%sql select * from pg_catalog.pg_tables pt where pt.schemaname = 'api';


    

    




0 rows affected.






    
Out[45]:





    

        
schemaname
        
tablename
        
tableowner
        
tablespace
        
hasindexes
        
hasrules
        
hastriggers
        
rowsecurity
    

In [46]:

    

%sql select * from pg_catalog.pg_views pv where pv.schemaname = 'api';


    

    




5 rows affected.






    
Out[46]:





    

        
schemaname
        
viewname
        
viewowner
        
definition
    
    

        
api
        
todos
        
api
        
 SELECT todo.id,
    todo.todo,
    todo.private,
    (todo.owner_id = request.user_id()) AS mine
   FROM data.todo;
    
    

        
api
        
clients
        
superuser
        
 SELECT client.id,
    client.name,
    client.address,
    client.created_on,
    client.updated_on
   FROM data.client;
    
    

        
api
        
projects
        
superuser
        
 SELECT project.id,
    project.name,
    project.client_id,
    project.created_on,
    project.updated_on
   FROM data.project;
    
    

        
api
        
tasks
        
superuser
        
 SELECT task.id,
    task.name,
    task.completed,
    task.project_id,
    task.created_on,
    task.updated_on
   FROM data.task;
    
    

        
api
        
comments
        
superuser
        
 SELECT project_comment.id,
    project_comment.body,
    'project'::text AS parent_type,
    project_comment.project_id AS parent_id,
    project_comment.project_id,
    NULL::integer AS task_id,
    project_comment.created_on,
    project_comment.updated_on
   FROM data.project_comment
UNION
 SELECT task_comment.id,
    task_comment.body,
    'task'::text AS parent_type,
    task_comment.task_id AS parent_id,
    NULL::integer AS project_id,
    task_comment.task_id,
    task_comment.created_on,
    task_comment.updated_on
   FROM data.task_comment;
    

In [48]:

    

%sql select user;


    

    




1 rows affected.






    
Out[48]:





    

        
current_user
    
    

        
superuser
    

In [50]:

    

%sql select '$user';


    

    




1 rows affected.






    
Out[50]:





    

        
?column?
    
    

        
$user
    

In [51]:

    

%sql select '{Alex,Sonia}'::text[] As name;


    

    




1 rows affected.






    
Out[51]:





    

        
name
    
    

        
['Alex', 'Sonia']
    

In [52]:

    

%sql select unnest('{Alex,Sonia}'::text[]) As name;


    

    




2 rows affected.






    
Out[52]:





    

        
name
    
    

        
Alex
    
    

        
Sonia
    

In [53]:

    

%sql SELECT unnest(string_to_array('split.this', '.')) As x;


    

    




2 rows affected.






    
Out[53]:





    

        
x
    
    

        
split
    
    

        
this
    

In [54]:

    

%sql SELECT unnest(regexp_matches('100 hats 200', '\d+', 'g'));


    

    




2 rows affected.






    
Out[54]:





    

        
unnest
    
    

        
100
    
    

        
200
    

In [106]:

    

%%sql
CREATE TABLE public.families_j (
  doc_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  profile jsonb
);

select * from public.families_j;


    

    




Done.
0 rows affected.






    
Out[106]:





    

        
doc_id
        
profile
    

In [107]:

    

%sql DROP TABLE public.families_j;


    

    




Done.






    
Out[107]:





[]

In [108]:

    

%sql CREATE SCHEMA study;


    

    




(psycopg2.ProgrammingError) schema "study" already exists
 [SQL: 'CREATE SCHEMA study;']

In [110]:

    

%%sql
CREATE TABLE study.families_j (
  doc_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  profile jsonb
);


    

    




Done.






    
Out[110]:





[]

In [111]:

    

%sql select * from study.families_j;


    

    




0 rows affected.






    
Out[111]:





    

        
doc_id
        
profile
    

In [122]:

    

%%sql 
-- multiple rows per insert values
INSERT INTO study.families_j (profile) VALUES 
     ('{"name":"Gomez", "members": [{"member":{"relation":"padre"}}]}')
    ,('{"name":"Sanchez", "members": [{"member":{"relation":"madre"}}]}')
;
SELECT * FROM study.families_j;


    

    




2 rows affected.
2 rows affected.






    
Out[122]:





    

        
doc_id
        
profile
    
    

        
6
        
{'name': 'Gomez', 'members': [{'member': {'relation': 'padre'}}]}
    
    

        
7
        
{'name': 'Sanchez', 'members': [{'member': {'relation': 'madre'}}]}
    

In [120]:

    

%sql select profile->'members' from study.families_j;


    

    




2 rows affected.






    
Out[120]:





    

        
?column?
    
    

        
[{'member': {'relation': 'padre'}}]
    
    

        
[{'member': {'relation': 'madre'}}]
    

In [128]:

    

%sql select profile->>'members' as name from study.families_j;


    

    




2 rows affected.






    
Out[128]:





    

        
name
    
    

        
[{"member": {"relation": "padre"}}]
    
    

        
[{"member": {"relation": "madre"}}]
    

In [129]:

    

%sql select profile->'name' as name from study.families_j;


    

    




2 rows affected.






    
Out[129]:





    

        
name
    
    

        
Gomez
    
    

        
Sanchez
    

In [130]:

    

%sql select profile->'members'->'member' from study.families_j;


    

    




2 rows affected.






    
Out[130]:





    

        
?column?
    
    

        
None
    
    

        
None
    

In [131]:

    

%sql select profile->'members'->>'member' from study.families_j;


    

    




2 rows affected.






    
Out[131]:





    

        
?column?
    
    

        
None
    
    

        
None
    

In [138]:

    

%sql select profile->'members'->'member'->>'relation' from study.families_j;


    

    




2 rows affected.






    
Out[138]:





    

        
?column?
    
    

        
None
    
    

        
None
    

In [139]:

    

%sql select profile->'members'->0#>>'{member}' from study.families_j;


    

    




2 rows affected.






    
Out[139]:





    

        
?column?
    
    

        
{"relation": "padre"}
    
    

        
{"relation": "madre"}
    

In [143]:

    

%sql select profile->'members'->0->'member'->'relation' from study.families_j;


    

    




2 rows affected.






    
Out[143]:





    

        
?column?
    
    

        
padre
    
    

        
madre
    

In [133]:

    

%sql CREATE TABLE study.books ( id integer, data json );


    

    




(psycopg2.ProgrammingError) relation "books" already exists
 [SQL: 'CREATE TABLE study.books ( id integer, data json );']

In [134]:

    

%%sql
INSERT INTO study.books VALUES (1,
  '{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }');
INSERT INTO study.books VALUES (2,
  '{ "name": "Book the Second", "author": { "first_name": "Charles", "last_name": "Xavier" } }');
INSERT INTO study.books VALUES (3,
  '{ "name": "Book the Third", "author": { "first_name": "Jim", "last_name": "Brown" } }');


    

    




1 rows affected.
1 rows affected.
1 rows affected.






    
Out[134]:





[]

In [136]:

    

%sql SELECT id, data->>'name' AS name FROM study.books;


    

    




3 rows affected.






    
Out[136]:





    

        
id
        
name
    
    

        
1
        
Book the First
    
    

        
2
        
Book the Second
    
    

        
3
        
Book the Third
    

In [137]:

    

%sql SELECT id, data->'author'->>'first_name' as author_first_name FROM study.books;


    

    




3 rows affected.






    
Out[137]:





    

        
id
        
author_first_name
    
    

        
1
        
Bob
    
    

        
2
        
Charles
    
    

        
3
        
Jim
    

In [146]:

    

!head sql/library.sql


    

    




CREATE TABLE Book(  --df: mult=100.0
  bid SERIAL PRIMARY KEY,
  title TEXT NOT NULL, 
  isbn ISBN13 NOT NULL 
);
CREATE TABLE Reader( 
  rid SERIAL PRIMARY KEY,
  firstname TEXT NOT NULL, 
  lastname TEXT NOT NULL, 
  born DATE NOT NULL, 

In [147]:

    

!datafiller --size=100 sql/library.sql > sql/library_test_data.sql


    

In [148]:

    

!head sql/library_test_data.sql


    

    




-- data generated by /usr/local/bin/datafiller version 2.0.0 (r792 on 2014-03-23) for postgresql

-- fill table book (10000)
\echo # filling table book (10000)
COPY book (bid,title,isbn) FROM STDIN (ENCODING 'utf-8');
1	title_240_2	9784253959209
2	title_1462_146	9780731202522
3	title_766_7	9788068420503
4	title_3402	9782520531288

In [1]:

    

!ddlgenerator postgresql sql/sample_data01.csv > sql/sample_data01.sql


    

In [2]:

    

!head sql/sample_data01.csv


    

    




product_id,title,added
1,laptop x101,2017-02-03
2,nvidia p80,2016-05-04

In [3]:

    

!head sql/sample_data01.sql


    

    




CREATE TABLE sample_data01 (
	product_id INTEGER NOT NULL, 
	title VARCHAR(11) NOT NULL, 
	added TIMESTAMP WITHOUT TIME ZONE NOT NULL
);

In [6]:

    

%load_ext pgcli.magic


    

    




The pgcli.magic extension is already loaded. To reload it, use:
  %reload_ext pgcli.magic

In [8]:

    

%pgcli postgres://superuser:superuserpass@localhost:5432/library


    

    




Connected: superuser@library_1






    




---------------------------------------------------------------------------
AssertionError                            Traceback (most recent call last)
<ipython-input-8-7fb2e1473d34> in <module>()
----> 1 get_ipython().magic('pgcli postgres://superuser:superuserpass@localhost:5432/library')

/Users/mertnuhoglu/miniconda3/lib/python3.5/site-packages/IPython/core/interactiveshell.py in magic(self, arg_s)
   2156         magic_name, _, magic_arg_s = arg_s.partition(' ')
   2157         magic_name = magic_name.lstrip(prefilter.ESC_MAGIC)
-> 2158         return self.run_line_magic(magic_name, magic_arg_s)
   2159 
   2160     #-------------------------------------------------------------------------

/Users/mertnuhoglu/miniconda3/lib/python3.5/site-packages/IPython/core/interactiveshell.py in run_line_magic(self, magic_name, line)
   2077                 kwargs['local_ns'] = sys._getframe(stack_depth).f_locals
   2078             with self.builtin_trap:
-> 2079                 result = fn(*args,**kwargs)
   2080             return result
   2081 

/Users/mertnuhoglu/miniconda3/lib/python3.5/site-packages/pgcli/magic.py in pgcli_line_magic(line)
     42 
     43     try:
---> 44         pgcli.run_cli()
     45     except SystemExit:
     46         pass

/Users/mertnuhoglu/miniconda3/lib/python3.5/site-packages/pgcli/main.py in run_cli(self)
    499                                  persist_priorities='none')
    500 
--> 501         self.cli = self._build_cli(history)
    502 
    503         if not self.less_chatty:

/Users/mertnuhoglu/miniconda3/lib/python3.5/site-packages/pgcli/main.py in _build_cli(self, history)
    616 
    617             cli = CommandLineInterface(application=application,
--> 618                                        eventloop=self.eventloop)
    619 
    620             return cli

/Users/mertnuhoglu/miniconda3/lib/python3.5/site-packages/prompt_toolkit/interface.py in __init__(self, application, eventloop, input, output)
     79         # Inputs and outputs.
     80         self.output = output or create_output()
---> 81         self.input = input or StdinInput(sys.stdin)
     82 
     83         #: The input buffers.

/Users/mertnuhoglu/miniconda3/lib/python3.5/site-packages/prompt_toolkit/input.py in __init__(self, stdin)
     65 
     66         # The input object should be a TTY.
---> 67         assert self.stdin.isatty()
     68 
     69         # Test whether the given input object has a file descriptor.

AssertionError: 

In [89]:

    

%%sql 
SELECT '2012-03-11 3:10 AM America/Los_Angeles'::timestamptz AS america
, '2012-03-11 3:10 AM'::timestamptz as istanbul1
, '2012-03-11 3:10 AM GMT-3'::timestamptz as istanbul2
, '2012-03-11 3:10'::timestamptz::date as date1
;


    

    




1 rows affected.






    
Out[89]:





    

        
america
        
istanbul1
        
istanbul2
        
date1
    
    

        
2012-03-11 10:10:00+00
        
2012-03-11 03:10:00+00
        
2012-03-11 00:10:00+00
        
2012-03-11
    

In [86]:

    

works = %sql SELECT 1,2


    

    




1 rows affected.

In [90]:

    

%%sql
SELECT
  a
  , extract('year' from b)
  FROM
    ( VALUES
      ( '2012-03-11 3:10 AM'::timestamptz
      , '1012-03-11 3:10'::timestamptz::date)
    ) dates(a,b)
;


    

    




1 rows affected.






    
Out[90]:





    

        
a
        
date_part
    
    

        
2012-03-11 03:10:00+00
        
1012.0
    

In [91]:

    

%%sql
SELECT
  *
  FROM
    ( VALUES
      ( '2012-03-11 3:10 AM'::timestamptz
      , '1012-03-11 3:10'::timestamptz::date)
    ) dates
;


    

    




1 rows affected.






    
Out[91]:





    

        
column1
        
column2
    
    

        
2012-03-11 03:10:00+00
        
1012-03-11
    

In [92]:

    

%%sql
SELECT
  column1
  FROM
    ( VALUES
      ( '2012-03-11 3:10 AM'::timestamptz
      , '1012-03-11 3:10'::timestamptz::date)
    ) dates
;


    

    




1 rows affected.






    
Out[92]:





    

        
column1
    
    

        
2012-03-11 03:10:00+00
    

In [93]:

    

%%sql
SELECT
  *
  FROM
    ( VALUES
      ( '2012-03-11 3:10 AM'::timestamptz
      , '1012-03-11 3:10'::timestamptz::date)
    ) AS dates
;


    

    




1 rows affected.






    
Out[93]:





    

        
column1
        
column2
    
    

        
2012-03-11 03:10:00+00
        
1012-03-11
    

In [94]:

    

%sql select '[2012-04-24, infinity]'::daterange;


    

    




1 rows affected.






    
Out[94]:





    

        
daterange
    
    

        
DateRange('2012-04-24', 'infinity', '[)')
    

In [4]:

    

%sql select '1 hour'::interval;


    

    




1 rows affected.






    
Out[4]:





    

        
interval
    
    

        
1:00:00
    

In [5]:

    

%sql select now() - interval '1 hour';


    

    




1 rows affected.






    
Out[5]:





    

        
?column?
    
    

        
2017-12-11 07:43:07.463983+00:00
    

In [6]:

    

%sql select '1 hour ago'::interval;


    

    




1 rows affected.






    
Out[6]:





    

        
interval
    
    

        
-1 day, 23:00:00
    

In [7]:

    

%sql select '1 day ago'::interval;


    

    




1 rows affected.






    
Out[7]:





    

        
interval
    
    

        
-1 day, 0:00:00
    

In [8]:

    

%sql select INTERVAL '1' YEAR;


    

    




1 rows affected.






    
Out[8]:





    

        
interval
    
    

        
365 days, 0:00:00
    

In [9]:

    

%sql select INTERVAL '1 year';


    

    




1 rows affected.






    
Out[9]:





    

        
interval
    
    

        
365 days, 0:00:00
    

In [17]:

    

%sql select INTERVAL '@ 1 year';


    

    




1 rows affected.






    
Out[17]:





    

        
interval
    
    

        
365 days, 0:00:00
    

In [21]:

    

@sql select INTERVAL '1-1';


    

    




  File "<ipython-input-21-af73b2e30388>", line 1
    @sql select INTERVAL '1-1';
              ^
SyntaxError: invalid syntax

In [23]:

    

%sql select INTERVAL '1 day 2:03:04' HOUR TO MINUTE;
# drops second field


    

    




1 rows affected.






    
Out[23]:





    

        
interval
    
    

        
1 day, 2:03:00
    

In [24]:

    

%sql select INTERVAL '2:03:04';


    

    




1 rows affected.






    
Out[24]:





    

        
interval
    
    

        
2:03:04
    

In [25]:

    

%sql select INTERVAL '-2:03:04';


    

    




1 rows affected.






    
Out[25]:





    

        
interval
    
    

        
-1 day, 21:56:56
    

In [26]:

    

%sql select INTERVAL '2:03:04 ago';


    

    




1 rows affected.






    
Out[26]:





    

        
interval
    
    

        
-1 day, 21:56:56
    

In [27]:

    

%sql select INTERVAL 'P0Y0M0DT2H3M4S';


    

    




1 rows affected.






    
Out[27]:





    

        
interval
    
    

        
2:03:04
    

In [ ]: