SqlAlchemy 2.0 中文文档（五十）（2）

>>> stmt = insert(my_table).values(
...     id='some_id',
...     data='inserted value',
...     author='jlh'
... )
>>> on_update_stmt = stmt.on_conflict_do_update(
...     index_elements=['id'],
...     set_=dict(data='updated value', author=stmt.excluded.author),
...     where=(my_table.c.status == 2)
... )
>>> print(on_update_stmt)
INSERT  INTO  my_table  (id,  data,  author)  VALUES  (?,  ?,  ?)
ON  CONFLICT  (id)  DO  UPDATE  SET  data  =  ?,  author  =  excluded.author
WHERE  my_table.status  =  ? 

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> stmt = stmt.on_conflict_do_nothing(index_elements=['id'])
>>> print(stmt)
INSERT  INTO  my_table  (id,  data)  VALUES  (?,  ?)  ON  CONFLICT  (id)  DO  NOTHING 

>>> stmt = insert(my_table).values(id='some_id', data='inserted value')
>>> stmt = stmt.on_conflict_do_nothing()
>>> print(stmt)
INSERT  INTO  my_table  (id,  data)  VALUES  (?,  ?)  ON  CONFLICT  DO  NOTHING 
```## 类型反射
SQLite 类型与大多数其他数据库后端的类型不同，因为类型的字符串名称通常不是一对一对应的“类型”。相反，SQLite 将每列的类型行为链接到五种所谓的“类型亲和性”之一，基于类型的字符串匹配模式。
SQLAlchemy 的反射过程，在检查类型时，使用一个简单的查找表将返回的关键字链接到提供的 SQLAlchemy 类型。这个查找表存在于 SQLite 方言中，就像所有其他方言一样。然而，当特定类型名称未在查找映射中找到时，SQLite 方言有一个不同的“回退”程序；它实际上实现了位于 [`www.sqlite.org/datatype3.html`](https://www.sqlite.org/datatype3.html) 第 2.1 节的 SQLite “类型亲和性”方案。
提供的类型映射将直接从以下类型的精确字符串名称匹配中进行关联：
如果类型名称包含字符串`BLOB`，则返回`BIGINT`、`BLOB`、`BOOLEAN`、`BOOLEAN`、`CHAR`、`DATE`、`DATETIME`、`FLOAT`、`DECIMAL`、`FLOAT`、`INTEGER`、`INTEGER`、`NUMERIC`、`REAL`、`SMALLINT`、`TEXT`、`TIME`、`TIMESTAMP`、`VARCHAR`、`NVARCHAR`、`NCHAR`
当类型名称不匹配上述类型之一时，将使用“类型亲和性”查找代替：
+   如果类型名称包含字符串`INT`，则返回`INTEGER`
+   如果类型名称包含字符串`CHAR`、`CLOB`或`TEXT`，则返回`TEXT`
+   如果类型名称包含字符串`BLOB`，则返回`NullType`
+   如果类型名称包含字符串`REAL`、`FLOA`或`DOUB`，则返回`REAL`
+   否则，将使用`NUMERIC`类型。## 部分索引
可以使用 DDL 系统使用参数`sqlite_where`来指定部分索引，例如使用 WHERE 子句的索引：
```py
tbl = Table('testtbl', m, Column('data', Integer))
idx = Index('test_idx1', tbl.c.data,
            sqlite_where=and_(tbl.c.data > 5, tbl.c.data < 10))

CREATE INDEX test_idx1 ON testtbl (data)
WHERE data > 5 AND data < 10
```## 点列名
使用明确包含句点的表格或列名**不推荐**。虽然这通常对关系数据库来说是个坏主意，因为句点是一个语法上重要的字符，但直到 SQLite 版本**3.10.0**之前的 SQLite 驱动程序存在一个 bug，需要 SQLAlchemy 在结果集中过滤掉这些句点。
这个 bug 完全不是 SQLAlchemy 的问题，可以这样说明：
```py
import sqlite3
assert sqlite3.sqlite_version_info < (3, 10, 0), "bug is fixed in this version"
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("create table x (a integer, b integer)")
cursor.execute("insert into x (a, b) values (1, 1)")
cursor.execute("insert into x (a, b) values (2, 2)")
cursor.execute("select x.a, x.b from x")
assert [c[0] for c in cursor.description] == ['a', 'b']
cursor.execute('''
 select x.a, x.b from x where a=1
 union
 select x.a, x.b from x where a=2
''')
assert [c[0] for c in cursor.description] == ['a', 'b'], \
    [c[0] for c in cursor.description]

Traceback (most recent call last):
  File "test.py", line 19, in <module>
    [c[0] for c in cursor.description]
AssertionError: ['x.a', 'x.b']

from sqlalchemy import create_engine
eng = create_engine("sqlite://")
conn = eng.connect()
conn.exec_driver_sql("create table x (a integer, b integer)")
conn.exec_driver_sql("insert into x (a, b) values (1, 1)")
conn.exec_driver_sql("insert into x (a, b) values (2, 2)")
result = conn.exec_driver_sql("select x.a, x.b from x")
assert result.keys() == ["a", "b"]
result = conn.exec_driver_sql('''
 select x.a, x.b from x where a=1
 union
 select x.a, x.b from x where a=2
''')
assert result.keys() == ["a", "b"]

>>> row = result.first()
>>> row["a"]
1
>>> row["x.a"]
1
>>> row["b"]
1
>>> row["x.b"]
1

result = conn.execution_options(sqlite_raw_colnames=True).exec_driver_sql('''
 select x.a, x.b from x where a=1
 union
 select x.a, x.b from x where a=2
''')
assert result.keys() == ["x.a", "x.b"]

engine = create_engine("sqlite://", execution_options={"sqlite_raw_colnames": True})

Table("some_table", metadata, ..., sqlite_with_rowid=False)

from sqlalchemy.dialects.sqlite import (
    BLOB,
    BOOLEAN,
    CHAR,
    DATE,
    DATETIME,
    DECIMAL,
    FLOAT,
    INTEGER,
    NUMERIC,
    JSON,
    SMALLINT,
    TEXT,
    TIME,
    TIMESTAMP,
    VARCHAR,
)

class sqlalchemy.dialects.sqlite.DATETIME

"%(year)04d-%(month)02d-%(day)02d  %(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"

2021-03-15 12:05:57.105542

import re
from sqlalchemy.dialects.sqlite import DATETIME
dt = DATETIME(storage_format="%(year)04d/%(month)02d/%(day)02d "
                             "%(hour)02d:%(minute)02d:%(second)02d",
              regexp=r"(\d+)/(\d+)/(\d+) (\d+)-(\d+)-(\d+)"
)

class sqlalchemy.dialects.sqlite.DATE

"%(year)04d-%(month)02d-%(day)02d"

2011-03-15

import re
from sqlalchemy.dialects.sqlite import DATE
d = DATE(
        storage_format="%(month)02d/%(day)02d/%(year)04d",
        regexp=re.compile("(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)")
    )

class sqlalchemy.dialects.sqlite.JSON

method __init__(none_as_null: bool = False)

from sqlalchemy import null
conn.execute(table.insert(), {"data": null()})

class sqlalchemy.dialects.sqlite.TIME

"%(hour)02d:%(minute)02d:%(second)02d.%(microsecond)06d"

12:05:57.10558

import re
from sqlalchemy.dialects.sqlite import TIME
t = TIME(storage_format="%(hour)02d-%(minute)02d-"
                        "%(second)02d-%(microsecond)06d",
         regexp=re.compile("(\d+)-(\d+)-(\d+)-(?:-(\d+))?")
)