Queries - The DataJoint Book

We will use the design produced in 004-Design. Please execute that notebook first to define and populate the app schema.

Recall the design¶

The following code connects to the app schema and generates Python classes to access its classes.

import datajoint as dj
schema = dj.Schema('app')
schema.spawn_missing_classes()
dj.Diagram(schema)

Purchase()

Queries¶

Simple queries¶

Account()

Account.fetch(as_dict=True, limit=5)

[{'phone': 10019062770,
  'first_name': 'Lauren',
  'last_name': 'Snyder',
  'dob': datetime.date(1961, 8, 18),
  'sex': 'F'},
 {'phone': 10020965828,
  'first_name': 'Victor',
  'last_name': 'Ross',
  'dob': datetime.date(1952, 7, 12),
  'sex': 'M'},
 {'phone': 10040756087,
  'first_name': 'Susan',
  'last_name': 'Brown',
  'dob': datetime.date(1992, 12, 14),
  'sex': 'F'},
 {'phone': 10047410808,
  'first_name': 'Paige',
  'last_name': 'Holder',
  'dob': datetime.date(1942, 9, 2),
  'sex': 'F'},
 {'phone': 10051015428,
  'first_name': 'William',
  'last_name': 'Estrada',
  'dob': datetime.date(1985, 12, 31),
  'sex': 'M'}]

Account.fetch(as_dict=True, order_by=('last_name', 'first_name'), limit=5)

[{'phone': 15366336995,
  'first_name': 'Rachel',
  'last_name': 'Abbott',
  'dob': datetime.date(2000, 4, 22),
  'sex': 'F'},
 {'phone': 91972525114,
  'first_name': 'Robert',
  'last_name': 'Abbott',
  'dob': datetime.date(1929, 1, 19),
  'sex': 'M'},
 {'phone': 36893728519,
  'first_name': 'William',
  'last_name': 'Abbott',
  'dob': datetime.date(2003, 6, 22),
  'sex': 'M'},
 {'phone': 51992023735,
  'first_name': 'Brianna',
  'last_name': 'Acevedo',
  'dob': datetime.date(1982, 4, 2),
  'sex': 'F'},
 {'phone': 38801647559,
  'first_name': 'Christian',
  'last_name': 'Acevedo',
  'dob': datetime.date(1940, 4, 10),
  'sex': 'M'}]

Account.fetch(as_dict=True, order_by=('dob DESC'), limit=5, offset=100)

[{'phone': 61348693087,
  'first_name': 'Jennifer',
  'last_name': 'Singh',
  'dob': datetime.date(2022, 8, 6),
  'sex': 'F'},
 {'phone': 40205369474,
  'first_name': 'Alexander',
  'last_name': 'Thomas',
  'dob': datetime.date(2022, 8, 6),
  'sex': 'M'},
 {'phone': 47397056664,
  'first_name': 'Garrett',
  'last_name': 'Mooney',
  'dob': datetime.date(2022, 7, 27),
  'sex': 'M'},
 {'phone': 72207025461,
  'first_name': 'Rhonda',
  'last_name': 'Bates',
  'dob': datetime.date(2022, 7, 21),
  'sex': 'F'},
 {'phone': 85504732206,
  'first_name': 'Desiree',
  'last_name': 'Perez',
  'dob': datetime.date(2022, 7, 20),
  'sex': 'F'}]

Restriction (selecting rows)¶

In SQL, this is the WHERE clause. In DataJoint, we use operators & and -.

Account() & {'phone': 69235537483}

Account() & {'first_name': 'Michael'}

Account & 'first_name="Michael"'

Account & 'first_name="Anne"' & 'dob > "2001-01-01"'

Account & 'DATEDIFF(now(), dob) < 300'

Account - 'DATEDIFF(now(), dob) < 300'

millennials = Account & 'dob > "1978-01-01"' & 'dob < "1997-01-01"'

millennials & 'first_name="Michael"'

millennials = Account & 'dob BETWEEN "1978-01-01" AND "1997-01-01"'

millennials

Projection (selecting, calculating, and renaming columns)¶

Account()

Account.proj()

Account.proj('last_name')

Account.proj(..., '-dob')

full_name = Account.proj(full_name='concat(last_name, ", ", first_name)')

d = full_name.fetch(order_by="full_name", format="frame")

accounts = Account.proj('last_name', age="floor(datediff(now(), dob)/365.25)")

accounts & 'age > 35'

accounts & 'age between 35 and 45'

Account().proj(..., id='phone', name='(first_name)')

(Account & 'last_name > "S"').proj()

Restrictions by a query¶

In SQL, this is a query where the WHERE clause includes another `SELECT`` clause.

dj.Diagram(schema)

Account & CreditCard

Account - CreditCard

# All the people that purchased AddOn #2.
Account & (Purchase & 'addon_id=2')

# Give me all accounts who have purchased both Addon 2 and 3
Account & (Purchase & 'addon_id=2') & (Purchase & 'addon_id=3')

# Give me all accounts who have purchased Addon 2 or 3
Account & (Purchase & 'addon_id=2 OR addon_id=3')
Account & (Purchase & 'addon_id in (2, 3)')
Account & (Purchase & ['addon_id=3', 'addon_id=2'])

# Give me all accounts who have purchased Addon 2 but not 3

# Accounts with credit cards that have no purchases
Account & (CreditCard - Purchase)

# Accounts with credit cards but no purchases
(Account & CreditCard) - Purchase

DeMorgan’s Laws¶

not (A or B) == not A and not B

not (A and B) == not A or not B

not (A and not B) == not A or B

  Cell In[3], line 1
    not (A or B) == not A and not B
                    ^
SyntaxError: invalid syntax

Queries

Homework 5. Simple Queries

Queries