verticapy.insert_into#

verticapy.insert_into(table_name: str, data: list, schema: str | None = None, column_names: list | None = None, copy: bool = True, genSQL: bool = False) → int | str#

Inserts the dataset into an existing Vertica table.

Parameters#

table_name: str: Name of the table to insert into.
data: list: The data to ingest.
schema: str, optional: Schema name.
column_names: list, optional: Name of the column(s) to insert into.
copy: bool, optional: If set to True, the batch insert is converted to a COPY statement with prepared statements. Otherwise, the INSERTs are performed sequentially.
genSQL: bool, optional: If set to True, the SQL code that would be used to insert the data is generated, but not executed.

Returns#

int: The number of rows ingested.

Examples#

For this example, we will use the Iris dataset.

import verticapy.datasets as vpd

data = vpd.load_iris()

	123 SepalLengthCm Numeric(7)	123 SepalWidthCm Numeric(7)	123 PetalLengthCm Numeric(7)	123 PetalWidthCm Numeric(7)	Abc Species Varchar(30)
1	3.3	4.5	5.6	7.8	Iris-setosa
2	3.3	4.5	5.6	7.8	Iris-setosa
3	3.3	4.5	5.6	7.8	Iris-setosa
4	3.3	4.5	5.6	7.8	Iris-setosa
5	3.3	4.5	5.6	7.8	Iris-setosa
6	3.3	4.5	5.6	7.8	Iris-setosa
7	3.3	4.5	5.6	7.8	Iris-setosa
8	3.3	4.5	5.6	7.8	Iris-setosa
9	3.3	4.5	5.6	7.8	Iris-setosa
10	3.3	4.5	5.6	7.8	Iris-setosa
11	3.3	4.5	5.6	7.8	Iris-setosa
12	3.3	4.5	5.6	7.8	Iris-setosa
13	3.3	4.5	5.6	7.8	Iris-setosa
14	3.3	4.5	5.6	7.8	Iris-setosa
15	3.3	4.5	5.6	7.8	Iris-setosa
16	3.3	4.5	5.6	7.8	Iris-setosa
17	3.3	4.5	5.6	7.8	Iris-setosa
18	3.3	4.5	5.6	7.8	Iris-setosa
19	3.3	4.5	5.6	7.8	Iris-setosa
20	3.3	4.5	5.6	7.8	Iris-setosa
21	3.3	4.5	5.6	7.8	Iris-setosa
22	3.3	4.5	5.6	7.8	Iris-setosa
23	3.3	4.5	5.6	7.8	Iris-setosa
24	3.3	4.5	5.6	7.8	Iris-setosa
25	3.3	4.5	5.6	7.8	Iris-setosa
26	3.3	4.5	5.6	7.8	Iris-setosa
27	3.3	4.5	5.6	7.8	Iris-setosa
28	3.3	4.5	5.6	7.8	Iris-setosa
29	3.3	4.5	5.6	7.8	Iris-setosa
30	3.3	4.5	5.6	7.8	Iris-setosa
31	3.3	4.5	5.6	7.8	Iris-setosa
32	3.3	4.5	5.6	7.8	Iris-setosa
33	3.3	4.5	5.6	7.8	Iris-setosa
34	3.3	4.5	5.6	7.8	Iris-setosa
35	3.3	4.5	5.6	7.8	Iris-setosa
36	3.3	4.5	5.6	7.8	Iris-setosa
37	3.3	4.5	5.6	7.8	Iris-setosa
38	3.3	4.5	5.6	7.8	Iris-setosa
39	3.3	4.5	5.6	7.8	Iris-setosa
40	3.3	4.5	5.6	7.8	Iris-setosa
41	3.3	4.5	5.6	7.8	Iris-setosa
42	3.3	4.5	5.6	7.8	Iris-setosa
43	4.3	3.0	1.1	0.1	Iris-setosa
44	4.3	4.7	9.6	1.8	Iris-virginica
45	4.3	4.7	9.6	1.8	Iris-virginica
46	4.3	4.7	9.6	1.8	Iris-virginica
47	4.3	4.7	9.6	1.8	Iris-virginica
48	4.3	4.7	9.6	1.8	Iris-virginica
49	4.3	4.7	9.6	1.8	Iris-virginica
50	4.3	4.7	9.6	1.8	Iris-virginica
51	4.3	4.7	9.6	1.8	Iris-virginica
52	4.3	4.7	9.6	1.8	Iris-virginica
53	4.3	4.7	9.6	1.8	Iris-virginica
54	4.3	4.7	9.6	1.8	Iris-virginica
55	4.3	4.7	9.6	1.8	Iris-virginica
56	4.3	4.7	9.6	1.8	Iris-virginica
57	4.3	4.7	9.6	1.8	Iris-virginica
58	4.3	4.7	9.6	1.8	Iris-virginica
59	4.3	4.7	9.6	1.8	Iris-virginica
60	4.3	4.7	9.6	1.8	Iris-virginica
61	4.3	4.7	9.6	1.8	Iris-virginica
62	4.3	4.7	9.6	1.8	Iris-virginica
63	4.3	4.7	9.6	1.8	Iris-virginica
64	4.3	4.7	9.6	1.8	Iris-virginica
65	4.3	4.7	9.6	1.8	Iris-virginica
66	4.3	4.7	9.6	1.8	Iris-virginica
67	4.3	4.7	9.6	1.8	Iris-virginica
68	4.3	4.7	9.6	1.8	Iris-virginica
69	4.3	4.7	9.6	1.8	Iris-virginica
70	4.3	4.7	9.6	1.8	Iris-virginica
71	4.3	4.7	9.6	1.8	Iris-virginica
72	4.3	4.7	9.6	1.8	Iris-virginica
73	4.3	4.7	9.6	1.8	Iris-virginica
74	4.3	4.7	9.6	1.8	Iris-virginica
75	4.3	4.7	9.6	1.8	Iris-virginica
76	4.3	4.7	9.6	1.8	Iris-virginica
77	4.3	4.7	9.6	1.8	Iris-virginica
78	4.3	4.7	9.6	1.8	Iris-virginica
79	4.3	4.7	9.6	1.8	Iris-virginica
80	4.3	4.7	9.6	1.8	Iris-virginica
81	4.3	4.7	9.6	1.8	Iris-virginica
82	4.3	4.7	9.6	1.8	Iris-virginica
83	4.3	4.7	9.6	1.8	Iris-virginica
84	4.3	4.7	9.6	1.8	Iris-virginica
85	4.3	4.7	9.6	1.8	Iris-virginica
86	4.4	2.9	1.4	0.2	Iris-setosa
87	4.4	3.0	1.3	0.2	Iris-setosa
88	4.4	3.2	1.3	0.2	Iris-setosa
89	4.5	2.3	1.3	0.3	Iris-setosa
90	4.6	3.1	1.5	0.2	Iris-setosa
91	4.6	3.2	1.4	0.2	Iris-setosa
92	4.6	3.4	1.4	0.3	Iris-setosa
93	4.6	3.6	1.0	0.2	Iris-setosa
94	4.7	3.2	1.3	0.2	Iris-setosa
95	4.7	3.2	1.6	0.2	Iris-setosa
96	4.8	3.0	1.4	0.1	Iris-setosa
97	4.8	3.0	1.4	0.3	Iris-setosa
98	4.8	3.1	1.6	0.2	Iris-setosa
99	4.8	3.4	1.6	0.2	Iris-setosa
100	4.8	3.4	1.9	0.2	Iris-setosa

