PyPika 튜토리얼 번역 - (4)

시리즈의 마지막 챕터다.

Inserting Data

쿼리 자체에 값을 전달하거나, 다른 쿼리(서브쿼리 등)를 통해 SELECT의 결과를 테이블에 삽입할 수 있다. 기본적으론 테이블에 정의된 순서대로 모든 열에 값을 제공하여 데이터를 삽입할 수 있다.

Insert with values

customers = Table('customers')

q = Query.into(
  customers
).insert(
  1, 'Jane', 'Doe', 'jane@example.com'
)

INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com')

여러 row를 한번에 insert하려면, Query.insert() 메소드를 이어붙이거나, INSERT문의 인자로 이루어진 tuple들을 넘겨주면 된다.

customers = Table('customers')

q = Query.into(
  customers
).insert(
  1, 'Jane', 'Doe', 'jane@example.com'
).insert(
  2, 'John', 'Doe', 'john@example.com'
)

customers = Table('customers')

q = Query.into(
  customers
).insert(
  (1, 'Jane', 'Doe', 'jane@example.com'),
  (2, 'John', 'Doe', 'john@example.com')
)

Insert with on Duplicate Key Update

customers = Table('customers')

q = Query.into(
  customers
).insert(
  1, 'Jane', 'Doe', 'jane@example.com'
).on_duplicate_key_update(
  customers.email, Values(customers.email)
)

INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com') ON DUPLICATE KEY UPDATE `email`=VALUES(`email`)

.on_duplicate_key_update는 명시된 key에 대해 row가 이미 존재하는 경우 UPDATE 쿼리를 수행하도록 만든다.

Insert from a SELECT Sub-query

INSERT INTO customers VALUES (1,'Jane','Doe','jane@example.com'),(2,'John','Doe','john@example.com')

INSERT 쿼리에서 values의 순서를 명시하려면 Query.columns() 메소드를 사용한다.

customers = Table('customers')

q = Query.into(
  customers
).columns(
  'id', 'fname', 'lname'
).insert(
  1, 'Jane', 'Doe'
)

INSERT INTO customers (id,fname,lname) VALUES (1,'Jane','Doe','jane@example.com')

쿼리를 통해 데이터를 삽입하는 것은 빌더 체인에서 insert 메소드 대신 from-select를 사용하면 된다.

customers, customers_backup = Tables('customers', 'customers_backup')

q = Query.into(
  customers_backup
).from_(
  customers
).select(
  '*'
)

INSERT INTO customers_backup SELECT * FROM customers

customers, customers_backup = Tables('customers', 'customers_backup')

q = Query.into(
  customers_backup
).columns(
  'id', 'fname', 'lname'
).from_(
  customers
).select(
  customers.id, customers.fname, customers.lname
)

INSERT INTO customers_backup SELECT "id", "fname", "lname" FROM customers

joined select를 insert하는 것도 그냥 select 쿼리를 생성할 때와 별다를 것 없다.

customers, orders, orders_backup = Tables('customers', 'orders', 'orders_backup')

q = Query.into(
  orders_backup
).columns(
  'id', 'address', 'customer_fname', 'customer_lname'
).from_(
  customers
).join(
  orders
).on(
  orders.customer_id == customers.id
).select(
  orders.id, customers.fname, customers.lname
)

INSERT INTO "orders_backup" ("id","address","customer_fname","customer_lname")
SELECT "orders"."id","customers"."fname","customers"."lname" FROM "customers"
JOIN "orders" ON "orders"."customer_id"="customers"."id"

Updating Data

customers = Table('customers')

Query.update(
  customers
).set(
  customers.last_login, '2017-01-01 10:00:00'
)

Query.update(
  customers
).set(
  customers.lname, 'smith'
).where(
  customers.id == 10
)

UPDATE "customers" SET "last_login"='2017-01-01 10:00:00'

UPDATE "customers" SET "lname"='smith' WHERE "id"=10

아래는 join해서 update하는 쿼리의 예다.

customers, profiles = Tables('customers', 'profiles')

Query.update(
  customers
).join(
  profiles
).on(
  profiles.customer_id == customers.id
).set(
  customers.lname, profiles.lname
)

UPDATE "customers"
JOIN "profiles" ON "profiles"."customer_id"="customers"."id"
SET "customers"."lname"="profiles"."lname"