Querying¶
SELECT queries¶
Here is the basic syntax to select records from the database
>>> users = User.select(
select=['id', 'name'],
where={'email': 'paul@beatl.es'},
order='id ASC',
limit=1)
# SELECT u.id, u.name FROM users u WHERE u.email = 'paul@beatl.es' ORDER BY u.id ASC LIMIT 1; /* My Great App */
>>> users
id name
1 Paul
Arguments¶
See API reference.
where argument
Here are the different ways to feed a condition clause to a query.
where = "name = 'John'"
where = {'name': 'John'}
where = {'id': (0, 3)}
– selects whereid
is between 0 and 3where = {'id': [0, 1, 2]}
– selects whereid
is in the arraywhere = [{'id': (0, 10), 'instrument': 'drums'}, ["created_at > %(created_at)s", {'created_at': '1963-03-22'}]]
For other operators than =
, see Comparators.
inner_join, left_join arguments
The simplest way to join another table is as follows
>>> User.select(inner_join=["instruments i ON i.id = u.instrument_id"])
If you have configured your models associations, see Features, you can simply pass the class as argument:
>>> User.select(inner_join=[Instrument])
Individual record selection¶
You can also look-up a single record by id:
>>> User.find(1)
# SELECT * FROM users u WHERE u.id = 1 LIMIT 1;
User(id=1, name='Paul', email='paul@beatl.es', ...)
>>> User.find_by(values={'name': 'Paul'})
# SELECT * FROM users u WHERE u.name = 'Paul' LIMIT 1;
User(id=1, name='Paul', email='paul@beatl.es', ...)
Note that the returned object is a Record
object which allows you to access attributes in those way:
>>> user['name']
Paul
>>> user.name
Paul
INSERT queries¶
>>> user = User.insert(values={'name': 'Pete', 'email': 'pete@beatl.es'})
# INSERT INTO users (name, email) VALUES ('Pete', 'pete@beatl.es') RETURNING id;
# SELECT u.* FROM users WHERE u.id = 4;
>>> user
id name email
4 Pete pete@beatl.es
UPDATE queries¶
>>> users = User.update(values={'hair': 'long'}, where={'name': 'John'})
# UPDATE users u SET (u.hair) = ('long') WHERE u.name = 'John' RETURNING id;
# SELECT * FROM users u WHERE u.name = 'John';
DELETE queries¶
>>> User.delete(where={'id': 1})
# DELETE FROM users u WHERE u.id = 1;
Raw queries¶
>>> jardin.query(sql='SELECT * FROM users WHERE id IN %(ids)s;', params={'ids': [1, 2, 3]})
# SELECT * FROM users WHERE id IN (1, 2, 3);
Query from SQL file¶
>>> jardin.query(filename='path/to/file.sql', params={...})
The path is relative to the working directory (i.e. where your app was launched).