Skip to main content
Version: 0.14.13

How to connect to a SQLite database

This guide will help you connect to data in a SQLite database. This will allow you to ValidateThe act of applying an Expectation Suite to a Batch. and explore your data.

Prerequisites: This how-to guide assumes you have:
  • Completed the Getting Started Tutorial
  • Have a working installation of Great Expectations
  • Have access to data in a SQLite database

Steps

1. Choose how to run the code in this guide

Get an environment to run the code in this guide. Please choose an option below.

If you use the Great Expectations CLICommand Line Interface, run this command to automatically generate a pre-configured Jupyter Notebook. Then you can follow along in the YAML-based workflow below:

great_expectations datasource new

2. Install required dependencies

First, install the necessary dependencies for Great Expectations to connect to your SQLite database by running the following in your terminal:

pip install sqlalchemy

3. Configure the URL for your SQLite database

Since SQLite connects to file-based databases, the URL format is slightly different from other DBs. For this guide we will use a connection_string that looks like this:

sqlite:///<PATH_TO_DB_FILE>

For more details on different ways to specify database files and information on how to connect to an in-memory SQLite database, please refer to the documentation on SQLAlchemy.

4. Instantiate your project's DataContext

Import these necessary packages and modules.

from ruamel import yaml

import great_expectations as ge
from great_expectations.core.batch import BatchRequest, RuntimeBatchRequest

Load your DataContext into memory using the get_context() method.

context = ge.get_context()

5. Configure your Datasource

Put your connection string in this template:

datasource_yaml = f"""
name: my_sqlite_datasource
class_name: Datasource
execution_engine:
class_name: SqlAlchemyExecutionEngine
connection_string: sqlite://<PATH_TO_DB_FILE>
data_connectors:
default_runtime_data_connector_name:
class_name: RuntimeDataConnector
batch_identifiers:
- default_identifier_name
default_inferred_data_connector_name:
class_name: InferredAssetSqlDataConnector
include_schema_name: true
"""

Run this code to test your configuration.

context.test_yaml_config(datasource_yaml)

You will see your database tables listed as Available data_asset_names in the output of test_yaml_config().

Feel free to adjust your configuration and re-run test_yaml_config as needed.

6. Save the Datasource configuration to your DataContext

Save the configuration into your DataContext by using the add_datasource() function.

context.add_datasource(**yaml.load(datasource_yaml))

7. Test your new Datasource

Verify your new DatasourceProvides a standard API for accessing and interacting with data from a wide variety of source systems. by loading data from it into a ValidatorUsed to run an Expectation Suite against data. using a BatchRequest.

Here is an example of loading data by specifying a SQL query.

batch_request = RuntimeBatchRequest(
datasource_name="my_sqlite_datasource",
data_connector_name="default_runtime_data_connector_name",
data_asset_name="default_name", # this can be anything that identifies this data
runtime_parameters={
"query": "SELECT * from main.yellow_tripdata_sample_2019_01 LIMIT 10"
},
batch_identifiers={"default_identifier_name": "default_identifier"},
)
context.create_expectation_suite(
expectation_suite_name="test_suite", overwrite_existing=True
)
validator = context.get_validator(
batch_request=batch_request, expectation_suite_name="test_suite"

🚀🚀 Congratulations! 🚀🚀 You successfully connected Great Expectations with your data.

Additional Notes

To view the full scripts used in this page, see them on GitHub:

Next Steps

Now that you've connected to your data, you'll want to work on these core skills: