>>> 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 驱动作为代理,可以从物理上访问外部数据源(实际上是 CSVTXT-tqab 文件)。 下面对 CREATE VIRTUAL TABLE ... USING VirtualText(...)参数进行说明:

  • 第一个参数( xx_out.txt ),是数据源文件。

  • 第二个参数,是文件使用的字符编码。

  • 第三个参数(可选的),用来确定如何处理文件:

    • 值为 1 ,表示文件的第一行包含了列的名称;

    • 值为 0 ,表示文件的第一行就是普通的列的值,在这种情况下, VirtualText 会对每一列生成缺省列名称。

  • 第四个参数(可选的),用来声明源文件的小数点分隔符:

    • 值为英文半角点号 . (缺省情况下),说明使用常规的小数点分隔符;

    • 否则,会将传入的控制符作为小数点分隔符(这种情况不常见,但还是有可能会碰到的,尤其是一些机器、设备产生或采集的数据)。

  • 第五个参数是双引号界定符:

    • 传递DOUBLEQUOTE值[* default * ]指定必须用做文本分隔符

    • 否则,传递您指定的“必须用作文本分隔符的”SINGLE QUOTE“值

  • 第六个参数(最后一个)是字段分隔符。缺省情况下 TAB , 可以使用 , , 或 :

一个虚拟表( VirtualText )是一个表, 所以你 不允许 执行任何的 DELETEINSERTUPDATE 命令。

>>> 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 实现的是“只读”操作,所以不允许执行任何的 DELETEINSERTUPDATE 命令。 可以执行的,是针对使用 VIRTUAL TABLE 命令转换成 VirtualShape的 Shapefile,执行任何类型的 SELECT 命令。

需要注意的是, 在运行过一些任务后,虚拟表的定义会存储在SQLite数据库中。 如果你停止了目前的SQLite任务,在下次使用相同的数据库开始一个新任务时, 你会发现 VIRTUAL TABLE 还在,并且还可以使用。

如果不想再使用任何的虚拟表,你必须删除掉她,就像删除普通的表一样。

>>> cur.execute('DROP TABLE uu')
<sqlite3.Cursor at 0x7f2ebee6e7c0>

经过上面的操作,你的虚拟表会被取消,且不能再被使用。 当然原始的外部数据不会受任何的影响,它还存在,也没有任何的改变。 删除一个虚拟表只是在SQLite中取消了与外部数据源的连接,而并非是删除数据。