verticapy.jupyter.extensions.sql_magic.sql_magic#

verticapy.jupyter.extensions.sql_magic.sql_magic(line: str, cell: str | None = None, local_ns: dict | None = None) → vDataFrame#

Executes SQL queries in the Jupyter cell.

Warning

In the case of profiling (using PROFILE keywords), the query will be executed twice: once for profiling and another time to build the vDataFrame.

Parameters#

-c / –commandstr, optional: SQL Command to execute.
-f / –filestr, optional: Input File. You can use this option if you want to execute the input file.
-ncolsint, optional: Maximum number of columns to display.
-nrowsint, optional: Maximum number of rows to display.
-o / –outputstr, optional: Output File. You can use this option if you want to export the result of the query to the CSV or JSON format.

Returns#

vDataFrame: Result of the query

Examples#

The following examples demonstrate:

Setting up the environment
Using 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 a connection, create one:

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:

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

Load the extension:

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

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:

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

Execution: 0.006s

	123 survived Integer	123 avg_fare Float(22)	123 avg_age Float(22)
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:

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

Execution: 0.006s

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

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

You can use a single cell for multiple queries:

Warning

Don’t forget to include a semicolon at the end of each query.

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

Execution: 0.05s

	Abc name Varchar(10)
1	Badr Ouali

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

To add comments to a query, use one of the following comment syntaxes:

Warning

Vertica uses ‘/’ and ‘/’ for both comments and query hints. Whenever possible, use ‘–’ to avoid conflicts.

%%sql
-- Comment Test
/* My Vertica Version */
SELECT version(); -- Select my current version

Execution: 0.005s

	Abc Varchar(128)
1

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

Get the vDataFrame of a query#

Results of a SQL Magic query are stored in a vDataFrame, which is assigned to a temporary variable called ‘_’. You can assign this temporary variable to a new variable to save your results.

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

Execution: 0.007s

Assign the results to a new variable:

titanic_clean = _
display(titanic_clean)

	123 age Numeric(8)	123 fare Numeric(12)	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, allowing you to call vDataFrame methods:

titanic_clean["age"].max()
Out[1]: 80.0

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.

import verticapy.sql.functions as vpf

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

	123 pclass Integer	123 avg_fare Float(22)
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:

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

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(8)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(12)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc home.dest Varchar(100)	123 avg_fare Float(22)
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 a TableSample:

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

%%sql
SELECT * FROM :tb;

Execution: 0.014s

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

Rows: 1-2 | Columns: 2

And with a pandas.DataFrame:

titanic_pandas = titanic.to_pandas()

titanic_pandas
Out[3]: 
      pclass  survived  ...   body                        home.dest
0          1         0  ...    NaN  Montreal, PQ / Chesterville, ON
1          1         0  ...  135.0  Montreal, PQ / Chesterville, ON
2          1         0  ...    NaN  Montreal, PQ / Chesterville, ON
3          1         0  ...    NaN                      Belfast, NI
4          1         0  ...   22.0              Montevideo, Uruguay
...      ...       ...  ...    ...                              ...
1229       3         1  ...    NaN                             None
1230       3         1  ...    NaN                             None
1231       3         1  ...    NaN                      Tampico, MT
1232       3         1  ...    NaN                      Tampico, MT
1233       3         1  ...    NaN              Belgium Detroit, MI

[1234 rows x 14 columns]

%%sql
SELECT * FROM :titanic_pandas;

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(10)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(13)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Numeric(9)	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.0	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.0	Montevideo, Uruguay
6	1	0		male	47.0	1	0	PC 17757	227.525	C62 C64	C	[null]	124.0	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.0	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.0	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.0	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.0	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.0	Deephaven, MN / Cedar Rapids, IA
34	1	0		male	39.0	0	0	PC 17580	29.7	A18	C	[null]	133.0	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.0	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.0	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.0	[null]
51	1	0		male	55.0	1	1	12749	93.5	B69	S	[null]	307.0	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.0	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.0	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.0	[null]
61	1	0		male	58.0	0	0	11771	29.7	B37	C	[null]	258.0	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.0	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.0	Vancouver, BC
70	1	0		male	54.0	0	0	17463	51.8625	E46	S	[null]	175.0	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.0	East Bridgewater, MA
73	1	0		male	44.0	2	0	19928	90.0	C78	Q	[null]	230.0	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.0	Lexington, MA
78	1	0		male	64.0	0	0	693	26.0	[null]	S	[null]	263.0	Isle of Wight, England
79	1	0		male	65.0	0	1	113509	61.9792	B30	C	[null]	234.0	Providence, RI
80	1	0		male	28.5	0	0	PC 17562	27.7208	D43	C	[null]	189.0	?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.0	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.0	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.0	[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.0	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

You can also use a sample loop with a variable:

Note

VerticaPy will store the object in a temporary local table before executing the overall query, which facilitates integration with in-memory objects.

%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

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

%%sql
SELECT * FROM test;

Execution: 0.005s

	123 id Integer
1	0
2	1
3	2
4	3

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

Change the maximum number of rows/columns to display#

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

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

Execution: 0.008s

	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

Export results to a JSON or CSV file#

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

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

Execution: 0.008s

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(8)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(12)	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

file = open("titanic_age_clean.csv", "r")

print(file.read())
"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"

file.close()

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

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

Execution: 0.008s

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(8)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(12)	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

file = open("titanic_age_clean.json", "r")

print(file.read())
[
{"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"}
]

file.close()

Execute SQL files#

To execute commands from a SQL file, use the following syntax:

file = open("query.sql", "w+")

file.write("SELECT version();")
Out[12]: 17

file.close()

Using the -f option, we can easily read SQL files:

%sql -f query.sql

Execution: 0.006s

	Abc Varchar(128)
1

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

Connect to an 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.