04-CRUD using SQLAlchemy Core.ipynb · dreamrise/OverIQ 的 SQLAlchemy 中文教程

加入 Gitee

与超过 1200万开发者一起发现、参与优秀开源项目，私有仓库也完全免费：）

文件

该仓库未声明开源许可证文件（LICENSE），使用请关注具体项目描述及其代码上游依赖。

克隆/下载

04-CRUD using SQLAlchemy Core.ipynb 123.92 KB

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# CRUD using SQLAlchemy Core 使用 SQLAlchemy Core 的 CRUD\r\n",
    "\r\n",
    "SQL Expression Language is a backend neutral way to writing SQL statement using Python.\r\n",
    "\r\n",
    "SQL 表达式语言是使用 Python 编写 SQL 语句的后端中立方式。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "from IPython.core.interactiveshell import InteractiveShell\r\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "from sqlalchemy import create_engine, MetaData, Table, Integer, String, \\\r\n",
    "    Column, DateTime, ForeignKey, Numeric, CheckConstraint\r\n",
    "\r\n",
    "from datetime import datetime\r\n",
    "\r\n",
    "metadata = MetaData()\r\n",
    "\r\n",
    "engine = create_engine(\"mysql+pymysql://root:flask123@localhost/mydb\")\r\n",
    "\r\n",
    "customers = Table('customers', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('first_name', String(100), nullable=False),\r\n",
    "    Column('last_name', String(100), nullable=False),\r\n",
    "    Column('username', String(50), nullable=False),\r\n",
    "    Column('email', String(200), nullable=False),\r\n",
    "    Column('address', String(200), nullable=False),\r\n",
    "    Column('town', String(50), nullable=False),\r\n",
    "    Column('created_on', DateTime(), default=datetime.now),\r\n",
    "    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)\r\n",
    ")\r\n",
    "\r\n",
    "\r\n",
    "items = Table('items', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('name', String(200), nullable=False),\r\n",
    "    Column('cost_price', Numeric(10, 2), nullable=False),\r\n",
    "    Column('selling_price', Numeric(10, 2),  nullable=False),\r\n",
    "    Column('quantity', Integer(), nullable=False),\r\n",
    "    CheckConstraint('quantity > 0', name='quantity_check')\r\n",
    ")\r\n",
    "\r\n",
    "\r\n",
    "orders = Table('orders', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('customer_id', ForeignKey('customers.id')),\r\n",
    "    Column('date_placed', DateTime(), default=datetime.now),\r\n",
    "    Column('date_shipped', DateTime())\r\n",
    ")\r\n",
    "\r\n",
    "\r\n",
    "order_lines = Table('order_lines', metadata,\r\n",
    "    Column('id', Integer(), primary_key=True),\r\n",
    "    Column('order_id', ForeignKey('orders.id')),\r\n",
    "    Column('item_id', ForeignKey('items.id')),\r\n",
    "    Column('quantity', Integer())\r\n",
    ")\r\n",
    "\r\n",
    "\r\n",
    "metadata.create_all(engine)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Inserting Records 记录插入\r\n",
    "\r\n",
    "There are several ways to insert records into the database. The most basic way is to use the insert() method of the Table instance and pass values of the columns as keyword arguments to the values() method.\r\n",
    "\r\n",
    "有几种方法可以将记录插入数据库。最基本的方法是使用 Table 实例的 insert ()方法，并将列的值作为关键字参数传递给 values ()方法。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "ins = customers.insert().values(\r\n",
    "    first_name = 'John',\r\n",
    "    last_name = 'Green',\r\n",
    "    username = 'johngreen',\r\n",
    "    email = 'johngreen@mail.com',\r\n",
    "    address = '164 Hidden Valley Road',\r\n",
    "    town = 'Norfolk'\r\n",
    ")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "# To view the SQL this code would generate type the following:\r\n",
    "# 要查看 SQL，这段代码会生成 SQL:\r\n",
    "\r\n",
    "str(ins)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'INSERT INTO customers (first_name, last_name, username, email, address, town, created_on, updated_on) VALUES (:first_name, :last_name, :username, :email, :address, :town, :created_on, :updated_on)'"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Notice that the VALUES clause contains the bind parameters (i.e a parameter of the form :name) instead of the values passed to the values() method.\r\n",
    "\r\n",
    "注意，VALUES 子句包含绑定参数(即 form: name 的参数) ，而不是传递给 VALUES ()方法的值。\r\n",
    "\r\n",
    "When the query is run against the database the dialect will replace the bind parameters with the actual values. The dialect will also escape the values to mitigate the risk of SQL injection.\r\n",
    "\r\n",
    "当对数据库运行查询时，方言将用实际值替换绑定参数。方言还可以避开这些值，以减轻 SQL 注入的风险。\r\n",
    "\r\n",
    "We can view the values that will replace the bind parameters by compiling the insert statement.\r\n",
    "\r\n",
    "我们可以通过编译 insert 语句来查看将要替换 bind 参数的值。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "ins.compile().params"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "{'first_name': 'John',\n",
       " 'last_name': 'Green',\n",
       " 'username': 'johngreen',\n",
       " 'email': 'johngreen@mail.com',\n",
       " 'address': '164 Hidden Valley Road',\n",
       " 'town': 'Norfolk',\n",
       " 'created_on': None,\n",
       " 'updated_on': None}"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "We have created the insert statement but we haven't sent it to the database. To do that, call the execute() method of the Connection object.\r\n",
    "\r\n",
    "我们已经创建了 insert 语句，但是还没有将其发送到数据库。为此，调用 Connection 对象的 execute ()方法。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "conn = engine.connect()\r\n",
    "conn\r\n",
    "r = conn.execute(ins)\r\n",
    "r"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.base.Connection at 0x2616d7c1d90>"
      ]
     },
     "metadata": {},
     "execution_count": 6
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.result.ResultProxy at 0x2616d5b5d90>"
      ]
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The above code inserts the following record in the customers table.\r\n",
    "\r\n",
    "上面的代码在 customers 表中插入以下记录。\r\n",
    "\r\n",
    "(图略)\r\n",
    "\r\n",
    "The execute() method returns an object of type ResultProxy. The ResultProxy provides several attributes, one of them is called `inserted_primary_key` which returns the primary key of the records just inserted.\r\n",
    "\r\n",
    "Execute ()方法返回一个 ResultProxy 类型的对象。ResultProxy 提供了几个属性，其中一个叫做 `inserted_primary_key`，它返回刚刚插入的记录的主键。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "r.inserted_primary_key\r\n",
    "type(r.inserted_primary_key)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[1]"
      ]
     },
     "metadata": {},
     "execution_count": 7
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "metadata": {},
     "execution_count": 7
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Another way to create insert statement is to use the standalone insert() function from the sqlalchemy package.\r\n",
    "\r\n",
    "创建 insert 语句的另一种方法是使用 sqlalchemy 包中的独立 insert ()函数。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "# metadata.drop_all(engine)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "from sqlalchemy import insert\r\n",
    "\r\n",
    "ins = insert(customers).values(\r\n",
    "    first_name = 'Katherine',\r\n",
    "    last_name = 'Wilson',\r\n",
    "    username = 'katwilson',\r\n",
    "    email = 'katwilson@gmail.com',\r\n",
    "    address = '4685 West Side Avenue',\r\n",
    "    town = 'Peterbrugh'\r\n",
    ")\r\n",
    "\r\n",
    "r = conn.execute(ins)\r\n",
    "r.inserted_primary_key"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[2]"
      ]
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Multiple Inserts 多重插入\r\n",
    "\r\n",
    "Instead of passing values to the values() method as keyword arguments, we can also pass them to the execute() method.\r\n",
    "\r\n",
    "与将值作为关键字参数传递给 values ()方法不同，我们还可以将它们传递给 execute ()方法。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "ins = insert(customers)\r\n",
    "\r\n",
    "r = conn.execute(ins, \r\n",
    "    first_name = \"Tim\", \r\n",
    "    last_name = \"Snyder\", \r\n",
    "    username = \"timsnyder\", \r\n",
    "    email = \"timsnyder@mail.com\",\r\n",
    "    address = '1611 Sundown Lane',\r\n",
    "    town = 'Langdale'\r\n",
    ")\r\n",
    "r.inserted_primary_key"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[3]"
      ]
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The execute() method is quite flexible because it allows us to insert multiple rows by passing a list of dictionaries each representing a row to be inserted.\r\n",
    "\r\n",
    "Execute ()方法非常灵活，因为它允许我们通过传递一个表示要插入的行的 ```字典列表``` 来插入多个行。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "r = conn.execute(ins, [\r\n",
    "        {\r\n",
    "            \"first_name\": \"John\", \r\n",
    "            \"last_name\": \"Lara\", \r\n",
    "            \"username\": \"johnlara\", \r\n",
    "            \"email\":\"johnlara@mail.com\", \r\n",
    "            \"address\": \"3073 Derek Drive\",\r\n",
    "            \"town\": \"Norfolk\"\r\n",
    "        },\r\n",
    "        {\r\n",
    "            \"first_name\": \"Sarah\", \r\n",
    "            \"last_name\": \"Tomlin\", \r\n",
    "            \"username\": \"sarahtomlin\", \r\n",
    "            \"email\":\"sarahtomlin@mail.com\",\r\n",
    "            \"address\": \"3572 Poplar Avenue\",\r\n",
    "            \"town\": \"Norfolk\"\r\n",
    "        },\r\n",
    "        {\r\n",
    "            \"first_name\": \"Pablo\", \r\n",
    "            \"last_name\": \"Gibson\", \r\n",
    "            \"username\": \"pablogibson\", \r\n",
    "            \"email\":\"pablogibson@mail.com\",\r\n",
    "            \"address\": \"3494 Murry Street\",\r\n",
    "            \"town\": \"Peterbrugh\"\r\n",
    "        },\r\n",
    "        {\r\n",
    "            \"first_name\": \"Pablo\", \r\n",
    "            \"last_name\": \"Lewis\", \r\n",
    "            \"username\": \"pablolewis\", \r\n",
    "            \"email\":\"pablolewis@mail.com\",\r\n",
    "            \"address\": \"3282 Jerry Toth Drive\",\r\n",
    "            \"town\": \"Peterbrugh\"\r\n",
    "        },\r\n",
    "    ])\r\n",
    "\r\n",
    "r.rowcount"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Before moving on to the next section, let's add some records to the items, orders and order_lines table.\r\n",
    "\r\n",
    "在继续下一部分之前，让我们向项目、订单和 order _ lines 表添加一些记录。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "items_list = [\r\n",
    "    {\r\n",
    "        \"name\":\"Chair\",\r\n",
    "        \"cost_price\": 9.21,\r\n",
    "        \"selling_price\": 10.81,\r\n",
    "        \"quantity\": 10\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"name\":\"Pen\",\r\n",
    "        \"cost_price\": 3.45,\r\n",
    "        \"selling_price\": 4.51,\r\n",
    "        \"quantity\": 3\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"name\":\"Headphone\",\r\n",
    "        \"cost_price\": 15.52,\r\n",
    "        \"selling_price\": 16.81,\r\n",
    "        \"quantity\": 50\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"name\":\"Travel Bag\",\r\n",
    "        \"cost_price\": 20.1,\r\n",
    "        \"selling_price\": 24.21,\r\n",
    "        \"quantity\": 50\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"name\":\"Keyboard\",\r\n",
    "        \"cost_price\": 20.12,\r\n",
    "        \"selling_price\": 22.11,\r\n",
    "        \"quantity\": 50\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"name\":\"Monitor\",\r\n",
    "        \"cost_price\": 200.14,\r\n",
    "        \"selling_price\": 212.89,\r\n",
    "        \"quantity\": 50\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"name\":\"Watch\",\r\n",
    "        \"cost_price\": 100.58,\r\n",
    "        \"selling_price\": 104.41,\r\n",
    "        \"quantity\": 50\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"name\":\"Water Bottle\",\r\n",
    "        \"cost_price\": 20.89,\r\n",
    "        \"selling_price\": 25.00,\r\n",
    "        \"quantity\": 50\r\n",
    "    },\r\n",
    "]\r\n",
    "\r\n",
    "order_list = [\r\n",
    "    {\r\n",
    "        \"customer_id\": 1\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"customer_id\": 1\r\n",
    "    }\r\n",
    "]\r\n",
    "\r\n",
    "order_line_list = [\r\n",
    "    {\r\n",
    "        \"order_id\": 1,\r\n",
    "        \"item_id\": 1,\r\n",
    "        \"quantity\": 5\r\n",
    "    }, \r\n",
    "    {\r\n",
    "        \"order_id\": 1,\r\n",
    "        \"item_id\": 2,\r\n",
    "        \"quantity\": 2\r\n",
    "    }, \r\n",
    "    {\r\n",
    "        \"order_id\": 1,\r\n",
    "        \"item_id\": 3,\r\n",
    "        \"quantity\": 1\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"order_id\": 2,\r\n",
    "        \"item_id\": 1,\r\n",
    "        \"quantity\": 5\r\n",
    "    },\r\n",
    "    {\r\n",
    "        \"order_id\": 2,\r\n",
    "        \"item_id\": 2,\r\n",
    "        \"quantity\": 4\r\n",
    "    },\r\n",
    "]\r\n",
    "\r\n",
    "r = conn.execute(insert(items), items_list)\r\n",
    "r.rowcount\r\n",
    "r = conn.execute(insert(orders), order_list)\r\n",
    "r.rowcount\r\n",
    "r = conn.execute(insert(order_lines), order_line_list)\r\n",
    "r.rowcount"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "metadata": {},
     "execution_count": 11
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "metadata": {},
     "execution_count": 11
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Selecting Records 选择记录\r\n",
    "\r\n",
    "To select records we use select() method of the Table object.\r\n",
    "\r\n",
    "要选择记录，我们使用 Table 对象的 select ()方法。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "s = customers.select()\r\n",
    "str(s)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \\nFROM customers'"
      ]
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "As you can see, this query is not qualified in any way, as a result, it will return all the rows from the customers table.\r\n",
    "\r\n",
    "正如您所看到的，这个查询无论如何都不具有限定性，因此，它将返回 customers 表中的所有行。\r\n",
    "\r\n",
    "Another way to create SELECT query is to use the standalone select() function. It accepts a list of tables or columns from where to retrieve data.\r\n",
    "\r\n",
    "创建 SELECT 查询的另一种方法是使用独立的 SELECT ()函数。它接受从哪里检索数据的表或列的列表。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "source": [
    "from sqlalchemy import select\r\n",
    "s = select([customers])\r\n",
    "str(s)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \\nFROM customers'"
      ]
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "As usual, to send the query to the database we use the execute() method:\r\n",
    "\r\n",
    "像往常一样，为了将查询发送到数据库，我们使用 execute ()方法:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "source": [
    "r = conn.execute(s)\r\n",
    "r.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 40, 59), datetime.datetime(2021, 8, 23, 15, 40, 59)),\n",
       " (2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 50, 40), datetime.datetime(2021, 8, 23, 15, 50, 40)),\n",
       " (3, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2021, 8, 23, 15, 51, 41), datetime.datetime(2021, 8, 23, 15, 51, 41)),\n",
       " (4, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44)),\n",
       " (5, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44)),\n",
       " (6, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44)),\n",
       " (7, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44))]"
      ]
     },
     "metadata": {},
     "execution_count": 14
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The fetchall() method of the ResultProxy object returns all the records matched by the query. Once the result set is exhausted, subsequent calls to fetchall() will return an empty list.\r\n",
    "\r\n",
    "ResultProxy 对象的 fetchall ()方法返回查询匹配的所有记录。一旦结果集用尽，对 fetchall ()的后续调用将返回一个空列表。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "r.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The fetchall() method loads all the results into the memory at once. Thus, it is not very efficient on a large reset set. Alternatively, you can use for loop to iterate over the result set one at a time.\r\n",
    "\r\n",
    "Fetchall ()方法立即将所有结果加载到内存中。因此，在大型复位集上它不是非常高效。或者，您可以使用 for 循环一次迭代一个结果集。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "source": [
    "rs = conn.execute(s)\r\n",
    "for row in rs:\r\n",
    "    print(row)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 40, 59), datetime.datetime(2021, 8, 23, 15, 40, 59))\n",
      "(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 50, 40), datetime.datetime(2021, 8, 23, 15, 50, 40))\n",
      "(3, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2021, 8, 23, 15, 51, 41), datetime.datetime(2021, 8, 23, 15, 51, 41))\n",
      "(4, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44))\n",
      "(5, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44))\n",
      "(6, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44))\n",
      "(7, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44))\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Here is a list of some common methods and attributes of the ResultProxy object.\r\n",
    "\r\n",
    "下面是 ResultProxy 对象的一些常用方法和属性的列表。\r\n",
    "\r\n",
    "| Method/Attribute 方法/属性 | Description 描述                                             |\r\n",
    "| :------------------------- | :----------------------------------------------------------- |\r\n",
    "| `fetchone()`               | fetch the next row from the result set. If the result set has been exhausted, subsequent calls to 从结果集中提取下一行。如果结果集已用尽，则后续调用`fetchone()` returns 回报`None`. |\r\n",
    "| `fetchmany(size=None)`     | fetch the specified number of rows from the result set. If the result set has been exhausted, subsequent calls to 从结果集中提取指定的行数。如果结果集已用尽，则后续调用`fetchmany()` returns 回报`None`. |\r\n",
    "| `fetchall()`               | fetch all the rows from the result set. If the result set has been exhausted, subsequent calls to 从结果集中提取所有行。如果结果集已用尽，则后续调用`fetchall()` returns 回报`None`. |\r\n",
    "| `first()`                  | fetch the first row from the result set and close the connection. This means that after calling the first() method we can't access any other rows in the result set, until we send the query to the database again (using the execute() method). \t从结果集中取出第一行并关闭连接 方法，我们不能访问结果集中的任何其他行，直到我们再次向数据库发送查询(使用 execute() 方法) |\r\n",
    "| `rowcount`                 | returns the number of rows in the result set. 返回结果集中的行数 |\r\n",
    "| `keys()`                   | returns a list of columns from where data is retrieved. 返回检索数据的列的列表 |\r\n",
    "| `scalar()`                 | fetch the first column from the first row and close the connection. If the result set is empty it returns 从第一行中取出第一列并关闭连接。如果结果集为空，则返回`None`. |\r\n",
    "\r\n",
    "The following shell sessions demonstrates methods and attributes we just discussed in action.\r\n",
    "\r\n",
    "下面的 shell 会话演示了我们刚才在实践中讨论的方法和属性。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "source": [
    "s = select([customers])"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### fetchone()"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "source": [
    "r = conn.execute(s)\r\n",
    "r.fetchone()\r\n",
    "r.fetchone()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 40, 59), datetime.datetime(2021, 8, 23, 15, 40, 59))"
      ]
     },
     "metadata": {},
     "execution_count": 18
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 50, 40), datetime.datetime(2021, 8, 23, 15, 50, 40))"
      ]
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### fetchmany()"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "source": [
    "r = conn.execute(s)\r\n",
    "r.fetchmany(3)\r\n",
    "r.fetchmany(5)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 40, 59), datetime.datetime(2021, 8, 23, 15, 40, 59)),\n",
       " (2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 50, 40), datetime.datetime(2021, 8, 23, 15, 50, 40)),\n",
       " (3, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2021, 8, 23, 15, 51, 41), datetime.datetime(2021, 8, 23, 15, 51, 41))]"
      ]
     },
     "metadata": {},
     "execution_count": 19
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(4, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44)),\n",
       " (5, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44)),\n",
       " (6, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44)),\n",
       " (7, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44))]"
      ]
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### first()"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "source": [
    "r = conn.execute(s)\r\n",
    "r.first()\r\n",
    "# r.first()  # the connection is now closed, the subsequent call to first() will result in an error"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 40, 59), datetime.datetime(2021, 8, 23, 15, 40, 59))"
      ]
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "source": [
    "r.first()  # ResourceClosedError: This result object is closed."
   ],
   "outputs": [
    {
     "output_type": "error",
     "ename": "ResourceClosedError",
     "evalue": "This result object is closed.",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\engine\\result.py\u001b[0m in \u001b[0;36m_fetchone_impl\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1214\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1215\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchone\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1216\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'NoneType' object has no attribute 'fetchone'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mResourceClosedError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp/ipykernel_78856/2305035133.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfirst\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m  \u001b[1;31m# ResourceClosedError: This result object is closed.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\engine\\result.py\u001b[0m in \u001b[0;36mfirst\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1361\u001b[0m             \u001b[0mrow\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_fetchone_impl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1362\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1363\u001b[1;33m             self.connection._handle_dbapi_exception(\n\u001b[0m\u001b[0;32m   1364\u001b[0m                 \u001b[0me\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcontext\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1365\u001b[0m             )\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\engine\\base.py\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[1;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[0;32m   1512\u001b[0m                 )\n\u001b[0;32m   1513\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1514\u001b[1;33m                 \u001b[0mutil\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mraise_\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mwith_traceback\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1515\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1516\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mraise_\u001b[1;34m(***failed resolving arguments***)\u001b[0m\n\u001b[0;32m    180\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    181\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 182\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    183\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    184\u001b[0m             \u001b[1;31m# credit to\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\engine\\result.py\u001b[0m in \u001b[0;36mfirst\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1359\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1360\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1361\u001b[1;33m             \u001b[0mrow\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_fetchone_impl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1362\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1363\u001b[0m             self.connection._handle_dbapi_exception(\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\engine\\result.py\u001b[0m in \u001b[0;36m_fetchone_impl\u001b[1;34m(self)\u001b[0m\n\u001b[0;32m   1215\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchone\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1216\u001b[0m         \u001b[1;32mexcept\u001b[0m \u001b[0mAttributeError\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1217\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_non_result\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1218\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1219\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_fetchmany_impl\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msize\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\engine\\result.py\u001b[0m in \u001b[0;36m_non_result\u001b[1;34m(self, default, err)\u001b[0m\n\u001b[0;32m   1242\u001b[0m             )\n\u001b[0;32m   1243\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mclosed\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1244\u001b[1;33m             util.raise_(\n\u001b[0m\u001b[0;32m   1245\u001b[0m                 \u001b[0mexc\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mResourceClosedError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"This result object is closed.\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1246\u001b[0m                 \u001b[0mreplace_context\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merr\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\PYTHON39\\lib\\site-packages\\sqlalchemy\\util\\compat.py\u001b[0m in \u001b[0;36mraise_\u001b[1;34m(***failed resolving arguments***)\u001b[0m\n\u001b[0;32m    180\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    181\u001b[0m         \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 182\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mexception\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    183\u001b[0m         \u001b[1;32mfinally\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    184\u001b[0m             \u001b[1;31m# credit to\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mResourceClosedError\u001b[0m: This result object is closed."
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### rowcount  returns the number of rows in the result set. 返回结果集中的行数"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "source": [
    "r = conn.execute(s)\r\n",
    "r.rowcount"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "7"
      ]
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### keys() returns a list of columns from where data is retrieved. 返回检索数据的列的列表"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "source": [
    "r.keys()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['id',\n",
       " 'first_name',\n",
       " 'last_name',\n",
       " 'username',\n",
       " 'email',\n",
       " 'address',\n",
       " 'town',\n",
       " 'created_on',\n",
       " 'updated_on']"
      ]
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### scalar() fetch the first column from the first row and close the connection. If the result set is empty it returns 从第一行中取出第一列并关闭连接。如果结果集为空，则返回None."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "source": [
    "r.scalar()  "
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "t is important to note that rows returns by methods fetchxxx() and first() are not tuples or dictionaries, instead, it is an object of type RowProxy, which allows us to access data in the row using column name, index position or Column instance. For example:\r\n",
    "\r\n",
    "需要注意的是，方法 fetchxxx ()和 first ()返回的行不是元组或字典，而是 RowProxy 类型的对象，它允许我们使用列名、索引位置或 Column 实例访问行中的数据。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "source": [
    "r = conn.execute(s)\r\n",
    "row = r.fetchone()\r\n",
    "row\r\n",
    "type(row)  # sqlalchemy.engine.result.RowProxy\r\n",
    "row['id'], row['first_name']    # access column data via column name 访问数据通过列名\r\n",
    "row[0], row[1]    # access column data via column index position 访问数据通过索引位置号\r\n",
    "row[customers.c.id], row[customers.c.first_name]    # access column data via Column object 通过Column对象访问Column数据\r\n",
    "row.id, row.first_name    # access column data via attribute 通过属性访问Column数据"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 40, 59), datetime.datetime(2021, 8, 23, 15, 40, 59))"
      ]
     },
     "metadata": {},
     "execution_count": 32
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "sqlalchemy.engine.result.RowProxy"
      ]
     },
     "metadata": {},
     "execution_count": 32
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, 'John')"
      ]
     },
     "metadata": {},
     "execution_count": 32
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, 'John')"
      ]
     },
     "metadata": {},
     "execution_count": 32
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, 'John')"
      ]
     },
     "metadata": {},
     "execution_count": 32
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "(1, 'John')"
      ]
     },
     "metadata": {},
     "execution_count": 32
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "To access data from multiple tables simply pass comma separated list of Table instances to the select() function.\r\n",
    "\r\n",
    "要访问多个表中的数据，只需将逗号分隔的 Table 实例列表传递给 select ()函数。\r\n",
    "\r\n",
    "```python\r\n",
    "select([tableOne, tableTwo])\r\n",
    "```\r\n",
    "\r\n",
    "This code would return the Cartesian product of rows present in both the tables. We will learn how to create an SQL JOIN later in this chapter.\r\n",
    "\r\n",
    "这段代码将返回两个表中的行的笛卡儿积。我们将在本章后面学习如何创建 SQL JOIN。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Filtering Records 过滤记录\r\n",
    "\r\n",
    "To filter records we use where() method. It accept a condition and adds a WHERE clause to the SELECT statement.\r\n",
    "\r\n",
    "要过滤记录，我们使用 WHERE ()方法。它接受一个条件，并向 SELECT 语句添加一个 WHERE 子句。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "source": [
    "s = select([items]).where(\r\n",
    "    items.c.cost_price > 20\r\n",
    ")\r\n",
    "\r\n",
    "# This query will return all the items whose cost price is greater than 20.\r\n",
    "# 此查询将返回成本价格大于20的所有项目。\r\n",
    "\r\n",
    "str(s)\r\n",
    "r = conn.execute(s)\r\n",
    "r.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "'SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \\nFROM items \\nWHERE items.cost_price > :cost_price_1'"
      ]
     },
     "metadata": {},
     "execution_count": 34
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 34
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "We can specify additional conditions by simple chaining the where() method.\r\n",
    "\r\n",
    "我们可以通过简单地链接 where ()方法来指定附加条件。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "source": [
    "s = select([items]).\\\r\n",
    "    where(items.c.cost_price + items.c.selling_price > 50).\\\r\n",
    "    where(items.c.quantity > 10)\r\n",
    "print(s)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.cost_price + items.selling_price > :param_1 AND items.quantity > :quantity_1\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "As you can see, when we chain the where() method the conditions are ANDed together.\r\n",
    "\r\n",
    "正如您所看到的，当我们链接 where ()方法时，条件被 组合 在一起。\r\n",
    "\r\n",
    "So how do we specify OR or NOT conditions to our SELECT statement?\r\n",
    "\r\n",
    "那么我们如何为 SELECT 语句指定 OR 或 NOT 条件呢？\r\n",
    "\r\n",
    "It turns out that instead of chaining where() method there are two other ways to combine conditions:\r\n",
    "\r\n",
    "结果表明，除了链接 where ()方法，还有两种其他方法可以组合条件:\r\n",
    "\r\n",
    "Bitwise Operators. 按位运算符\r\n",
    "\r\n",
    "Conjunctions. 连词\r\n",
    "\r\n",
    "Let's start with the first one.\r\n",
    "\r\n",
    "让我们从第一个开始。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Bitwise Operators 按位运算符\r\n",
    "\r\n",
    "Bitwise Operators &, | and ~ allow us to connect conditions with SQL AND, OR and NOT operators respectively.\r\n",
    "\r\n",
    "按位运算符 & 、 | 和 ~ 允许我们分别使用 SQL 的 AND、 OR 和 NOT 运算符连接条件。\r\n",
    "\r\n",
    "The preceding query can be coded using bitwise operators as follows:\r\n",
    "\r\n",
    "前面的查询可以使用按位运算符编码，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "source": [
    "s = select([items]).\\\r\n",
    "where(\r\n",
    "    (items.c.cost_price > 200 ) &  # 和\r\n",
    "    (items.c.quantity < 5)\r\n",
    ") \r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.cost_price > :cost_price_1 OR items.quantity < :quantity_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 39
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    " Notice that the conditions are wrapped using parentheses, this is because the precedence of bitwise operators is greater than that of + and > operators.\r\n",
    " 注意，条件使用括号包装，这是因为位运算符的优先级大于 + 和 > 运算符。\r\n",
    "\r\n",
    " 下面是 or 以及 not"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "source": [
    "s = select([items]).\\\r\n",
    "where(\r\n",
    "    (items.c.cost_price > 200 ) | # or 或\r\n",
    "    (items.c.quantity < 5)\r\n",
    ") \r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()\r\n",
    "\r\n"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.cost_price > :cost_price_1 OR items.quantity < :quantity_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 40
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "source": [
    "s = select([items]).\\\r\n",
    "where(    \r\n",
    "    ~(items.c.quantity == 50)  # ~ not 求反, 这里是不等于\r\n",
    ") \r\n",
    "\r\n",
    "\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity != :quantity_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3)]"
      ]
     },
     "metadata": {},
     "execution_count": 41
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "source": [
    "s = select([items]).\\\r\n",
    "where(\r\n",
    "    ~(items.c.quantity == 50) &\r\n",
    "    (items.c.cost_price < 20)      # 组合使用 不等于, 并且\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity != :quantity_1 AND items.cost_price < :cost_price_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3)]"
      ]
     },
     "metadata": {},
     "execution_count": 42
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Conjunctions 连词\r\n",
    "\r\n",
    "Another way to connect conditions is to use conjunction functions i.e and_(), or_() and not_(). This is the preferred way of defining conditions in SQLAlchemy.\r\n",
    "\r\n",
    "连接条件的另一种方法是使用连接函数，即 _ () ，或 _ ()和不 _ ()。这是在 SQLAlchemy 中定义条件的首选方法。\r\n",
    "\r\n",
    "Here are some examples:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "source": [
    "from sqlalchemy import and_, or_, not_"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "source": [
    "s = select([items]).\\\r\n",
    "where(    \r\n",
    "    and_(\r\n",
    "        items.c.quantity >= 50,\r\n",
    "        items.c.cost_price < 100,\r\n",
    "    )\r\n",
    ") \r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity >= :quantity_1 AND items.cost_price < :cost_price_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 31
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "source": [
    "s = select([items]).\\\r\n",
    "where(    \r\n",
    "    or_(\r\n",
    "        items.c.quantity >= 50,\r\n",
    "        items.c.cost_price < 100,\r\n",
    "    )\r\n",
    ") \r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity >= :quantity_1 OR items.cost_price < :cost_price_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 32
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "source": [
    "s = select([items]).\\\r\n",
    "where(    \r\n",
    "    and_(\r\n",
    "        items.c.quantity >= 50,\r\n",
    "        items.c.cost_price < 100,\r\n",
    "        not_(\r\n",
    "            items.c.name == 'Headphone'            \r\n",
    "        ),        \r\n",
    "    )\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity >= :quantity_1 AND items.cost_price < :cost_price_1 AND items.name != :name_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 33
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Other Common Comparison Operators 其他常用的比较运算子\r\n",
    "\r\n",
    "The following listing demonstrates how to use some other comparison operators while defining conditions in SQLAlchemy.\r\n",
    "\r\n",
    "下面的清单演示了如何在 SQLAlchemy 中定义条件时使用其他一些比较运算符。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### IS NULL  为空"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "source": [
    "s = select([orders]).where(\r\n",
    "    orders.c.date_shipped == None\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT orders.id, orders.customer_id, orders.date_placed, orders.date_shipped \n",
      "FROM orders \n",
      "WHERE orders.date_shipped IS NULL\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 1, datetime.datetime(2021, 8, 23, 15, 52, 43), None),\n",
       " (2, 1, datetime.datetime(2021, 8, 23, 15, 52, 43), None)]"
      ]
     },
     "metadata": {},
     "execution_count": 43
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### IS NOT NULL 不为空"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "source": [
    "s = select([orders]).where(\r\n",
    "    orders.c.date_shipped != None\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT orders.id, orders.customer_id, orders.date_placed, orders.date_shipped \n",
      "FROM orders \n",
      "WHERE orders.date_shipped IS NOT NULL\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "metadata": {},
     "execution_count": 46
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### IN 在...中"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "source": [
    "s = select([customers]).where(\r\n",
    "    customers.c.first_name.in_([\"Sarah\", \"John\"])\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \n",
      "FROM customers \n",
      "WHERE customers.first_name IN (:first_name_1, :first_name_2)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 40, 59), datetime.datetime(2021, 8, 23, 15, 40, 59)),\n",
       " (4, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44)),\n",
       " (5, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2021, 8, 23, 15, 51, 44), datetime.datetime(2021, 8, 23, 15, 51, 44))]"
      ]
     },
     "metadata": {},
     "execution_count": 47
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### NOT IN 不在...中"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "source": [
    "s = select([customers]).where(\r\n",
    "    customers.c.first_name.notin_([\"Sarah\", \"John\"])\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \n",
      "FROM customers \n",
      "WHERE customers.first_name NOT IN (:first_name_1, :first_name_2)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 14, 15, 645901), datetime.datetime(2018, 8, 1, 17, 14, 15, 645936)),\n",
       " (3, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2018, 8, 1, 17, 22, 4, 349494), datetime.datetime(2018, 8, 1, 17, 22, 4, 349530)),\n",
       " (6, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783068), datetime.datetime(2018, 8, 1, 17, 22, 35, 783077)),\n",
       " (7, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2018, 8, 1, 17, 22, 35, 783086), datetime.datetime(2018, 8, 1, 17, 22, 35, 783095))]"
      ]
     },
     "metadata": {},
     "execution_count": 37
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### BETWEEN  在...中间"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "source": [
    "s = select([items]).where(\r\n",
    "    items.c.cost_price.between(10, 20)\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.cost_price BETWEEN :cost_price_1 AND :cost_price_2\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 38
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### NOT BETWEEN 不在...中间"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "source": [
    "s = select([items]).where(\r\n",
    "    not_(items.c.cost_price.between(10, 20))\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.cost_price NOT BETWEEN :cost_price_1 AND :cost_price_2\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 39
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### LIKE  类似于"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "source": [
    "s = select([items]).where(\r\n",
    "    items.c.name.like(\"Wa%\")\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 48
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### ilike 不区分大小写的like"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "source": [
    "s = select([items]).where(\r\n",
    "    items.c.name.ilike(\"wa%\")\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE lower(items.name) LIKE lower(:name_1)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 49
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### NOT LIKE"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "source": [
    "s = select([items]).where(\r\n",
    "    not_(items.c.name.like(\"wa%\"))\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.name NOT LIKE :name_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 42
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Ordering Result 结果排序\r\n",
    "The order_by() method adds ORDER BY clause to the SELECT statement. It accepts one or more columns to sort by. For each column listed in the order_by() clause, you can specify whether the rows are sorted in ascending order (using asc()) or descending order (using desc()). If neither specified rows are sorted in ascending order. For example:\r\n",
    "\r\n",
    "ORDER_BY ()方法将 orderby 子句添加到 SELECT 语句中。它接受一个或多个列进行排序。对于 order_by()子句中列出的每一列，您可以指定行是按升序排序(使用 asc())还是按降序排序(使用 desc())。如果指定的行都不按升序排序。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "source": [
    "s = select([items]).where(\r\n",
    "    items.c.quantity > 10\r\n",
    ").order_by(items.c.cost_price)\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity > :quantity_1 ORDER BY items.cost_price\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 50
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "source": [
    "from sqlalchemy import desc\r\n",
    "\r\n",
    "s = select([items]).where(\r\n",
    "    items.c.quantity > 10\r\n",
    ").order_by(desc(items.c.cost_price))\r\n",
    "\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity > :quantity_1 ORDER BY items.cost_price DESC\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 51
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "source": [
    "s = select([items]).order_by(\r\n",
    "    items.c.quantity, \r\n",
    "    desc(items.c.cost_price)   # 它根据两列对行进行排序，首先是按 quantity (按升序) ，然后是按成本 _ 价格(按降序)。\r\n",
    ")\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items ORDER BY items.quantity, items.cost_price DESC\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10),\n",
       " (6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),\n",
       " (8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50),\n",
       " (5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),\n",
       " (3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 52
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Limiting Results 限制结果\r\n",
    "\r\n",
    "The limit() method adds the LIMIT clause to the SELECT statement. It accepts an integer which indicates the number of rows to return. For example:\r\n",
    "\r\n",
    "LIMIT ()方法将 LIMIT 子句添加到 SELECT 语句中。它接受一个整数，该整数表示要返回的行数。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "source": [
    "s = select([items]).order_by(\r\n",
    "    items.c.quantity\r\n",
    ").limit(2)\r\n",
    "\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items ORDER BY items.quantity\n",
      " LIMIT :param_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),\n",
       " (1, 'Chair', Decimal('9.21'), Decimal('10.81'), 10)]"
      ]
     },
     "metadata": {},
     "execution_count": 53
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "To specify an offset (i.e. the starting position) to the LIMIT clause use the offset() method.\r\n",
    "\r\n",
    "要为 LIMIT 子句指定偏移量(即起始位置) ，请使用偏移量()方法。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "source": [
    "s = select([items]).order_by(\r\n",
    "    items.c.quantity\r\n",
    ").limit(2).offset(2)\r\n",
    "\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items ORDER BY items.quantity\n",
      " LIMIT :param_1 OFFSET :param_2\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),\n",
       " (4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 54
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Limiting Columns  限制字段\r\n",
    "\r\n",
    "The SELECT statements we have created so far has returned data from all the columns of the table. We can limit the number of fields returned by the query by passing the name of the fields as a list to the select() function. For example:\r\n",
    "\r\n",
    "到目前为止，我们创建的 SELECT 语句已经从表的所有列返回了数据。我们可以通过将字段名称作为列表传递给 select() 函数来限制查询返回的字段数量。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "source": [
    "s = select([items.c.name, items.c.quantity]).where(\r\n",
    "    items.c.quantity ==  50\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.name, items.quantity \n",
      "FROM items \n",
      "WHERE items.quantity = :quantity_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['name', 'quantity']"
      ]
     },
     "metadata": {},
     "execution_count": 56
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Headphone', 50),\n",
       " ('Travel Bag', 50),\n",
       " ('Keyboard', 50),\n",
       " ('Monitor', 50),\n",
       " ('Watch', 50),\n",
       " ('Water Bottle', 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 56
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "This query returns the data only from the name and quantity columns from the items table.\r\n",
    "\r\n",
    "此查询仅返回 items 表中的 name 和 quantity 列中的数据。\r\n",
    "\r\n",
    "Just as in SQL, we can perform simple calculations on the rows retrieved before sending them to the output. For example:\r\n",
    "\r\n",
    "正如在 SQL 中一样，我们可以在将检索到的行发送到输出之前对它们执行简单的计算。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "source": [
    "s = select([items.c.name, items.c.quantity, items.c.selling_price * 5 ]).where(\r\n",
    "    items.c.quantity ==  50\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.name, items.quantity, items.selling_price * :selling_price_1 AS anon_1 \n",
      "FROM items \n",
      "WHERE items.quantity = :quantity_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['name', 'quantity', 'anon_1']"
      ]
     },
     "metadata": {},
     "execution_count": 57
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Headphone', 50, Decimal('84.05')),\n",
       " ('Travel Bag', 50, Decimal('121.05')),\n",
       " ('Keyboard', 50, Decimal('110.55')),\n",
       " ('Monitor', 50, Decimal('1064.45')),\n",
       " ('Watch', 50, Decimal('522.05')),\n",
       " ('Water Bottle', 50, Decimal('125.00'))]"
      ]
     },
     "metadata": {},
     "execution_count": 57
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Notice that the items.c.selling_price * 5 is not an actual column, therefore, an anonymous name called anon_1 is generated to display the query result (line 5).\r\n",
    "\r\n",
    "请注意，items.c.selling _ price * 5并不是一个实际的列，因此，将生成一个名为 anon _ 1的匿名名称来显示查询结果(第5行)。\r\n",
    "\r\n",
    "We can assign a label to a column or expression using the label() method, which works by adding an AS subclause to the SELECT statement.\r\n",
    "\r\n",
    "我们可以使用 label ()方法为列或表达式分配标签，该方法通过向 SELECT 语句添加 AS 子句来工作。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "source": [
    "s = select([\r\n",
    "        items.c.name, \r\n",
    "        items.c.quantity, \r\n",
    "        (items.c.selling_price * 5).label('price') \r\n",
    "    ]).where(\r\n",
    "    items.c.quantity ==  50\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.name, items.quantity, items.selling_price * :selling_price_1 AS price \n",
      "FROM items \n",
      "WHERE items.quantity = :quantity_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['name', 'quantity', 'price']"
      ]
     },
     "metadata": {},
     "execution_count": 50
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Headphone', 50, Decimal('84.05')),\n",
       " ('Travel Bag', 50, Decimal('121.05')),\n",
       " ('Keyboard', 50, Decimal('110.55')),\n",
       " ('Monitor', 50, Decimal('1064.45')),\n",
       " ('Watch', 50, Decimal('522.05')),\n",
       " ('Water Bottle', 50, Decimal('125.00'))]"
      ]
     },
     "metadata": {},
     "execution_count": 50
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Accessing Built-in Functions 访问内置函数\r\n",
    "\r\n",
    "To access the built-in functions provided by the database we use func object. The following listing shows how to use date/time, mathematical and string functions found in PostgreSQL database.\r\n",
    "\r\n",
    "要访问数据库提供的内置函数，我们使用 func 对象。下面的清单展示了如何使用 PostgreSQL 数据库中的日期/时间、数学和字符串函数。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "source": [
    "from sqlalchemy.sql import func\r\n",
    "\r\n",
    "c = [\r\n",
    "    \r\n",
    "    ##  date/time functions  ##\r\n",
    "    \r\n",
    "    func.timeofday(),\r\n",
    "    func.localtime(),\r\n",
    "    func.current_timestamp(),    \r\n",
    "    func.date_part(\"month\", func.now()),        \r\n",
    "    func.now(),\r\n",
    "    \r\n",
    "    ##  mathematical functions  ##\r\n",
    "    \r\n",
    "    func.pow(4,2),\r\n",
    "    func.sqrt(441),\r\n",
    "    func.pi(),        \r\n",
    "    func.floor(func.pi()),\r\n",
    "    func.ceil(func.pi()),\r\n",
    "    \r\n",
    "    ##  string functions  ##\r\n",
    "    \r\n",
    "    func.lower(\"ABC\"),\r\n",
    "    func.upper(\"abc\"),\r\n",
    "    func.length(\"abc\"),\r\n",
    "    func.trim(\"  ab c  \"),    \r\n",
    "    func.chr(65),        \r\n",
    "]\r\n",
    "\r\n",
    "s = select(c)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['timeofday_1',\n",
       " 'localtime_1',\n",
       " 'current_timestamp_1',\n",
       " 'date_part_1',\n",
       " 'now_1',\n",
       " 'pow_1',\n",
       " 'sqrt_1',\n",
       " 'pi_1',\n",
       " 'floor_1',\n",
       " 'ceil_1',\n",
       " 'lower_1',\n",
       " 'upper_1',\n",
       " 'length_1',\n",
       " 'trim_1',\n",
       " 'chr_1']"
      ]
     },
     "metadata": {},
     "execution_count": 51
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Wed Aug 01 17:58:55.627110 2018 IST', datetime.time(17, 27, 1, 784686), datetime.datetime(2018, 8, 1, 17, 27, 1, 784686, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), 8.0, datetime.datetime(2018, 8, 1, 17, 27, 1, 784686, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), 16.0, 21.0, 3.14159265358979, 3.0, 4.0, 'abc', 'ABC', 3, 'ab c', 'A')]"
      ]
     },
     "metadata": {},
     "execution_count": 51
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "You also have access to aggregate functions via the func object.\r\n",
    "\r\n",
    "您还可以通过 func 对象访问聚合函数。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "source": [
    "from sqlalchemy.sql import func\r\n",
    "\r\n",
    "c = [ \r\n",
    "    func.sum(items.c.quantity),\r\n",
    "    func.avg(items.c.quantity),    \r\n",
    "    func.max(items.c.quantity),\r\n",
    "    func.min(items.c.quantity),\r\n",
    "    func.count(customers.c.id),    \r\n",
    "]\r\n",
    "\r\n",
    "s = select(c)\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT sum(items.quantity) AS sum_1, avg(items.quantity) AS avg_1, max(items.quantity) AS max_1, min(items.quantity) AS min_1, count(customers.id) AS count_1 \n",
      "FROM items, customers\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['sum_1', 'avg_1', 'max_1', 'min_1', 'count_1']"
      ]
     },
     "metadata": {},
     "execution_count": 52
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(2191, Decimal('39.1250000000000000'), 50, 3, 56)]"
      ]
     },
     "metadata": {},
     "execution_count": 52
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Grouping Results 分组结果\r\n",
    "\r\n",
    "Grouping results is done via GROUP BY clause. It is commonly used in conjunction with the aggregate functions. We add GROUP BY clause to the select statement using group_by() method. It accepts one or more columns and groups the rows according to the values in the column. For example:\r\n",
    "\r\n",
    "通过 groupby 子句对结果进行分组。它通常与聚合函数一起使用。我们使用 group_by ()方法将 groupby 子句添加到 select 语句中。它接受一个或多个列，并根据列中的值对行进行分组。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "source": [
    "from sqlalchemy.sql import func\r\n",
    "\r\n",
    "c = [ \r\n",
    "    func.count(\"*\").label('count'),         \r\n",
    "    customers.c.town      \r\n",
    "]\r\n",
    "\r\n",
    "s = select(c).group_by(customers.c.town)\r\n",
    "\r\n",
    "print(s)\r\n",
    "conn.execute(s).fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT count(:count_1) AS count, customers.town \n",
      "FROM customers GROUP BY customers.town\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'Langdale'), (3, 'Peterbrugh'), (3, 'Norfolk')]"
      ]
     },
     "metadata": {},
     "execution_count": 53
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "This query returns the number of customers lives in each town.\r\n",
    "\r\n",
    "此查询返回每个城镇中的客户数量。\r\n",
    "\r\n",
    "To filter out the results based on the values returned by aggregate functions we use having() method which adds the HAVING clause to the SELECT statement. Just like the where() clause, it accepts a condition.\r\n",
    "\r\n",
    "为了根据聚合函数返回的值过滤掉结果，我们使用 HAVING ()方法，该方法将 HAVING 子句添加到 SELECT 语句中。就像 where ()子句一样，它接受一个条件。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "source": [
    "from sqlalchemy.sql import func\r\n",
    "\r\n",
    "c = [\r\n",
    "    func.count(\"*\").label('count'),\r\n",
    "    customers.c.town      \r\n",
    "]\r\n",
    "\r\n",
    "s = select(c).group_by(customers.c.town).having(func.count(\"*\") > 2)\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT count(:count_1) AS count, customers.town \n",
      "FROM customers GROUP BY customers.town \n",
      "HAVING count(:count_2) > :count_3\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['count', 'town']"
      ]
     },
     "metadata": {},
     "execution_count": 54
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(3, 'Peterbrugh'), (3, 'Norfolk')]"
      ]
     },
     "metadata": {},
     "execution_count": 54
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "source": [
    "# 同上 \r\n",
    "from sqlalchemy.sql import func\r\n",
    "\r\n",
    "s = select([customers.c.town,func.count(\"*\").label('count')]).group_by(customers.c.town).having(func.count(\"*\")> 2)\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.town, count(:count_1) AS count \n",
      "FROM customers GROUP BY customers.town \n",
      "HAVING count(:count_2) > :count_3\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['town', 'count']"
      ]
     },
     "metadata": {},
     "execution_count": 63
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Norfolk', 3), ('Peterbrugh', 3)]"
      ]
     },
     "metadata": {},
     "execution_count": 63
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Joins 连接\r\n",
    "\r\n",
    "The Table instance provides the following two methods to create joins:\r\n",
    "\r\n",
    "Table 实例提供了以下两个创建连接的方法:\r\n",
    "\r\n",
    "join() - creates inner join - 创建内部连接\r\n",
    "outerjoin() - creates outer join ( - 创建外部连接(LEFT OUTER JOIN to be specific) 详细说明)\r\n",
    "\r\n",
    "The inner join returns only the rows which matches the join condition, whereas the outer join returns the rows which matches the join condition as well as some additional rows.\r\n",
    "\r\n",
    "内部联接只返回与联接条件匹配的行，而外部联接返回与联接条件匹配的行以及一些附加行。\r\n",
    "\r\n",
    "Both methods accept a Table instance, figures out the join condition based on the foreign key relationship and returns a JOIN construct.\r\n",
    "\r\n",
    "这两个方法都接受 Table 实例，根据外键关系确定连接条件，并返回一个 JOIN 构造。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "source": [
    "print(customers.join(orders))"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "customers JOIN orders ON customers.id = orders.customer_id\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "If the methods can't figure out the join condition correctly or you want to specify an alternate condition, you can do so by passing the join condition manually as a second argument.\r\n",
    "\r\n",
    "如果方法不能正确地计算出连接条件，或者您希望指定替代条件，则可以通过手动将连接条件作为第二个参数传递来实现。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "source": [
    "print(customers.join(items,\r\n",
    "                 customers.c.address.like(customers.c.first_name + '%')\r\n",
    "             )\r\n",
    ")"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "customers JOIN items ON customers.address LIKE customers.first_name || :first_name_1\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "When we specify tables or list of columns in the select() function, SQLAlchemy automatically places those tables in the FROM clause. However, when we use join, we know exactly the tables we want in the FROM clause, so we use the select_from() method. However, if we want we can use select_from() in queries not involving joins too. For example:\r\n",
    "\r\n",
    "当我们在 select ()函数中指定表或列列表时，SQLAlchemy 会自动将这些表放到 FROM 子句中。但是，当我们使用 join 时，我们确切地知道 FROM 子句中需要的表，因此我们使用 select _ FROM ()方法。\r\n",
    "但是，如果需要，我们也可以在不涉及连接的查询中使用 select_from ()。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "source": [
    "s = select([        \r\n",
    "    customers.c.id,\r\n",
    "    customers.c.first_name\r\n",
    "]).select_from(\r\n",
    "    customers\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.id, customers.first_name \n",
      "FROM customers\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['id', 'first_name']"
      ]
     },
     "metadata": {},
     "execution_count": 57
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'John'),\n",
       " (2, 'Katherine'),\n",
       " (3, 'Tim'),\n",
       " (4, 'John'),\n",
       " (5, 'Sarah'),\n",
       " (6, 'Pablo'),\n",
       " (7, 'Pablo')]"
      ]
     },
     "metadata": {},
     "execution_count": 57
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Let's use this knowledge to find all the orders placed by customer name John Green.\r\n",
    "\r\n",
    "让我们利用这些知识来查找客户名为 John Green 的所有订单。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "source": [
    "s = select([\r\n",
    "            orders.c.id,\r\n",
    "            orders.c.date_placed\r\n",
    "]).select_from(\r\n",
    "    orders.join(customers)\r\n",
    ").where(\r\n",
    "    and_(\r\n",
    "        customers.c.first_name == \"John\",\r\n",
    "        customers.c.last_name == \"Green\",\r\n",
    "    )\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT orders.id, orders.date_placed \n",
      "FROM orders JOIN customers ON customers.id = orders.customer_id \n",
      "WHERE customers.first_name = :first_name_1 AND customers.last_name = :last_name_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['id', 'date_placed']"
      ]
     },
     "metadata": {},
     "execution_count": 58
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277)),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299))]"
      ]
     },
     "metadata": {},
     "execution_count": 58
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The preceding query only returns the order id and date_placed. Wouldn't it be better to know the items and quantity of items in each order?\r\n",
    "\r\n",
    "前面的查询只返回订单 id 和 date _ placed。知道每个订单的项目和数量不是更好吗？\r\n",
    "\r\n",
    "To get the items and quantity of items in each order we have to create a total of 3 joins, spanning all the way to the items table.\r\n",
    "\r\n",
    "为了获得每个订单中的项目和项目数量，我们必须创建总共3个连接，从项目表一直到项目表。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "source": [
    "s = select([        \r\n",
    "    orders.c.id.label('order_id'),\r\n",
    "    orders.c.date_placed,\r\n",
    "    order_lines.c.quantity,\r\n",
    "    items.c.name,\r\n",
    "            \r\n",
    "]).select_from(\r\n",
    "    orders.join(customers).join(order_lines).join(items)\r\n",
    ").where(\r\n",
    "    and_(\r\n",
    "        customers.c.first_name == \"John\",\r\n",
    "        customers.c.last_name == \"Green\",\r\n",
    "    )\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT orders.id AS order_id, orders.date_placed, order_lines.quantity, items.name \n",
      "FROM orders JOIN customers ON customers.id = orders.customer_id JOIN order_lines ON orders.id = order_lines.order_id JOIN items ON items.id = order_lines.item_id \n",
      "WHERE customers.first_name = :first_name_1 AND customers.last_name = :last_name_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['order_id', 'date_placed', 'quantity', 'name']"
      ]
     },
     "metadata": {},
     "execution_count": 59
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 5, 'Chair'),\n",
       " (1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 2, 'Pen'),\n",
       " (1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 1, 'Headphone'),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), 5, 'Chair'),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), 4, 'Pen')]"
      ]
     },
     "metadata": {},
     "execution_count": 59
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Finally, here is an example of how to define an outer join.\r\n",
    "\r\n",
    "最后，这里是一个如何定义外部联接的示例。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "source": [
    "s = select([        \r\n",
    "    customers.c.first_name,\r\n",
    "    orders.c.id,\r\n",
    "]).select_from(\r\n",
    "    customers.outerjoin(orders)\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.first_name, orders.id \n",
      "FROM customers LEFT OUTER JOIN orders ON customers.id = orders.customer_id\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['first_name', 'id']"
      ]
     },
     "metadata": {},
     "execution_count": 60
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('John', 1),\n",
       " ('John', 2),\n",
       " ('Katherine', None),\n",
       " ('Sarah', None),\n",
       " ('Pablo', None),\n",
       " ('John', None),\n",
       " ('Tim', None),\n",
       " ('Pablo', None)]"
      ]
     },
     "metadata": {},
     "execution_count": 60
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "The Table instance we pass to the outerjoin() method is placed on the right side of the outer join. As a result, the above query will return all the rows from customers table (the left table), and only the rows that meets the join condition are returned from the orders table (the right table).\r\n",
    "\r\n",
    "我们传递给 outerjoin ()方法的 Table 实例位于外部联接的右侧。因此，上面的查询将返回 customers 表(左表)中的所有行，并且只有满足连接条件的行从 orders 表(右表)返回。\r\n",
    "\r\n",
    "If you want all the rows from the order table but only the rows that meets the join condition from the orders table, call outerjoin() as follows:\r\n",
    "\r\n",
    "如果需要订单表中的所有行，但只需要订单表中满足连接条件的行，请按如下方式调用 outerjoin () :\r\n",
    "\r\n",
    "注: 相当于右连接"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "source": [
    "s = select([        \r\n",
    "    customers.c.first_name,\r\n",
    "    orders.c.id,\r\n",
    "]).select_from(\r\n",
    "    orders.outerjoin(customers)\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.first_name, orders.id \n",
      "FROM orders LEFT OUTER JOIN customers ON customers.id = orders.customer_id\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['first_name', 'id']"
      ]
     },
     "metadata": {},
     "execution_count": 61
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('John', 1), ('John', 2)]"
      ]
     },
     "metadata": {},
     "execution_count": 61
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "You can also create a FULL OUTER JOIN by passing full=True to outerjoin() method. For example:\r\n",
    "\r\n",
    "还可以通过传递 FULL = True to outerjoin ()方法创建 FULL OUTER JOIN:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "source": [
    "s = select([        \r\n",
    "    customers.c.first_name,\r\n",
    "    orders.c.id,\r\n",
    "]).select_from(\r\n",
    "    orders.outerjoin(customers, full=True)\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.first_name, orders.id \n",
      "FROM orders FULL OUTER JOIN customers ON customers.id = orders.customer_id\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['first_name', 'id']"
      ]
     },
     "metadata": {},
     "execution_count": 62
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('John', 1),\n",
       " ('John', 2),\n",
       " ('Katherine', None),\n",
       " ('Sarah', None),\n",
       " ('Pablo', None),\n",
       " ('John', None),\n",
       " ('Tim', None),\n",
       " ('Pablo', None)]"
      ]
     },
     "metadata": {},
     "execution_count": 62
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Updating Records 更新记录\r\n",
    "\r\n",
    "Updating records is achieved using the update() function. For example, the following query updates the selling_price and quantity of Water Bottle to 30 and 60, respectively.\r\n",
    "\r\n",
    "更新记录是通过 update ()函数实现的。例如，下面的查询将水瓶的销售价格和数量分别更新为30和60。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "source": [
    "from sqlalchemy import update\r\n",
    "\r\n",
    "s = update(items).where(\r\n",
    "    items.c.name == 'Water Bottle'\r\n",
    ").values(\r\n",
    "    selling_price = 30,\r\n",
    "    quantity = 60,\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.rowcount  # count of rows updated"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "UPDATE items SET selling_price=:selling_price, quantity=:quantity WHERE items.name = :name_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "metadata": {},
     "execution_count": 63
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Deleting Records 删除记录\r\n",
    "To delete records we use the delete() function.\r\n",
    "\r\n",
    "要删除记录，我们使用 delete ()函数。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "source": [
    "from sqlalchemy import delete\r\n",
    "\r\n",
    "s = delete(customers).where(\r\n",
    "    customers.c.username.like('pablo%')\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.rowcount"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "DELETE FROM customers WHERE customers.username LIKE :username_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "metadata": {},
     "execution_count": 64
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Dealing with Duplicates 处理重复行\r\n",
    "To deal with the duplicate rows in the result set we use the DISTINCT option. We can add DISTINCT option to the SELECT statement using the distinct() method. For example:\r\n",
    "\r\n",
    "为了处理结果集中的重复行，我们使用 DISTINCT 选项。我们可以使用 DISTINCT ()方法向 SELECT 语句添加 DISTINCT 选项。例如:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "source": [
    "# without DISTINCT\r\n",
    "\r\n",
    "s = select([customers.c.town]).where(customers.c.id  < 10)\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.fetchall()\r\n",
    "\r\n",
    "\r\n",
    "# with DISTINCT\r\n",
    "\r\n",
    "s = select([customers.c.town]).where(customers.c.id  < 10).distinct()\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT customers.town \n",
      "FROM customers \n",
      "WHERE customers.id < :id_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Norfolk',),\n",
       " ('Peterbrugh',),\n",
       " ('Langdale',),\n",
       " ('Norfolk',),\n",
       " ('Norfolk',),\n",
       " ('Peterbrugh',),\n",
       " ('Peterbrugh',)]"
      ]
     },
     "metadata": {},
     "execution_count": 66
    },
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT DISTINCT customers.town \n",
      "FROM customers \n",
      "WHERE customers.id < :id_1\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[('Norfolk',), ('Peterbrugh',), ('Langdale',)]"
      ]
     },
     "metadata": {},
     "execution_count": 66
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Here is another example which uses distinct() option with the count() aggregate function and counts the distinct number of towns in the customers table.\r\n",
    "\r\n",
    "下面是另一个示例，它使用 distinct ()选项和 count ()聚合函数，并计算 customers 表中的不同城镇数量。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "source": [
    "from sqlalchemy import distinct\r\n",
    "\r\n",
    "s = select([        \r\n",
    "    func.count(distinct(customers.c.town)),\r\n",
    "    func.count(customers.c.town)\r\n",
    "])\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT count(DISTINCT customers.town) AS count_1, count(customers.town) AS count_2 \n",
      "FROM customers\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['count_1', 'count_2']"
      ]
     },
     "metadata": {},
     "execution_count": 67
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(3, 7)]"
      ]
     },
     "metadata": {},
     "execution_count": 67
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Casting 类型申明\r\n",
    "\r\n",
    "Casting (converting) data from one type to another is a common operation and is done via cast() function from the sqlalchemy package.\r\n",
    "\r\n",
    "将数据从一种类型转换为另一种类型是一种常见的操作，通过 sqlalchemy 包中的 cast ()函数完成。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "source": [
    "from sqlalchemy import cast, Date\r\n",
    "\r\n",
    "s = select([\r\n",
    "    cast(func.pi(), Integer),\r\n",
    "    cast(func.pi(), Numeric(10,2)),\r\n",
    "    cast(\"2010-12-01\", DateTime),\r\n",
    "    cast(\"2010-12-01\", Date),\r\n",
    "])    \r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT CAST(pi() AS INTEGER) AS anon_1, CAST(pi() AS NUMERIC(10, 2)) AS anon_2, CAST(:param_1 AS DATETIME) AS anon_3, CAST(:param_2 AS DATE) AS anon_4\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['anon_1', 'anon_2', 'anon_3', 'anon_4']"
      ]
     },
     "metadata": {},
     "execution_count": 69
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(3, Decimal('3.14'), datetime.datetime(2010, 12, 1, 0, 0), datetime.date(2010, 12, 1))]"
      ]
     },
     "metadata": {},
     "execution_count": 69
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Unions 联合\r\n",
    "\r\n",
    "The SQL's UNION operator allows us to combine result set of multiple SELECT statements. To add UNION operator to our SELECT statement we use union() function.\r\n",
    "\r\n",
    "SQL 的 UNION 操作符允许我们组合多个 SELECT 语句的结果集。要将 UNION 运算符添加到 SELECT 语句中，我们使用 UNION ()函数。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "source": [
    "from sqlalchemy import union\r\n",
    "\r\n",
    "s = union(\r\n",
    "    select([items.c.id, items.c.name]).where(items.c.name.like(\"Wa%\")),\r\n",
    "    select([items.c.id, items.c.name]).where(items.c.name.like(\"%e%\")),\r\n",
    ").order_by(desc(\"id\"))\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_1 UNION SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_2 ORDER BY id DESC\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['id', 'name']"
      ]
     },
     "metadata": {},
     "execution_count": 70
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(8, 'Water Bottle'),\n",
       " (7, 'Watch'),\n",
       " (5, 'Keyboard'),\n",
       " (4, 'Travel Bag'),\n",
       " (3, 'Headphone'),\n",
       " (2, 'Pen')]"
      ]
     },
     "metadata": {},
     "execution_count": 70
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "By default, union() removes all the duplicate rows from the result set. If you want to keep the duplicates use union_all().\r\n",
    "\r\n",
    "默认情况下，union ()从结果集中删除所有重复的行。如果要保留副本，请使用 union _ all ()。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "source": [
    "from sqlalchemy import union_all\r\n",
    "\r\n",
    "s = union_all(\r\n",
    "    select([items.c.id, items.c.name]).where(items.c.name.like(\"Wa%\")),\r\n",
    "    select([items.c.id, items.c.name]).where(items.c.name.like(\"%e%\")),\r\n",
    ").order_by(desc(\"id\"))\r\n",
    "\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_1 UNION ALL SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.name LIKE :name_2 ORDER BY id DESC\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['id', 'name']"
      ]
     },
     "metadata": {},
     "execution_count": 71
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(8, 'Water Bottle'),\n",
       " (8, 'Water Bottle'),\n",
       " (7, 'Watch'),\n",
       " (5, 'Keyboard'),\n",
       " (4, 'Travel Bag'),\n",
       " (3, 'Headphone'),\n",
       " (2, 'Pen')]"
      ]
     },
     "metadata": {},
     "execution_count": 71
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Creating Subqueries 创建子查询\r\n",
    "\r\n",
    "We can also access data from multiple tables using subqueries.\r\n",
    "\r\n",
    "我们还可以使用子查询来访问多个表中的数据。\r\n",
    "\r\n",
    "The following query returns the id and name of the items ordered by John Green in his first order:\r\n",
    "\r\n",
    "下面的查询返回 John Green 第一次订购的项的 id 和名称:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "source": [
    "s = select([items.c.id, items.c.name]).where(\r\n",
    "    items.c.id.in_( \r\n",
    "        select([order_lines.c.item_id]).select_from(customers.join(orders).join(order_lines)).where(    \r\n",
    "                and_(\r\n",
    "                    customers.c.first_name == 'John',\r\n",
    "                    customers.c.last_name == 'Green',\r\n",
    "                    orders.c.id == 1\r\n",
    "                )    \r\n",
    "        )\r\n",
    "    )\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name \n",
      "FROM items \n",
      "WHERE items.id IN (SELECT order_lines.item_id \n",
      "FROM customers JOIN orders ON customers.id = orders.customer_id JOIN order_lines ON orders.id = order_lines.order_id \n",
      "WHERE customers.first_name = :first_name_1 AND customers.last_name = :last_name_1 AND orders.id = :id_1)\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['id', 'name']"
      ]
     },
     "metadata": {},
     "execution_count": 72
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(3, 'Headphone'), (1, 'Chair'), (2, 'Pen')]"
      ]
     },
     "metadata": {},
     "execution_count": 72
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "This query can also be written using JOINs as follows:\r\n",
    "\r\n",
    "这个查询也可以用 join 编写，如下所示:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "source": [
    "s = select([items.c.id, items.c.name]).select_from(customers.join(orders).join(order_lines).join(items)).where(    \r\n",
    "        and_(\r\n",
    "            customers.c.first_name == 'John',\r\n",
    "            customers.c.last_name == 'Green',\r\n",
    "            orders.c.id == 1\r\n",
    "        )    \r\n",
    ")\r\n",
    "\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.keys()\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['id', 'name']"
      ]
     },
     "metadata": {},
     "execution_count": 73
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 'Chair'), (2, 'Pen'), (3, 'Headphone')]"
      ]
     },
     "metadata": {},
     "execution_count": 73
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Raw Queries  原始查询\r\n",
    "\r\n",
    "SQLAlchemy also gives you the flexibility to execute raw SQL using the text() function. For example, the following SELECT statement returns all the orders, along with the items ordered by John Green.\r\n",
    "\r\n",
    "SQLAlchemy 还提供了使用 text ()函数执行原始 SQL 的灵活性。例如，下面的 SELECT 语句返回所有订单以及 John Green 订购的项目。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "source": [
    "from sqlalchemy.sql import text\r\n",
    "\r\n",
    "s = text(\r\n",
    "\"\"\"\r\n",
    "SELECT\r\n",
    "    orders.id as \"Order ID\", orders.date_placed, items.id, items.name\r\n",
    "FROM\r\n",
    "    customers\r\n",
    "INNER JOIN orders ON customers.id = orders.customer_id\r\n",
    "INNER JOIN order_lines ON order_lines.order_id = orders.id\r\n",
    "INNER JOIN items ON items.id= order_lines.item_id\r\n",
    "where customers.first_name = :first_name and customers.last_name = :last_name\r\n",
    "\"\"\"\r\n",
    ")\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s, first_name=\"John\", last_name='Green')\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "\n",
      "SELECT\n",
      "    orders.id as \"Order ID\", orders.date_placed, items.id, items.name\n",
      "FROM\n",
      "    customers\n",
      "INNER JOIN orders ON customers.id = orders.customer_id\n",
      "INNER JOIN order_lines ON order_lines.order_id = orders.id\n",
      "INNER JOIN items ON items.id= order_lines.item_id\n",
      "where customers.first_name = :first_name and customers.last_name = :last_name\n",
      "\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 1, 'Chair'),\n",
       " (1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 2, 'Pen'),\n",
       " (1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), 3, 'Headphone'),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), 1, 'Chair'),\n",
       " (2, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), 2, 'Pen')]"
      ]
     },
     "metadata": {},
     "execution_count": 74
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Notice that the SELECT statement contains two bind parameters: first_name and last_name. The values to these parameters is passed via the execute() method.\r\n",
    "\r\n",
    "注意，SELECT 语句包含两个绑定参数: first _ name 和 last _ name。这些参数的值通过 execute ()方法传递。\r\n",
    "\r\n",
    "The text() function can also be embedded inside a select() construct. For example:\r\n",
    "\r\n",
    "Text ()函数也可以嵌入到 select ()构造中:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "source": [
    "s = select([items]).where(\r\n",
    "    text(\"items.name like 'Wa%'\")\r\n",
    ").order_by(text(\"items.id desc\"))\r\n",
    "\r\n",
    "print(s)\r\n",
    "rs = conn.execute(s)\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \n",
      "FROM items \n",
      "WHERE items.name like 'Wa%' ORDER BY items.id desc\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(8, 'Water Bottle', Decimal('20.89'), Decimal('30.00'), 60),\n",
       " (7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50)]"
      ]
     },
     "metadata": {},
     "execution_count": 75
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Another way to execute raw SQL is to pass it directly to the execute() method. For example:\r\n",
    "\r\n",
    "执行原始 SQL 的另一种方法是直接将其传递给 execute ()方法:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "source": [
    "rs = conn.execute(\"select * from  orders;\")\r\n",
    "rs.fetchall()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "[(1, 1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104277), None),\n",
       " (2, 1, datetime.datetime(2018, 8, 1, 17, 23, 32, 104299), None)]"
      ]
     },
     "metadata": {},
     "execution_count": 76
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Transactions 事务\r\n",
    "\r\n",
    "A transaction is a way to execute a set of SQL statements such that either all of the statements are executed successfully or nothing at all. If any of the statement involved in the transaction fails then the database is returned to the state it was in before the transaction was initiated.\r\n",
    "\r\n",
    "事务是一种执行一组 SQL 语句的方法，这样可以成功地执行所有语句，也可以什么都不执行。如果事务中涉及的任何语句失败，那么数据库将返回到事务启动之前的状态。\r\n",
    "\r\n",
    "We currently have two orders in the database. To fulfill an order we need to perform following two actions:\r\n",
    "\r\n",
    "我们目前在数据库中有两个订单。为了完成一个订单，我们需要执行以下两个操作:\r\n",
    "\r\n",
    "Subtract the quantity of ordered items from the 方法中减去已订购项的数量items table 表\r\n",
    "Update the 更新date_shipped column to contain the datetime value. 列来包含日期时间值\r\n",
    "Both of these action must be performed as a unit to ensure that the data in the tables are correct.\r\n",
    "\r\n",
    "这两个操作必须作为一个单元来执行，以确保表中的数据是正确的。\r\n",
    "\r\n",
    "The Connection object provides a begin() method, which starts the transaction and returns an object of type Transaction. The Transaction object in turn provides rollback() and commit() method, to rollback and commit the transaction, respectively.\r\n",
    "\r\n",
    "Connection 对象提供了一个 begin ()方法，该方法启动事务并返回 Transaction 类型的对象。Transaction 对象依次提供 rollback ()和 commit ()方法，分别用于回滚和提交事务。\r\n",
    "\r\n",
    "In the following listing we define dispatch_order() method which accepts order_id as argument, and performs the above mentioned actions using transaction.\r\n",
    "\r\n",
    "在下面的清单中，我们定义 dispatch _ order ()方法，该方法接受 order _ id 作为参数，并使用 transaction 执行上面提到的操作。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "source": [
    "from sqlalchemy.exc import IntegrityError\r\n",
    "\r\n",
    "\r\n",
    "def dispatch_order(order_id):\r\n",
    "\r\n",
    "    # check whether order_id is valid or not\r\n",
    "    r = conn.execute(select([func.count(\"*\")]).where(orders.c.id == order_id))\r\n",
    "    if not r.scalar():\r\n",
    "        raise ValueError(\"Invalid order id: {}\".format(order_id))\r\n",
    "\r\n",
    "    # fetch items in the order\r\n",
    "    s = select([order_lines.c.item_id, order_lines.c.quantity]).where(\r\n",
    "        order_lines.c.order_id == order_id\r\n",
    "    )\r\n",
    "\r\n",
    "    rs = conn.execute(s)\r\n",
    "    ordered_items_list = rs.fetchall()\r\n",
    "\r\n",
    "    # start transaction\r\n",
    "    t = conn.begin()\r\n",
    "\r\n",
    "    try:\r\n",
    "        for i in ordered_items_list:\r\n",
    "            u = update(items).where(\r\n",
    "                items.c.id == i.item_id\r\n",
    "            ).values(quantity = items.c.quantity - i.quantity)\r\n",
    "\r\n",
    "            rs = conn.execute(u)\r\n",
    "\r\n",
    "        u = update(orders).where(orders.c.id == order_id).values(date_shipped=datetime.now())\r\n",
    "        rs = conn.execute(u)\r\n",
    "        t.commit()\r\n",
    "        print(\"Transaction completed.\")\r\n",
    "\r\n",
    "    except IntegrityError as e:\r\n",
    "        print(e)\r\n",
    "        t.rollback()\r\n",
    "        print(\"Transaction failed.\")"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "Our first order is for 5 chairs, 2 pens and 1 headphone. Calling dispatch_order() function with order id of 1, will return the following output:\r\n",
    "\r\n",
    "我们的第一个订单是5把椅子，2支钢笔和1个耳机。调用 dispatch _ order ()函数，其顺序 id 为1，将返回以下输出:"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "source": [
    "dispatch_order(1)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Transaction completed.\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "At this point, items and order_lines tables should look like this:\r\n",
    "\r\n",
    "现在，items 和 order _ lines 表看起来应该是这样的:\r\n",
    "\r\n",
    "\r\n",
    "\r\n",
    "\r\n",
    "\r\n",
    "Our next order is for 5 chairs and 4 pens, but we now only have 5 chairs and 1 pen in the stock!\r\n",
    "\r\n",
    "我们的下一个订单是5把椅子和4支钢笔，但我们现在只有5把椅子和1支钢笔存货！\r\n",
    "\r\n",
    "Let's try running dispatch_order() for second order and see what happens.\r\n",
    "\r\n",
    "让我们尝试为二阶运行 dispatch _ order () ，看看会发生什么。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "source": [
    "dispatch_order(2)"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "(psycopg2.IntegrityError) new row for relation \"items\" violates check constraint \"quantity_check\"\n",
      "DETAIL:  Failing row contains (1, Chair, 9.21, 10.81, 0).\n",
      " [SQL: 'UPDATE items SET quantity=(items.quantity - %(quantity_1)s) WHERE items.id = %(id_1)s'] [parameters: {'quantity_1': 5, 'id_1': 1}] (Background on this error at: http://sqlalche.me/e/gkpj)\n",
      "Transaction failed.\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "As expected, our shipment failed because we don't have enough pens in the stock and because we are using transaction our database is returned to the state it was in before the transaction was started.\r\n",
    "\r\n",
    "正如预期的那样，我们的发货失败了，因为我们没有足够的钢笔在库存，因为我们正在使用事务，我们的数据库返回到它在事务开始之前的状态。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "source": [
    "# metadata.drop_all(engine)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "[粗翻]  2021-08-24 Fully jupyter notebook and Chinese Verion CRAFTED BY YULK "
   ],
   "metadata": {}
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.6 64-bit"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "interpreter": {
   "hash": "c644d696b95f5e0f4df3c6556741cf30bcf9ea6ca93c3e1f29fcf31d885534fc"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}

一键复制编辑原始数据按行查看历史

提交于 2021-08-31 08:40 . version 0.72

马建仓 AI 助手

尝试更多

代码解读

代码找茬

代码优化

dreamrise/OverIQ 的 SQLAlchemy 中文教程

简介

发行版

贡献者

近期动态