SQL Magic¶

In [ ]:

%sql -c sql_command -f input_file -ncols nb_of_cols -nrows nb_of_rows -o output_file

Executes SQL queries in the Jupyter cell.

Parameters¶

Name	Type	Optional	Description
-c / --command	str	✓	SQL Command to execute.
-f / --file	str	✓	Input file to execute.
-ncols	int	✓	Maximum number of columns to display.
-nrows	int	✓	Maximum number of rows to display.
-o / --output	str	✓	Output file. Use this option to export the result of the query to the CSV or JSON format.

Returns¶

vDataFrame : Result of the query.

Examples¶

These examples demonstrate the following:

Setting up the environment.
SQL Magic.
Getting the vDataFrame of a query.
Using variables inside a query.
Limiting the number of rows and columns.
Exporting a query to JSON or CSV
Executing SQL files

Setting up the environment¶

If you don't already have one, create a new connection:

In [11]:

import verticapy as vp

# Save a new connection
vp.new_connection({"host": "10.211.55.14", 
                   "port": "5433", 
                   "database": "testdb", 
                   "password": "XxX", 
                   "user": "dbadmin"},
                   name = "VerticaDSN")

If you already have a connection in a connection file, you can use it by running the following command:

In [12]:

# Connection using the VerticaDSN connection
vp.connect("VerticaDSN")

Load the extension:

In [13]:

%load_ext verticapy.sql

Load a sample dataset. These sample datasets are loaded into the public schema by default. You can specify a target schema with the 'name' and 'schema' parameters:

In [14]:

from verticapy.datasets import load_titanic, load_iris
titanic = load_titanic()
iris = load_iris()

SQL Magic¶

Use '%%sql' to run a query on the dataset:

In [15]:

%%sql
SELECT 
    survived,
    AVG(fare) AS avg_fare,
    AVG(age) AS avg_age
FROM titanic
GROUP BY 1;

Execution: 0.006s

Out[15]:

	123 survived Integer	123 avg_fare Float	123 avg_age Float
1	0	23.4255950191571	30.6420462046205
2	1	52.3002593333333	29.3936572890026

Rows: 1-2 | Columns: 3

You can also run queries with '%sql' and the '-c' option:

In [17]:

%sql -c 'SELECT DISTINCT Species FROM iris;'

Execution: 0.006s

Out[17]:

	Abc Species Varchar(30)
1	Iris-versicolor
2	Iris-virginica
3	Iris-setosa

Rows: 1-3 | Column: Species | Type: varchar(30)

You can use a single cell for multiple queries:

In [18]:

%%sql
DROP TABLE IF EXISTS test;
CREATE TABLE test AS SELECT 'Badr Ouali' AS name;
SELECT * FROM test;

DROP
CREATE

Execution: 0.05s

Out[18]:

	Abc name Varchar(10)
1	Badr Ouali

Rows: 1-1 | Column: name | Type: varchar(10)

To add comments to query:

In [19]:

%%sql
-- Comment Test
/* My Vertica Version */
SELECT version(); -- Looking at the current Vertica version.

Execution: 0.005s

Out[19]:

	Abc Varchar(128)
1

Rows: 1-1 | Column: version | Type: varchar(128)

Getting the vDataFrame of a query¶

Results are stored in a vDataFrame, which is assigned to a temporary variable called '_'. As shown in the following procedure, you can assign this to a variable to save your results.

In [20]:

%%sql
SELECT
    age,
    fare,
    pclass
FROM titanic
WHERE age IS NOT NULL AND fare IS NOT NULL;

Execution: 0.007s

Out[20]:

	123 age Numeric(6,3)	123 fare Numeric(10,5)	123 pclass Integer
1	2.0	151.55	1
2	30.0	151.55	1
3	25.0	151.55	1
4	39.0	0.0	1
5	71.0	49.5042	1
6	47.0	227.525	1
7	24.0	247.5208	1
8	36.0	75.2417	1
9	25.0	26.0	1
10	45.0	35.5	1
11	42.0	26.55	1
12	41.0	30.5	1
13	48.0	50.4958	1
14	45.0	26.55	1
15	33.0	5.0	1
16	28.0	47.1	1
17	17.0	47.1	1
18	49.0	26.0	1
19	36.0	78.85	1
20	46.0	61.175	1
21	27.0	136.7792	1
22	47.0	25.5875	1
23	37.0	83.1583	1
24	70.0	71.0	1
25	39.0	71.2833	1
26	31.0	52.0	1
27	50.0	106.425	1
28	39.0	29.7	1
29	36.0	31.6792	1
30	30.0	27.75	1
31	19.0	263.0	1
32	64.0	263.0	1
33	37.0	53.1	1
34	47.0	38.5	1
35	24.0	79.2	1
36	71.0	34.6542	1
37	38.0	153.4625	1
38	46.0	79.2	1
39	45.0	83.475	1
40	40.0	0.0	1
41	55.0	93.5	1
42	42.0	42.5	1
43	55.0	50.0	1
44	42.0	52.0	1
45	50.0	28.7125	1
46	46.0	26.0	1
47	50.0	26.0	1
48	32.5	211.5	1
49	58.0	29.7	1
50	41.0	51.8625	1
51	29.0	30.0	1
52	30.0	45.5	1
53	30.0	26.0	1
54	19.0	53.1	1
55	46.0	75.2417	1
56	54.0	51.8625	1
57	28.0	82.1708	1
58	65.0	26.55	1
59	44.0	90.0	1
60	55.0	30.5	1
61	47.0	42.4	1
62	37.0	29.7	1
63	58.0	113.275	1
64	64.0	26.0	1
65	65.0	61.9792	1
66	28.5	27.7208	1
67	45.5	28.5	1
68	23.0	93.5	1
69	29.0	66.6	1
70	18.0	108.9	1
71	47.0	52.0	1
72	38.0	0.0	1
73	22.0	135.6333	1
74	31.0	50.4958	1
75	36.0	40.125	1
76	55.0	59.4	1
77	33.0	26.55	1
78	61.0	262.375	1
79	50.0	55.9	1
80	56.0	26.55	1
81	56.0	30.6958	1
82	24.0	60.0	1
83	57.0	146.5208	1
84	62.0	26.55	1
85	67.0	221.7792	1
86	63.0	221.7792	1
87	61.0	32.3208	1
88	52.0	79.65	1
89	49.0	110.8833	1
90	40.0	27.7208	1
91	61.0	33.5	1
92	47.0	34.0208	1
93	64.0	75.25	1
94	60.0	26.55	1
95	54.0	77.2875	1
96	21.0	77.2875	1
97	57.0	164.8667	1
98	50.0	211.5	1
99	27.0	211.5	1
100	51.0	61.3792	1

