Manage Platform data using Python and SQLAlchemy

Learn how to use SQLAlchemy for greater flexibility in the management of your Adobe Experience Platform data. For those who are not as familiar with SQL, SQLAlchemy can greatly improve development time when working with relational databases. This document provides instructions and examples to connect SQLAlchemy to Query Service and begin using Python to interact with your databases.

SQLAlchemy is an Object Relational Mapper (ORM) and a Python code library that can transfer data stored in a SQL database into Python objects. You can then perform CRUD operations on data held within the Platform data lake using Python code. This removes the need to manage data using only PSQL.

Getting started

To acquire the necessary credentials for connecting SQLAlchemy to Experience Platform, you must have access to the Queries workspace in the Platform UI. Please contact your organization administrator if you do not currently have access to the Queries workspace.

Query Service credentials credentials

To find your credentials, log in to the Platform UI and select Queries from the left navigation, followed by Credentials. For complete directions on how to find your login credentials, please read the credentials guide.

Although port 80 is the recommended port for a connection to Query Service, you can also use port 5432.

Once you have access to your QS credentials open your Python editor of choice.

Store credentials in Python store-credentials

In your Python editor, import the urllib.parse.quote library and save each credential variable as a parameter. The urllib.parse module provides a standard interface to break URL strings into components. The quote function replaces special characters in the URL string to make the data safe for use as URL components. An example of the required code is seen below:

from urllib.parse import quote

host = "<YOUR_HOST>"

port = "<YOUR_PORT>"

dbname = "<YOUR_DATABASE>"

user = "<YOUR_USERNAME>"

password = quote('''
<YOUR_PASSWORD>
''')

Create an engine instance [#create-engine]

After the variables have been created, import the create_engine function and create a string to compile and format your Query Service credentials in SQLAlchemy. The create_engine function is then used to construct an engine instance.

SSL must be enabled when accessing Platform using third-party clients. As part of your engine, use the connect_args to enter additional keyword arguments. You are recommended to set your SSL mode to require. See the SSL modes documentation for more information on accepted values.

The example below displays the Python code necessary to initialize an engine and connection string.

from sqlalchemy import create_engine

db_string = "postgresql://{user}:{password}@{host}:{port}/{dbname}".format(
    user=user,
    password=password,
    host=host,
    port = port,
    dbname = dbname
)

engine = create_engine(db_string, connect_args={'sslmode':'require'})

You are now ready to query Platform data using Python. The example shown below returns an array of Query Service table names.

from sqlalchemy import inspect
insp = inspect(engine)
print(insp.get_table_names())