1. Getting Started

Basic usage

from pydynamodb import connect

cursor = connect(aws_access_key_id="aws_access_key_id",
                aws_secret_access_key="aws_secret_access_key"
                 region_name="region_name").cursor()
cursor.execute('SELECT * FROM "ddb_table_name"')
print(cursor.fetchall())

Cursor iteration

from pydynamodb import connect

cursor = connect(aws_access_key_id="aws_access_key_id",
                aws_secret_access_key="aws_secret_access_key"
                 region_name="region_name").cursor()
cursor.execute('SELECT * FROM "ddb_table_name"')
rows = cursor.fetchall()
for row in rows:
    print(row)

Query with complex parameters

PyDynamoDB is able to serialize the parameters which passed to DDB.

from pydynamodb import connect
cursor = connect(aws_access_key_id="aws_access_key_id",
                aws_secret_access_key="aws_secret_access_key"
                 region_name="region_name").cursor()
cursor.execute("""INSERT INTO "ddb_table_name" VALUE {
                    'partition_key' = ?, 'sort_key' = ?,
                    'col_str' = ?, 'col_num' = ?, 'col_byte' = ?,
                    'col_ss' = ?, 'col_ns' = ?, 'col_bs' = ?,
                    'col_list' = ?, 'col_map' = ?, 'col_nested' = ?
                }""", ["pkey_value", "skey_value", "str", 100, b"ABC", # String, Number, Bytes
                        {"str", "str"}, {100, 100}, {b"A", b"B"}, # String/Numnber/Bytes Set
                        ["str", 100, b"ABC"],  # List
                        {"key1": "val", "key2": "val"}, # Map
                        ["str", 100, {"key1": "val"}] # Nested Structure
                    ])

cursor.execute('SELECT col_nested FROM "ddb_table_name" WHERE partition_key = ?', ["pkey_value"])
print(cursor.fetchall())

Handle Result Set

PyDynamoDB is able to deserialize the result set to Python built-in data types.

cursor.execute("""
     SELECT col_nested FROM "ddb_table_name" WHERE partition_key = ? AND sort_key = ?
""", ["pkey_value", "skey_value"])
assert cursor.fetchone() == (["str", 100, {"key1": "val"}])

Description of Result Set

DDB is a NoSQL database. That means except key schema, the data in each row may have flexible columns or types. PyDynamoDB cannot get a completed result set description before fetching all result data. So you have to use fetch* method to iterate the whole result set, then call cursor.description to get the full columns description.

from pydynamodb import connect

cursor = connect(aws_access_key_id="aws_access_key_id",
                aws_secret_access_key="aws_secret_access_key"
                 region_name="region_name").cursor()
cursor.execute('SELECT * FROM "ddb_table_name"')
print(cursor.fetchall())
print(cursor.description)

Data/Datatime & Annotation Functions

DDB can only support limited data types. To extend the data type support, PyDynamoDB can support for date and datetime conversion when you pass the datetime type data into write operations. It will be stored in DDB with ISO 8601 format as string. If want to get correct type, you could use Annotation Functions in read operations to convert back.

Available Functions

• DATE(column [, 'date format']) - Use ISO 8601 to convert data by default. For the special format, the second parameter should be given.
• DATETIME(column [, 'datetime format']) - Use ISO 8601 to convert data by default. For the special format, the second parameter should be given.
• NUMBER(column) - Convert to float
• BOOL(column) - Convert to bool

Write date & datetime data:

from datetime import date, datetime

sql = """
    INSERT INTO "ddb_table_name" VALUE {
        'key_partition': ?, 'key_sort': ?, 'col_date': ?, 'col_datetime': ?
    }
"""
cursor.execute(sql, [
    "pk_value", 0, date(2022, 10, 18), datetime(2022, 10, 18, 13, 55, 34),
])

Read data:

cursor.execute("""
        SELECT DATE(col_date), DATETIME(col_datetime) FROM "ddb_table_name"
        WHERE key_partition = 'pk_value' AND key_sort = 0
"""