Rows: 1-100 | Columns: 3

Assign the results to a new variable:

In [21]:

titanic_clean = _
display(titanic_clean)

	123 age Numeric(6,3)	123 fare Numeric(10,5)	123 pclass Integer
1	2.0	151.55	1
2	30.0	151.55	1
3	25.0	151.55	1
4	39.0	0.0	1
5	71.0	49.5042	1
6	47.0	227.525	1
7	24.0	247.5208	1
8	36.0	75.2417	1
9	25.0	26.0	1
10	45.0	35.5	1
11	42.0	26.55	1
12	41.0	30.5	1
13	48.0	50.4958	1
14	45.0	26.55	1
15	33.0	5.0	1
16	28.0	47.1	1
17	17.0	47.1	1
18	49.0	26.0	1
19	36.0	78.85	1
20	46.0	61.175	1
21	27.0	136.7792	1
22	47.0	25.5875	1
23	37.0	83.1583	1
24	70.0	71.0	1
25	39.0	71.2833	1
26	31.0	52.0	1
27	50.0	106.425	1
28	39.0	29.7	1
29	36.0	31.6792	1
30	30.0	27.75	1
31	19.0	263.0	1
32	64.0	263.0	1
33	37.0	53.1	1
34	47.0	38.5	1
35	24.0	79.2	1
36	71.0	34.6542	1
37	38.0	153.4625	1
38	46.0	79.2	1
39	45.0	83.475	1
40	40.0	0.0	1
41	55.0	93.5	1
42	42.0	42.5	1
43	55.0	50.0	1
44	42.0	52.0	1
45	50.0	28.7125	1
46	46.0	26.0	1
47	50.0	26.0	1
48	32.5	211.5	1
49	58.0	29.7	1
50	41.0	51.8625	1
51	29.0	30.0	1
52	30.0	45.5	1
53	30.0	26.0	1
54	19.0	53.1	1
55	46.0	75.2417	1
56	54.0	51.8625	1
57	28.0	82.1708	1
58	65.0	26.55	1
59	44.0	90.0	1
60	55.0	30.5	1
61	47.0	42.4	1
62	37.0	29.7	1
63	58.0	113.275	1
64	64.0	26.0	1
65	65.0	61.9792	1
66	28.5	27.7208	1
67	45.5	28.5	1
68	23.0	93.5	1
69	29.0	66.6	1
70	18.0	108.9	1
71	47.0	52.0	1
72	38.0	0.0	1
73	22.0	135.6333	1
74	31.0	50.4958	1
75	36.0	40.125	1
76	55.0	59.4	1
77	33.0	26.55	1
78	61.0	262.375	1
79	50.0	55.9	1
80	56.0	26.55	1
81	56.0	30.6958	1
82	24.0	60.0	1
83	57.0	146.5208	1
84	62.0	26.55	1
85	67.0	221.7792	1
86	63.0	221.7792	1
87	61.0	32.3208	1
88	52.0	79.65	1
89	49.0	110.8833	1
90	40.0	27.7208	1
91	61.0	33.5	1
92	47.0	34.0208	1
93	64.0	75.25	1
94	60.0	26.55	1
95	54.0	77.2875	1
96	21.0	77.2875	1
97	57.0	164.8667	1
98	50.0	211.5	1
99	27.0	211.5	1
100	51.0	61.3792	1

Rows: 1-100 | Columns: 3

Temporary results are stored in a vDataFrame, so you can call vDataFrame methods:

In [22]:

titanic_clean.corr()

Out[22]:

	"age"	"fare"	"pclass"
"age"	1.0	0.178575164117464	-0.403900427391808
"fare"	0.178575164117464	1.0	-0.568258663041552
"pclass"	-0.403900427391808	-0.568258663041552	1.0

Rows: 1-3 | Columns: 4

Using variables inside a query¶

You can use variables in a SQL query with the ':' operator. This variable can be a vDataFrame, a tablesample, a pandas.DataFrame, or any standard Python type.

In [23]:

import verticapy.stats as st

class_fare = titanic_clean.groupby("pclass", 
                                   [st.avg(titanic_clean["fare"])._as("avg_fare")])
class_fare

Out[23]:

	123 pclass Integer	123 avg_fare Float
1	1	93.1410288321168
2	2	21.9666833333333
3	3	12.8090323529412

Rows: 1-3 | Columns: 2

Use the 'class_fare' variable in a SQL query:

In [17]:

%%sql
SELECT 
    x.*, 
    y.avg_fare
FROM titanic AS x LEFT JOIN (SELECT * FROM :class_fare) AS y 
ON x.pclass = y.pclass;

Execution: 0.011s

Out[17]:

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(6,3)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(10,5)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc home.dest Varchar(100)	123 avg_fare Float
1	1	0		female	2.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON	93.1410288321168
2	1	0		male	30.0	1	2	113781	151.55	C22 C26	S	[null]	135	Montreal, PQ / Chesterville, ON	93.1410288321168
3	1	0		female	25.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON	93.1410288321168
4	1	0		male	39.0	0	0	112050	0.0	A36	S	[null]	[null]	Belfast, NI	93.1410288321168
5	1	0		male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22	Montevideo, Uruguay	93.1410288321168
6	1	0		male	47.0	1	0	PC 17757	227.525	C62 C64	C	[null]	124	New York, NY	93.1410288321168
7	1	0		male	[null]	0	0	PC 17318	25.925	[null]	S	[null]	[null]	New York, NY	93.1410288321168
8	1	0		male	24.0	0	1	PC 17558	247.5208	B58 B60	C	[null]	[null]	Montreal, PQ	93.1410288321168
9	1	0		male	36.0	0	0	13050	75.2417	C6	C	A	[null]	Winnipeg, MN	93.1410288321168
10	1	0		male	25.0	0	0	13905	26.0	[null]	C	[null]	148	San Francisco, CA	93.1410288321168
11	1	0		male	45.0	0	0	113784	35.5	T	S	[null]	[null]	Trenton, NJ	93.1410288321168
12	1	0		male	42.0	0	0	110489	26.55	D22	S	[null]	[null]	London / Winnipeg, MB	93.1410288321168
13	1	0		male	41.0	0	0	113054	30.5	A21	S	[null]	[null]	Pomeroy, WA	93.1410288321168
14	1	0		male	48.0	0	0	PC 17591	50.4958	B10	C	[null]	208	Omaha, NE	93.1410288321168
15	1	0		male	[null]	0	0	112379	39.6	[null]	C	[null]	[null]	Philadelphia, PA	93.1410288321168
16	1	0		male	45.0	0	0	113050	26.55	B38	S	[null]	[null]	Washington, DC	93.1410288321168
17	1	0		male	[null]	0	0	113798	31.0	[null]	S	[null]	[null]	[null]	93.1410288321168
18	1	0		male	33.0	0	0	695	5.0	B51 B53 B55	S	[null]	[null]	New York, NY	93.1410288321168
19	1	0		male	28.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay	93.1410288321168
20	1	0		male	17.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay	93.1410288321168
21	1	0		male	49.0	0	0	19924	26.0	[null]	S	[null]	[null]	Ascot, Berkshire / Rochester, NY	93.1410288321168
22	1	0		male	36.0	1	0	19877	78.85	C46	S	[null]	172	Little Onn Hall, Staffs	93.1410288321168
23	1	0		male	46.0	1	0	W.E.P. 5734	61.175	E31	S	[null]	[null]	Amenia, ND	93.1410288321168
24	1	0		male	[null]	0	0	112051	0.0	[null]	S	[null]	[null]	Liverpool, England / Belfast	93.1410288321168
25	1	0		male	27.0	1	0	13508	136.7792	C89	C	[null]	[null]	Los Angeles, CA	93.1410288321168
26	1	0		male	[null]	0	0	110465	52.0	A14	S	[null]	[null]	Stoughton, MA	93.1410288321168
27	1	0		male	47.0	0	0	5727	25.5875	E58	S	[null]	[null]	Victoria, BC	93.1410288321168
28	1	0		male	37.0	1	1	PC 17756	83.1583	E52	C	[null]	[null]	Lakewood, NJ	93.1410288321168
29	1	0		male	[null]	0	0	113791	26.55	[null]	S	[null]	[null]	Roachdale, IN	93.1410288321168
30	1	0		male	70.0	1	1	WE/P 5735	71.0	B22	S	[null]	269	Milwaukee, WI	93.1410288321168
31	1	0		male	39.0	1	0	PC 17599	71.2833	C85	C	[null]	[null]	New York, NY	93.1410288321168
32	1	0		male	31.0	1	0	F.C. 12750	52.0	B71	S	[null]	[null]	Montreal, PQ	93.1410288321168
33	1	0		male	50.0	1	0	PC 17761	106.425	C86	C	[null]	62	Deephaven, MN / Cedar Rapids, IA	93.1410288321168
34	1	0		male	39.0	0	0	PC 17580	29.7	A18	C	[null]	133	Philadelphia, PA	93.1410288321168
35	1	0		female	36.0	0	0	PC 17531	31.6792	A29	C	[null]	[null]	New York, NY	93.1410288321168
36	1	0		male	[null]	0	0	PC 17483	221.7792	C95	S	[null]	[null]	[null]	93.1410288321168
37	1	0		male	30.0	0	0	113051	27.75	C111	C	[null]	[null]	New York, NY	93.1410288321168
38	1	0		male	19.0	3	2	19950	263.0	C23 C25 C27	S	[null]	[null]	Winnipeg, MB	93.1410288321168
39	1	0		male	64.0	1	4	19950	263.0	C23 C25 C27	S	[null]	[null]	Winnipeg, MB	93.1410288321168
40	1	0		male	[null]	0	0	113778	26.55	D34	S	[null]	[null]	Westcliff-on-Sea, Essex	93.1410288321168
41	1	0		male	[null]	0	0	112058	0.0	B102	S	[null]	[null]	[null]	93.1410288321168
42	1	0		male	37.0	1	0	113803	53.1	C123	S	[null]	[null]	Scituate, MA	93.1410288321168
43	1	0		male	47.0	0	0	111320	38.5	E63	S	[null]	275	St Anne's-on-Sea, Lancashire	93.1410288321168
44	1	0		male	24.0	0	0	PC 17593	79.2	B86	C	[null]	[null]	[null]	93.1410288321168
45	1	0		male	71.0	0	0	PC 17754	34.6542	A5	C	[null]	[null]	New York, NY	93.1410288321168
46	1	0		male	38.0	0	1	PC 17582	153.4625	C91	S	[null]	147	Winnipeg, MB	93.1410288321168
47	1	0		male	46.0	0	0	PC 17593	79.2	B82 B84	C	[null]	[null]	New York, NY	93.1410288321168
48	1	0		male	[null]	0	0	113796	42.4	[null]	S	[null]	[null]	[null]	93.1410288321168
49	1	0		male	45.0	1	0	36973	83.475	C83	S	[null]	[null]	New York, NY	93.1410288321168
50	1	0		male	40.0	0	0	112059	0.0	B94	S	[null]	110	[null]	93.1410288321168
51	1	0		male	55.0	1	1	12749	93.5	B69	S	[null]	307	Montreal, PQ	93.1410288321168
52	1	0		male	42.0	0	0	113038	42.5	B11	S	[null]	[null]	London / Middlesex	93.1410288321168
53	1	0		male	[null]	0	0	17463	51.8625	E46	S	[null]	[null]	Brighton, MA	93.1410288321168
54	1	0		male	55.0	0	0	680	50.0	C39	S	[null]	[null]	London / Birmingham	93.1410288321168
55	1	0		male	42.0	1	0	113789	52.0	[null]	S	[null]	38	New York, NY	93.1410288321168
56	1	0		male	[null]	0	0	PC 17600	30.6958	[null]	C	14	[null]	New York, NY	93.1410288321168
57	1	0		female	50.0	0	0	PC 17595	28.7125	C49	C	[null]	[null]	Paris, France New York, NY	93.1410288321168
58	1	0		male	46.0	0	0	694	26.0	[null]	S	[null]	80	Bennington, VT	93.1410288321168
59	1	0		male	50.0	0	0	113044	26.0	E60	S	[null]	[null]	London	93.1410288321168
60	1	0		male	32.5	0	0	113503	211.5	C132	C	[null]	45	[null]	93.1410288321168
61	1	0		male	58.0	0	0	11771	29.7	B37	C	[null]	258	Buffalo, NY	93.1410288321168
62	1	0		male	41.0	1	0	17464	51.8625	D21	S	[null]	[null]	Southington / Noank, CT	93.1410288321168
63	1	0		male	[null]	0	0	113028	26.55	C124	S	[null]	[null]	Portland, OR	93.1410288321168
64	1	0		male	[null]	0	0	PC 17612	27.7208	[null]	C	[null]	[null]	Chicago, IL	93.1410288321168
65	1	0		male	29.0	0	0	113501	30.0	D6	S	[null]	126	Springfield, MA	93.1410288321168
66	1	0		male	30.0	0	0	113801	45.5	[null]	S	[null]	[null]	London / New York, NY	93.1410288321168
67	1	0		male	30.0	0	0	110469	26.0	C106	S	[null]	[null]	Brockton, MA	93.1410288321168
68	1	0		male	19.0	1	0	113773	53.1	D30	S	[null]	[null]	New York, NY	93.1410288321168
69	1	0		male	46.0	0	0	13050	75.2417	C6	C	[null]	292	Vancouver, BC	93.1410288321168
70	1	0		male	54.0	0	0	17463	51.8625	E46	S	[null]	175	Dorchester, MA	93.1410288321168
71	1	0		male	28.0	1	0	PC 17604	82.1708	[null]	C	[null]	[null]	New York, NY	93.1410288321168
72	1	0		male	65.0	0	0	13509	26.55	E38	S	[null]	249	East Bridgewater, MA	93.1410288321168
73	1	0		male	44.0	2	0	19928	90.0	C78	Q	[null]	230	Fond du Lac, WI	93.1410288321168
74	1	0		male	55.0	0	0	113787	30.5	C30	S	[null]	[null]	Montreal, PQ	93.1410288321168
75	1	0		male	47.0	0	0	113796	42.4	[null]	S	[null]	[null]	Washington, DC	93.1410288321168
76	1	0		male	37.0	0	1	PC 17596	29.7	C118	C	[null]	[null]	Brooklyn, NY	93.1410288321168
77	1	0		male	58.0	0	2	35273	113.275	D48	C	[null]	122	Lexington, MA	93.1410288321168
78	1	0		male	64.0	0	0	693	26.0	[null]	S	[null]	263	Isle of Wight, England	93.1410288321168
79	1	0		male	65.0	0	1	113509	61.9792	B30	C	[null]	234	Providence, RI	93.1410288321168
80	1	0		male	28.5	0	0	PC 17562	27.7208	D43	C	[null]	189	?Havana, Cuba	93.1410288321168
81	1	0		male	[null]	0	0	112052	0.0	[null]	S	[null]	[null]	Belfast	93.1410288321168
82	1	0		male	45.5	0	0	113043	28.5	C124	S	[null]	166	Surbiton Hill, Surrey	93.1410288321168
83	1	0		male	23.0	0	0	12749	93.5	B24	S	[null]	[null]	Montreal, PQ	93.1410288321168
84	1	0		male	29.0	1	0	113776	66.6	C2	S	[null]	[null]	Isleworth, England	93.1410288321168
85	1	0		male	18.0	1	0	PC 17758	108.9	C65	C	[null]	[null]	Madrid, Spain	93.1410288321168
86	1	0		male	47.0	0	0	110465	52.0	C110	S	[null]	207	Worcester, MA	93.1410288321168
87	1	0		male	38.0	0	0	19972	0.0	[null]	S	[null]	[null]	Rotterdam, Netherlands	93.1410288321168
88	1	0		male	22.0	0	0	PC 17760	135.6333	[null]	C	[null]	232	[null]	93.1410288321168
89	1	0		male	[null]	0	0	PC 17757	227.525	[null]	C	[null]	[null]	[null]	93.1410288321168
90	1	0		male	31.0	0	0	PC 17590	50.4958	A24	S	[null]	[null]	Trenton, NJ	93.1410288321168
91	1	0		male	[null]	0	0	113767	50.0	A32	S	[null]	[null]	Seattle, WA	93.1410288321168
92	1	0		male	36.0	0	0	13049	40.125	A10	C	[null]	[null]	Winnipeg, MB	93.1410288321168
93	1	0		male	55.0	1	0	PC 17603	59.4	[null]	C	[null]	[null]	New York, NY	93.1410288321168
94	1	0		male	33.0	0	0	113790	26.55	[null]	S	[null]	109	London	93.1410288321168
95	1	0		male	61.0	1	3	PC 17608	262.375	B57 B59 B63 B66	C	[null]	[null]	Haverford, PA / Cooperstown, NY	93.1410288321168
96	1	0		male	50.0	1	0	13507	55.9	E44	S	[null]	[null]	Duluth, MN	93.1410288321168
97	1	0		male	56.0	0	0	113792	26.55	[null]	S	[null]	[null]	New York, NY	93.1410288321168
98	1	0		male	56.0	0	0	17764	30.6958	A7	C	[null]	[null]	St James, Long Island, NY	93.1410288321168
99	1	0		male	24.0	1	0	13695	60.0	C31	S	[null]	[null]	Huntington, WV	93.1410288321168
100	1	0		male	[null]	0	0	113056	26.0	A19	S	[null]	[null]	Streatham, Surrey	93.1410288321168

