Skip to content

Vanna Functions

Note

You won't normally need to use these functions unless you are doing heavy customization work.

Nomenclature

Prefix Definition Examples
vn.get_ Fetch some data vn.get_related_ddl(...)
vn.add_ Adds something to the retrieval layer vn.add_question_sql(...)
vn.add_ddl(...)
vn.generate_ Generates something using AI based on the information in the model vn.generate_sql(...)
[vn.generate_explanation()][vanna.base.base.VannaBase.generate_explanation]
vn.run_ Runs code (SQL) vn.run_sql
vn.remove_ Removes something from the retrieval layer vn.remove_training_data
vn.connect_ Connects to a database [vn.connect_to_snowflake(...)][vanna.base.base.VannaBase.connect_to_snowflake]
vn.update_ Updates something N/A -- unused
vn.set_ Sets something N/A -- unused

Open-Source and Extending

Vanna.AI is open-source and extensible. If you'd like to use Vanna without the servers, see an example here.

The following is an example of where various functions are implemented in the codebase when using the default "local" version of Vanna. vanna.base.VannaBase is the base class which provides a vanna.base.VannaBase.ask and vanna.base.VannaBase.train function. Those rely on abstract methods which are implemented in the subclasses vanna.openai_chat.OpenAI_Chat and vanna.chromadb_vector.ChromaDB_VectorStore. vanna.openai_chat.OpenAI_Chat uses the OpenAI API to generate SQL and Plotly code. vanna.chromadb_vector.ChromaDB_VectorStore uses ChromaDB to store training data and generate embeddings.

If you want to use Vanna with other LLMs or databases, you can create your own subclass of vanna.base.VannaBase and implement the abstract methods.

flowchart
    subgraph VannaBase
        ask
        train
    end

    subgraph OpenAI_Chat
        get_sql_prompt
        submit_prompt
        generate_question
        generate_plotly_code
    end

    subgraph ChromaDB_VectorStore
        generate_embedding
        add_question_sql
        add_ddl
        add_documentation
        get_similar_question_sql
        get_related_ddl
        get_related_documentation
    end

VannaBase

Bases: ABC

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
  71
  72
  73
  74
  75
  76
  77
  78
  79
  80
  81
  82
  83
  84
  85
  86
  87
  88
  89
  90
  91
  92
  93
  94
  95
  96
  97
  98
  99
 100
 101
 102
 103
 104
 105
 106
 107
 108
 109
 110
 111
 112
 113
 114
 115
 116
 117
 118
 119
 120
 121
 122
 123
 124
 125
 126
 127
 128
 129
 130
 131
 132
 133
 134
 135
 136
 137
 138
 139
 140
 141
 142
 143
 144
 145
 146
 147
 148
 149
 150
 151
 152
 153
 154
 155
 156
 157
 158
 159
 160
 161
 162
 163
 164
 165
 166
 167
 168
 169
 170
 171
 172
 173
 174
 175
 176
 177
 178
 179
 180
 181
 182
 183
 184
 185
 186
 187
 188
 189
 190
 191
 192
 193
 194
 195
 196
 197
 198
 199
 200
 201
 202
 203
 204
 205
 206
 207
 208
 209
 210
 211
 212
 213
 214
 215
 216
 217
 218
 219
 220
 221
 222
 223
 224
 225
 226
 227
 228
 229
 230
 231
 232
 233
 234
 235
 236
 237
 238
 239
 240
 241
 242
 243
 244
 245
 246
 247
 248
 249
 250
 251
 252
 253
 254
 255
 256
 257
 258
 259
 260
 261
 262
 263
 264
 265
 266
 267
 268
 269
 270
 271
 272
 273
 274
 275
 276
 277
 278
 279
 280
 281
 282
 283
 284
 285
 286
 287
 288
 289
 290
 291
 292
 293
 294
 295
 296
 297
 298
 299
 300
 301
 302
 303
 304
 305
 306
 307
 308
 309
 310
 311
 312
 313
 314
 315
 316
 317
 318
 319
 320
 321
 322
 323
 324
 325
 326
 327
 328
 329
 330
 331
 332
 333
 334
 335
 336
 337
 338
 339
 340
 341
 342
 343
 344
 345
 346
 347
 348
 349
 350
 351
 352
 353
 354
 355
 356
 357
 358
 359
 360
 361
 362
 363
 364
 365
 366
 367
 368
 369
 370
 371
 372
 373
 374
 375
 376
 377
 378
 379
 380
 381
 382
 383
 384
 385
 386
 387
 388
 389
 390
 391
 392
 393
 394
 395
 396
 397
 398
 399
 400
 401
 402
 403
 404
 405
 406
 407
 408
 409
 410
 411
 412
 413
 414
 415
 416
 417
 418
 419
 420
 421
 422
 423
 424
 425
 426
 427
 428
 429
 430
 431
 432
 433
 434
 435
 436
 437
 438
 439
 440
 441
 442
 443
 444
 445
 446
 447
 448
 449
 450
 451
 452
 453
 454
 455
 456
 457
 458
 459
 460
 461
 462
 463
 464
 465
 466
 467
 468
 469
 470
 471
 472
 473
 474
 475
 476
 477
 478
 479
 480
 481
 482
 483
 484
 485
 486
 487
 488
 489
 490
 491
 492
 493
 494
 495
 496
 497
 498
 499
 500
 501
 502
 503
 504
 505
 506
 507
 508
 509
 510
 511
 512
 513
 514
 515
 516
 517
 518
 519
 520
 521
 522
 523
 524
 525
 526
 527
 528
 529
 530
 531
 532
 533
 534
 535
 536
 537
 538
 539
 540
 541
 542
 543
 544
 545
 546
 547
 548
 549
 550
 551
 552
 553
 554
 555
 556
 557
 558
 559
 560
 561
 562
 563
 564
 565
 566
 567
 568
 569
 570
 571
 572
 573
 574
 575
 576
 577
 578
 579
 580
 581
 582
 583
 584
 585
 586
 587
 588
 589
 590
 591
 592
 593
 594
 595
 596
 597
 598
 599
 600
 601
 602
 603
 604
 605
 606
 607
 608
 609
 610
 611
 612
 613
 614
 615
 616
 617
 618
 619
 620
 621
 622
 623
 624
 625
 626
 627
 628
 629
 630
 631
 632
 633
 634
 635
 636
 637
 638
 639
 640
 641
 642
 643
 644
 645
 646
 647
 648
 649
 650
 651
 652
 653
 654
 655
 656
 657
 658
 659
 660
 661
 662
 663
 664
 665
 666
 667
 668
 669
 670
 671
 672
 673
 674
 675
 676
 677
 678
 679
 680
 681
 682
 683
 684
 685
 686
 687
 688
 689
 690
 691
 692
 693
 694
 695
 696
 697
 698
 699
 700
 701
 702
 703
 704
 705
 706
 707
 708
 709
 710
 711
 712
 713
 714
 715
 716
 717
 718
 719
 720
 721
 722
 723
 724
 725
 726
 727
 728
 729
 730
 731
 732
 733
 734
 735
 736
 737
 738
 739
 740
 741
 742
 743
 744
 745
 746
 747
 748
 749
 750
 751
 752
 753
 754
 755
 756
 757
 758
 759
 760
 761
 762
 763
 764
 765
 766
 767
 768
 769
 770
 771
 772
 773
 774
 775
 776
 777
 778
 779
 780
 781
 782
 783
 784
 785
 786
 787
 788
 789
 790
 791
 792
 793
 794
 795
 796
 797
 798
 799
 800
 801
 802
 803
 804
 805
 806
 807
 808
 809
 810
 811
 812
 813
 814
 815
 816
 817
 818
 819
 820
 821
 822
 823
 824
 825
 826
 827
 828
 829
 830
 831
 832
 833
 834
 835
 836
 837
 838
 839
 840
 841
 842
 843
 844
 845
 846
 847
 848
 849
 850
 851
 852
 853
 854
 855
 856
 857
 858
 859
 860
 861
 862
 863
 864
 865
 866
 867
 868
 869
 870
 871
 872
 873
 874
 875
 876
 877
 878
 879
 880
 881
 882
 883
 884
 885
 886
 887
 888
 889
 890
 891
 892
 893
 894
 895
 896
 897
 898
 899
 900
 901
 902
 903
 904
 905
 906
 907
 908
 909
 910
 911
 912
 913
 914
 915
 916
 917
 918
 919
 920
 921
 922
 923
 924
 925
 926
 927
 928
 929
 930
 931
 932
 933
 934
 935
 936
 937
 938
 939
 940
 941
 942
 943
 944
 945
 946
 947
 948
 949
 950
 951
 952
 953
 954
 955
 956
 957
 958
 959
 960
 961
 962
 963
 964
 965
 966
 967
 968
 969
 970
 971
 972
 973
 974
 975
 976
 977
 978
 979
 980
 981
 982
 983
 984
 985
 986
 987
 988
 989
 990
 991
 992
 993
 994
 995
 996
 997
 998
 999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
