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.
-stdin: bool, optional: If set to False and you’re trying to perform a local copy, the parser will not replace the file name with STDIN, simplifying the ingestion.

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-virginica
2	Iris-versicolor
3	Iris-setosa

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	71.0	49.5042	1
2	45.0	35.5	1
3	17.0	47.1	1
4	27.0	136.7792	1
5	37.0	83.1583	1
6	31.0	52.0	1
7	50.0	106.425	1
8	36.0	31.6792	1
9	37.0	53.1	1
10	24.0	79.2	1
11	45.0	83.475	1
12	40.0	0.0	1
13	42.0	42.5	1
14	42.0	52.0	1
15	29.0	30.0	1
16	46.0	75.2417	1
17	54.0	51.8625	1
18	47.0	42.4	1
19	58.0	113.275	1
20	45.5	28.5	1
21	29.0	66.6	1
22	47.0	52.0	1
23	38.0	0.0	1
24	22.0	135.6333	1
25	31.0	50.4958	1
26	50.0	55.9	1
27	56.0	30.6958	1
28	57.0	146.5208	1
29	63.0	221.7792	1
30	61.0	32.3208	1
31	21.0	77.2875	1
32	51.0	61.3792	1
33	63.0	77.9583	1
34	32.0	76.2917	1
35	58.0	26.55	1
36	44.0	27.7208	1
37	41.0	134.5	1
38	53.0	27.4458	1
39	36.0	512.3292	1
40	58.0	512.3292	1
41	11.0	120.0	1
42	76.0	78.85	1
43	39.0	83.1583	1
44	27.0	52.0	1
45	35.0	211.5	1
46	22.0	59.4	1
47	25.0	55.4417	1
48	48.0	76.7292	1
49	35.0	83.475	1
50	27.0	76.7292	1
51	24.0	83.1583	1
52	52.0	93.5	1
53	44.0	57.9792	1
54	15.0	211.3375	1
55	30.0	57.75	1
56	31.0	113.275	1
57	39.0	108.9	1
58	22.0	61.9792	1
59	52.0	30.5	1
60	43.0	211.3375	1
61	33.0	86.5	1
62	45.0	134.5	1
63	40.0	134.5	1
64	48.0	52.0	1
65	35.0	512.3292	1
66	60.0	75.25	1
67	21.0	61.3792	1
68	23.0	10.5	2
69	28.0	26.0	2
70	60.0	39.0	2
71	44.0	26.0	2
72	29.0	26.0	2
73	18.0	73.5	2
74	18.0	73.5	2
75	54.0	26.0	2
76	18.0	13.0	2
77	36.0	13.0	2
78	34.0	21.0	2
79	21.0	11.5	2
80	21.0	11.5	2
81	24.0	13.0	2
82	34.0	13.0	2
83	30.0	13.0	2
84	44.0	13.0	2
85	49.0	65.0	2
86	21.0	73.5	2
87	21.0	73.5	2
88	60.0	26.0	2
89	24.0	31.5	2
90	22.0	31.5	2
91	35.0	12.35	2
92	31.0	10.5	2
93	36.0	12.875	2
94	40.0	16.0	2
95	48.0	13.0	2
96	23.0	10.5	2
97	34.0	13.0	2
98	34.0	21.0	2
99	35.0	10.5	2
100	41.0	15.0458	2

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	2	21.9666833333333
2	3	12.8090323529412
3	1	93.1410288321168

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 Varchar(100)	123 avg_fare Float(22)
1	1	0		male	47.0	1	0	PC 17757	227.525	C62 C64	C	[null]	124		93.1410288321168
2	1	0		male	[null]	0	0	PC 17318	25.925	[null]	S	[null]	[null]		93.1410288321168
3	1	0		male	24.0	0	1	PC 17558	247.5208	B58 B60	C	[null]	[null]		93.1410288321168
4	1	0		male	25.0	0	0	13905	26.0	[null]	C	[null]	148		93.1410288321168
5	1	0		male	42.0	0	0	110489	26.55	D22	S	[null]	[null]		93.1410288321168
6	1	0		male	45.0	0	0	113050	26.55	B38	S	[null]	[null]		93.1410288321168
7	1	0		male	46.0	1	0	W.E.P. 5734	61.175	E31	S	[null]	[null]		93.1410288321168
8	1	0		male	[null]	0	0	113791	26.55	[null]	S	[null]	[null]		93.1410288321168
9	1	0		male	39.0	0	0	PC 17580	29.7	A18	C	[null]	133		93.1410288321168
10	1	0		male	64.0	1	4	19950	263.0	C23 C25 C27	S	[null]	[null]		93.1410288321168
11	1	0		male	[null]	0	0	113778	26.55	D34	S	[null]	[null]		93.1410288321168
12	1	0		male	71.0	0	0	PC 17754	34.6542	A5	C	[null]	[null]		93.1410288321168
13	1	0		male	[null]	0	0	113796	42.4	[null]	S	[null]	[null]		93.1410288321168
14	1	0		male	32.5	0	0	113503	211.5	C132	C	[null]	45		93.1410288321168
15	1	0		male	41.0	1	0	17464	51.8625	D21	S	[null]	[null]		93.1410288321168
16	1	0		male	[null]	0	0	113028	26.55	C124	S	[null]	[null]		93.1410288321168
17	1	0		male	28.0	1	0	PC 17604	82.1708	[null]	C	[null]	[null]		93.1410288321168
18	1	0		male	55.0	0	0	113787	30.5	C30	S	[null]	[null]		93.1410288321168
19	1	0		male	37.0	0	1	PC 17596	29.7	C118	C	[null]	[null]		93.1410288321168
20	1	0		male	64.0	0	0	693	26.0	[null]	S	[null]	263		93.1410288321168
21	1	0		male	28.5	0	0	PC 17562	27.7208	D43	C	[null]	189		93.1410288321168
22	1	0		male	[null]	0	0	PC 17757	227.525	[null]	C	[null]	[null]		93.1410288321168
23	1	0		male	56.0	0	0	113792	26.55	[null]	S	[null]	[null]		93.1410288321168
24	1	0		male	24.0	1	0	13695	60.0	C31	S	[null]	[null]		93.1410288321168
25	1	0		male	49.0	1	1	17421	110.8833	C68	C	[null]	[null]		93.1410288321168
26	1	0		male	47.0	0	0	36967	34.0208	D46	S	[null]	[null]		93.1410288321168
27	1	0		male	64.0	1	0	110813	75.25	D37	C	[null]	[null]		93.1410288321168
28	1	1		male	0.92	1	2	113781	151.55	C22 C26	S	11	[null]		93.1410288321168
29	1	1		female	53.0	2	0	11769	51.4792	C101	S	D	[null]		93.1410288321168
30	1	1		female	18.0	1	0	PC 17757	227.525	C62 C64	C	4	[null]		93.1410288321168
31	1	1		male	80.0	0	0	27042	30.0	A23	S	B	[null]		93.1410288321168
32	1	1		male	37.0	1	1	11751	52.5542	D35	S	5	[null]		93.1410288321168
33	1	1		male	26.0	0	0	111369	30.0	C148	C	5	[null]		93.1410288321168
34	1	1		female	42.0	0	0	PC 17757	227.525	[null]	C	4	[null]		93.1410288321168
35	1	1		male	25.0	1	0	11967	91.0792	B49	C	7	[null]		93.1410288321168
36	1	1		female	35.0	0	0	PC 17760	135.6333	C99	S	8	[null]		93.1410288321168
37	1	1		female	45.0	0	0	PC 17608	262.375	[null]	C	4	[null]		93.1410288321168
38	1	1		female	22.0	0	1	113505	55.0	E33	S	6	[null]		93.1410288321168
39	1	1		female	60.0	0	0	11813	76.2917	D15	C	8	[null]		93.1410288321168
40	1	1		female	14.0	1	2	113760	120.0	B96 B98	S	4	[null]		93.1410288321168
41	1	1		female	[null]	0	0	17770	27.7208	[null]	C	5	[null]		93.1410288321168
42	1	1		male	45.0	0	0	PC 17594	29.7	A9	C	7	[null]		93.1410288321168
43	1	1		female	22.0	0	0	113781	151.55	[null]	S	11	[null]		93.1410288321168
44	1	1		female	64.0	0	2	PC 17756	83.1583	E45	C	14	[null]		93.1410288321168
45	1	1		female	36.0	0	2	WE/P 5735	71.0	B22	S	7	[null]		93.1410288321168
46	1	1		female	27.0	1	1	PC 17558	247.5208	B58 B60	C	6	[null]		93.1410288321168
47	1	1		female	54.0	1	0	36947	78.2667	D20	C	4	[null]		93.1410288321168
48	1	1		male	43.0	1	0	17765	27.7208	D40	C	5	[null]		93.1410288321168
49	1	1		female	22.0	0	2	13568	49.5	B39	C	5	[null]		93.1410288321168
50	1	1		female	35.0	1	0	113803	53.1	C123	S	D	[null]		93.1410288321168
51	1	1		male	53.0	0	0	113780	28.5	C51	C	B	[null]		93.1410288321168
52	1	1		female	19.0	0	0	112053	30.0	B42	S	3	[null]		93.1410288321168
53	1	1		female	58.0	0	1	PC 17582	153.4625	C125	S	3	[null]		93.1410288321168
54	1	1		male	23.0	0	1	PC 17759	63.3583	D10 D12	C	7	[null]		93.1410288321168
55	1	1		male	25.0	1	0	11765	55.4417	E50	C	5	[null]		93.1410288321168
56	1	1		male	[null]	0	0	16988	30.0	D45	S	3	[null]		93.1410288321168
57	1	1		female	35.0	1	0	113789	52.0	[null]	S	8	[null]		93.1410288321168
58	1	1		female	[null]	1	0	17464	51.8625	D21	S	8	[null]		93.1410288321168
59	1	1		male	42.0	1	0	11753	52.5542	D19	S	5	[null]		93.1410288321168
60	1	1		female	45.0	1	0	11753	52.5542	D19	S	5	[null]		93.1410288321168
61	1	1		female	39.0	0	0	24160	211.3375	[null]	S	2	[null]		93.1410288321168
62	1	1		female	16.0	0	1	PC 17592	39.4	D28	S	9	[null]		93.1410288321168
63	1	1		female	21.0	0	0	13502	77.9583	D9	S	10	[null]		93.1410288321168
64	1	1		female	18.0	1	0	113773	53.1	D30	S	10	[null]		93.1410288321168
65	1	1		male	36.0	0	0	PC 17473	26.2875	E25	S	7	[null]		93.1410288321168
66	1	1		female	37.0	1	0	19928	90.0	C78	Q	14	[null]		93.1410288321168
67	1	1		male	[null]	0	0	F.C. 12998	25.7417	[null]	C	7	[null]		93.1410288321168
68	1	1		female	22.0	1	0	113776	66.6	C2	S	8	[null]		93.1410288321168
69	1	1		female	30.0	0	0	12749	93.5	B73	S	3	[null]		93.1410288321168
70	1	1		female	33.0	0	0	PC 17613	27.7208	A11	C	11	[null]		93.1410288321168
71	1	1		female	54.0	1	0	PC 17603	59.4	[null]	C	6	[null]		93.1410288321168
72	1	1		female	18.0	2	2	PC 17608	262.375	B57 B59 B63 B66	C	4	[null]		93.1410288321168
73	1	1		female	48.0	1	3	PC 17608	262.375	B57 B59 B63 B66	C	4	[null]		93.1410288321168
74	1	1		male	35.0	0	0	PC 17475	26.2875	E24	S	5	[null]		93.1410288321168
75	1	1		female	23.0	1	0	21228	82.2667	B45	S	7	[null]		93.1410288321168
76	1	1		female	43.0	1	0	11778	55.4417	C116	C	5	[null]		93.1410288321168
77	1	1		female	39.0	1	1	110413	79.65	E67	S	8	[null]		93.1410288321168
78	1	1		female	39.0	1	1	17421	110.8833	C68	C	4	[null]		93.1410288321168
79	1	1		female	55.0	0	0	PC 17760	135.6333	C32	C	8	[null]		93.1410288321168
80	1	1		female	31.0	0	2	36928	164.8667	C7	S	8	[null]		93.1410288321168
81	2	0		male	30.0	1	0	P/PP 3381	24.0	[null]	C	[null]	[null]		21.9666833333333
82	2	0		male	30.0	0	0	248744	13.0	[null]	S	[null]	[null]		21.9666833333333
83	2	0		male	57.0	0	0	244346	13.0	[null]	S	[null]	[null]		21.9666833333333
84	2	0		male	51.0	0	0	S.O.P. 1166	12.525	[null]	S	[null]	174		21.9666833333333
85	2	0		male	[null]	0	0	239853	0.0	[null]	S	[null]	[null]		21.9666833333333
86	2	0		male	52.0	0	0	248731	13.5	[null]	S	[null]	130		21.9666833333333
87	2	0		male	37.0	1	0	SC/AH 29037	26.0	[null]	S	[null]	17		21.9666833333333
88	2	0		female	29.0	1	0	SC/AH 29037	26.0	[null]	S	[null]	[null]		21.9666833333333
89	2	0		male	29.0	0	0	W./C. 14263	10.5	[null]	S	[null]	[null]		21.9666833333333
90	2	0		female	30.0	0	0	237249	13.0	[null]	S	[null]	[null]		21.9666833333333
91	2	0		male	[null]	0	0	239853	0.0	[null]	S	[null]	[null]		21.9666833333333
92	2	0		male	17.0	0	0	S.O.C. 14879	73.5	[null]	S	[null]	[null]		21.9666833333333
93	2	0		male	18.0	0	0	C.A. 15185	10.5	[null]	S	[null]	[null]		21.9666833333333
94	2	0		male	24.0	0	0	248726	13.5	[null]	S	[null]	297		21.9666833333333
95	2	0		male	30.0	0	0	250646	13.0	[null]	S	[null]	305		21.9666833333333
96	2	0		male	52.0	0	0	250647	13.0	[null]	S	[null]	19		21.9666833333333
97	2	0		female	18.0	1	1	250650	13.0	[null]	S	[null]	[null]		21.9666833333333
98	2	0		male	23.0	2	1	29104	11.5	[null]	S	[null]	[null]		21.9666833333333
99	2	0		male	36.0	0	0	242963	13.0	[null]	S	[null]	[null]		21.9666833333333
100	2	0		male	44.0	1	0	26707	26.0	[null]	S	[null]	[null]		21.9666833333333

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                                             name     sex     age  sibsp  parch    ticket       fare cabin embarked  boat  body            home.dest
0          1         0                          Artagaveytia, Mr. Ramon    male  71.000      0      0  PC 17609   49.50420  None        C  None  22.0  Montevideo, Uruguay
1          1         0                     Blackwell, Mr. Stephen Weart    male  45.000      0      0    113784   35.50000     T        S  None   NaN          Trenton, NJ
2          1         0                            Cairns, Mr. Alexander    male    None      0      0    113798   31.00000  None        S  None   NaN                 None
3          1         0                           Carrau, Mr. Jose Pedro    male  17.000      0      0    113059   47.10000  None        S  None   NaN  Montevideo, Uruguay
4          1         0                         Clark, Mr. Walter Miller    male  27.000      1      0     13508  136.77920   C89        C  None   NaN      Los Angeles, CA
...      ...       ...                                              ...     ...     ...    ...    ...       ...        ...   ...      ...   ...   ...                  ...
1229       3         1          Touma, Mrs. Darwis (Hanne Youssef Razi)  female  29.000      0      2      2650   15.24580  None        C     C   NaN                 None
1230       3         1                           Turkula, Mrs. (Hedwig)  female  63.000      0      0      4134    9.58750  None        S    15   NaN                 None
1231       3         1  Whabee, Mrs. George Joseph (Shawneene Abi-Saab)  female  38.000      0      0      2688    7.22920  None        C     C   NaN                 None
1232       3         1             de Messemaeker, Mr. Guillaume Joseph    male  36.500      1      0    345572   17.40000  None        S    15   NaN          Tampico, MT
1233       3         1     de Messemaeker, Mrs. Guillaume Joseph (Emma)  female  36.000      1      0    345572   17.40000  None        S    13   NaN          Tampico, MT