Rows: 1-100 | Columns: 15

You can do the same with tablesample:

In [28]:

tb = {"name": ["Badr", "Arash"], "specialty": ["Python", "C++"]}
tb = vp.tablesample(tb)

In [27]:

%%sql
SELECT * FROM :tb;

Execution: 0.014s

Out[27]:

	Abc name Varchar(5)	Abc specialty Varchar(6)
1	Badr	Python
2	Arash	C++

Rows: 1-2 | Columns: 2

Or with a pandas.DataFrame:

In [29]:

titanic_pandas = titanic.to_pandas()
titanic_pandas

Out[29]:

	pclass	survived	name	sex	age	sibsp	parch	ticket	fare	cabin	embarked	boat	body	home.dest
0	1	0	Allison, Miss. Helen Loraine	female	2.000	1	2	113781	151.55000	C22 C26	S	None	NaN	Montreal, PQ / Chesterville, ON
1	1	0	Allison, Mr. Hudson Joshua Creighton	male	30.000	1	2	113781	151.55000	C22 C26	S	None	135.0	Montreal, PQ / Chesterville, ON
2	1	0	Allison, Mrs. Hudson J C (Bessie Waldo Daniels)	female	25.000	1	2	113781	151.55000	C22 C26	S	None	NaN	Montreal, PQ / Chesterville, ON
3	1	0	Andrews, Mr. Thomas Jr	male	39.000	0	0	112050	0.00000	A36	S	None	NaN	Belfast, NI
4	1	0	Artagaveytia, Mr. Ramon	male	71.000	0	0	PC 17609	49.50420	None	C	None	22.0	Montevideo, Uruguay
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1229	3	1	Wilkes, Mrs. James (Ellen Needs)	female	47.000	1	0	363272	7.00000	None	S	None	NaN	None
1230	3	1	Yasbeck, Mrs. Antoni (Selini Alexander)	female	15.000	1	0	2659	14.45420	None	C	None	NaN	None
1231	3	1	de Messemaeker, Mr. Guillaume Joseph	male	36.500	1	0	345572	17.40000	None	S	15	NaN	Tampico, MT
1232	3	1	de Messemaeker, Mrs. Guillaume Joseph (Emma)	female	36.000	1	0	345572	17.40000	None	S	13	NaN	Tampico, MT
1233	3	1	de Mulder, Mr. Theodore	male	30.000	0	0	345774	9.50000	None	S	11	NaN	Belgium Detroit, MI