class VannaBase(ABC):
    def __init__(self, config=None):
        self.config = config
        self.run_sql_is_set = False
        self.static_documentation = ""

    def log(self, message: str):
        print(message)

    def generate_sql(self, question: str, **kwargs) -> str:
        """
        Example:
        ```python
        vn.generate_sql("What are the top 10 customers by sales?")
        ```

        Uses the LLM to generate a SQL query that answers a question. It runs the following methods:

        - [`get_similar_question_sql`][vanna.base.base.VannaBase.get_similar_question_sql]

        - [`get_related_ddl`][vanna.base.base.VannaBase.get_related_ddl]

        - [`get_related_documentation`][vanna.base.base.VannaBase.get_related_documentation]

        - [`get_sql_prompt`][vanna.base.base.VannaBase.get_sql_prompt]

        - [`submit_prompt`][vanna.base.base.VannaBase.submit_prompt]


        Args:
            question (str): The question to generate a SQL query for.

        Returns:
            str: The SQL query that answers the question.
        """
        if self.config is not None:
            initial_prompt = self.config.get("initial_prompt", None)
        else:
            initial_prompt = None
        question_sql_list = self.get_similar_question_sql(question, **kwargs)
        ddl_list = self.get_related_ddl(question, **kwargs)
        doc_list = self.get_related_documentation(question, **kwargs)
        prompt = self.get_sql_prompt(
            initial_prompt=initial_prompt,
            question=question,
            question_sql_list=question_sql_list,
            ddl_list=ddl_list,
            doc_list=doc_list,
            **kwargs,
        )
        self.log(prompt)
        llm_response = self.submit_prompt(prompt, **kwargs)
        self.log(llm_response)
        return self.extract_sql(llm_response)

    def extract_sql(self, llm_response: str) -> str:
        # If the llm_response contains a markdown code block, with or without the sql tag, extract the sql from it
        sql = re.search(r"```sql\n(.*)```", llm_response, re.DOTALL)
        if sql:
            self.log(f"Output from LLM: {llm_response} \nExtracted SQL: {sql.group(1)}")
            return sql.group(1)

        sql = re.search(r"```(.*)```", llm_response, re.DOTALL)
        if sql:
            self.log(f"Output from LLM: {llm_response} \nExtracted SQL: {sql.group(1)}")
            return sql.group(1)

        return llm_response

    def is_sql_valid(self, sql: str) -> bool:
        # This is a check to see the SQL is valid and should be run
        # This simple function just checks if the SQL contains a SELECT statement

        if "SELECT" in sql.upper():
            return True
        else:
            return False

    def generate_followup_questions(
        self, question: str, sql: str, df: pd.DataFrame, **kwargs
    ) -> list:
        """
        **Example:**
        ```python
        vn.generate_followup_questions("What are the top 10 customers by sales?", df)
        ```

        Generate a list of followup questions that you can ask Vanna.AI.

        Args:
            question (str): The question that was asked.
            df (pd.DataFrame): The results of the SQL query.

        Returns:
            list: A list of followup questions that you can ask Vanna.AI.
        """

        message_log = [
            self.system_message(
                f"You are a helpful data assistant. The user asked the question: '{question}'\n\nThe SQL query for this question was: {sql}\n\nThe following is a pandas DataFrame with the results of the query: \n{df.to_markdown()}\n\n"
            ),
            self.user_message(
                "Generate a list of followup questions that the user might ask about this data. Respond with a list of questions, one per line. Do not answer with any explanations -- just the questions. Remember that there should be an unambiguous SQL query that can be generated from the question. Prefer questions that are answerable outside of the context of this conversation. Prefer questions that are slight modifications of the SQL query that was generated that allow digging deeper into the data. Each question will be turned into a button that the user can click to generate a new SQL query so don't use 'example' type questions. Each question must have a one-to-one correspondence with an instantiated SQL query."
            ),
        ]

        llm_response = self.submit_prompt(message_log, **kwargs)

        numbers_removed = re.sub(r"^\d+\.\s*", "", llm_response, flags=re.MULTILINE)
        return numbers_removed.split("\n")

    def generate_questions(self, **kwargs) -> List[str]:
        """
        **Example:**
        ```python
        vn.generate_questions()
        ```

        Generate a list of questions that you can ask Vanna.AI.
        """
        question_sql = self.get_similar_question_sql(question="", **kwargs)

        return [q["question"] for q in question_sql]

    def generate_summary(self, question: str, df: pd.DataFrame, **kwargs) -> str:
        """
        **Example:**
        ```python
        vn.generate_summary("What are the top 10 customers by sales?", df)
        ```

        Generate a summary of the results of a SQL query.

        Args:
            question (str): The question that was asked.
            df (pd.DataFrame): The results of the SQL query.

        Returns:
            str: The summary of the results of the SQL query.
        """

        message_log = [
            self.system_message(
                f"You are a helpful data assistant. The user asked the question: '{question}'\n\nThe following is a pandas DataFrame with the results of the query: \n{df.to_markdown()}\n\n"
            ),
            self.user_message(
                "Briefly summarize the data based on the question that was asked. Do not respond with any additional explanation beyond the summary."
            ),
        ]

        summary = self.submit_prompt(message_log, **kwargs)

        return summary

    # ----------------- Use Any Embeddings API ----------------- #
    @abstractmethod
    def generate_embedding(self, data: str, **kwargs) -> List[float]:
        pass

    # ----------------- Use Any Database to Store and Retrieve Context ----------------- #
    @abstractmethod
    def get_similar_question_sql(self, question: str, **kwargs) -> list:
        """
        This method is used to get similar questions and their corresponding SQL statements.

        Args:
            question (str): The question to get similar questions and their corresponding SQL statements for.

        Returns:
            list: A list of similar questions and their corresponding SQL statements.
        """
        pass

    @abstractmethod
    def get_related_ddl(self, question: str, **kwargs) -> list:
        """
        This method is used to get related DDL statements to a question.

        Args:
            question (str): The question to get related DDL statements for.

        Returns:
            list: A list of related DDL statements.
        """
        pass

    @abstractmethod
    def get_related_documentation(self, question: str, **kwargs) -> list:
        """
        This method is used to get related documentation to a question.

        Args:
            question (str): The question to get related documentation for.

        Returns:
            list: A list of related documentation.
        """
        pass

    @abstractmethod
    def add_question_sql(self, question: str, sql: str, **kwargs) -> str:
        """
        This method is used to add a question and its corresponding SQL query to the training data.

        Args:
            question (str): The question to add.
            sql (str): The SQL query to add.

        Returns:
            str: The ID of the training data that was added.
        """
        pass

    @abstractmethod
    def add_ddl(self, ddl: str, **kwargs) -> str:
        """
        This method is used to add a DDL statement to the training data.

        Args:
            ddl (str): The DDL statement to add.

        Returns:
            str: The ID of the training data that was added.
        """
        pass

    @abstractmethod
    def add_documentation(self, documentation: str, **kwargs) -> str:
        """
        This method is used to add documentation to the training data.

        Args:
            documentation (str): The documentation to add.

        Returns:
            str: The ID of the training data that was added.
        """
        pass

    @abstractmethod
    def get_training_data(self, **kwargs) -> pd.DataFrame:
        """
        Example:
        ```python
        vn.get_training_data()
        ```

        This method is used to get all the training data from the retrieval layer.

        Returns:
            pd.DataFrame: The training data.
        """
        pass

    @abstractmethod
    def remove_training_data(id: str, **kwargs) -> bool:
        """
        Example:
        ```python
        vn.remove_training_data(id="123-ddl")
        ```

        This method is used to remove training data from the retrieval layer.

        Args:
            id (str): The ID of the training data to remove.

        Returns:
            bool: True if the training data was removed, False otherwise.
        """
        pass

    # ----------------- Use Any Language Model API ----------------- #

    @abstractmethod
    def system_message(self, message: str) -> any:
        pass

    @abstractmethod
    def user_message(self, message: str) -> any:
        pass

    @abstractmethod
    def assistant_message(self, message: str) -> any:
        pass

    def str_to_approx_token_count(self, string: str) -> int:
        return len(string) / 4

    def add_ddl_to_prompt(
        self, initial_prompt: str, ddl_list: list[str], max_tokens: int = 14000
    ) -> str:
        if len(ddl_list) > 0:
            initial_prompt += f"\nYou may use the following DDL statements as a reference for what tables might be available. Use responses to past questions also to guide you:\n\n"

            for ddl in ddl_list:
                if (
                    self.str_to_approx_token_count(initial_prompt)
                    + self.str_to_approx_token_count(ddl)
                    < max_tokens
                ):
                    initial_prompt += f"{ddl}\n\n"

        return initial_prompt

    def add_documentation_to_prompt(
        self,
        initial_prompt: str,
        documentation_list: list[str],
        max_tokens: int = 14000,
    ) -> str:
        if len(documentation_list) > 0:
            initial_prompt += f"\nYou may use the following documentation as a reference for what tables might be available. Use responses to past questions also to guide you:\n\n"

            for documentation in documentation_list:
                if (
                    self.str_to_approx_token_count(initial_prompt)
                    + self.str_to_approx_token_count(documentation)
                    < max_tokens
                ):
                    initial_prompt += f"{documentation}\n\n"

        return initial_prompt

    def add_sql_to_prompt(
        self, initial_prompt: str, sql_list: list[str], max_tokens: int = 14000
    ) -> str:
        if len(sql_list) > 0:
            initial_prompt += f"\nYou may use the following SQL statements as a reference for what tables might be available. Use responses to past questions also to guide you:\n\n"

            for question in sql_list:
                if (
                    self.str_to_approx_token_count(initial_prompt)
                    + self.str_to_approx_token_count(question["sql"])
                    < max_tokens
                ):
                    initial_prompt += f"{question['question']}\n{question['sql']}\n\n"

        return initial_prompt

    def get_sql_prompt(
        self,
        initial_prompt : str,
        question: str,
        question_sql_list: list,
        ddl_list: list,
        doc_list: list,
        **kwargs,
    ):
        """
        Example:
        ```python
        vn.get_sql_prompt(
            question="What are the top 10 customers by sales?",
            question_sql_list=[{"question": "What are the top 10 customers by sales?", "sql": "SELECT * FROM customers ORDER BY sales DESC LIMIT 10"}],
            ddl_list=["CREATE TABLE customers (id INT, name TEXT, sales DECIMAL)"],
            doc_list=["The customers table contains information about customers and their sales."],
        )

        ```

        This method is used to generate a prompt for the LLM to generate SQL.

        Args:
            question (str): The question to generate SQL for.
            question_sql_list (list): A list of questions and their corresponding SQL statements.
            ddl_list (list): A list of DDL statements.
            doc_list (list): A list of documentation.

        Returns:
            any: The prompt for the LLM to generate SQL.
        """

        if initial_prompt is None:
            initial_prompt = "The user provides a question and you provide SQL. You will only respond with SQL code and not with any explanations.\n\nRespond with only SQL code. Do not answer with any explanations -- just the code.\n"

        initial_prompt = self.add_ddl_to_prompt(
            initial_prompt, ddl_list, max_tokens=14000
        )

        if self.static_documentation != "":
            doc_list.append(self.static_documentation)

        initial_prompt = self.add_documentation_to_prompt(
            initial_prompt, doc_list, max_tokens=14000
        )

        message_log = [self.system_message(initial_prompt)]

        for example in question_sql_list:
            if example is None:
                print("example is None")
            else:
                if example is not None and "question" in example and "sql" in example:
                    message_log.append(self.user_message(example["question"]))
                    message_log.append(self.assistant_message(example["sql"]))

        message_log.append(self.user_message(question))

        return message_log

    def get_followup_questions_prompt(
        self,
        question: str,
        question_sql_list: list,
        ddl_list: list,
        doc_list: list,
        **kwargs,
    ) -> list:
        initial_prompt = f"The user initially asked the question: '{question}': \n\n"

        initial_prompt = self.add_ddl_to_prompt(
            initial_prompt, ddl_list, max_tokens=14000
        )

        initial_prompt = self.add_documentation_to_prompt(
            initial_prompt, doc_list, max_tokens=14000
        )

        initial_prompt = self.add_sql_to_prompt(
            initial_prompt, question_sql_list, max_tokens=14000
        )

        message_log = [self.system_message(initial_prompt)]
        message_log.append(
            self.user_message(
                "Generate a list of followup questions that the user might ask about this data. Respond with a list of questions, one per line. Do not answer with any explanations -- just the questions."
            )
        )

        return message_log

    @abstractmethod
    def submit_prompt(self, prompt, **kwargs) -> str:
        """
        Example:
        ```python
        vn.submit_prompt(
            [
                vn.system_message("The user will give you SQL and you will try to guess what the business question this query is answering. Return just the question without any additional explanation. Do not reference the table name in the question."),
                vn.user_message("What are the top 10 customers by sales?"),
            ]
        )
        ```

        This method is used to submit a prompt to the LLM.

        Args:
            prompt (any): The prompt to submit to the LLM.

        Returns:
            str: The response from the LLM.
        """
        pass

    def generate_question(self, sql: str, **kwargs) -> str:
        response = self.submit_prompt(
            [
                self.system_message(
                    "The user will give you SQL and you will try to guess what the business question this query is answering. Return just the question without any additional explanation. Do not reference the table name in the question."
                ),
                self.user_message(sql),
            ],
            **kwargs,
        )

        return response

    def _extract_python_code(self, markdown_string: str) -> str:
        # Regex pattern to match Python code blocks
        pattern = r"```[\w\s]*python\n([\s\S]*?)```|```([\s\S]*?)```"

        # Find all matches in the markdown string
        matches = re.findall(pattern, markdown_string, re.IGNORECASE)

        # Extract the Python code from the matches
        python_code = []
        for match in matches:
            python = match[0] if match[0] else match[1]
            python_code.append(python.strip())

        if len(python_code) == 0:
            return markdown_string

        return python_code[0]

    def _sanitize_plotly_code(self, raw_plotly_code: str) -> str:
        # Remove the fig.show() statement from the plotly code
        plotly_code = raw_plotly_code.replace("fig.show()", "")

        return plotly_code

    def generate_plotly_code(
        self, question: str = None, sql: str = None, df_metadata: str = None, **kwargs
    ) -> str:
        if question is not None:
            system_msg = f"The following is a pandas DataFrame that contains the results of the query that answers the question the user asked: '{question}'"
        else:
            system_msg = "The following is a pandas DataFrame "

        if sql is not None:
            system_msg += f"\n\nThe DataFrame was produced using this query: {sql}\n\n"

        system_msg += f"The following is information about the resulting pandas DataFrame 'df': \n{df_metadata}"

        message_log = [
            self.system_message(system_msg),
            self.user_message(
                "Can you generate the Python plotly code to chart the results of the dataframe? Assume the data is in a pandas dataframe called 'df'. If there is only one value in the dataframe, use an Indicator. Respond with only Python code. Do not answer with any explanations -- just the code."
            ),
        ]

        plotly_code = self.submit_prompt(message_log, kwargs=kwargs)

        return self._sanitize_plotly_code(self._extract_python_code(plotly_code))

    # ----------------- Connect to Any Database to run the Generated SQL ----------------- #

    def connect_to_snowflake(
        self,
        account: str,
        username: str,
        password: str,
        database: str,
        role: Union[str, None] = None,
        warehouse: Union[str, None] = None,
    ):
        try:
            snowflake = __import__("snowflake.connector")
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method, run command:"
                " \npip install vanna[snowflake]"
            )

        if username == "my-username":
            username_env = os.getenv("SNOWFLAKE_USERNAME")

            if username_env is not None:
                username = username_env
            else:
                raise ImproperlyConfigured("Please set your Snowflake username.")

        if password == "my-password":
            password_env = os.getenv("SNOWFLAKE_PASSWORD")

            if password_env is not None:
                password = password_env
            else:
                raise ImproperlyConfigured("Please set your Snowflake password.")

        if account == "my-account":
            account_env = os.getenv("SNOWFLAKE_ACCOUNT")

            if account_env is not None:
                account = account_env
            else:
                raise ImproperlyConfigured("Please set your Snowflake account.")

        if database == "my-database":
            database_env = os.getenv("SNOWFLAKE_DATABASE")

            if database_env is not None:
                database = database_env
            else:
                raise ImproperlyConfigured("Please set your Snowflake database.")

        conn = snowflake.connector.connect(
            user=username,
            password=password,
            account=account,
            database=database,
        )

        def run_sql_snowflake(sql: str) -> pd.DataFrame:
            cs = conn.cursor()

            if role is not None:
                cs.execute(f"USE ROLE {role}")

            if warehouse is not None:
                cs.execute(f"USE WAREHOUSE {warehouse}")
            cs.execute(f"USE DATABASE {database}")

            cur = cs.execute(sql)

            results = cur.fetchall()

            # Create a pandas dataframe from the results
            df = pd.DataFrame(results, columns=[desc[0] for desc in cur.description])

            return df

        self.static_documentation = "This is a Snowflake database"
        self.run_sql = run_sql_snowflake
        self.run_sql_is_set = True

    def connect_to_sqlite(self, url: str):
        """
        Connect to a SQLite database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

        Args:
            url (str): The URL of the database to connect to.

        Returns:
            None
        """

        # URL of the database to download

        # Path to save the downloaded database
        path = os.path.basename(urlparse(url).path)

        # Download the database if it doesn't exist
        if not os.path.exists(url):
            response = requests.get(url)
            response.raise_for_status()  # Check that the request was successful
            with open(path, "wb") as f:
                f.write(response.content)
            url = path

        # Connect to the database
        conn = sqlite3.connect(url, check_same_thread=False)

        def run_sql_sqlite(sql: str):
            return pd.read_sql_query(sql, conn)

        self.static_documentation = "This is a SQLite database"
        self.run_sql = run_sql_sqlite
        self.run_sql_is_set = True

    def connect_to_postgres(
        self,
        host: str = None,
        dbname: str = None,
        user: str = None,
        password: str = None,
        port: int = None,
    ):
        """
        Connect to postgres using the psycopg2 connector. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
        **Example:**
        ```python
        vn.connect_to_postgres(
            host="myhost",
            dbname="mydatabase",
            user="myuser",
            password="mypassword",
            port=5432
        )
        ```
        Args:
            host (str): The postgres host.
            dbname (str): The postgres database name.
            user (str): The postgres user.
            password (str): The postgres password.
            port (int): The postgres Port.
        """

        try:
            import psycopg2
            import psycopg2.extras
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: \npip install vanna[postgres]"
            )

        if not host:
            host = os.getenv("HOST")

        if not host:
            raise ImproperlyConfigured("Please set your postgres host")

        if not dbname:
            dbname = os.getenv("DATABASE")

        if not dbname:
            raise ImproperlyConfigured("Please set your postgres database")

        if not user:
            user = os.getenv("PG_USER")

        if not user:
            raise ImproperlyConfigured("Please set your postgres user")

        if not password:
            password = os.getenv("PASSWORD")

        if not password:
            raise ImproperlyConfigured("Please set your postgres password")

        if not port:
            port = os.getenv("PORT")

        if not port:
            raise ImproperlyConfigured("Please set your postgres port")

        conn = None

        try:
            conn = psycopg2.connect(
                host=host,
                dbname=dbname,
                user=user,
                password=password,
                port=port,
            )
        except psycopg2.Error as e:
            raise ValidationError(e)

        def run_sql_postgres(sql: str) -> Union[pd.DataFrame, None]:
            if conn:
                try:
                    cs = conn.cursor()
                    cs.execute(sql)
                    results = cs.fetchall()

                    # Create a pandas dataframe from the results
                    df = pd.DataFrame(
                        results, columns=[desc[0] for desc in cs.description]
                    )
                    return df

                except psycopg2.Error as e:
                    conn.rollback()
                    raise ValidationError(e)

                except Exception as e:
                    conn.rollback()
                    raise e

        self.static_documentation = "This is a Postgres database"
        self.run_sql_is_set = True
        self.run_sql = run_sql_postgres


    def connect_to_mysql(
            self,
            host: str = None,
            dbname: str = None,
            user: str = None,
            password: str = None,
            port: int = None,
    ):

        try:
            import pymysql.cursors
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: \npip install PyMySQL"
            )

        if not host:
            host = os.getenv("HOST")

        if not host:
            raise ImproperlyConfigured("Please set your MySQL host")

        if not dbname:
            dbname = os.getenv("DATABASE")

        if not dbname:
            raise ImproperlyConfigured("Please set your MySQL database")

        if not user:
            user = os.getenv("USER")

        if not user:
            raise ImproperlyConfigured("Please set your MySQL user")

        if not password:
            password = os.getenv("PASSWORD")

        if not password:
            raise ImproperlyConfigured("Please set your MySQL password")

        if not port:
            port = os.getenv("PORT")

        if not port:
            raise ImproperlyConfigured("Please set your MySQL port")

        conn = None

        try:
            conn = pymysql.connect(host=host,
                                   user=user,
                                   password=password,
                                   database=dbname,
                                   port=port,
                                   cursorclass=pymysql.cursors.DictCursor)
        except pymysql.Error as e:
            raise ValidationError(e)

        def run_sql_mysql(sql: str) -> Union[pd.DataFrame, None]:
            if conn:
                try:
                    cs = conn.cursor()
                    cs.execute(sql)
                    results = cs.fetchall()

                    # Create a pandas dataframe from the results
                    df = pd.DataFrame(
                        results, columns=[desc[0] for desc in cs.description]
                    )
                    return df

                except pymysql.Error as e:
                    conn.rollback()
                    raise ValidationError(e)

                except Exception as e:
                    conn.rollback()
                    raise e

        self.run_sql_is_set = True
        self.run_sql = run_sql_mysql


    def connect_to_bigquery(self, cred_file_path: str = None, project_id: str = None):
        """
        Connect to gcs using the bigquery connector. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
        **Example:**
        ```python
        vn.connect_to_bigquery(
            project_id="myprojectid",
            cred_file_path="path/to/credentials.json",
        )
        ```
        Args:
            project_id (str): The gcs project id.
            cred_file_path (str): The gcs credential file path
        """

        try:
            from google.api_core.exceptions import GoogleAPIError
            from google.cloud import bigquery
            from google.oauth2 import service_account
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method, run command:"
                " \npip install vanna[bigquery]"
            )

        if not project_id:
            project_id = os.getenv("PROJECT_ID")

        if not project_id:
            raise ImproperlyConfigured("Please set your Google Cloud Project ID.")

        import sys

        if "google.colab" in sys.modules:
            try:
                from google.colab import auth

                auth.authenticate_user()
            except Exception as e:
                raise ImproperlyConfigured(e)
        else:
            print("Not using Google Colab.")

        conn = None

        try:
            conn = bigquery.Client(project=project_id)
        except:
            print("Could not found any google cloud implicit credentials")

        if cred_file_path:
            # Validate file path and pemissions
            validate_config_path(cred_file_path)
        else:
            if not conn:
                raise ValidationError(
                    "Pleae provide a service account credentials json file"
                )

        if not conn:
            with open(cred_file_path, "r") as f:
                credentials = service_account.Credentials.from_service_account_info(
                    json.loads(f.read()),
                    scopes=["https://www.googleapis.com/auth/cloud-platform"],
                )

            try:
                conn = bigquery.Client(project=project_id, credentials=credentials)
            except:
                raise ImproperlyConfigured(
                    "Could not connect to bigquery please correct credentials"
                )

        def run_sql_bigquery(sql: str) -> Union[pd.DataFrame, None]:
            if conn:
                try:
                    job = conn.query(sql)
                    df = job.result().to_dataframe()
                    return df
                except GoogleAPIError as error:
                    errors = []
                    for error in error.errors:
                        errors.append(error["message"])
                    raise errors
            return None

        self.static_documentation = "This is a BigQuery database"
        self.run_sql_is_set = True
        self.run_sql = run_sql_bigquery

    def connect_to_duckdb(self, url: str, init_sql: str = None):
        """
        Connect to a DuckDB database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

        Args:
            url (str): The URL of the database to connect to. Use :memory: to create an in-memory database. Use md: or motherduck: to use the MotherDuck database.
            init_sql (str, optional): SQL to run when connecting to the database. Defaults to None.

        Returns:
            None
        """
        try:
            import duckdb
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: \npip install vanna[duckdb]"
            )
        # URL of the database to download
        if url == ":memory:" or url == "":
            path = ":memory:"
        else:
            # Path to save the downloaded database
            print(os.path.exists(url))
            if os.path.exists(url):
                path = url
            elif url.startswith("md") or url.startswith("motherduck"):
                path = url
            else:
                path = os.path.basename(urlparse(url).path)
                # Download the database if it doesn't exist
                if not os.path.exists(path):
                    response = requests.get(url)
                    response.raise_for_status()  # Check that the request was successful
                    with open(path, "wb") as f:
                        f.write(response.content)

        # Connect to the database
        conn = duckdb.connect(path)
        if init_sql:
            conn.query(init_sql)

        def run_sql_duckdb(sql: str):
            return conn.query(sql).to_df()

        self.static_documentation = "This is a DuckDB database"
        self.run_sql = run_sql_duckdb
        self.run_sql_is_set = True

    def connect_to_mssql(self, odbc_conn_str: str):
        """
        Connect to a Microsoft SQL Server database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

        Args:
            odbc_conn_str (str): The ODBC connection string.

        Returns:
            None
        """
        try:
            import pyodbc
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: pip install pyodbc"
            )

        try:
            import sqlalchemy as sa
            from sqlalchemy.engine import URL
        except ImportError:
            raise DependencyError(
                "You need to install required dependencies to execute this method,"
                " run command: pip install sqlalchemy"
            )

        connection_url = URL.create(
            "mssql+pyodbc", query={"odbc_connect": odbc_conn_str}
        )

        from sqlalchemy import create_engine

        engine = create_engine(connection_url)

        def run_sql_mssql(sql: str):
            # Execute the SQL statement and return the result as a pandas DataFrame
            with engine.begin() as conn:
                df = pd.read_sql_query(sa.text(sql), conn)
                return df

            raise Exception("Couldn't run sql")

        self.static_documentation = "This is a Microsoft SQL Server database"
        self.run_sql = run_sql_mssql
        self.run_sql_is_set = True

    def run_sql(self, sql: str, **kwargs) -> pd.DataFrame:
        """
        Example:
        ```python
        vn.run_sql("SELECT * FROM my_table")
        ```

        Run a SQL query on the connected database.

        Args:
            sql (str): The SQL query to run.

        Returns:
            pd.DataFrame: The results of the SQL query.
        """
        raise Exception(
            "You need to connect to a database first by running vn.connect_to_snowflake(), vn.connect_to_postgres(), similar function, or manually set vn.run_sql"
        )

    def ask(
        self,
        question: Union[str, None] = None,
        print_results: bool = True,
        auto_train: bool = True,
        visualize: bool = True,  # if False, will not generate plotly code
    ) -> Union[
        Tuple[
            Union[str, None],
            Union[pd.DataFrame, None],
            Union[plotly.graph_objs.Figure, None],
        ],
        None,
    ]:
        """
        **Example:**
        ```python
        vn.ask("What are the top 10 customers by sales?")
        ```

        Ask Vanna.AI a question and get the SQL query that answers it.

        Args:
            question (str): The question to ask.
            print_results (bool): Whether to print the results of the SQL query.
            auto_train (bool): Whether to automatically train Vanna.AI on the question and SQL query.
            visualize (bool): Whether to generate plotly code and display the plotly figure.

        Returns:
            Tuple[str, pd.DataFrame, plotly.graph_objs.Figure]: The SQL query, the results of the SQL query, and the plotly figure.
        """

        if question is None:
            question = input("Enter a question: ")

        try:
            sql = self.generate_sql(question=question)
        except Exception as e:
            print(e)
            return None, None, None

        if print_results:
            try:
                Code = __import__("IPython.display", fromList=["Code"]).Code
                display(Code(sql))
            except Exception as e:
                print(sql)

        if self.run_sql_is_set is False:
            print(
                "If you want to run the SQL query, connect to a database first. See here: https://vanna.ai/docs/databases.html"
            )

            if print_results:
                return None
            else:
                return sql, None, None

        try:
            df = self.run_sql(sql)

            if print_results:
                try:
                    display = __import__(
                        "IPython.display", fromList=["display"]
                    ).display
                    display(df)
                except Exception as e:
                    print(df)

            if len(df) > 0 and auto_train:
                self.add_question_sql(question=question, sql=sql)
            # Only generate plotly code if visualize is True
            if visualize:
                try:
                    plotly_code = self.generate_plotly_code(
                        question=question,
                        sql=sql,
                        df_metadata=f"Running df.dtypes gives:\n {df.dtypes}",
                    )
                    fig = self.get_plotly_figure(plotly_code=plotly_code, df=df)
                    if print_results:
                        try:
                            display = __import__(
                                "IPython.display", fromlist=["display"]
                            ).display
                            Image = __import__(
                                "IPython.display", fromlist=["Image"]
                            ).Image
                            img_bytes = fig.to_image(format="png", scale=2)
                            display(Image(img_bytes))
                        except Exception as e:
                            fig.show()
                except Exception as e:
                    # Print stack trace
                    traceback.print_exc()
                    print("Couldn't run plotly code: ", e)
                    if print_results:
                        return None
                    else:
                        return sql, df, None
            else:
                return sql, df, None

        except Exception as e:
            print("Couldn't run sql: ", e)
            if print_results:
                return None
            else:
                return sql, None, None
        return sql, df, None

    def train(
        self,
        question: str = None,
        sql: str = None,
        ddl: str = None,
        documentation: str = None,
        plan: TrainingPlan = None,
    ) -> str:
        """
        **Example:**
        ```python
        vn.train()
        ```

        Train Vanna.AI on a question and its corresponding SQL query.
        If you call it with no arguments, it will check if you connected to a database and it will attempt to train on the metadata of that database.
        If you call it with the sql argument, it's equivalent to [`vn.add_question_sql()`][vanna.base.base.VannaBase.add_question_sql].
        If you call it with the ddl argument, it's equivalent to [`vn.add_ddl()`][vanna.base.base.VannaBase.add_ddl].
        If you call it with the documentation argument, it's equivalent to [`vn.add_documentation()`][vanna.base.base.VannaBase.add_documentation].
        Additionally, you can pass a [`TrainingPlan`][vanna.types.TrainingPlan] object. Get a training plan with [`vn.get_training_plan_generic()`][vanna.base.base.VannaBase.get_training_plan_generic].

        Args:
            question (str): The question to train on.
            sql (str): The SQL query to train on.
            ddl (str):  The DDL statement.
            documentation (str): The documentation to train on.
            plan (TrainingPlan): The training plan to train on.
        """

        if question and not sql:
            raise ValidationError(f"Please also provide a SQL query")

        if documentation:
            print("Adding documentation....")
            return self.add_documentation(documentation)

        if sql:
            if question is None:
                question = self.generate_question(sql)
                print("Question generated with sql:", question, "\nAdding SQL...")
            return self.add_question_sql(question=question, sql=sql)

        if ddl:
            print("Adding ddl:", ddl)
            return self.add_ddl(ddl)

        if plan:
            for item in plan._plan:
                if item.item_type == TrainingPlanItem.ITEM_TYPE_DDL:
                    self.add_ddl(item.item_value)
                elif item.item_type == TrainingPlanItem.ITEM_TYPE_IS:
                    self.add_documentation(item.item_value)
                elif item.item_type == TrainingPlanItem.ITEM_TYPE_SQL:
                    self.add_question_sql(question=item.item_name, sql=item.item_value)

    def _get_databases(self) -> List[str]:
        try:
            print("Trying INFORMATION_SCHEMA.DATABASES")
            df_databases = self.run_sql("SELECT * FROM INFORMATION_SCHEMA.DATABASES")
        except Exception as e:
            print(e)
            try:
                print("Trying SHOW DATABASES")
                df_databases = self.run_sql("SHOW DATABASES")
            except Exception as e:
                print(e)
                return []

        return df_databases["DATABASE_NAME"].unique().tolist()

    def _get_information_schema_tables(self, database: str) -> pd.DataFrame:
        df_tables = self.run_sql(f"SELECT * FROM {database}.INFORMATION_SCHEMA.TABLES")

        return df_tables

    def get_training_plan_generic(self, df) -> TrainingPlan:
        """
        This method is used to generate a training plan from an information schema dataframe.

        Basically what it does is breaks up INFORMATION_SCHEMA.COLUMNS into groups of table/column descriptions that can be used to pass to the LLM.

        Args:
            df (pd.DataFrame): The dataframe to generate the training plan from.

        Returns:
            TrainingPlan: The training plan.
        """
        # For each of the following, we look at the df columns to see if there's a match:
        database_column = df.columns[
            df.columns.str.lower().str.contains("database")
            | df.columns.str.lower().str.contains("table_catalog")
        ].to_list()[0]
        schema_column = df.columns[
            df.columns.str.lower().str.contains("table_schema")
        ].to_list()[0]
        table_column = df.columns[
            df.columns.str.lower().str.contains("table_name")
        ].to_list()[0]
        column_column = df.columns[
            df.columns.str.lower().str.contains("column_name")
        ].to_list()[0]
        data_type_column = df.columns[
            df.columns.str.lower().str.contains("data_type")
        ].to_list()[0]

        plan = TrainingPlan([])

        for database in df[database_column].unique().tolist():
            for schema in (
                df.query(f'{database_column} == "{database}"')[schema_column]
                .unique()
                .tolist()
            ):
                for table in (
                    df.query(
                        f'{database_column} == "{database}" and {schema_column} == "{schema}"'
                    )[table_column]
                    .unique()
                    .tolist()
                ):
                    df_columns_filtered_to_table = df.query(
                        f'{database_column} == "{database}" and {schema_column} == "{schema}" and {table_column} == "{table}"'
                    )
                    doc = f"The following columns are in the {table} table in the {database} database:\n\n"
                    doc += df_columns_filtered_to_table[
                        [
                            database_column,
                            schema_column,
                            table_column,
                            column_column,
                            data_type_column,
                        ]
                    ].to_markdown()

                    plan._plan.append(
                        TrainingPlanItem(
                            item_type=TrainingPlanItem.ITEM_TYPE_IS,
                            item_group=f"{database}.{schema}",
                            item_name=table,
                            item_value=doc,
                        )
                    )

        return plan

    def get_training_plan_snowflake(
        self,
        filter_databases: Union[List[str], None] = None,
        filter_schemas: Union[List[str], None] = None,
        include_information_schema: bool = False,
        use_historical_queries: bool = True,
    ) -> TrainingPlan:
        plan = TrainingPlan([])

        if self.run_sql_is_set is False:
            raise ImproperlyConfigured("Please connect to a database first.")

        if use_historical_queries:
            try:
                print("Trying query history")
                df_history = self.run_sql(
                    """ select * from table(information_schema.query_history(result_limit => 5000)) order by start_time"""
                )

                df_history_filtered = df_history.query("ROWS_PRODUCED > 1")
                if filter_databases is not None:
                    mask = (
                        df_history_filtered["QUERY_TEXT"]
                        .str.lower()
                        .apply(
                            lambda x: any(
                                s in x for s in [s.lower() for s in filter_databases]
                            )
                        )
                    )
                    df_history_filtered = df_history_filtered[mask]

                if filter_schemas is not None:
                    mask = (
                        df_history_filtered["QUERY_TEXT"]
                        .str.lower()
                        .apply(
                            lambda x: any(
                                s in x for s in [s.lower() for s in filter_schemas]
                            )
                        )
                    )
                    df_history_filtered = df_history_filtered[mask]

                if len(df_history_filtered) > 10:
                    df_history_filtered = df_history_filtered.sample(10)

                for query in df_history_filtered["QUERY_TEXT"].unique().tolist():
                    plan._plan.append(
                        TrainingPlanItem(
                            item_type=TrainingPlanItem.ITEM_TYPE_SQL,
                            item_group="",
                            item_name=self.generate_question(query),
                            item_value=query,
                        )
                    )

            except Exception as e:
                print(e)

        databases = self._get_databases()

        for database in databases:
            if filter_databases is not None and database not in filter_databases:
                continue

            try:
                df_tables = self._get_information_schema_tables(database=database)

                print(f"Trying INFORMATION_SCHEMA.COLUMNS for {database}")
                df_columns = self.run_sql(
                    f"SELECT * FROM {database}.INFORMATION_SCHEMA.COLUMNS"
                )

                for schema in df_tables["TABLE_SCHEMA"].unique().tolist():
                    if filter_schemas is not None and schema not in filter_schemas:
                        continue

                    if (
                        not include_information_schema
                        and schema == "INFORMATION_SCHEMA"
                    ):
                        continue

                    df_columns_filtered_to_schema = df_columns.query(
                        f"TABLE_SCHEMA == '{schema}'"
                    )

                    try:
                        tables = (
                            df_columns_filtered_to_schema["TABLE_NAME"]
                            .unique()
                            .tolist()
                        )

                        for table in tables:
                            df_columns_filtered_to_table = (
                                df_columns_filtered_to_schema.query(
                                    f"TABLE_NAME == '{table}'"
                                )
                            )
                            doc = f"The following columns are in the {table} table in the {database} database:\n\n"
                            doc += df_columns_filtered_to_table[
                                [
                                    "TABLE_CATALOG",
                                    "TABLE_SCHEMA",
                                    "TABLE_NAME",
                                    "COLUMN_NAME",
                                    "DATA_TYPE",
                                    "COMMENT",
                                ]
                            ].to_markdown()

                            plan._plan.append(
                                TrainingPlanItem(
                                    item_type=TrainingPlanItem.ITEM_TYPE_IS,
                                    item_group=f"{database}.{schema}",
                                    item_name=table,
                                    item_value=doc,
                                )
                            )

                    except Exception as e:
                        print(e)
                        pass
            except Exception as e:
                print(e)

        return plan

    def get_plotly_figure(
        self, plotly_code: str, df: pd.DataFrame, dark_mode: bool = True
    ) -> plotly.graph_objs.Figure:
        """
        **Example:**
        ```python
        fig = vn.get_plotly_figure(
            plotly_code="fig = px.bar(df, x='name', y='salary')",
            df=df
        )
        fig.show()
        ```
        Get a Plotly figure from a dataframe and Plotly code.

        Args:
            df (pd.DataFrame): The dataframe to use.
            plotly_code (str): The Plotly code to use.

        Returns:
            plotly.graph_objs.Figure: The Plotly figure.
        """
        ldict = {"df": df, "px": px, "go": go}
        try:
            exec(plotly_code, globals(), ldict)

            fig = ldict.get("fig", None)
        except Exception as e:
            # Inspect data types
            numeric_cols = df.select_dtypes(include=["number"]).columns.tolist()
            categorical_cols = df.select_dtypes(
                include=["object", "category"]
            ).columns.tolist()

            # Decision-making for plot type
            if len(numeric_cols) >= 2:
                # Use the first two numeric columns for a scatter plot
                fig = px.scatter(df, x=numeric_cols[0], y=numeric_cols[1])
            elif len(numeric_cols) == 1 and len(categorical_cols) >= 1:
                # Use a bar plot if there's one numeric and one categorical column
                fig = px.bar(df, x=categorical_cols[0], y=numeric_cols[0])
            elif len(categorical_cols) >= 1 and df[categorical_cols[0]].nunique() < 10:
                # Use a pie chart for categorical data with fewer unique values
                fig = px.pie(df, names=categorical_cols[0])
            else:
                # Default to a simple line plot if above conditions are not met
                fig = px.line(df)

        if fig is None:
            return None

        if dark_mode:
            fig.update_layout(template="plotly_dark")

        return fig

