Install psycopg2 on mac sierra

需要在我的mbp上进行数据入库测试,因此就需要安装psycopg2,但安装的时候一直有提示:

ld: library not found for -lssl

clang: error: linker command failed with exit code 1 (use -v to see invocation)

开始搜索以为库的位置不对,比如之前类似的问题:http://stackoverflow.com/questions/27264574/import-psycopg2-library-not-loaded-libssl-1-0-0-dylib

后来感觉不太对,又加上sierra关键词找,果然找到一个类似问题:
http://stackoverflow.com/questions/39767810/cant-install-psycopg2-package-through-pip-install-is-this-because-of-sierra
发现解决方法非常简单,就是安装xcode-select工具。

xcode-select –install

然后再安装psycopg2就没有任何问题了:

sudo pip install psycopg2

数据巧处理:重复数据的合并处理

要处理一批数据,形式为:
lat,lon,num
每个文件有一个timestamp,文件数量巨大,开始我感觉lat、lat应该唯一,但后来检查数据后发现,这批数据存在大量重复的点(可能之前高精度数据是唯一的,但精度降低后导致有重复数据出现),因此需要专门处理。
开始是想在bash里直接处理,但点位相同的数据,其后的num感觉应该sum后替换,因此还没有想到较好的方法。
后来一想数据不是要入库么,入库后先放松唯一性检查,然后通过group操作,可以简单获取到对应的结果。
SQL:
create table sum_point as
select ts,lat,lon,sum(num) from point group by ts,lat,lon

postgresql中字符串与数字的混合排序

数据中心部分数据的标题是混合字符串与数组的,比如:

… site no. 1
… site no. 10
… site no. 2

之前,是按照title的字符进行升序排序了,排序结果就如上所示。这里和我们常规理解有所区别的就在于其排序是按照1、10、11、…、2、3进行排序的,而我们通常想要的结果是希望按照站点编号的自然数大小进行排序,即1、2、…、9、10…。

之前的sql语句是这样的:

… order by title

而要满足这个要求,第一个改造的结果是这样的:

… order by substring(title, ‘[^0-9]*’), (substring(title, ‘No.([0-9]+)’))::int

这样可以满足上面的需求,即No.1之类的标题可以排序,但若数字在中间就难以处理了,比如:

…10 July, 2012
…9 August, 2012

因此又继续改造:

…order by regexp_replace(title,'[0-9]+’,”,’g’),(substring(title, ‘([0-9]+)’))::int

这样可以解决上面的问题,但还是不完美,即有多个间断的数字,排序还是有问题的。
比如:

…10 July, 2012
…9 July, 2013

这种问题留待以后解决吧。

postgresql: 提取同类别的第一个排序记录

在数据中心中,我们记录了元数据的多个版本,现在需要提取所有元数据的最新版本,如何用一个sql来表达呢?

CREATE TABLE mdversion
(
  id serial NOT NULL,
  xml text NOT NULL DEFAULT ''::text,
  ts_created timestamp without time zone NOT NULL DEFAULT now(),
  uuid uuid NOT NULL,
  CONSTRAINT mdversion_pkey PRIMARY KEY (id)
)

 

第一感觉,是用group功能,但group不能对同类下的行进行排序,所以解决不了这个问题。

 

用这几个关键词搜索:postgresql group  get first,找到了一个直接相关的解决方案:

Select first row in each GROUP BY group?

解决方案很简单,就是用distinct on方法:

select distinct on (uuid) id from en.mdversion
order by uuid,ts_created desc

 

 

用SQL的正则处理人名的缩写问题

数据中心的数据在向期刊进行投稿时,通常需要对数据引用有一定的格式变换。比如,Nature Scientific Data期刊,对Data Citation部分的建议是:

1. Lastname1, Initial1., Lastname2, Initial2., … & LastnameN. InitialN. Repository_name Dataset_accession_number_or_DOI (YYYY).

数据中心的数据作者在数据库中是有记录的,其通过一个表存储:

CREATE TABLE datadoi
(
id serial NOT NULL,
doi character varying(250) NOT NULL DEFAULT ''::character varying,
authors text[],
uuid uuid,
author_en text[],
CONSTRAINT datadoi_pkey PRIMARY KEY (id),
CONSTRAINT datadoi_doi_key UNIQUE (doi)
)

 

可以看出,作者是通过一个字符串数据存储。在PostgreSQL中,数组是通过如下形式记录的:

{value1,value2,…}

对于数据的作者信息,数据中心则要求其通过如下的形式记录到数据库中:

Wang Yijia, Zhang Eryi,…

而为了满足期刊的投稿要求,则需要将格式转换为:

Wang Y., Zhang E.,…

在数据库里,最终通过这样的形式进行处理:

select translate(regexp_replace(datadoi.author_en::text,'\s+(\w)\w*"',' \1.','g'),'{}"','')

 

即,将数组转换为字符串,剔除大括号,用逗号区分不同作者,用空格区分作者的姓和名,提取名的首字母,然后全局替换即可。

ubuntu 14.04重新构建postgresql数据库

一台服务器,在升级过程中数据库发生了损坏,已经恢复不了,因此决定将旧版本数据备份后删除,直接创建一个新的数据库。

sudo pg_dropcluster --stop 9.1 main

sudo apt-get remove postgresql-9.1

sudo pg_createcluster 9.3 main

然后修改postgresql配置文件,修改ip绑定地址,将localhost修改为*,

sudo nano /etc/postgresql/9.3/main/postgresql.conf

加入远程访问的ip地址控制,

sudo nano /etc/postgresql/9.3/main/pg_hba.conf

然后重新启动数据库服务:

sudo service postgresql start

添加一个新的超级用户:

sudo createuser -P -s username

然后就通过pgadmin3进行远程控制访问。

ubuntu 14.04下升级postgresql 9.1到9.3

一台服务器从12.04升级到14.04,在升级过程中ssh中断了,重新连接后只能直接升级了,在升级过程中遇到了postgresql问题。

1. 升级错误

正在设置 postgresql-client-9.3 (9.3.6-0ubuntu0.14.04) …
update-alternatives: 错误: 候选项 pg_basebackup.1.gz 不能作 psql.1.gz 的次要项:它是 postmaster.1.gz 的次要项
dpkg: 处理软件包 postgresql-client-9.3 (–configure)时出错:
子进程 已安装 post-installation 脚本 返回了错误号 2

这个错误在serverfault上找到了答案:

http://serverfault.com/questions/563073/postgresql-9-3-installation-fails

解决方法是:

sudo update-alternatives --remove postmaster.1.gz /usr/share/postgresql/9.1/man/man1/postmaster.1.gz

 

然后继续升级:

sudo apt-get install -f

 

2. 数据库升级

升级完成后,默认的9.1版数据并没有升级,需要手动升级。可以采用这种方案进行快速升级(做好备份工作):

sudo service postgresql stop
sudo pg_dropcluster --stop 9.3 main
sudo pg_upgradecluster 9.1 main

升级完成后,启动9.3服务:

sudo service postgresql start 9.3

 

使用后确认升级成功,可以删除9.1的数据:

sudo pg_dropcluster --stop 9.1 main

 

多表的合并查询SQL

数据中心的专题文献,涉及到多个表。前期是保存到mdref表,通过类型字段定义,现在又增加了一个新表,专门用来管理专题文献。而有的时候,可能两个表的专题都会涉及到。因此SQL查询比较复杂。

需求:

1. 保留两个表的数据

2. 保持文献的排序

最终SQL如下:

select  r.* from reference r right join
(
select refid,place from (
(
select sr.refid,sr.place from sourceref sr left join datasource ds on ds.sourceid=sr.sourceid
where ds.uuid='55667374-fe5c-4c98-8756-37e3b5496d5e'
order by sr.place
)  
union
(
 select mr.refid,mr.place from mdref mr where  mr.reftype=4 and mr.uuid='55667374-fe5c-4c98-8756-37e3b5496d5e' and mr.refid not in (
select sr.refid from sourceref sr left join datasource ds on ds.sourceid=sr.sourceid
where ds.uuid='55667374-fe5c-4c98-8756-37e3b5496d5e'
)
)
) a
) p on r.id=p.refid order by p.place

用到了union、临时表。

 

PostgreSQL中1:M两表的联合排序实现

数据库中有a、b两表,两表的对应关系为1:M,即a表的1条记录可能对应b表的多条记录。现在想根据b表中的最近更新来对a表进行排序。之前实现的时候没有深入处理,只是进行了随机排序。

之前的SQL:

SELECT md.title,md.uuid,count(v.id) as c FROM mdversion v
LEFT JOIN metadata md ON md.uuid=v.uuid
WHERE md.title IS NOT NULL group by md.uuid,md.title

修改后,将ab两表先联合在排序查询,即可满足要求:

 

SELECT md.title,md.uuid,count(v.id) as c FROM mdversion v
LEFT JOIN (select m.uuid,m.title,m.description,max(v.ts_created) as ts_created from mdversion v left join metadata m on v.uuid=m.uuid
group by m.uuid,m.title,m.description) md ON md.uuid=v.uuid
WHERE md.title IS NOT NULL group by md.uuid,md.title,md.ts_created order by md.ts_created desc

关键点:先将需要排序的要素提取到临时表中,然后再进行对应的联合处理。

SQL中处理serial类型字段

通常我们在设计数据库时会设计一个自增长的主键(如id、oid),提高数据的查询效率。但有的时候有批量入库的需求,而通常在入库时这个自增长字段是不用赋值的。我们在处理可视化数据入库时(通过CSV数据)时遇到这个问题。
用CSV入库时,若数据的顺序和表中字段的次序一致,则可以这样写SQL:

 insert into some_table values(value1,value2,value3)

 
但若有一个serial字段(假设为第一个字段),则可以这样处理:

 insert into some_table values(default,value1,value2,value3)

 
即用default代表自增长字段的值。