SQL Magic

In [ ]:
%%sql -limit 100 -ncols 100 -percent False -vdf False

Executes SQL queries in the Jupyter cell.

Parameters

Name Type Optional Description
limit
int
Number of elements to display.
ncols
int
Number of columns to display.
percent
bool
If set to True, displays the percent of non-missing values.
vdf
bool
If set to True, returns a vDataFrame of the input query.

Returns

tablesample : An object containing the result. For more information, see utilities.tablesample.

Example

If you don't already have one, create an auto-connection. This connection will be used to run SQL queries.

In [ ]:
from verticapy.connect import *
# Save a new connection
new_auto_connection({"host": "10.211.55.14", 
                     "port": "5433", 
                     "database": "testdb", 
                     "password": "XxX", 
                     "user": "dbadmin"},
                    name = "VerticaDSN")
# Set the primary auto-connection
change_auto_connection("VerticaDSN")

You can then load the extension.

In [1]:
%load_ext verticapy.sql

Now you can run your SQL queries.

In [2]:
%%sql
SELECT 
    survived,
    AVG(fare) AS avg_fare,
    AVG(age) AS avg_age
FROM titanic
GROUP BY 1;
Execution: 0.025s
123
survived
Integer
123
avg_fare
Float
123
avg_age
Float
1152.300259333333329.3936572890026
2023.425595019157130.6420462046205
Out[2]:
Rows: 2 | Columns: 3

Note that you can't add parameters to the magic function.

In [3]:
%sql SELECT DISTINCT Species FROM iris;
Execution: 0.022s
Abc
Species
Varchar(30)
1Iris-setosa
2Iris-versicolor
3Iris-virginica
Out[3]:
Rows: 3 | Column: Species | Type: Varchar(30)

A single cell can handle multiple queries.

In [4]:
%%sql
DROP TABLE test;
CREATE TABLE test AS SELECT 'Badr Ouali' AS name;
SELECT * FROM test;
DROP
CREATE
Execution: 0.077s
Abc
name
Varchar(10)
1Badr Ouali
Out[4]:
Rows: 1 | Column: name | Type: Varchar(10)

You can assign your results to a variable by assigning the temporary value (represented by '_').

The following cell must be executed once and separately from the previous cell.

In [5]:
x = _
display(x)
Abc
name
Varchar(10)
1Badr Ouali
Rows: 1 | Column: name | Type: Varchar(10)

You can also specify options in your queries.