add_ddl(ddl, **kwargs) abstractmethod

This method is used to add a DDL statement to the training data.

Parameters:

Name Type Description Default
ddl str

The DDL statement to add.

required

Returns:

Name Type Description
str str

The ID of the training data that was added.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
284
285
286
287
288
289
290
291
292
293
294
295
@abstractmethod
def add_ddl(self, ddl: str, **kwargs) -> str:
    """
    This method is used to add a DDL statement to the training data.

    Args:
        ddl (str): The DDL statement to add.

    Returns:
        str: The ID of the training data that was added.
    """
    pass

add_documentation(documentation, **kwargs) abstractmethod

This method is used to add documentation to the training data.

Parameters:

Name Type Description Default
documentation str

The documentation to add.

required

Returns:

Name Type Description
str str

The ID of the training data that was added.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
297
298
299
300
301
302
303
304
305
306
307
308
@abstractmethod
def add_documentation(self, documentation: str, **kwargs) -> str:
    """
    This method is used to add documentation to the training data.

    Args:
        documentation (str): The documentation to add.

    Returns:
        str: The ID of the training data that was added.
    """
    pass

add_question_sql(question, sql, **kwargs) abstractmethod

This method is used to add a question and its corresponding SQL query to the training data.

Parameters:

Name Type Description Default
question str