[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 Varchar(100)
1	1	0		female	25.0	1	2	113781	151.55	C22 C26	S	[null]	[null]
2	1	0		male	47.0	1	0	PC 17757	227.525	C62 C64	C	[null]	124.0
3	1	0		male	45.0	0	0	113784	35.5	T	S	[null]	[null]
4	1	0		male	42.0	0	0	110489	26.55	D22	S	[null]	[null]
5	1	0		male	17.0	0	0	113059	47.1	[null]	S	[null]	[null]
6	1	0		male	31.0	1	0	F.C. 12750	52.0	B71	S	[null]	[null]
7	1	0		male	39.0	0	0	PC 17580	29.7	A18	C	[null]	133.0
8	1	0		male	[null]	0	0	113796	42.4	[null]	S	[null]	[null]
9	1	0		male	42.0	1	0	113789	52.0	[null]	S	[null]	38.0
10	1	0		male	50.0	0	0	113044	26.0	E60	S	[null]	[null]
11	1	0		male	58.0	0	0	11771	29.7	B37	C	[null]	258.0
12	1	0		male	41.0	1	0	17464	51.8625	D21	S	[null]	[null]
13	1	0		male	29.0	0	0	113501	30.0	D6	S	[null]	126.0
14	1	0		male	19.0	1	0	113773	53.1	D30	S	[null]	[null]
15	1	0		male	54.0	0	0	17463	51.8625	E46	S	[null]	175.0
16	1	0		male	65.0	0	0	13509	26.55	E38	S	[null]	249.0
17	1	0		male	55.0	0	0	113787	30.5	C30	S	[null]	[null]
18	1	0		male	64.0	0	0	693	26.0	[null]	S	[null]	263.0
19	1	0		male	45.5	0	0	113043	28.5	C124	S	[null]	166.0
20	1	0		male	[null]	0	0	PC 17757	227.525	[null]	C	[null]	[null]
21	1	0		male	36.0	0	0	13049	40.125	A10	C	[null]	[null]
22	1	0		male	33.0	0	0	113790	26.55	[null]	S	[null]	109.0
23	1	0		male	61.0	1	3	PC 17608	262.375	B57 B59 B63 B66	C	[null]	[null]
24	1	0		male	50.0	1	0	13507	55.9	E44	S	[null]	[null]
25	1	0		male	56.0	0	0	113792	26.55	[null]	S	[null]	[null]
26	1	0		male	[null]	0	0	113056	26.0	A19	S	[null]	[null]
27	1	0		male	57.0	1	0	PC 17569	146.5208	B78	C	[null]	[null]
28	1	0		female	63.0	1	0	PC 17483	221.7792	C55 C57	S	[null]	[null]
29	1	0		male	64.0	1	0	110813	75.25	D37	C	[null]	[null]
30	1	0		male	21.0	0	1	35281	77.2875	D26	S	[null]	169.0
31	1	0		male	27.0	0	2	113503	211.5	C82	C	[null]	[null]
32	1	1		male	0.92	1	2	113781	151.55	C22 C26	S	11	[null]
33	1	1		male	48.0	0	0	19952	26.55	E12	S	3	[null]
34	1	1		female	32.0	0	0	11813	76.2917	D15	C	8	[null]
35	1	1		female	47.0	1	1	11751	52.5542	D35	S	5	[null]
36	1	1		female	59.0	2	0	11769	51.4792	C101	S	D	[null]
37	1	1		female	53.0	0	0	PC 17606	27.4458	[null]	C	6	[null]
38	1	1		male	36.0	1	2	113760	120.0	B96 B98	S	C	[null]
39	1	1		female	33.0	1	0	113806	53.1	E8	S	5	[null]
40	1	1		female	[null]	0	1	113505	55.0	E33	S	6	[null]
41	1	1		female	64.0	1	1	112901	26.55	B26	S	7	[null]
42	1	1		male	51.0	0	0	113055	26.55	E17	S	5 9	[null]
43	1	1		female	27.0	1	2	F.C. 12750	52.0	B71	S	3	[null]
44	1	1		female	54.0	1	1	33638	81.8583	A34	S	5	[null]
45	1	1		female	27.0	1	1	PC 17558	247.5208	B58 B60	C	6	[null]
46	1	1		female	48.0	1	0	PC 17761	106.425	C86	C	2	[null]
47	1	1		female	[null]	0	0	PC 17598	31.6833	[null]	S	7	[null]
48	1	1		female	28.0	3	2	19950	263.0	C23 C25 C27	S	10	[null]
49	1	1		female	60.0	1	4	19950	263.0	C23 C25 C27	S	10	[null]
50	1	1		male	50.0	2	0	PC 17611	133.65	[null]	S	5	[null]
51	1	1		female	[null]	1	0	PC 17611	133.65	[null]	S	5	[null]
52	1	1		female	35.0	0	0	113503	211.5	C130	C	4	[null]
53	1	1		female	22.0	0	1	112378	59.4	[null]	C	7	[null]
54	1	1		female	45.0	0	1	112378	59.4	[null]	C	7	[null]
55	1	1		male	25.0	1	0	11765	55.4417	E50	C	5	[null]
56	1	1		female	16.0	0	1	111361	57.9792	B18	C	4	[null]
57	1	1		female	51.0	1	0	13502	77.9583	D11	S	10	[null]
58	1	1		female	35.0	1	0	113789	52.0	[null]	S	8	[null]
59	1	1		male	38.0	1	0	19943	90.0	C93	S	D	[null]
60	1	1		female	35.0	1	0	19943	90.0	C93	S	D	[null]
61	1	1		female	45.0	1	0	11753	52.5542	D19	S	5	[null]
62	1	1		female	49.0	0	0	17465	25.9292	D17	S	8	[null]
63	1	1		female	16.0	0	1	PC 17592	39.4	D28	S	9	[null]
64	1	1		female	15.0	0	1	24160	211.3375	B5	S	2	[null]
65	1	1		female	[null]	1	0	PC 17604	82.1708	[null]	C	6	[null]
66	1	1		female	22.0	0	1	113509	61.9792	B36	C	5	[null]
67	1	1		female	33.0	0	0	110152	86.5	B77	S	8	[null]
68	1	1		female	18.0	2	2	PC 17608	262.375	B57 B59 B63 B66	C	4	[null]
69	1	1		male	[null]	0	0	111163	26.0	[null]	S	1	[null]
70	1	1		male	28.0	0	0	113788	35.5	A6	S	7	[null]
71	1	1		female	18.0	1	0	13695	60.0	C31	S	6	[null]
72	1	1		female	40.0	1	1	16966	134.5	E34	C	3	[null]
73	1	1		female	43.0	1	0	11778	55.4417	C116	C	5	[null]
74	1	1		female	48.0	0	0	17466	25.9292	D17	S	8	[null]
75	1	1		female	18.0	0	2	110413	79.65	E68	S	8	[null]
76	1	1		male	17.0	0	2	17421	110.8833	C70	C	B	[null]
77	1	1		female	55.0	0	0	PC 17760	135.6333	C32	C	8	[null]
78	1	1		female	31.0	0	2	36928	164.8667	C7	S	8	[null]
79	2	0		male	18.0	0	0	231945	11.5	[null]	S	[null]	[null]
80	2	0		male	18.0	0	0	29108	11.5	[null]	S	[null]	[null]
81	2	0		male	23.0	0	0	C.A. 31030	10.5	[null]	S	[null]	[null]
82	2	0		male	51.0	0	0	S.O.P. 1166	12.525	[null]	S	[null]	174.0
83	2	0		male	28.0	0	0	244358	26.0	[null]	S	[null]	[null]
84	2	0		male	60.0	1	1	29750	39.0	[null]	S	[null]	[null]
85	2	0		male	19.0	0	0	28424	13.0	[null]	S	[null]	18.0
86	2	0		male	37.0	1	0	SC/AH 29037	26.0	[null]	S	[null]	17.0
87	2	0		female	30.0	0	0	237249	13.0	[null]	S	[null]	[null]
88	2	0		male	25.0	0	0	C.A. 31029	31.5	[null]	S	[null]	[null]
89	2	0		male	42.0	1	1	28220	32.5	[null]	S	[null]	[null]
90	2	0		male	40.0	1	0	2926	26.0	[null]	S	[null]	286.0
91	2	0		male	35.0	0	0	239865	26.0	[null]	S	[null]	322.0
92	2	0		male	34.0	1	0	28664	21.0	[null]	S	[null]	[null]
93	2	0		male	16.0	0	0	239865	26.0	[null]	S	[null]	[null]
94	2	0		male	26.0	0	0	31028	10.5	[null]	S	[null]	[null]
95	2	0		male	30.0	0	0	W/C 14208	10.5	[null]	S	[null]	[null]
96	2	0		male	24.0	2	0	S.O.C. 14879	73.5	[null]	S	[null]	[null]
97	2	0		male	36.0	0	0	242963	13.0	[null]	S	[null]	[null]
98	2	0		male	50.0	0	0	250643	13.0	[null]	S	[null]	149.0
99	2	0		male	44.0	1	0	26707	26.0	[null]	S	[null]	[null]
100	2	0		male	24.0	2	0	C.A. 31029	31.5	[null]	S	[null]	[null]

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

DROP
<IPython.core.display.HTML object>
CREATE
<IPython.core.display.HTML object>
INSERT
<IPython.core.display.HTML object>
INSERT
<IPython.core.display.HTML object>
INSERT
<IPython.core.display.HTML object>
INSERT
<IPython.core.display.HTML object>

%%sql
SELECT * FROM test;

Execution: 0.005s

	123 id Integer
1	2
2	3
3	0
4	1

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	...	Montevideo, Uruguay
2	1	...	Trenton, NJ
3	1	...	[null]
4	1	...	Montevideo, Uruguay
5	1	...	Los Angeles, CA

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 name 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	Artagaveytia, Mr. Ramon	male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22	Montevideo, Uruguay
2	1	0	Blackwell, Mr. Stephen Weart	male	45.0	0	0	113784	35.5	T	S	[null]	[null]	Trenton, NJ
3	1	0	Carrau, Mr. Jose Pedro	male	17.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay
4	1	0	Clark, Mr. Walter Miller	male	27.0	1	0	13508	136.7792	C89	C	[null]	[null]	Los Angeles, CA
5	1	0	Compton, Mr. Alexander Taylor Jr	male	37.0	1	1	PC 17756	83.1583	E52	C	[null]	[null]	Lakewood, NJ

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,"Artagaveytia, Mr. Ramon","male",71.000,0,0,"PC 17609",49.50420,,"C",,22,"Montevideo, Uruguay"
1,0,"Blackwell, Mr. Stephen Weart","male",45.000,0,0,"113784",35.50000,"T","S",,,"Trenton, NJ"
1,0,"Carrau, Mr. Jose Pedro","male",17.000,0,0,"113059",47.10000,,"S",,,"Montevideo, Uruguay"
1,0,"Clark, Mr. Walter Miller","male",27.000,1,0,"13508",136.77920,"C89","C",,,"Los Angeles, CA"
1,0,"Compton, Mr. Alexander Taylor Jr","male",37.000,1,1,"PC 17756",83.15830,"E52","C",,,"Lakewood, NJ"

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 name 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	Artagaveytia, Mr. Ramon	male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22	Montevideo, Uruguay
2	1	0	Blackwell, Mr. Stephen Weart	male	45.0	0	0	113784	35.5	T	S	[null]	[null]	Trenton, NJ
3	1	0	Carrau, Mr. Jose Pedro	male	17.0	0	0	113059	47.1	[null]	S	[null]	[null]	Montevideo, Uruguay
4	1	0	Clark, Mr. Walter Miller	male	27.0	1	0	13508	136.7792	C89	C	[null]	[null]	Los Angeles, CA
5	1	0	Compton, Mr. Alexander Taylor Jr	male	37.0	1	1	PC 17756	83.1583	E52	C	[null]	[null]	Lakewood, NJ

Rows: 5 | Columns: 14

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

print(file.read())
[
{"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"},
{"pclass": 1, "survived": 0, "name": "Blackwell, Mr. Stephen Weart", "sex": "male", "age": 45.000, "sibsp": 0, "parch": 0, "ticket": "113784", "fare": 35.50000, "cabin": "T", "embarked": "S", "home.dest": "Trenton, NJ"},
{"pclass": 1, "survived": 0, "name": "Carrau, Mr. Jose Pedro", "sex": "male", "age": 17.000, "sibsp": 0, "parch": 0, "ticket": "113059", "fare": 47.10000, "embarked": "S", "home.dest": "Montevideo, Uruguay"},
{"pclass": 1, "survived": 0, "name": "Clark, Mr. Walter Miller", "sex": "male", "age": 27.000, "sibsp": 1, "parch": 0, "ticket": "13508", "fare": 136.77920, "cabin": "C89", "embarked": "C", "home.dest": "Los Angeles, CA"},
{"pclass": 1, "survived": 0, "name": "Compton, Mr. Alexander Taylor Jr", "sex": "male", "age": 37.000, "sibsp": 1, "parch": 1, "ticket": "PC 17756", "fare": 83.15830, "cabin": "E52", "embarked": "C", "home.dest": "Lakewood, NJ"}
]

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 DBLINK in VerticaPy