1234 rows × 14 columns

In [30]:

%%sql
SELECT * FROM :titanic_pandas;

Execution: 0.008s

Out[30]:

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(6,3)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(10,5)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc home.dest Varchar(100)
1	1	0		female	2.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON
2	1	0		male	30.0	1	2	113781	151.55	C22 C26	S	[null]	135	Montreal, PQ / Chesterville, ON
3	1	0		female	25.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON
4	1	0		male	39.0	0	0	112050	0.0	A36	S	[null]	[null]	Belfast, NI
5	1	0		male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22	Montevideo, Uruguay
6	1	0		male	47.0	1	0	PC 17757	227.525	C62 C64	C	[null]	124	New York, NY
7	1	0		male	[null]	0	0	PC 17318	25.925	[null]	S	[null]	[null]	New York, NY
8	1	0		male	24.0	0	1	PC 17558	247.5208	B58 B60	C	[null]	[null]	Montreal, PQ
9	1	0		male	36.0	0	0	13050	75.2417	C6	C	A	[null]	Winnipeg, MN
10	1	0		male	25.0	0	0	13905	26.0	[null]	C	[null]	148	San Francisco, CA
11	1	0		male	45.0	0	0	113784	35.5	T	S	[null]	[null]	Trenton, NJ
12	1	0		male	42.0	0	0	110489	26.55	D22	S	[null]	[null]	London / Winnipeg, MB
13	1	0		male	41.0	0	0	113054	30.5	A21	S	[null]	[null]	Pomeroy, WA
14	1	0		male	48.0	0	0	PC 17591	50.4958	B10	C	[null]	208	Omaha, NE
15	1	0		male	[null]	0	0	112379	39.6	[null]	C	[null]	[null]	Philadelphia, PA
16	1	0		male	45.0	0	0	113050	26.55	B38	S	[null]	[null]	Washington, DC
17	1	0		male	[null]	0	0	113798	31.0	[null]	S	[null]	[null]	[null]
18	1	0		male	33.0	0	0	695	5.0	B51 B53 B55	S	[null]	[null]	New York, NY
19	1	0		male	28.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay
20	1	0		male	17.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay
21	1	0		male	49.0	0	0	19924	26.0	[null]	S	[null]	[null]	Ascot, Berkshire / Rochester, NY
22	1	0		male	36.0	1	0	19877	78.85	C46	S	[null]	172	Little Onn Hall, Staffs
23	1	0		male	46.0	1	0	W.E.P. 5734	61.175	E31	S	[null]	[null]	Amenia, ND
24	1	0		male	[null]	0	0	112051	0.0	[null]	S	[null]	[null]	Liverpool, England / Belfast
25	1	0		male	27.0	1	0	13508	136.7792	C89	C	[null]	[null]	Los Angeles, CA
26	1	0		male	[null]	0	0	110465	52.0	A14	S	[null]	[null]	Stoughton, MA
27	1	0		male	47.0	0	0	5727	25.5875	E58	S	[null]	[null]	Victoria, BC
28	1	0		male	37.0	1	1	PC 17756	83.1583	E52	C	[null]	[null]	Lakewood, NJ
29	1	0		male	[null]	0	0	113791	26.55	[null]	S	[null]	[null]	Roachdale, IN
30	1	0		male	70.0	1	1	WE/P 5735	71.0	B22	S	[null]	269	Milwaukee, WI
31	1	0		male	39.0	1	0	PC 17599	71.2833	C85	C	[null]	[null]	New York, NY
32	1	0		male	31.0	1	0	F.C. 12750	52.0	B71	S	[null]	[null]	Montreal, PQ
33	1	0		male	50.0	1	0	PC 17761	106.425	C86	C	[null]	62	Deephaven, MN / Cedar Rapids, IA
34	1	0		male	39.0	0	0	PC 17580	29.7	A18	C	[null]	133	Philadelphia, PA
35	1	0		female	36.0	0	0	PC 17531	31.6792	A29	C	[null]	[null]	New York, NY
36	1	0		male	[null]	0	0	PC 17483	221.7792	C95	S	[null]	[null]	[null]
37	1	0		male	30.0	0	0	113051	27.75	C111	C	[null]	[null]	New York, NY
38	1	0		male	19.0	3	2	19950	263.0	C23 C25 C27	S	[null]	[null]	Winnipeg, MB
39	1	0		male	64.0	1	4	19950	263.0	C23 C25 C27	S	[null]	[null]	Winnipeg, MB
40	1	0		male	[null]	0	0	113778	26.55	D34	S	[null]	[null]	Westcliff-on-Sea, Essex
41	1	0		male	[null]	0	0	112058	0.0	B102	S	[null]	[null]	[null]
42	1	0		male	37.0	1	0	113803	53.1	C123	S	[null]	[null]	Scituate, MA
43	1	0		male	47.0	0	0	111320	38.5	E63	S	[null]	275	St Anne's-on-Sea, Lancashire
44	1	0		male	24.0	0	0	PC 17593	79.2	B86	C	[null]	[null]	[null]
45	1	0		male	71.0	0	0	PC 17754	34.6542	A5	C	[null]	[null]	New York, NY
46	1	0		male	38.0	0	1	PC 17582	153.4625	C91	S	[null]	147	Winnipeg, MB
47	1	0		male	46.0	0	0	PC 17593	79.2	B82 B84	C	[null]	[null]	New York, NY
48	1	0		male	[null]	0	0	113796	42.4	[null]	S	[null]	[null]	[null]
49	1	0		male	45.0	1	0	36973	83.475	C83	S	[null]	[null]	New York, NY
50	1	0		male	40.0	0	0	112059	0.0	B94	S	[null]	110	[null]
51	1	0		male	55.0	1	1	12749	93.5	B69	S	[null]	307	Montreal, PQ
52	1	0		male	42.0	0	0	113038	42.5	B11	S	[null]	[null]	London / Middlesex
53	1	0		male	[null]	0	0	17463	51.8625	E46	S	[null]	[null]	Brighton, MA
54	1	0		male	55.0	0	0	680	50.0	C39	S	[null]	[null]	London / Birmingham
55	1	0		male	42.0	1	0	113789	52.0	[null]	S	[null]	38	New York, NY
56	1	0		male	[null]	0	0	PC 17600	30.6958	[null]	C	14	[null]	New York, NY
57	1	0		female	50.0	0	0	PC 17595	28.7125	C49	C	[null]	[null]	Paris, France New York, NY
58	1	0		male	46.0	0	0	694	26.0	[null]	S	[null]	80	Bennington, VT
59	1	0		male	50.0	0	0	113044	26.0	E60	S	[null]	[null]	London
60	1	0		male	32.5	0	0	113503	211.5	C132	C	[null]	45	[null]
61	1	0		male	58.0	0	0	11771	29.7	B37	C	[null]	258	Buffalo, NY
62	1	0		male	41.0	1	0	17464	51.8625	D21	S	[null]	[null]	Southington / Noank, CT
63	1	0		male	[null]	0	0	113028	26.55	C124	S	[null]	[null]	Portland, OR
64	1	0		male	[null]	0	0	PC 17612	27.7208	[null]	C	[null]	[null]	Chicago, IL
65	1	0		male	29.0	0	0	113501	30.0	D6	S	[null]	126	Springfield, MA
66	1	0		male	30.0	0	0	113801	45.5	[null]	S	[null]	[null]	London / New York, NY
67	1	0		male	30.0	0	0	110469	26.0	C106	S	[null]	[null]	Brockton, MA
68	1	0		male	19.0	1	0	113773	53.1	D30	S	[null]	[null]	New York, NY
69	1	0		male	46.0	0	0	13050	75.2417	C6	C	[null]	292	Vancouver, BC
70	1	0		male	54.0	0	0	17463	51.8625	E46	S	[null]	175	Dorchester, MA
71	1	0		male	28.0	1	0	PC 17604	82.1708	[null]	C	[null]	[null]	New York, NY
72	1	0		male	65.0	0	0	13509	26.55	E38	S	[null]	249	East Bridgewater, MA
73	1	0		male	44.0	2	0	19928	90.0	C78	Q	[null]	230	Fond du Lac, WI
74	1	0		male	55.0	0	0	113787	30.5	C30	S	[null]	[null]	Montreal, PQ
75	1	0		male	47.0	0	0	113796	42.4	[null]	S	[null]	[null]	Washington, DC
76	1	0		male	37.0	0	1	PC 17596	29.7	C118	C	[null]	[null]	Brooklyn, NY
77	1	0		male	58.0	0	2	35273	113.275	D48	C	[null]	122	Lexington, MA
78	1	0		male	64.0	0	0	693	26.0	[null]	S	[null]	263	Isle of Wight, England
79	1	0		male	65.0	0	1	113509	61.9792	B30	C	[null]	234	Providence, RI
80	1	0		male	28.5	0	0	PC 17562	27.7208	D43	C	[null]	189	?Havana, Cuba
81	1	0		male	[null]	0	0	112052	0.0	[null]	S	[null]	[null]	Belfast
82	1	0		male	45.5	0	0	113043	28.5	C124	S	[null]	166	Surbiton Hill, Surrey
83	1	0		male	23.0	0	0	12749	93.5	B24	S	[null]	[null]	Montreal, PQ
84	1	0		male	29.0	1	0	113776	66.6	C2	S	[null]	[null]	Isleworth, England
85	1	0		male	18.0	1	0	PC 17758	108.9	C65	C	[null]	[null]	Madrid, Spain
86	1	0		male	47.0	0	0	110465	52.0	C110	S	[null]	207	Worcester, MA
87	1	0		male	38.0	0	0	19972	0.0	[null]	S	[null]	[null]	Rotterdam, Netherlands
88	1	0		male	22.0	0	0	PC 17760	135.6333	[null]	C	[null]	232	[null]
89	1	0		male	[null]	0	0	PC 17757	227.525	[null]	C	[null]	[null]	[null]
90	1	0		male	31.0	0	0	PC 17590	50.4958	A24	S	[null]	[null]	Trenton, NJ
91	1	0		male	[null]	0	0	113767	50.0	A32	S	[null]	[null]	Seattle, WA
92	1	0		male	36.0	0	0	13049	40.125	A10	C	[null]	[null]	Winnipeg, MB
93	1	0		male	55.0	1	0	PC 17603	59.4	[null]	C	[null]	[null]	New York, NY
94	1	0		male	33.0	0	0	113790	26.55	[null]	S	[null]	109	London
95	1	0		male	61.0	1	3	PC 17608	262.375	B57 B59 B63 B66	C	[null]	[null]	Haverford, PA / Cooperstown, NY
96	1	0		male	50.0	1	0	13507	55.9	E44	S	[null]	[null]	Duluth, MN
97	1	0		male	56.0	0	0	113792	26.55	[null]	S	[null]	[null]	New York, NY
98	1	0		male	56.0	0	0	17764	30.6958	A7	C	[null]	[null]	St James, Long Island, NY
99	1	0		male	24.0	1	0	13695	60.0	C31	S	[null]	[null]	Huntington, WV
100	1	0		male	[null]	0	0	113056	26.0	A19	S	[null]	[null]	Streatham, Surrey