The question to add.

required
sql str

The SQL query to add.

required

Returns:

Name Type Description
str str

The ID of the training data that was added.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
270
271
272
273
274
275
276
277
278
279
280
281
282
@abstractmethod
def add_question_sql(self, question: str, sql: str, **kwargs) -> str:
    """
    This method is used to add a question and its corresponding SQL query to the training data.

    Args:
        question (str): The question to add.
        sql (str): The SQL query to add.

    Returns:
        str: The ID of the training data that was added.
    """
    pass

ask(question=None, print_results=True, auto_train=True, visualize=True)

Example:

vn.ask("What are the top 10 customers by sales?")

Ask Vanna.AI a question and get the SQL query that answers it.

Parameters:

Name Type Description Default
question str

The question to ask.

None
print_results bool

Whether to print the results of the SQL query.

True
auto_train bool

Whether to automatically train Vanna.AI on the question and SQL query.

True
visualize bool

Whether to generate plotly code and display the plotly figure.

True

Returns:

Type Description
Union[Tuple[Union[str, None], Union[DataFrame, None], Union[Figure, None]], None]

Tuple[str, pd.DataFrame, plotly.graph_objs.Figure]: The SQL query, the results of the SQL query, and the plotly figure.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
def ask(
    self,
    question: Union[str, None] = None,
    print_results: bool = True,
    auto_train: bool = True,
    visualize: bool = True,  # if False, will not generate plotly code
) -> Union[
    Tuple[
        Union[str, None],
        Union[pd.DataFrame, None],
        Union[plotly.graph_objs.Figure, None],
    ],
    None,
]:
    """
    **Example:**
    ```python
    vn.ask("What are the top 10 customers by sales?")
    ```

    Ask Vanna.AI a question and get the SQL query that answers it.

    Args:
        question (str): The question to ask.
        print_results (bool): Whether to print the results of the SQL query.
        auto_train (bool): Whether to automatically train Vanna.AI on the question and SQL query.
        visualize (bool): Whether to generate plotly code and display the plotly figure.

    Returns:
        Tuple[str, pd.DataFrame, plotly.graph_objs.Figure]: The SQL query, the results of the SQL query, and the plotly figure.
    """

    if question is None:
        question = input("Enter a question: ")

    try:
        sql = self.generate_sql(question=question)
    except Exception as e:
        print(e)
        return None, None, None

    if print_results:
        try:
            Code = __import__("IPython.display", fromList=["Code"]).Code
            display(Code(sql))
        except Exception as e:
            print(sql)

    if self.run_sql_is_set is False:
        print(
            "If you want to run the SQL query, connect to a database first. See here: https://vanna.ai/docs/databases.html"
        )

        if print_results:
            return None
        else:
            return sql, None, None

    try:
        df = self.run_sql(sql)

        if print_results:
            try:
                display = __import__(
                    "IPython.display", fromList=["display"]
                ).display
                display(df)
            except Exception as e:
                print(df)

        if len(df) > 0 and auto_train:
            self.add_question_sql(question=question, sql=sql)
        # Only generate plotly code if visualize is True
        if visualize:
            try:
                plotly_code = self.generate_plotly_code(
                    question=question,
                    sql=sql,
                    df_metadata=f"Running df.dtypes gives:\n {df.dtypes}",
                )
                fig = self.get_plotly_figure(plotly_code=plotly_code, df=df)
                if print_results:
                    try:
                        display = __import__(
                            "IPython.display", fromlist=["display"]
                        ).display
                        Image = __import__(
                            "IPython.display", fromlist=["Image"]
                        ).Image
                        img_bytes = fig.to_image(format="png", scale=2)
                        display(Image(img_bytes))
                    except Exception as e:
                        fig.show()
            except Exception as e:
                # Print stack trace
                traceback.print_exc()
                print("Couldn't run plotly code: ", e)
                if print_results:
                    return None
                else:
                    return sql, df, None
        else:
            return sql, df, None

    except Exception as e:
        print("Couldn't run sql: ", e)
        if print_results:
            return None
        else:
            return sql, None, None
    return sql, df, None

