MaxCompute:Integrate SQLAlchemy

Create a connection as follows.

import os
from sqlalchemy import create_engine

# Ensure that the ALIBABA_CLOUD_ACCESS_KEY_ID environment variable is set to your AccessKey ID,
# and the ALIBABA_CLOUD_ACCESS_KEY_SECRET environment variable is set to your AccessKey secret.
# For security, avoid hardcoding your AccessKey ID and AccessKey secret in the connection string.
conn_string = 'odps://%s:%s@<project>/?endpoint=<endpoint>' % (
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_ID'),
    os.getenv('ALIBABA_CLOUD_ACCESS_KEY_SECRET'),
)
engine = create_engine(conn_string)
conn = engine.connect()

• ALIBABA_CLOUD_ACCESS_KEY_ID: Your AccessKey ID with permissions to access the target MaxCompute project. We recommend setting it as an environment variable.

  You can get your AccessKey ID from the AccessKey Pair page.

• ALIBABA_CLOUD_ACCESS_KEY_SECRET: The AccessKey secret that corresponds to your AccessKey ID. We recommend setting it as an environment variable.

  You can get your AccessKey secret from the AccessKey Pair page.

• project: The name of the target MaxCompute project.

  This refers to the MaxCompute project name, not the workspace name. To find your project name, log in to the MaxCompute console and go to Workspace > Projects in the left-side navigation pane.

• endpoint: The endpoint of the region where the target MaxCompute project is located.

  For a list of endpoints by region, see Endpoints.

If you have an existing ODPS object o and have set it as a global ODPS object by calling o.to_global(), you can omit the preceding parameters from the connection string. For example:

from sqlalchemy import create_engine
o.to_global()  # Make the ODPS object global
engine = create_engine('odps://')

The following examples demonstrate how to create a table, insert data, and query data:

• 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')