In [7]:
%%sql -limit 5 -ncols 5
SELECT * FROM public.titanic;
Execution: 0.023s
123
pclass
Integer
123
survived
Integer
...
123
body
Integer
Abc
homedest
Varchar(64)
123
split
Float
110...[null]Montreal, PQ / Chesterville, ON0.815318015636876
210...135Montreal, PQ / Chesterville, ON0.514822213212028
310...[null]Montreal, PQ / Chesterville, ON0.142754525179043
410...[null]Belfast, NI0.0399594560731202
510...22Montevideo, Uruguay0.402364013250917
Out[7]:
Rows: 1-5 of 1234 | Columns: 15
In [9]:
%%sql -percent True
SELECT * FROM public.titanic;
Execution: 0.187s
123
pclass
Int
100%
123
survived
Int
100%
Abc
Varchar(96)
100%
Abc
gender
Char(1)
100%
123
age
Numeric(6,3)
80%
123
sibsp
Int
100%
123
parch
Int
100%
Abc
ticket
Varchar(24)
100%
123
fare
Numeric(7,4)
99%
Abc
cabin
Char(10)
23%
Abc
embarked
Char(1)
99%
Abc
boat
Char(4)
35%
123
body
Int
9%
Abc
homedest
Varchar(64)
57%
123
split
Float
100%
110f2.00012113781151.5500C22 C26 S[null][null]Montreal, PQ / Chesterville, ON0.815318015636876
210m30.00012113781151.5500C22 C26 S[null]135Montreal, PQ / Chesterville, ON0.514822213212028
310f25.00012113781151.5500C22 C26 S[null][null]Montreal, PQ / Chesterville, ON0.142754525179043
410m39.000001120500.0000A36 S[null][null]Belfast, NI0.0399594560731202
510m71.00000PC 1760949.5042[null]C[null]22Montevideo, Uruguay0.402364013250917
610m47.00010PC 17757227.5250C62 C64 C[null]124New York, NY0.635426498716697
710m[null]00PC 1731825.9250[null]S[null][null]New York, NY0.597051517339423
810m24.00001PC 17558247.5208B58 B60 C[null][null]Montreal, PQ0.00836621411144733
910m36.000001305075.2417C6 CA [null]Winnipeg, MN0.111589096253738
1010m25.000001390526.0000[null]C[null]148San Francisco, CA0.886645357124507
1110m45.0000011378435.5000T S[null][null]Trenton, NJ0.107878888258711
1210m42.0000011048926.5500D22 S[null][null]London / Winnipeg, MB0.358096122043207
1310m41.0000011305430.5000A21 S[null][null]Pomeroy, WA0.0518329064361751
1410m48.00000PC 1759150.4958B10 C[null]208Omaha, NE0.200647147372365
1510m[null]0011237939.6000[null]C[null][null]Philadelphia, PA0.720063246553764
1610m45.0000011305026.5500B38 S[null][null]Washington, DC0.917834898689762
1710m[null]0011379831.0000[null]S[null][null][null]0.947958540637046
1810m33.000006955.0000B51 B53 B5S[null][null]New York, NY0.533363076625392
1910m28.0000011305947.1000[null]S[null][null]Montevideo, Uruguay0.200074948370457
2010m17.0000011305947.1000[null]S[null][null]Montevideo, Uruguay0.993818185292184
2110m49.000001992426.0000[null]S[null][null]Ascot, Berkshire / Rochester, NY0.137975917430595
2210m36.000101987778.8500C46 S[null]172Little Onn Hall, Staffs0.619254318298772
2310m46.00010W.E.P. 573461.1750E31 S[null][null]Amenia, ND0.355981041444466
2410m[null]001120510.0000[null]S[null][null]Liverpool, England / Belfast0.17994736135006
2510m27.0001013508136.7792C89 C[null][null]Los Angeles, CA0.775647930568084
2610m[null]0011046552.0000A14 S[null][null]Stoughton, MA0.935470992932096
2710m47.00000572725.5875E58 S[null][null]Victoria, BC0.568378621246666
2810m37.00011PC 1775683.1583E52 C[null][null]Lakewood, NJ0.607994917081669
2910m[null]0011379126.5500[null]S[null][null]Roachdale, IN0.705552167724818
3010m70.00011WE/P 573571.0000B22 S[null]269Milwaukee, WI0.185576476156712
3110m39.00010PC 1759971.2833C85 C[null][null]New York, NY0.516148791648448
3210m31.00010F.C. 1275052.0000B71 S[null][null]Montreal, PQ0.851567223668098
3310m50.00010PC 17761106.4250C86 C[null]62Deephaven, MN / Cedar Rapids, IA0.632833027513698
3410m39.00000PC 1758029.7000A18 C[null]133Philadelphia, PA0.72644979134202
3510f36.00000PC 1753131.6792A29 C[null][null]New York, NY0.847605429124087
3610m[null]00PC 17483221.7792C95 S[null][null][null]0.74904768075794
3710m30.0000011305127.7500C111 C[null][null]New York, NY0.431305234320462
3810m19.0003219950263.0000C23 C25 C2S[null][null]Winnipeg, MB0.0793363316915929
3910m64.0001419950263.0000C23 C25 C2S[null][null]Winnipeg, MB0.388494573766366
4010m[null]0011377826.5500D34 S[null][null]Westcliff-on-Sea, Essex0.142541378969327
4110m[null]001120580.0000B102 S[null][null][null]0.954079678747803
4210m37.0001011380353.1000C123 S[null][null]Scituate, MA0.937022707192227
4310m47.0000011132038.5000E63 S[null]275St Anne's-on-Sea, Lancashire0.371648256899789
4410m24.00000PC 1759379.2000B86 C[null][null][null]0.665388232795522
4510m71.00000PC 1775434.6542A5 C[null][null]New York, NY0.741307334508747
4610m38.00001PC 17582153.4625C91 S[null]147Winnipeg, MB0.345036457292736
4710m46.00000PC 1759379.2000B82 B84 C[null][null]New York, NY0.837107699597254
4810m[null]0011379642.4000[null]S[null][null][null]0.778836605837569
4910m45.000103697383.4750C83 S[null][null]New York, NY0.452243909239769
5010m40.000001120590.0000B94 S[null]110[null]0.0371354282833636
5110m55.000111274993.5000B69 S[null]307Montreal, PQ0.563097916543484
5210m42.0000011303842.5000B11 S[null][null]London / Middlesex0.754689895315096
5310m[null]001746351.8625E46 S[null][null]Brighton, MA0.46268887934275
5410m55.0000068050.0000C39 S[null][null]London / Birmingham0.569614446721971
5510m42.0001011378952.0000[null]S[null]38New York, NY0.701291786506772
5610m[null]00PC 1760030.6958[null]C14 [null]New York, NY0.589644859079272
5710f50.00000PC 1759528.7125C49 C[null][null]Paris, France New York, NY0.49467936810106
5810m46.0000069426.0000[null]S[null]80Bennington, VT0.779803531477228
5910m50.0000011304426.0000E60 S[null][null]London0.334872611565515
6010m32.50000113503211.5000C132 C[null]45[null]0.561064395355061
6110m58.000001177129.7000B37 C[null]258Buffalo, NY0.628222591010854
6210m41.000101746451.8625D21 S[null][null]Southington / Noank, CT0.356504800729454
6310m[null]0011302826.5500C124 S[null][null]Portland, OR0.599147979635745
6410m[null]00PC 1761227.7208[null]C[null][null]Chicago, IL0.578147665364668
6510m29.0000011350130.0000D6 S[null]126Springfield, MA0.19623118150048
6610m30.0000011380145.5000[null]S[null][null]London / New York, NY0.94992582895793
6710m30.0000011046926.0000C106 S[null][null]Brockton, MA0.363202170934528
6810m19.0001011377353.1000D30 S[null][null]New York, NY0.827336158370599
6910m46.000001305075.2417C6 C[null]292Vancouver, BC0.285753501113504
7010m54.000001746351.8625E46 S[null]175Dorchester, MA0.688862106297165
7110m28.00010PC 1760482.1708[null]C[null][null]New York, NY0.728661807486787
7210m65.000001350926.5500E38 S[null]249East Bridgewater, MA0.617859082994983
7310m44.000201992890.0000C78 Q[null]230Fond du Lac, WI0.954004928469658
7410m55.0000011378730.5000C30 S[null][null]Montreal, PQ0.503857570467517
7510m47.0000011379642.4000[null]S[null][null]Washington, DC0.154437867924571
7610m37.00001PC 1759629.7000C118 C[null][null]Brooklyn, NY0.22336406307295
7710m58.0000235273113.2750D48 C[null]122Lexington, MA0.878490646136925
7810m64.0000069326.0000[null]S[null]263Isle of Wight, England0.305325420340523
7910m65.0000111350961.9792B30 C[null]234Providence, RI0.524437925312668
8010m28.50000PC 1756227.7208D43 C[null]189?Havana, Cuba0.666664712131023
8110m[null]001120520.0000[null]S[null][null]Belfast0.364063322544098
8210m45.5000011304328.5000C124 S[null]166Surbiton Hill, Surrey0.396239920286462
8310m23.000001274993.5000B24 S[null][null]Montreal, PQ0.323091795900837
8410m29.0001011377666.6000C2 S[null][null]Isleworth, England0.835115849273279
8510m18.00010PC 17758108.9000C65 C[null][null]Madrid, Spain0.19482533656992
8610m47.0000011046552.0000C110 S[null]207Worcester, MA0.2082355087623
8710m38.00000199720.0000[null]S[null][null]Rotterdam, Netherlands0.746992202242836
8810m22.00000PC 17760135.6333[null]C[null]232[null]0.186588425887749
8910m[null]00PC 17757227.5250[null]C[null][null][null]0.577199265360832
9010m31.00000PC 1759050.4958A24 S[null][null]Trenton, NJ0.212681184988469
9110m[null]0011376750.0000A32 S[null][null]Seattle, WA0.149814031785354
9210m36.000001304940.1250A10 C[null][null]Winnipeg, MB0.0017111434135586
9310m55.00010PC 1760359.4000[null]C[null][null]New York, NY0.879928017500788
9410m33.0000011379026.5500[null]S[null]109London0.626925394171849
9510m61.00013PC 17608262.3750B57 B59 B6C[null][null]Haverford, PA / Cooperstown, NY0.725577870383859
9610m50.000101350755.9000E44 S[null][null]Duluth, MN0.521040184423327
9710m56.0000011379226.5500[null]S[null][null]New York, NY0.833983679767698
9810m56.000001776430.6958A7 C[null][null]St James, Long Island, NY0.972882559057325
9910m24.000101369560.0000C31 S[null][null]Huntington, WV0.335882832761854
10010m[null]0011305626.0000A19 S[null][null]Streatham, Surrey0.620575522771105
Out[9]:
Rows: 1-100 of 1234 | Columns: 15