Rows: 1-100 | Columns: 14

Using a sample loop with a variable:

In [24]:

%sql -c 'DROP TABLE IF EXISTS test;'
%sql -c 'CREATE TABLE test (id INT);'
for i in range(4):
    %sql -c 'INSERT INTO test(id) SELECT :i;'

DROP

Execution: 0.014s

CREATE

Execution: 0.008s

INSERT

Execution: 0.05s

INSERT

Execution: 0.015s

INSERT

Execution: 0.016s

INSERT

Execution: 0.013s

In [25]:

%%sql
SELECT * FROM test;

Execution: 0.005s

Out[25]:

	123 id Integer
1	0
2	1
3	2
4	3

Rows: 1-4 | Column: id | Type: integer

Changing the maximum number of rows/columns to display¶

Use the '-nrows' and '-ncols' option to limit the number of rows and columns displayed:

In [26]:

%%sql -nrows 5 -ncols 2
SELECT * FROM public.titanic;

Execution: 0.008s

Out[26]:

	123 pclass Integer	...	Abc home.dest Varchar(100)
1	1	...	Montreal, PQ / Chesterville, ON
2	1	...	Montreal, PQ / Chesterville, ON
3	1	...	Montreal, PQ / Chesterville, ON
4	1	...	Belfast, NI
5	1	...	Montevideo, Uruguay

