PyODPS integrates with SQLAlchemy, letting you use standard SQLAlchemy syntax to query data in MaxCompute without writing ODPS-specific API calls.
Connect to a MaxCompute project
Build a connection string in the following format, then pass it to create_engine:
odps://<AccessKey ID>:<AccessKey secret>@<project>/?endpoint=<endpoint>Example:
import os
from sqlalchemy import create_engine
engine = create_engine(
'odps://%s:%s@<project>/?endpoint=<endpoint>' % (
os.getenv('ALIBABA_CLOUD_ACCESS_KEY_ID'),
os.getenv('ALIBABA_CLOUD_ACCESS_KEY_SECRET'),
)
)
conn = engine.connect()Store your credentials as environment variables rather than hardcoding them in source code.
Connection string parameters:
| Parameter | Description |
|---|---|
ALIBABA_CLOUD_ACCESS_KEY_ID | Your AccessKey ID. Get it from the AccessKey Pair page |
ALIBABA_CLOUD_ACCESS_KEY_SECRET | The AccessKey secret that corresponds to your AccessKey ID. Get it from the AccessKey Pair page |
project | The name of your MaxCompute project. This is the project name, not the DataWorks workspace name. Find it in the MaxCompute console under Workspace > Projects |
endpoint | The endpoint of the region where your MaxCompute project resides. See Endpoints |
Connect using a global ODPS object
If you already have an ODPS object o and have called o.to_global(), you do not need to specify credentials or project parameters in the connection string:
from sqlalchemy import create_engine
o.to_global() # set ODPS object as global one
engine = create_engine('odps://')Use the SQLAlchemy interface
Create a table
from sqlalchemy import Table, Column, Integer, String, MetaData
metadata = MetaData()
users = Table('users', metadata,
Column('id', Integer),
Column('name', String),
Column('fullname', String),
)
metadata.create_all(engine)Insert data
ins = users.insert().values(id=1, name='jack', fullname='Jack Jones')
conn.execute(ins)Query data
from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)
for row in result:
print(row)Expected output:
(1, 'jack', 'Jack Jones')