connect_to_bigquery(cred_file_path=None, project_id=None)

Connect to gcs using the bigquery connector. This is just a helper function to set vn.run_sql Example:

vn.connect_to_bigquery(
    project_id="myprojectid",
    cred_file_path="path/to/credentials.json",
)
Args: project_id (str): The gcs project id. cred_file_path (str): The gcs credential file path

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
def connect_to_bigquery(self, cred_file_path: str = None, project_id: str = None):
    """
    Connect to gcs using the bigquery connector. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
    **Example:**
    ```python
    vn.connect_to_bigquery(
        project_id="myprojectid",
        cred_file_path="path/to/credentials.json",
    )
    ```
    Args:
        project_id (str): The gcs project id.
        cred_file_path (str): The gcs credential file path
    """

    try:
        from google.api_core.exceptions import GoogleAPIError
        from google.cloud import bigquery
        from google.oauth2 import service_account
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method, run command:"
            " \npip install vanna[bigquery]"
        )

    if not project_id:
        project_id = os.getenv("PROJECT_ID")

    if not project_id:
        raise ImproperlyConfigured("Please set your Google Cloud Project ID.")

    import sys

    if "google.colab" in sys.modules:
        try:
            from google.colab import auth

            auth.authenticate_user()
        except Exception as e:
            raise ImproperlyConfigured(e)
    else:
        print("Not using Google Colab.")

    conn = None

    try:
        conn = bigquery.Client(project=project_id)
    except:
        print("Could not found any google cloud implicit credentials")

    if cred_file_path:
        # Validate file path and pemissions
        validate_config_path(cred_file_path)
    else:
        if not conn:
            raise ValidationError(
                "Pleae provide a service account credentials json file"
            )

    if not conn:
        with open(cred_file_path, "r") as f:
            credentials = service_account.Credentials.from_service_account_info(
                json.loads(f.read()),
                scopes=["https://www.googleapis.com/auth/cloud-platform"],
            )

        try:
            conn = bigquery.Client(project=project_id, credentials=credentials)
        except:
            raise ImproperlyConfigured(
                "Could not connect to bigquery please correct credentials"
            )

    def run_sql_bigquery(sql: str) -> Union[pd.DataFrame, None]:
        if conn:
            try:
                job = conn.query(sql)
                df = job.result().to_dataframe()
                return df
            except GoogleAPIError as error:
                errors = []
                for error in error.errors:
                    errors.append(error["message"])
                raise errors
        return None

    self.static_documentation = "This is a BigQuery database"
    self.run_sql_is_set = True
    self.run_sql = run_sql_bigquery

