直接在CSV与TXT-tab文件上进行SQL查询

基于带分隔符的文本格式已经得到了广泛的应用。所以你可以使用CSV或TXT Tab格式导出任何形式的表格数据。

任何主流的电子表格软件,如微软的 Excel ,或Open Office的Calc组件, 都支持导入或导出带分隔符的文本。

VirtualText 模块是 SpatiaLite 直接支持的另一个虚拟表驱动(只读), 可以用来直接访问 CVS 与 Tab分隔的TXT文件。

下面是一些简单的例子:

In [23]:
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 文件。

In [24]:
with open('xx_out.txt', 'w') as fo:
    fo.write(cvs_text)

现在可以开始一个 SpatiaLite 任务:

In [25]:
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 命令。

In [26]:
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]
(0, 'Alighieri, Dante', 'Divina commedia', 'Italian', 'Literature', '12,50')
(2, 'Alighieri, Dante', 'Divina commedia', 'Italian', 'Literature', '12,50')
(3, 'Alighieri, Dante', 'Divina commedia', 'Italian', 'Literature', '12,50')
(1, 'Alighieri, Dante', 'Divina commedia', '中国', 'Literature', '12,50')

当使用 VirtualText 模块创建了虚拟表,可以在这个表上显式执行的任何 SQL SELECT 方法。

In [27]:
sql = '''SELECT Book, Author FROM Books
     WHERE Category = 'Literature' AND Price < 10 AND Lang =
    'English'; '''
cur.execute(sql)
for rec in cur:
    print(rec)
In [ ]:
 

直接在Shapefile上执行SQL查询

基本的使用方法

SQLite数据库实现了一个非常特别的特性。你可以开发一些特殊的驱动来访问任何通用(甚至是有些奇怪的)的数据源, 然后你可以在SQL引擎中像访问原生数据源一样来访问这些外部数据。

这意味着,在适当的情况下,驱动提供了足够的支持,你就可以在这些数据上应用任何的SQL操作。

VirtualShape是基于SQLite实现了这种类型的驱动。 而且,VirtualShape设计并实现了与 SpatiaLite 进行直接的交互操作, 来通过标准的SQL来完全直接访问 Shapefile , 包括数据的属性与几何图形。

SpatiaLite扩展会自动支持 VirtualShape 扩展,所以在加载 SpatiaLite 之后,你可以直接在 Shapefile上执行 SQL 的标准查询,而不必引入其他任何的扩展。

我们来看一个实际的例子。我们使用 shape_towns.shp 数据文件,来进行实验。

下面是使用pyspatialite实现的代码:

In [18]:
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(sqlite_file)
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)
In [19]:
cur.execute('SELECT PK_UID, Name, Peoples, AsText(Geometry) FROM uu LIMIT 5')
for rec in cur:
    print(rec) 
(1, 'Brozolo', 435, 'POINT(427002.77 4996361.33)')
(2, 'Campiglione-Fenile', 1284, 'POINT(367470.48 4962414.5)')
(3, 'Canischio', 274, 'POINT(390084.12 5025551.73)')
(4, 'Cavagnolo', 2281, 'POINT(425246.99 5000248.3)')
(5, 'Magliano Alfieri', 1674, 'POINT(426418.89 4957737.37)')

通过使用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) 几何图形。

进一步查看

In [20]:
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 还在,并且还可以使用。

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

In [21]:
cur.execute('DROP TABLE uu')
Out[21]:
<sqlite3.Cursor at 0x7f3578608f10>

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