You can get a vDataFrame of the input query.

In [6]:
%%sql -vdf True
SELECT * FROM public.titanic;
Execution: 0.132s
123
pclass
Int
123
survived
Int
Abc
Varchar(96)
Abc
gender
Char(1)
123
age
Numeric(6,3)
123
sibsp
Int
123
parch
Int
Abc
ticket
Varchar(24)
123
fare
Numeric(7,4)
Abc
cabin
Char(10)
Abc
embarked
Char(1)
Abc
boat
Char(4)
123
body
Int
Abc
homedest
Varchar(64)
123
split
Float
110f2.00012113781151.5500C22 C26 S[null][null]Montreal, PQ / Chesterville, ON0.815318015636876
210m30.00012113781151.5500C22 C26 S[null]135Montreal, PQ / Chesterville, ON0.514822213212028
310f25.00012113781151.5500C22 C26 S[null][null]Montreal, PQ / Chesterville, ON0.142754525179043
410m39.000001120500.0000A36 S[null][null]Belfast, NI0.0399594560731202
510m71.00000PC 1760949.5042[null]C[null]22Montevideo, Uruguay0.402364013250917
610m47.00010PC 17757227.5250C62 C64 C[null]124New York, NY0.635426498716697
710m[null]00PC 1731825.9250[null]S[null][null]New York, NY0.597051517339423
810m24.00001PC 17558247.5208B58 B60 C[null][null]Montreal, PQ0.00836621411144733
910m36.000001305075.2417C6 CA [null]Winnipeg, MN0.111589096253738
1010m25.000001390526.0000[null]C[null]148San Francisco, CA0.886645357124507
1110m45.0000011378435.5000T S[null][null]Trenton, NJ0.107878888258711
1210m42.0000011048926.5500D22 S[null][null]London / Winnipeg, MB0.358096122043207
1310m41.0000011305430.5000A21 S[null][null]Pomeroy, WA0.0518329064361751
1410m48.00000PC 1759150.4958B10 C[null]208Omaha, NE0.200647147372365
1510m[null]0011237939.6000[null]C[null][null]Philadelphia, PA0.720063246553764
1610m45.0000011305026.5500B38 S[null][null]Washington, DC0.917834898689762
1710m[null]0011379831.0000[null]S[null][null][null]0.947958540637046
1810m33.000006955.0000B51 B53 B5S[null][null]New York, NY0.533363076625392
1910m28.0000011305947.1000[null]S[null][null]Montevideo, Uruguay0.200074948370457
2010m17.0000011305947.1000[null]S[null][null]Montevideo, Uruguay0.993818185292184
2110m49.000001992426.0000[null]S[null][null]Ascot, Berkshire / Rochester, NY0.137975917430595
2210m36.000101987778.8500C46 S[null]172Little Onn Hall, Staffs0.619254318298772
2310m46.00010W.E.P. 573461.1750E31 S[null][null]Amenia, ND0.355981041444466
2410m[null]001120510.0000[null]S[null][null]Liverpool, England / Belfast0.17994736135006
2510m27.0001013508136.7792C89 C[null][null]Los Angeles, CA