connect_to_duckdb(url, init_sql=None)

Connect to a DuckDB database. This is just a helper function to set vn.run_sql

Parameters:

Name Type Description Default
url str

The URL of the database to connect to. Use :memory: to create an in-memory database. Use md: or motherduck: to use the MotherDuck database.

required
init_sql str

SQL to run when connecting to the database. Defaults to None.

None

Returns:

Type Description

None

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
 982
 983
 984
 985
 986
 987
 988
 989
 990
 991
 992
 993
 994
 995
 996
 997
 998
 999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
def connect_to_duckdb(self, url: str, init_sql: str = None):
    """
    Connect to a DuckDB database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

    Args:
        url (str): The URL of the database to connect to. Use :memory: to create an in-memory database. Use md: or motherduck: to use the MotherDuck database.
        init_sql (str, optional): SQL to run when connecting to the database. Defaults to None.

    Returns:
        None
    """
    try:
        import duckdb
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method,"
            " run command: \npip install vanna[duckdb]"
        )
    # URL of the database to download
    if url == ":memory:" or url == "":
        path = ":memory:"
    else:
        # Path to save the downloaded database
        print(os.path.exists(url))
        if os.path.exists(url):
            path = url
        elif url.startswith("md") or url.startswith("motherduck"):
            path = url
        else:
            path = os.path.basename(urlparse(url).path)
            # Download the database if it doesn't exist
            if not os.path.exists(path):
                response = requests.get(url)
                response.raise_for_status()  # Check that the request was successful
                with open(path, "wb") as f:
                    f.write(response.content)

    # Connect to the database
    conn = duckdb.connect(path)
    if init_sql:
        conn.query(init_sql)

    def run_sql_duckdb(sql: str):
        return conn.query(sql).to_df()

    self.static_documentation = "This is a DuckDB database"
    self.run_sql = run_sql_duckdb
    self.run_sql_is_set = True

connect_to_mssql(odbc_conn_str)

Connect to a Microsoft SQL Server database. This is just a helper function to set vn.run_sql

Parameters:

Name Type Description Default
odbc_conn_str str

The ODBC connection string.

required

Returns:

Type Description

None

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
def connect_to_mssql(self, odbc_conn_str: str):
    """
    Connect to a Microsoft SQL Server database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

    Args:
        odbc_conn_str (str): The ODBC connection string.

    Returns:
        None
    """
    try:
        import pyodbc
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method,"
            " run command: pip install pyodbc"
        )

    try:
        import sqlalchemy as sa
        from sqlalchemy.engine import URL
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method,"
            " run command: pip install sqlalchemy"
        )

    connection_url = URL.create(
        "mssql+pyodbc", query={"odbc_connect": odbc_conn_str}
    )

    from sqlalchemy import create_engine

    engine = create_engine(connection_url)

    def run_sql_mssql(sql: str):
        # Execute the SQL statement and return the result as a pandas DataFrame
        with engine.begin() as conn:
            df = pd.read_sql_query(sa.text(sql), conn)
            return df

        raise Exception("Couldn't run sql")

    self.static_documentation = "This is a Microsoft SQL Server database"
    self.run_sql = run_sql_mssql
    self.run_sql_is_set = True

connect_to_postgres(host=None, dbname=None, user=None, password=None, port=None)

Connect to postgres using the psycopg2 connector. This is just a helper function to set vn.run_sql Example:

vn.connect_to_postgres(
    host="myhost",
    dbname="mydatabase",
    user="myuser",
    password="mypassword",
    port=5432
)
Args: host (str): The postgres host. dbname (str): The postgres database name. user (str): The postgres user. password (str): The postgres password. port (int): The postgres Port.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
def connect_to_postgres(
    self,
    host: str = None,
    dbname: str = None,
    user: str = None,
    password: str = None,
    port: int = None,
):
    """
    Connect to postgres using the psycopg2 connector. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]
    **Example:**
    ```python
    vn.connect_to_postgres(
        host="myhost",
        dbname="mydatabase",
        user="myuser",
        password="mypassword",
        port=5432
    )
    ```
    Args:
        host (str): The postgres host.
        dbname (str): The postgres database name.
        user (str): The postgres user.
        password (str): The postgres password.
        port (int): The postgres Port.
    """

    try:
        import psycopg2
        import psycopg2.extras
    except ImportError:
        raise DependencyError(
            "You need to install required dependencies to execute this method,"
            " run command: \npip install vanna[postgres]"
        )

    if not host:
        host = os.getenv("HOST")

    if not host:
        raise ImproperlyConfigured("Please set your postgres host")

    if not dbname:
        dbname = os.getenv("DATABASE")

    if not dbname:
        raise ImproperlyConfigured("Please set your postgres database")

    if not user:
        user = os.getenv("PG_USER")

    if not user:
        raise ImproperlyConfigured("Please set your postgres user")

    if not password:
        password = os.getenv("PASSWORD")

    if not password:
        raise ImproperlyConfigured("Please set your postgres password")

    if not port:
        port = os.getenv("PORT")

    if not port:
        raise ImproperlyConfigured("Please set your postgres port")

    conn = None

    try:
        conn = psycopg2.connect(
            host=host,
            dbname=dbname,
            user=user,
            password=password,
            port=port,
        )
    except psycopg2.Error as e:
        raise ValidationError(e)

    def run_sql_postgres(sql: str) -> Union[pd.DataFrame, None]:
        if conn:
            try:
                cs = conn.cursor()
                cs.execute(sql)
                results = cs.fetchall()

                # Create a pandas dataframe from the results
                df = pd.DataFrame(
                    results, columns=[desc[0] for desc in cs.description]
                )
                return df

            except psycopg2.Error as e:
                conn.rollback()
                raise ValidationError(e)

            except Exception as e:
                conn.rollback()
                raise e

    self.static_documentation = "This is a Postgres database"
    self.run_sql_is_set = True
    self.run_sql = run_sql_postgres

connect_to_sqlite(url)

Connect to a SQLite database. This is just a helper function to set vn.run_sql

Parameters:

Name Type Description Default
url str

The URL of the database to connect to.

required

Returns:

Type Description

None

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
def connect_to_sqlite(self, url: str):
    """
    Connect to a SQLite database. This is just a helper function to set [`vn.run_sql`][vanna.base.base.VannaBase.run_sql]

    Args:
        url (str): The URL of the database to connect to.

    Returns:
        None
    """

    # URL of the database to download

    # Path to save the downloaded database
    path = os.path.basename(urlparse(url).path)

    # Download the database if it doesn't exist
    if not os.path.exists(url):
        response = requests.get(url)
        response.raise_for_status()  # Check that the request was successful
        with open(path, "wb") as f:
            f.write(response.content)
        url = path

    # Connect to the database
    conn = sqlite3.connect(url, check_same_thread=False)

    def run_sql_sqlite(sql: str):
        return pd.read_sql_query(sql, conn)

    self.static_documentation = "This is a SQLite database"
    self.run_sql = run_sql_sqlite
    self.run_sql_is_set = True

generate_followup_questions(question, sql, df, **kwargs)

Example:

vn.generate_followup_questions("What are the top 10 customers by sales?", df)

Generate a list of followup questions that you can ask Vanna.AI.

Parameters:

Name Type Description Default
question str

The question that was asked.

required
df DataFrame

The results of the SQL query.

required

Returns:

Name Type Description
list list

A list of followup questions that you can ask Vanna.AI.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
def generate_followup_questions(
    self, question: str, sql: str, df: pd.DataFrame, **kwargs
) -> list:
    """
    **Example:**
    ```python
    vn.generate_followup_questions("What are the top 10 customers by sales?", df)
    ```

    Generate a list of followup questions that you can ask Vanna.AI.

    Args:
        question (str): The question that was asked.
        df (pd.DataFrame): The results of the SQL query.

    Returns:
        list: A list of followup questions that you can ask Vanna.AI.
    """

    message_log = [
        self.system_message(
            f"You are a helpful data assistant. The user asked the question: '{question}'\n\nThe SQL query for this question was: {sql}\n\nThe following is a pandas DataFrame with the results of the query: \n{df.to_markdown()}\n\n"
        ),
        self.user_message(
            "Generate a list of followup questions that the user might ask about this data. Respond with a list of questions, one per line. Do not answer with any explanations -- just the questions. Remember that there should be an unambiguous SQL query that can be generated from the question. Prefer questions that are answerable outside of the context of this conversation. Prefer questions that are slight modifications of the SQL query that was generated that allow digging deeper into the data. Each question will be turned into a button that the user can click to generate a new SQL query so don't use 'example' type questions. Each question must have a one-to-one correspondence with an instantiated SQL query."
        ),
    ]

    llm_response = self.submit_prompt(message_log, **kwargs)

    numbers_removed = re.sub(r"^\d+\.\s*", "", llm_response, flags=re.MULTILINE)
    return numbers_removed.split("\n")

generate_questions(**kwargs)

Example:

vn.generate_questions()

Generate a list of questions that you can ask Vanna.AI.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
182
183
184
185
186
187
188
189
190
191
192
193
def generate_questions(self, **kwargs) -> List[str]:
    """
    **Example:**
    ```python
    vn.generate_questions()
    ```

    Generate a list of questions that you can ask Vanna.AI.
    """
    question_sql = self.get_similar_question_sql(question="", **kwargs)

    return [q["question"] for q in question_sql]

generate_sql(question, **kwargs)

Example:

vn.generate_sql("What are the top 10 customers by sales?")

Uses the LLM to generate a SQL query that answers a question. It runs the following methods:

Parameters:

Name Type Description Default
question str

The question to generate a SQL query for.

required

Returns:

Name Type Description
str str

The SQL query that answers the question.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
def generate_sql(self, question: str, **kwargs) -> str:
    """
    Example:
    ```python
    vn.generate_sql("What are the top 10 customers by sales?")
    ```

    Uses the LLM to generate a SQL query that answers a question. It runs the following methods:

    - [`get_similar_question_sql`][vanna.base.base.VannaBase.get_similar_question_sql]

    - [`get_related_ddl`][vanna.base.base.VannaBase.get_related_ddl]

    - [`get_related_documentation`][vanna.base.base.VannaBase.get_related_documentation]

    - [`get_sql_prompt`][vanna.base.base.VannaBase.get_sql_prompt]

    - [`submit_prompt`][vanna.base.base.VannaBase.submit_prompt]


    Args:
        question (str): The question to generate a SQL query for.

    Returns:
        str: The SQL query that answers the question.
    """
    if self.config is not None:
        initial_prompt = self.config.get("initial_prompt", None)
    else:
        initial_prompt = None
    question_sql_list = self.get_similar_question_sql(question, **kwargs)
    ddl_list = self.get_related_ddl(question, **kwargs)
    doc_list = self.get_related_documentation(question, **kwargs)
    prompt = self.get_sql_prompt(
        initial_prompt=initial_prompt,
        question=question,
        question_sql_list=question_sql_list,
        ddl_list=ddl_list,
        doc_list=doc_list,
        **kwargs,
    )
    self.log(prompt)
    llm_response = self.submit_prompt(prompt, **kwargs)
    self.log(llm_response)
    return self.extract_sql(llm_response)

generate_summary(question, df, **kwargs)

Example:

vn.generate_summary("What are the top 10 customers by sales?", df)

Generate a summary of the results of a SQL query.

Parameters:

Name Type Description Default
question str

The question that was asked.

required
df DataFrame

The results of the SQL query.

required

Returns:

Name Type Description
str str

The summary of the results of the SQL query.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
def generate_summary(self, question: str, df: pd.DataFrame, **kwargs) -> str:
    """
    **Example:**
    ```python
    vn.generate_summary("What are the top 10 customers by sales?", df)
    ```

    Generate a summary of the results of a SQL query.

    Args:
        question (str): The question that was asked.
        df (pd.DataFrame): The results of the SQL query.

    Returns:
        str: The summary of the results of the SQL query.
    """

    message_log = [
        self.system_message(
            f"You are a helpful data assistant. The user asked the question: '{question}'\n\nThe following is a pandas DataFrame with the results of the query: \n{df.to_markdown()}\n\n"
        ),
        self.user_message(
            "Briefly summarize the data based on the question that was asked. Do not respond with any additional explanation beyond the summary."
        ),
    ]

    summary = self.submit_prompt(message_log, **kwargs)

    return summary

get_plotly_figure(plotly_code, df, dark_mode=True)

Example:

fig = vn.get_plotly_figure(
    plotly_code="fig = px.bar(df, x='name', y='salary')",
    df=df
)
fig.show()
Get a Plotly figure from a dataframe and Plotly code.

Parameters:

Name Type Description Default
df DataFrame

The dataframe to use.

required
plotly_code str

The Plotly code to use.

required

Returns:

Type Description
Figure

plotly.graph_objs.Figure: The Plotly figure.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
def get_plotly_figure(
    self, plotly_code: str, df: pd.DataFrame, dark_mode: bool = True
) -> plotly.graph_objs.Figure:
    """
    **Example:**
    ```python
    fig = vn.get_plotly_figure(
        plotly_code="fig = px.bar(df, x='name', y='salary')",
        df=df
    )
    fig.show()
    ```
    Get a Plotly figure from a dataframe and Plotly code.

    Args:
        df (pd.DataFrame): The dataframe to use.
        plotly_code (str): The Plotly code to use.

    Returns:
        plotly.graph_objs.Figure: The Plotly figure.
    """
    ldict = {"df": df, "px": px, "go": go}
    try:
        exec(plotly_code, globals(), ldict)

        fig = ldict.get("fig", None)
    except Exception as e:
        # Inspect data types
        numeric_cols = df.select_dtypes(include=["number"]).columns.tolist()
        categorical_cols = df.select_dtypes(
            include=["object", "category"]
        ).columns.tolist()

        # Decision-making for plot type
        if len(numeric_cols) >= 2:
            # Use the first two numeric columns for a scatter plot
            fig = px.scatter(df, x=numeric_cols[0], y=numeric_cols[1])
        elif len(numeric_cols) == 1 and len(categorical_cols) >= 1:
            # Use a bar plot if there's one numeric and one categorical column
            fig = px.bar(df, x=categorical_cols[0], y=numeric_cols[0])
        elif len(categorical_cols) >= 1 and df[categorical_cols[0]].nunique() < 10:
            # Use a pie chart for categorical data with fewer unique values
            fig = px.pie(df, names=categorical_cols[0])
        else:
            # Default to a simple line plot if above conditions are not met
            fig = px.line(df)

    if fig is None:
        return None

    if dark_mode:
        fig.update_layout(template="plotly_dark")

    return fig

This method is used to get related DDL statements to a question.

Parameters:

Name Type Description Default
question str

The question to get related DDL statements for.

required

Returns:

Name Type Description
list list

A list of related DDL statements.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
244
245
246
247
248
249
250
251
252
253
254
255
@abstractmethod
def get_related_ddl(self, question: str, **kwargs) -> list:
    """
    This method is used to get related DDL statements to a question.

    Args:
        question (str): The question to get related DDL statements for.

    Returns:
        list: A list of related DDL statements.
    """
    pass

This method is used to get related documentation to a question.

Parameters:

Name Type Description Default
question str

The question to get related documentation for.

required

Returns:

Name Type Description
list list

A list of related documentation.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
257
258
259
260
261
262
263
264
265
266
267
268
@abstractmethod
def get_related_documentation(self, question: str, **kwargs) -> list:
    """
    This method is used to get related documentation to a question.

    Args:
        question (str): The question to get related documentation for.

    Returns:
        list: A list of related documentation.
    """
    pass

get_similar_question_sql(question, **kwargs) abstractmethod

This method is used to get similar questions and their corresponding SQL statements.

Parameters:

Name Type Description Default
question str

The question to get similar questions and their corresponding SQL statements for.

required

Returns:

Name Type Description
list list

A list of similar questions and their corresponding SQL statements.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
231
232
233
234
235
236
237
238
239
240
241
242
@abstractmethod
def get_similar_question_sql(self, question: str, **kwargs) -> list:
    """
    This method is used to get similar questions and their corresponding SQL statements.

    Args:
        question (str): The question to get similar questions and their corresponding SQL statements for.

    Returns:
        list: A list of similar questions and their corresponding SQL statements.
    """
    pass

get_sql_prompt(initial_prompt, question, question_sql_list, ddl_list, doc_list, **kwargs)

Example:

vn.get_sql_prompt(
    question="What are the top 10 customers by sales?",
    question_sql_list=[{"question": "What are the top 10 customers by sales?", "sql": "SELECT * FROM customers ORDER BY sales DESC LIMIT 10"}],
    ddl_list=["CREATE TABLE customers (id INT, name TEXT, sales DECIMAL)"],
    doc_list=["The customers table contains information about customers and their sales."],
)

This method is used to generate a prompt for the LLM to generate SQL.

Parameters:

Name Type Description Default
question str

The question to generate SQL for.

required
question_sql_list list

A list of questions and their corresponding SQL statements.

required
ddl_list list

A list of DDL statements.

required
doc_list list

A list of documentation.

required

Returns:

Name Type Description
any

