Python Model.make_cursor方法代码示例

# 需要导入模块: from model import Model [as 别名]
# 或者: from model.Model import make_cursor [as 别名]
def index():
  if request.method == 'GET':
    conn, cur = Model.make_cursor()
    rows = cur.execute('''
    SELECT name, price, SUM(quantity)
    FROM cookies, stock
    WHERE name=cookie and quantity > 0
    GROUP BY cookie
    ''')

    cookies = []
    for r in rows:
      r = {
        "name": r[0],
        "price": r[1],
        "count": r[2]
      }
      cookies.append(r)

    return render_template('cookies/index.html', cookies=cookies)

  if request.method == 'POST':
    cookie = request.get_json()
    name = cookie["name"]
    price = cookie["price"]
    conn, cur = Model.make_cursor()
    cur.execute('''
    INSERT INTO cookies(name, price)
    VALUES ("{}", {})
    '''.format(name, price))
    conn.commit()
    return "OK"

# 需要导入模块: from model import Model [as 别名]
# 或者: from model.Model import make_cursor [as 别名]
def show(buyer_id):
  #find buyer with buyer_id

  if request.method == 'GET':
    buyer = Model.execute('SELECT first, last, id FROM buyers WHERE id={}'.format(buyer_id)).fetchone()
    print("GET BUYER:", buyer)
    buyer = {
      "name": "{} {}".format(buyer[0], buyer[1]),
      "id": buyer[2]
    }

    orders = []
    for order in Model.execute('SELECT id, buyer, description, total FROM buyer_orders WHERE buyer={}'.format(buyer_id)):
      print("ORDER:", order)
      oid = order[0]
      order = {
        "id": order[0],
        "description": order[2],
        "total": order[3]
      }

      purchases = []
      for p in Model.execute('SELECT cookie, warehouse, buyer_order, amount FROM purchases WHERE buyer_order={}'.format(oid)):
        p = {
          "cookie": p[0],
          "warehouse": p[1],
          "amount": p[3]
        }
        purchases.append(p)

      order["purchases"] = purchases
      orders.append(order)
    buyer["orders"] = orders
    return jsonify(**buyer)

  if request.method == 'PUT':
    update = request.get_json()
    first, last = update["name"].split(" ")
    #update buyer with this information
    conn, cur = Model.make_cursor()
    cur.execute('''
    UPDATE buyers
    SET first="{}", last="{}"
    WHERE id={}
    '''.format(first, last, buyer_id))
    conn.commit()
    return "OK"

  if request.method == 'DELETE':
    #delete buyer with id = buyer_id
    conn, cur = Model.make_cursor()
    cur.execute('DELETE FROM buyers WHERE id={}'.format(buyer_id))
    conn.commit()
    return "OK"

# 需要导入模块: from model import Model [as 别名]
# 或者: from model.Model import make_cursor [as 别名]
def transaction(name):
    conn, cur = Model.make_cursor()
    transaction = request.get_json()
    cookie = transaction["cookie"]
    warehouse = transaction["warehouse"]
    price = transaction["price"]
    amount = transaction["amount"]

    cur.execute(
        """
  INSERT INTO transactions(distributor, cookie, warehouse, price, amount)
  VALUES ("{}", "{}", "{}",{}, {})
  """.format(
            name, cookie, warehouse, price, amount
        )
    )

    cur.execute(
        """
  UPDATE stock
  SET quantity = quantity + {}
  WHERE warehouse = "{}" and cookie = "{}"
  """.format(
            amount, warehouse, cookie
        )
    )

    conn.commit()
    return "OK"

# 需要导入模块: from model import Model [as 别名]
# 或者: from model.Model import make_cursor [as 别名]
def warehouse(name):
  conn, cur = Model.make_cursor()
  if request.method == 'GET':
    warehouses = []
    ware = cur.execute('''
    SELECT name, address
    FROM warehouses
    WHERE name = "{}"
    '''.format(name)).fetchone()
    print("GET", ware)
    ware = {
      "name": ware[0],
      "address" : ware[1]
    }

    cookies = []
    rows = cur.execute('''
    SELECT name, SUM(quantity)
    FROM cookies, stock
    WHERE name = cookie and warehouse = "{}" and quantity > 0
    GROUP BY cookie
    '''.format(name))
    for r in rows:
      print("Cookie", r)
      cook = {
        "name": r[0],
        "count": r[1]
      }
      cookies.append(cook)

    ware["cookies"] = cookies
    return jsonify(**ware)

  if request.method == 'PUT':
    update = request.get_json()
    new_name = update["name"]
    address = update["address"]

    cur.execute('''
    UPDATE warehouses
    SET name = "{}", address = "{}"
    WHERE name = "{}"
    '''.format(new_name, address, name))

    cur.execute('''
    UPDATE stock
    SET warehouse = "{}"
    WHERE warehouse = "{}"
    '''.format(new_name, name))

    conn.commit()
    return jsonify(**update)

  if request.method == 'DELETE':
    cur.execute('DELETE FROM warehouses WHERE name = "{}"'.format(name))
    cur.execute('DELETE FROM stock WHERE warehouse = "{}"'.format(name))
    conn.commit()
    return "OK"

