把 CSV 文件导入数据表

机器翻译

本页由英文原文机器翻译而来，尚未经过母语审校，欢迎在 GitHub 上提出修改。内容如有出入，以英文原文为准。

任务： 把商品列表导入数据库，要么全部成功，要么全部不导入。

yaml

# Recipe: load the rows of a CSV file into a table, all or nothing.
paths:
  components: ./components
  migrations: ./migrations

datasources:
  db:
    driver: sqlite
    database: ./data/shop.db

sql

CREATE TABLE products (
    sku TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL CHECK (price > 0)
);

text

sku,name,price
MUG-1,Mug,30
TEE-1,T-shirt,80
STK-1,Sticker,5

动作用 q:data 读取文件，然后在一个 q:transaction 中插入每一行；其中的查询，包括循环里的查询，都使用它的数据源。

xml

<q:component name="Import">
  <q:action name="load" method="POST">
    <q:data name="rows" source="import/products.csv" type="csv">
      <q:column name="price" type="integer" />
    </q:data>

    <!-- One transaction for the whole file: if one row is refused (a sku
         already there, a price that is not positive), none stays. -->
    <q:transaction datasource="db">
      <q:loop items="{rows}" var="row">
        <q:query name="inserted">
          INSERT INTO products (sku, name, price) VALUES (:sku, :name, :price)
          <q:param name="sku" value="{row.sku}" type="string" />
          <q:param name="name" value="{row.name}" type="string" />
          <q:param name="price" value="{row.price}" type="integer" />
        </q:query>
      </q:loop>
    </q:transaction>

    <q:redirect url="/" flash="Loaded {len(rows)} products." />
  </q:action>

  <q:query name="products" datasource="db">SELECT sku, name, price FROM products ORDER BY sku</q:query>

  <ui:window title="Products">
    <q:if condition="flash">
      <ui:alert variant="success">{flash}</ui:alert>
    </q:if>
    <ui:text>{products_result.recordCount} products</ui:text>
    <ui:table source="{products}" />
    <ui:form on-submit="load" submit="Load import/products.csv" />
  </ui:window>
</q:component>

第二个测试在文件中的某个商品已经存在时导入它：第二行失败，已经插入的第一行被回滚。

xml

<q:test name="the file's rows land in the table" page="/">
  <test:submit action="load" />
  <test:expect redirect="/" flash="Loaded 3 products." />
  <test:expect table="products" count="3" />
  <test:expect table="products" count="1" where="sku = 'TEE-1' AND price = 80" />
  <test:expect text="3 products" />
</q:test>

<q:test name="a file that fails halfway leaves nothing behind" page="/">
  <!-- TEE-1 is already there: the second row fails, so the first is undone too. -->
  <test:given table="products" sku="TEE-1" name="T-shirt" price="80" />
  <test:submit action="load" />
  <test:expect status="500" />
  <test:expect table="products" count="1" />
  <test:expect table="products" count="0" where="sku = 'MUG-1'" />
</q:test>

text

tests/load.test.q
  PASS  the file's rows land in the table
  PASS  a file that fails halfway leaves nothing behind
2 passed, 0 failed

参见 DATA-1 和 DB-4。

把 CSV 文件导入数据表 ​

把 CSV 文件导入数据表