The prompt for the LLM to generate SQL.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
def get_sql_prompt(
    self,
    initial_prompt : str,
    question: str,
    question_sql_list: list,
    ddl_list: list,
    doc_list: list,
    **kwargs,
):
    """
    Example:
    ```python
    vn.get_sql_prompt(
        question="What are the top 10 customers by sales?",
        question_sql_list=[{"question": "What are the top 10 customers by sales?", "sql": "SELECT * FROM customers ORDER BY sales DESC LIMIT 10"}],
        ddl_list=["CREATE TABLE customers (id INT, name TEXT, sales DECIMAL)"],
        doc_list=["The customers table contains information about customers and their sales."],
    )

    ```

    This method is used to generate a prompt for the LLM to generate SQL.

    Args:
        question (str): The question to generate SQL for.
        question_sql_list (list): A list of questions and their corresponding SQL statements.
        ddl_list (list): A list of DDL statements.
        doc_list (list): A list of documentation.

    Returns:
        any: The prompt for the LLM to generate SQL.
    """

    if initial_prompt is None:
        initial_prompt = "The user provides a question and you provide SQL. You will only respond with SQL code and not with any explanations.\n\nRespond with only SQL code. Do not answer with any explanations -- just the code.\n"

    initial_prompt = self.add_ddl_to_prompt(
        initial_prompt, ddl_list, max_tokens=14000
    )

    if self.static_documentation != "":
        doc_list.append(self.static_documentation)

    initial_prompt = self.add_documentation_to_prompt(
        initial_prompt, doc_list, max_tokens=14000
    )

    message_log = [self.system_message(initial_prompt)]

    for example in question_sql_list:
        if example is None:
            print("example is None")
        else:
            if example is not None and "question" in example and "sql" in example:
                message_log.append(self.user_message(example["question"]))
                message_log.append(self.assistant_message(example["sql"]))

    message_log.append(self.user_message(question))

    return message_log

get_training_data(**kwargs) abstractmethod

Example:

vn.get_training_data()

This method is used to get all the training data from the retrieval layer.

Returns:

Type Description
DataFrame

pd.DataFrame: The training data.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
310
311
312
313
314
315
316
317
318
319
320
321
322
323
@abstractmethod
def get_training_data(self, **kwargs) -> pd.DataFrame:
    """
    Example:
    ```python
    vn.get_training_data()
    ```

    This method is used to get all the training data from the retrieval layer.

    Returns:
        pd.DataFrame: The training data.
    """
    pass

get_training_plan_generic(df)

This method is used to generate a training plan from an information schema dataframe.

Basically what it does is breaks up INFORMATION_SCHEMA.COLUMNS into groups of table/column descriptions that can be used to pass to the LLM.

Parameters:

Name Type Description Default
df DataFrame

The dataframe to generate the training plan from.

required

Returns:

Name Type Description
TrainingPlan TrainingPlan

The training plan.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
def get_training_plan_generic(self, df) -> TrainingPlan:
    """
    This method is used to generate a training plan from an information schema dataframe.

    Basically what it does is breaks up INFORMATION_SCHEMA.COLUMNS into groups of table/column descriptions that can be used to pass to the LLM.

    Args:
        df (pd.DataFrame): The dataframe to generate the training plan from.

    Returns:
        TrainingPlan: The training plan.
    """
    # For each of the following, we look at the df columns to see if there's a match:
    database_column = df.columns[
        df.columns.str.lower().str.contains("database")
        | df.columns.str.lower().str.contains("table_catalog")
    ].to_list()[0]
    schema_column = df.columns[
        df.columns.str.lower().str.contains("table_schema")
    ].to_list()[0]
    table_column = df.columns[
        df.columns.str.lower().str.contains("table_name")
    ].to_list()[0]
    column_column = df.columns[
        df.columns.str.lower().str.contains("column_name")
    ].to_list()[0]
    data_type_column = df.columns[
        df.columns.str.lower().str.contains("data_type")
    ].to_list()[0]

    plan = TrainingPlan([])

    for database in df[database_column].unique().tolist():
        for schema in (
            df.query(f'{database_column} == "{database}"')[schema_column]
            .unique()
            .tolist()
        ):
            for table in (
                df.query(
                    f'{database_column} == "{database}" and {schema_column} == "{schema}"'
                )[table_column]
                .unique()
                .tolist()
            ):
                df_columns_filtered_to_table = df.query(
                    f'{database_column} == "{database}" and {schema_column} == "{schema}" and {table_column} == "{table}"'
                )
                doc = f"The following columns are in the {table} table in the {database} database:\n\n"
                doc += df_columns_filtered_to_table[
                    [
                        database_column,
                        schema_column,
                        table_column,
                        column_column,
                        data_type_column,
                    ]
                ].to_markdown()

                plan._plan.append(
                    TrainingPlanItem(
                        item_type=TrainingPlanItem.ITEM_TYPE_IS,
                        item_group=f"{database}.{schema}",
                        item_name=table,
                        item_value=doc,
                    )
                )

    return plan

remove_training_data(id, **kwargs) abstractmethod

Example:

vn.remove_training_data(id="123-ddl")

This method is used to remove training data from the retrieval layer.

Parameters:

Name Type Description Default
id str

The ID of the training data to remove.

required

Returns:

Name Type Description
bool bool

True if the training data was removed, False otherwise.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
@abstractmethod
def remove_training_data(id: str, **kwargs) -> bool:
    """
    Example:
    ```python
    vn.remove_training_data(id="123-ddl")
    ```

    This method is used to remove training data from the retrieval layer.

    Args:
        id (str): The ID of the training data to remove.

    Returns:
        bool: True if the training data was removed, False otherwise.
    """
    pass

run_sql(sql, **kwargs)

Example:

vn.run_sql("SELECT * FROM my_table")

Run a SQL query on the connected database.

Parameters:

Name Type Description Default
sql str

The SQL query to run.

required

Returns:

Type Description
DataFrame

pd.DataFrame: The results of the SQL query.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
def run_sql(self, sql: str, **kwargs) -> pd.DataFrame:
    """
    Example:
    ```python
    vn.run_sql("SELECT * FROM my_table")
    ```

    Run a SQL query on the connected database.

    Args:
        sql (str): The SQL query to run.

    Returns:
        pd.DataFrame: The results of the SQL query.
    """
    raise Exception(
        "You need to connect to a database first by running vn.connect_to_snowflake(), vn.connect_to_postgres(), similar function, or manually set vn.run_sql"
    )

submit_prompt(prompt, **kwargs) abstractmethod

Example:

vn.submit_prompt(
    [
        vn.system_message("The user will give you SQL and you will try to guess what the business question this query is answering. Return just the question without any additional explanation. Do not reference the table name in the question."),
        vn.user_message("What are the top 10 customers by sales?"),
    ]
)

This method is used to submit a prompt to the LLM.

Parameters:

Name Type Description Default
prompt any

The prompt to submit to the LLM.

required

Returns:

Name Type Description
str str

The response from the LLM.

Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
@abstractmethod
def submit_prompt(self, prompt, **kwargs) -> str:
    """
    Example:
    ```python
    vn.submit_prompt(
        [
            vn.system_message("The user will give you SQL and you will try to guess what the business question this query is answering. Return just the question without any additional explanation. Do not reference the table name in the question."),
            vn.user_message("What are the top 10 customers by sales?"),
        ]
    )
    ```

    This method is used to submit a prompt to the LLM.

    Args:
        prompt (any): The prompt to submit to the LLM.

    Returns:
        str: The response from the LLM.
    """
    pass

train(question=None, sql=None, ddl=None, documentation=None, plan=None)

Example:

vn.train()

Train Vanna.AI on a question and its corresponding SQL query. If you call it with no arguments, it will check if you connected to a database and it will attempt to train on the metadata of that database. If you call it with the sql argument, it's equivalent to vn.add_question_sql(). If you call it with the ddl argument, it's equivalent to vn.add_ddl(). If you call it with the documentation argument, it's equivalent to vn.add_documentation(). Additionally, you can pass a [TrainingPlan][vanna.types.TrainingPlan] object. Get a training plan with vn.get_training_plan_generic().

Parameters:

Name Type Description Default
question str

The question to train on.

None
sql str

The SQL query to train on.

None
ddl str

The DDL statement.

None
documentation str

The documentation to train on.

None
plan TrainingPlan

The training plan to train on.

None
Source code in venv/lib/python3.11/site-packages/vanna/base/base.py
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
def train(
    self,
    question: str = None,
    sql: str = None,
    ddl: str = None,
    documentation: str = None,
    plan: TrainingPlan = None,
) -> str:
    """
    **Example:**
    ```python
    vn.train()
    ```

    Train Vanna.AI on a question and its corresponding SQL query.
    If you call it with no arguments, it will check if you connected to a database and it will attempt to train on the metadata of that database.
    If you call it with the sql argument, it's equivalent to [`vn.add_question_sql()`][vanna.base.base.VannaBase.add_question_sql].
    If you call it with the ddl argument, it's equivalent to [`vn.add_ddl()`][vanna.base.base.VannaBase.add_ddl].
    If you call it with the documentation argument, it's equivalent to [`vn.add_documentation()`][vanna.base.base.VannaBase.add_documentation].
    Additionally, you can pass a [`TrainingPlan`][vanna.types.TrainingPlan] object. Get a training plan with [`vn.get_training_plan_generic()`][vanna.base.base.VannaBase.get_training_plan_generic].

    Args:
        question (str): The question to train on.
        sql (str): The SQL query to train on.
        ddl (str):  The DDL statement.
        documentation (str): The documentation to train on.
        plan (TrainingPlan): The training plan to train on.
    """

    if question and not sql:
        raise ValidationError(f"Please also provide a SQL query")

    if documentation:
        print("Adding documentation....")
        return self.add_documentation(documentation)

    if sql:
        if question is None:
            question = self.generate_question(sql)
            print("Question generated with sql:", question, "\nAdding SQL...")
        return self.add_question_sql(question=question, sql=sql)

    if ddl:
        print("Adding ddl:", ddl)
        return self.add_ddl(ddl)

    if plan:
        for item in plan._plan:
            if item.item_type == TrainingPlanItem.ITEM_TYPE_DDL:
                self.add_ddl(item.item_value)
            elif item.item_type == TrainingPlanItem.ITEM_TYPE_IS:
                self.add_documentation(item.item_value)
            elif item.item_type == TrainingPlanItem.ITEM_TYPE_SQL:
                self.add_question_sql(question=item.item_name, sql=item.item_value)
Vanna Logo Vanna.AI

The fastest way to get insights from your database just by asking questions