# 需要导入模块: from model import Model [as 别名]
# 或者: from model.Model import make_cursor [as 别名]
def signin():
  conn, cur = Model.make_cursor()
  data = request.get_json()
  response = {}
  first = data["name"]
  password = data["password"]
  user = cur.execute('SELECT password FROM buyers WHERE first = "{}"'.format(first)).fetchone()
  response["ok"] = password == user[0]
  return jsonify(**response)

# 需要导入模块: from model import Model [as 别名]
# 或者: from model.Model import make_cursor [as 别名]
def dist(name):
    conn, cur = Model.make_cursor()

    if request.method == "GET":
        dist = cur.execute('SELECT name, address FROM distributors WHERE name="{}"'.format(name)).fetchone()
        dist = {"name": dist[0], "address": dist[1]}

        transactions = []
        rows = cur.execute(
            """
    SELECT id, price, cookie, amount
    FROM transactions
    WHERE distributor = "{}"
    """.format(
                name
            )
        )
        for r in rows:
            trans = {"id": r[0], "price": r[1], "cookie": r[2], "amount": r[3]}
            transactions.append(trans)

        dist["transactions"] = transactions
        return jsonify(**dist)

    if request.method == "PUT":
        update = request.get_json()
        new_name = update["name"]
        address = update["address"]
        cur.execute(
            """
    UPDATE distributors
    SET name = "{}", address = "{}"
    WHERE name = "{}"
    """.format(
                new_name, address, name
            )
        )

        # update in transactions
        cur.execute(
            """
    UPDATE transactions
    set distributor = "{}"
    WHERE distributor = "{}"
    """.format(
                new_name, name
            )
        )
        conn.commit()
        return "OK"

    if request.method == "DELETE":
        cur.execute('DELETE FROM distributors WHERE name = "{}"'.format(name))
        conn.commit()
        return "OK"

# 需要导入模块: from model import Model [as 别名]
# 或者: from model.Model import make_cursor [as 别名]
def cookie(name):
  conn, cur = Model.make_cursor()

  if request.method == 'GET':
    row = cur.execute('''
    SELECT name, price
    FROM cookies
    WHERE name="{}"
    '''.format(name)).fetchone()

    cookie = {
      "name": row[0],
      "price": row[1]
    }

    return jsonify(**cookie)

  if request.method == 'PUT':
    #update
    cookie = request.get_json()
    new_name = cookie["name"]
    price = cookie["price"]
    cur.execute('''
    UPDATE cookies
    SET name = "{}", price = {}
    WHERE name="{}"
    '''.format(new_name, price, name))

    #also update stock references
    cur.execute('''
    UPDATE stock
    SET cookie = "{}"
    WHERE cookie = "{}"
    '''.format(new_name, name))
    conn.commit()
    return "OK"

  if request.method == 'DELETE':
    conn, cur = Model.make_cursor()
    cur.execute('DELETE FROM cookies WHERE name = "{}"'.format(name))
    conn.commit()
    return "OK"

# 需要导入模块: from model import Model [as 别名]
# 或者: from model.Model import make_cursor [as 别名]
def shop():
  conn, cur = Model.make_cursor()

  if request.method == 'GET':
    return render_template("buyers/shop.html")

  if request.method == 'POST':
    update = request.get_json()
    total = 0
    buyer = update['buyer']
    del update['buyer']
    print(buyer)
    print(update)

    buyer = cur.execute('SELECT id FROM buyers WHERE first = "{}"'.format(buyer)).fetchone()[0]
    for cookie, amt in update.items():
      price = float(cur.execute('SELECT price FROM cookies WHERE name = "{}"'.format(cookie)).fetchone()[0])
      total += price

    cur.execute('''
    INSERT INTO buyer_orders(buyer, total)
    VALUES ({}, {})
    '''.format(buyer, total))

    conn.commit()
    order = cur.lastrowid

    for cookie, amt in update.items():
      price = float(cur.execute('SELECT price FROM cookies WHERE name = "{}"'.format(cookie)).fetchone()[0])
      warehouse = cur.execute('SELECT warehouse FROM stock WHERE cookie = "{}"'.format(cookie)).fetchone()[0]
      print(warehouse, price)

      cur.execute('''
      INSERT INTO purchases(cookie, warehouse, buyer_order, amount)
      VALUES ("{}", "{}", {}, {})
      '''.format(cookie, warehouse, order, amt))

      cur.execute('''
      UPDATE stock
      SET quantity = quantity - {}
      WHERE cookie = "{}" and warehouse = "{}"
      '''.format(amt, cookie, warehouse))

    conn.commit()
    print(total)
    return "OK"