Rows: 1-100 | Columns: 5

Note

VerticaPy offers a wide range of sample datasets that are ideal for training and testing purposes. You can explore the full list of available datasets in the Datasets, which provides detailed information on each dataset and how to use them effectively. These datasets are invaluable resources for honing your data analysis and machine learning skills within the VerticaPy environment.

We import the insert_into function and insert different element to the iris table.

from verticapy.sql import insert_into

You can insert all the elements at once with a single COPY statement by using the following command.

insert_into(
    table_name = "iris",
    schema = "public",
    data = [
        [3.3, 4.5, 5.6, 7.8, "Iris-setosa"],
        [4.3, 4.7, 9.6, 1.8, "Iris-virginica"],
    ],
)

Out[2]: 2

If you want to use multiple inserts to avoid a general failure and insert what you can, use the following approach.

insert_into(
    table_name = "iris",
    schema = "public",
    data = [
        [3.3, 4.5, 5.6, 7.8, "Iris-setosa"],
        [4.3, 4.7, 9.6, 1.8, "Iris-virginica"],
    ],
    copy = False,
)

Out[3]: 2

If you want to examine the generated SQL without executing it, use the following command.

insert_into(
    table_name = "iris",
    schema = "public",
    data = [
        [3.3, 4.5, 5.6, 7.8, "Iris-setosa"],
        [4.3, 4.7, 9.6, 1.8, "Iris-virginica"],
    ],
    genSQL = True,
)

Out[4]: 
['INSERT INTO "public"."iris" ("SepalLengthCm", "SepalWidthCm", "PetalLengthCm", "PetalWidthCm", "Species") VALUES (\'3.3\',\'4.5\',\'5.6\',\'7.8\',\'Iris-setosa\')',
 'INSERT INTO "public"."iris" ("SepalLengthCm", "SepalWidthCm", "PetalLengthCm", "PetalWidthCm", "Species") VALUES (\'4.3\',\'4.7\',\'9.6\',\'1.8\',\'Iris-virginica\')']

Note

Set copy to False for multiple inserts.