Rows: 1-5 | Columns: 14

Exporting results to a JSON or CSV¶

To export the results of a query to a CSV file:

In [27]:

%%sql -o titanic_age_clean.csv
SELECT
    * 
FROM public.titanic
WHERE age IS NOT NULL LIMIT 5;

Execution: 0.008s

Out[27]:

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(6,3)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(10,5)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc home.dest Varchar(100)
1	1	0		female	2.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON
2	1	0		male	30.0	1	2	113781	151.55	C22 C26	S	[null]	135	Montreal, PQ / Chesterville, ON
3	1	0		female	25.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON
4	1	0		male	39.0	0	0	112050	0.0	A36	S	[null]	[null]	Belfast, NI
5	1	0		male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22	Montevideo, Uruguay

Rows: 5 | Columns: 14

In [28]:

file = open("titanic_age_clean.csv", "r")
print(file.read())
file.close()

pclass,survived,name,sex,age,sibsp,parch,ticket,fare,cabin,embarked,boat,body,home.dest
1,0,"Allison, Miss. Helen Loraine","female",2.000,1,2,"113781",151.55000,"C22 C26","S",,,"Montreal, PQ / Chesterville, ON"
1,0,"Allison, Mr. Hudson Joshua Creighton","male",30.000,1,2,"113781",151.55000,"C22 C26","S",,135,"Montreal, PQ / Chesterville, ON"
1,0,"Allison, Mrs. Hudson J C (Bessie Waldo Daniels)","female",25.000,1,2,"113781",151.55000,"C22 C26","S",,,"Montreal, PQ / Chesterville, ON"
1,0,"Andrews, Mr. Thomas Jr","male",39.000,0,0,"112050",0.00000,"A36","S",,,"Belfast, NI"
1,0,"Artagaveytia, Mr. Ramon","male",71.000,0,0,"PC 17609",49.50420,,"C",,22,"Montevideo, Uruguay"

