程序设计:sqlite3

SQLite

SQLite,是一款轻型的关系数据库,单文件,比较好用,读写效率一般,建立索引后查询速度不错. 现在我们使用它来保存空气质量数据.

首先我们使用Perl脚本建立数据库(使用strawberry perl,自带DBI模块,如果使用其他的版本Perl,请自行安装DBI模块):

createdb.pl
#!/usr/bin/perl
use strict;
use v5.10;
use DBI;
my $driver   = "SQLite";
my $database = "air.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
                      or die $DBI::errstr;
print "Opened database successfully\n";
 
       my $stmt = qq(
CREATE TABLE LOCATION
      (AREA              TEXT   ,
POSITION_NAME      TEXT   ,
Latitude          REAL   ,
Longitude         REAL   ,
STATION_CODE       text PRIMARY KEY);
);
my $rv = $dbh->do($stmt);
if($rv < 0){
   print $DBI::errstr;
} else {
   print "Table created successfully\n";
}
 
my $stmt = qq(CREATE TABLE CONTENT
      (AQI                  INT  ,
AREA                 TEXT  ,
CO                   REAL  ,
CO_24H               REAL  ,
NO2                  INT  ,
NO2_24H              INT  ,
O3                   INT  ,
O3_24H               INT  ,
O3_8H                INT  ,
O3_8H_24H            INT  ,
PM10                 INT  ,
PM10_24H             INT  ,
PM2_5                INT  ,
PM2_5_24H            INT  ,
POSITION_NAME        TEXT  ,
PRIMARY_POLLUTANT    TEXT  ,
QUALITY              TEXT  ,
SO2                  INT  ,
SO2_24H              INT  ,
STATION_CODE         TEXT  ,
TIME_POINT           TEXT  ,
constraint pk_t2 primary key (STATION_CODE,TIME_POINT)););
 
my $rv = $dbh->do($stmt);
if($rv < 0){
   print $DBI::errstr;
} else {
   print "Table created successfully\n";
}
 
$dbh->disconnect();

然后我们表中插入数据,使用如下脚本向 content 表中导入数据,因为设置了时间和站点编号为主键,所以理论上不会出现重复:

insert.pl
#!/usr/bin/perl
use strict;
use v5.10;
#数据文件夹
my $path = "d:/data/perl/else";
#选择所有csv文件
my @csvfiles = <$path/*.csv>;
foreach my $csvfile (@csvfiles) {
  # 显示文件名
  say $csvfile;
  #向数据库中插入数据
  system "sqlite3.exe -separator ',' air.db '.import $csvfile content'";
}

插入完成后,建立索引,使用sqlite提供的shell工具,可以在SQLite官网下载,下载Precompiled Binaries for Windows版本

注意:表中的数据都是utf8编码,如果直接输出在命令行中可能为乱码,且cmd中查询也有可能为乱码,最好输出到文件!最好使用msys2,安装sqlite后使用sqlite3工具查询

进入cmd:输入 shell.exe air.db命令,可以看到如下信息:

D:\data\perl>shell.exe air.db
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite>

我们进行查询/输出/建立索引等功能将都在此进行

下面对time_point建立索引,请在数据插入完成后建立索引,索引会影响插入速度:

#sqlite> 是命令行显示,不需要输入
# 建立索引
sqlite>create index time_index on content(time_point);
#将结果输出到文件中,以逗号分隔(默认输出以 | 分隔)
sqlite>.separator ','
#将结果输出到文件中
sqlite>.output result.txt
#按日期选择
sqlite>select * from content where time_point between date('2013-05-22') and date('2013-05-24');
#按站点选择
sqlite>select * from content where station_code = "1001A";
#按地区选择
sqlite>select * from content where area like "北京%" limit 10;
#选择同时输出经纬度
sqlite>select content.*,location.latitude,location.longitude from content,location where content.station_code = "2011A" and content.station_code = location.station_code;

可以按照上面的描述,自己进行查询.

  • 程序设计/sqlite3.txt
  • 最后更改: 2021/09/21 13:28
  • 106.11.158.73