Blurbs ansible coffeescript django dvcs erlang es6 hg javascript json lua mercurial peewee postgresql python scope sql sqlalchemy yaml

# peewee

query = Entry.update(counter=Entry.counter + 1)
query.execute()

SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE i % 2 != 0) AS odd
FROM generate_series(1, 10) AS s(i);

--  total | odd
-- -------+-----
--     10 |   5
-- (1 row)

SELECT section_id
FROM section_relations
GROUP BY section_id
HAVING COUNT(*) > 500;

UPDATE users u SET hobby = (SELECT hobby FROM hobbies h WHERE u.id = h.user_id);

UPDATE users u SET hobby = h.hobby FROM hobbies h WHERE u.id = h.user_id;

SELECT DISTINCT ON (genre) artist, genre, count_by_genre
FROM artists_and_genres
ORDER BY genre, count_by_genre DESC;

UPDATE questions SET bounty = bounty * 2 WHERE id = 5432
RETURNING bounty;

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id = 1
  RETURNING *
)
INSERT INTO elog SELECT row_to_json(upd), current_timestamp FROM upd;

WITH busytables AS (
  SELECT relname, (seq_scan + idx_scan) AS scans
  FROM pg_stat_user_tables
)
SELECT relname, scans
FROM busytables
WHERE scans > 100
ORDER BY scans DESC;

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'my_books';

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY relname;

SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY relname;

SELECT array_to_json(array_agg(books))
FROM (SELECT isbn, author, title FROM my_books) AS books;

# sqlalchemy

from sqlalchemy import update

stmt = update(entry).values(counter=entry.c.counter + 1)
conn.execute(stmt)


# sqlalchemy.orm

session.query(Entry).update({'counter': Entry.counter + 1})


# django

from django.db.models import F

Entry.objects.update(counter=F('counter') + 1)