DBAPI Interface

Note

Supported since PyODPS 0.10.0. As transaction operations are not supported in MaxCompute, related interfaces are not implemented.

PyODPS supports accessing MaxCompute data via Python DBAPI compatible interfaces.

Create connections

Connections can be established via access_id, access_key, project` and endpoint.

>>> import odps.dbapi
>>> conn = odps.dbapi.connect('<access_id>', '<access_key>', '<project>', '<endpoint>')

Existing ODPS entry objects can also be used.

>>> import odps.dbapi
>>> conn = odps.dbapi.connect(o)  # type(o) is ODPS

If you want to use MaxQA (MCQA 2.0), you can specify parameters use_sqa='v2' and quota_name.

>>> conn = odps.dbapi.connect(o, use_sqa='v2', quota_name='my_quota')

Execute SQL statements

You can create a cursor and execute SQL statement on it.

>>> cursor = conn.cursor()
>>> cursor.execute("SELECT * FROM pyodps_iris")
>>> print(cursor.description)
[('sepal_length', 'double', None, None, None, None, True),
 ('sepal_width', 'double', None, None, None, None, True),
 ('petal_length', 'double', None, None, None, None, True),
 ('petal_width', 'double', None, None, None, None, True),
 ('category', 'string', None, None, None, None, True)]

PyODPS uses the same parameter specification as Python standard library sqlite3. You can use ? to specify anonymous parameters and use :name to specify named parameters. Tuples can be used to replace non-named parameters and dicts replace named parameters.

>>> # use anonymous parameters
>>> cursor.execute("SELECT * FROM pyodps_iris WHERE petal_length > ?", (1.5,))
>>> print(cursor.fetchone())
[5.4, 3.9, 1.7, 0.4, 'Iris-setosa']
>>> # use named parameters
>>> cursor.execute("SELECT * FROM pyodps_iris WHERE petal_length > :length", {'length': 1.5})
>>> print(cursor.fetchone())
[5.4, 3.9, 1.7, 0.4, 'Iris-setosa']

Read results

You can use iterations to read results just like standard DBAPI.

>>> for rec in cursor:
>>>     print(rec)

You can also fetch all results at once.

>>> print(cursor.fetchall())