>>> from env_helper import info; info()
页面更新时间： 2024-07-23 21:46:19
运行环境：
Linux发行版本: Debian GNU/Linux 12 (bookworm)
操作系统内核: Linux-6.1.0-23-amd64-x86_64-with-glibc2.36
Python版本: 3.11.2
8.9. SQLite 的虚拟数据库¶
SQLite数据库实现了一个非常特别的特性。你可以开发一些特殊的驱动来访问任何通用（甚至是有些奇怪的）的数据源， 然后你可以在SQL引擎中像访问原生数据源一样来访问这些外部数据。
这意味着，在适当的情况下，驱动提供了足够的支持，你就可以在这些数据上应用任何的SQL操作。
8.9.1. 直接在CSV与TXT-tab文件上进行SQL查询¶
基于带分隔符的文本格式已经得到了广泛的应用。所以你可以使用CSV或TXT Tab格式导出任何形式的表格数据。
任何主流的电子表格软件，如微软的 Excel ，或Open Office的Calc组件， 都支持导入或导出带分隔符的文本。
VirtualText 模块是 SpatiaLite 直接支持的另一个虚拟表驱动（只读），
可以用来直接访问 CVS 与 Tab分隔的TXT文件。
下面是一些简单的例子：
>>> cvs_text = '''Author,Book,Lang,Category,Price
>>> "Alighieri, Dante",Divina commedia,Italian,Literature,"12,50"
>>> "Alighieri, Dante",Divina commedia,中国,Literature,"12,50"
>>> "Alighieri, Dante",Divina commedia,Italian,Literature,"12,50"
>>> "Alighieri, Dante",Divina commedia,Italian,Literature,"12,50"
>>> '''
上面是一个CSV文件的示例，展示了一个逗号分隔的纯文本文件。
将上面的CSV文本内容，粘贴到你常用的文本编辑器中， 并将其保存为
books.txt 文件。
>>> with open('xx_out.txt', 'w') as fo:
>>> fo.write(cvs_text)
现在可以开始一个 SpatiaLite 任务：
>>> import sqlite3 as sqlite
>>> conn = sqlite.connect(':memory:')
>>> conn.enable_load_extension(True)
>>> conn.execute('SELECT load_extension("mod_spatialite.so.7")')
>>> cur = conn.cursor()
>>> sql = "CREATE VIRTUAL TABLE books USING VirtualText(xx_out.txt, utf8, 1, COMMA, DOUBLEQUOTE, ',');"
>>> cur.execute(sql)
>>> cur.execute('PRAGMA table_info(books)')
>>> # cur.execute('select * from books')
>>> for rec in cur:
>>> print(rec)
(0, 'ROWNO', 'INTEGER', 0, None, 0)
(1, 'Author', 'TEXT', 0, None, 0)
(2, 'Book', 'TEXT', 0, None, 0)
(3, 'Lang', 'TEXT', 0, None, 0)
(4, 'Category', 'TEXT', 0, None, 0)
(5, 'Price', 'TEXT', 0, None, 0)
VirtualText 驱动作为代理，可以从物理上访问外部数据源（实际上是
CSV 或 TXT-tqab 文件）。 下面对
CREATE VIRTUAL TABLE ... USING VirtualText(...)参数进行说明：
第一个参数（
xx_out.txt），是数据源文件。第二个参数，是文件使用的字符编码。
第三个参数（可选的），用来确定如何处理文件：
值为
1，表示文件的第一行包含了列的名称；值为
0，表示文件的第一行就是普通的列的值，在这种情况下，VirtualText会对每一列生成缺省列名称。
第四个参数（可选的），用来声明源文件的小数点分隔符：
值为英文半角点号
.（缺省情况下），说明使用常规的小数点分隔符；否则，会将传入的控制符作为小数点分隔符（这种情况不常见，但还是有可能会碰到的，尤其是一些机器、设备产生或采集的数据）。
第五个参数是双引号界定符：
传递DOUBLEQUOTE值[* default * ]指定必须用做文本分隔符
否则，传递您指定的“必须用作文本分隔符的”SINGLE QUOTE“值
第六个参数（最后一个）是字段分隔符。缺省情况下
TAB， 可以使用,， 或:。
一个虚拟表（ VirtualText ）是一个表， 所以你 不允许 执行任何的
DELETE ， INSERT ， UPDATE 命令。
>>> cur.execute("SELECT count(*) FROM books")
>>> for rec in cur:
>>> print(rec)
>>>
>>> sql2 = "SELECT * FROM books ORDER BY Lang, Author limit 5;"
>>> cur.execute(sql2)
>>> for rec in cur:
>>> print(rec)
>>> # [print(rec) for rec in cur]
(4,)
当使用 VirtualText 模块创建了虚拟表，可以在这个表上显式执行的任何
SQL SELECT 方法。
>>> sql = '''SELECT Book, Author FROM Books
>>> WHERE Category = 'Literature' AND Price < 10 AND Lang =
>>> 'English'; '''
>>> cur.execute(sql)
>>> for rec in cur:
>>> print(rec)
8.9.2. 直接在Shapefile上执行SQL查询¶
基本的使用方法¶
VirtualShape是基于SQLite实现了这种类型的驱动。
而且，VirtualShape设计并实现了与 SpatiaLite 进行直接的交互操作，
来通过标准的SQL来完全直接访问 Shapefile ， 包括数据的属性与几何图形。
SpatiaLite扩展会自动支持 VirtualShape 扩展，所以在加载 SpatiaLite
之后，你可以直接在 Shapefile上执行 SQL
的标准查询，而不必引入其他任何的扩展。
我们来看一个实际的例子。我们使用 shape_towns.shp
数据文件，来进行实验。
下面是使用pyspatialite实现的代码：
>>> import os, sys, stat
>>> import shutil
>>> import sqlite3 as sqlite
>>> # sqlite_file = '/tmp/xx_new_db.sqlite'
>>> # if os.path.exists(sqlite_file):
>>> # os.remove(sqlite_file)
>>> # shutil.copy("/gdata/test-2.3.sqlite", sqlite_file)
>>> # os.chmod(sqlite_file, stat.S_IRUSR + stat.S_IWUSR)
>>> # os.chmod(sqlite_file, stat.S_IWUSR)
>>> conn = sqlite.connect(':memory:')
>>> conn.enable_load_extension(True)
>>> conn.execute('SELECT load_extension("mod_spatialite.so.7")')
>>> cur = conn.cursor()
>>> sql = 'create virtual table uu using virtualshape("/gdata/shape_towns", cp1252, 32632)'
>>> cur.execute(sql)
>>> cur.execute('PRAGMA table_info(uu)')
>>> for rec in cur:
>>> print(rec)
(0, 'pkuid', 'INTEGER', 0, None, 0)
(1, 'geometry', 'BLOB', 0, None, 0)
(2, 'pk_uid', 'INTEGER', 0, None, 0)
(3, 'name', 'VARCHAR(62)', 0, None, 0)
(4, 'peoples', 'INTEGER', 0, None, 0)
(5, 'localcounc', 'INTEGER', 0, None, 0)
(6, 'county', 'INTEGER', 0, None, 0)
(7, 'region', 'INTEGER', 0, None, 0)
>>> cur.execute('SELECT PK_UID, Name, Peoples, AsText(Geometry) FROM uu LIMIT 5')
>>> for rec in cur:
>>> print(rec)
>>> cur.execute('create table vv as SELECT PK_UID, Name, Peoples, AsText(Geometry) FROM uu LIMIT 5')
<sqlite3.Cursor at 0x7f2ebee6e7c0>
>>> cur.execute('select * from vv')
>>>
>>> for rec in cur: print(rec)
通过使用CREATE VIRTUAL TABLE语句，你可以利用SQLite来访问一些外部的数据源：
test_shape是表的名称（从SQL的观点） ；USING VirtualShape()声明语句指定了你想使用的驱动的名称；shape_towns是 VirtualShape 驱动所需要的第一个参数，来指明一些 shapefile 的路径（不带后缀）。第二个参数
CP1252， 指明了 Shapfile 中字符与数字等属性值编码的字符名称（Charset Name）；第三个参数
32632指明了在 Shapefile上 中几何图形使用的 SRID。
PRAGMA info_table 是将 SQLite 中所有的属性列列出。 你可以看到，在
Shapefile 中定义的属性，都映射成了对应的 SQL 类型。
还有两列要注意：
PK_UID属性列是唯一来标识每一个实体（记录）的值。这个值标识了实体的相对位置，又叫做记录号码。Geometry列包含了 Shapefile 的几何图形，但是转换成了标准的 SpatiaLite （OpenGIS） 几何图形。
进一步查看¶
>>> sql2 = '''SELECT PK_UID, Name, Peoples, AsText(Geometry)
>>> FROM uu WHERE Peoples > 350000 ORDER BY Name;'''
>>> cur.execute(sql2)
>>>
>>> for rec in cur:
>>> print(rec)
(8079, 'Bologna', 371217, 'POINT(686263.23 4929405.15)')
(7981, 'Firenze', 356118, 'POINT(681514.97 4848768.02)')
(204, 'Genova', 610307, 'POINT(492370.69 4918665.57)')
(8004, 'Milano', 1256211, 'POINT(514820.49 5034534.56)')
(8073, 'Napoli', 1004500, 'POINT(942636.1 4535272.55)')
(7923, 'Palermo', 686722, 'POINT(880179.17 4227024.08)')
(7919, 'Roma', 2546804, 'POINT(788703.57 4645636.3)')
(8013, 'Torino', 865263, 'POINT(395553.63 4991768.9)')
VirtualShape 驱动设计了从物理上访问外部数据源（实现上是
Shapefile）， 然后来执行任何的数据格式转换的请求，
这样就可以像对待原始的SQL数据一样允许SQL引擎来遍历数据。
目前的唯一限制，是 VirtualShape 实现的是“只读”操作，所以不允许执行任何的
DELETE 、 INSERT 、 UPDATE 命令。 可以执行的，是针对使用
VIRTUAL TABLE 命令转换成 VirtualShape的 Shapefile，执行任何类型的
SELECT 命令。
需要注意的是， 在运行过一些任务后，虚拟表的定义会存储在SQLite数据库中。
如果你停止了目前的SQLite任务，在下次使用相同的数据库开始一个新任务时，
你会发现 VIRTUAL TABLE 还在，并且还可以使用。
如果不想再使用任何的虚拟表，你必须删除掉她，就像删除普通的表一样。
>>> cur.execute('DROP TABLE uu')
<sqlite3.Cursor at 0x7f2ebee6e7c0>
经过上面的操作，你的虚拟表会被取消，且不能再被使用。 当然原始的外部数据不会受任何的影响，它还存在，也没有任何的改变。 删除一个虚拟表只是在SQLite中取消了与外部数据源的连接，而并非是删除数据。