To export query results to a JSON file:

In [29]:

%%sql -o titanic_age_clean.json
SELECT 
    * 
FROM public.titanic
WHERE age IS NOT NULL LIMIT 5;

Execution: 0.008s

Out[29]:

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(6,3)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(10,5)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc home.dest Varchar(100)
1	1	0		female	2.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON
2	1	0		male	30.0	1	2	113781	151.55	C22 C26	S	[null]	135	Montreal, PQ / Chesterville, ON
3	1	0		female	25.0	1	2	113781	151.55	C22 C26	S	[null]	[null]	Montreal, PQ / Chesterville, ON
4	1	0		male	39.0	0	0	112050	0.0	A36	S	[null]	[null]	Belfast, NI
5	1	0		male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22	Montevideo, Uruguay

Rows: 5 | Columns: 14

In [30]:

file = open("titanic_age_clean.json", "r")
print(file.read())
file.close()

[
{"pclass": 1, "survived": 0, "name": "Allison, Miss. Helen Loraine", "sex": "female", "age": 2.000, "sibsp": 1, "parch": 2, "ticket": "113781", "fare": 151.55000, "cabin": "C22 C26", "embarked": "S", "home.dest": "Montreal, PQ / Chesterville, ON"},
{"pclass": 1, "survived": 0, "name": "Allison, Mr. Hudson Joshua Creighton", "sex": "male", "age": 30.000, "sibsp": 1, "parch": 2, "ticket": "113781", "fare": 151.55000, "cabin": "C22 C26", "embarked": "S", "body": 135, "home.dest": "Montreal, PQ / Chesterville, ON"},
{"pclass": 1, "survived": 0, "name": "Allison, Mrs. Hudson J C (Bessie Waldo Daniels)", "sex": "female", "age": 25.000, "sibsp": 1, "parch": 2, "ticket": "113781", "fare": 151.55000, "cabin": "C22 C26", "embarked": "S", "home.dest": "Montreal, PQ / Chesterville, ON"},
{"pclass": 1, "survived": 0, "name": "Andrews, Mr. Thomas Jr", "sex": "male", "age": 39.000, "sibsp": 0, "parch": 0, "ticket": "112050", "fare": 0.00000, "cabin": "A36", "embarked": "S", "home.dest": "Belfast, NI"},
{"pclass": 1, "survived": 0, "name": "Artagaveytia, Mr. Ramon", "sex": "male", "age": 71.000, "sibsp": 0, "parch": 0, "ticket": "PC 17609", "fare": 49.50420, "embarked": "C", "body": 22, "home.dest": "Montevideo, Uruguay"},
]

Executing SQL files¶

Executing commands from a SQL file:

In [31]:

file = open("query.sql", "w+")
file.write("SELECT version();")
file.close()

In [32]:

%sql -f query.sql

Execution: 0.006s

Out[32]:

	Abc Varchar(128)
1

Rows: 1-1 | Column: version | Type: varchar(128)

Connect to External Database¶

Since v0.12.0, it is possible to connect to external Databases using the connection symbol. Detailled examples are available in this notebook.