Python Web Scraping ———08.03.2017

Postgresql database and data structure.

Just write down what I've learned about web data scraping so that I won't forget everything and start all over next time I need to use the technique. 

How to set Primary Key postgresql:

--Firstly, remove PRIMARY KEY attribute of former PRIMARY KEY 

ALTER TABLE … DROP CONSTRAINT ..._pkey;

--Then change column name of  your PRIMARY KEY and PRIMARY KEY candidates properly.

ALTER TABLE ... RENAME COLUMN … TO id;

--Lastly set your new PRIMARY KEY

ALTER TABLE … ADD PRIMARY KEY(id);

How to Insert if not exists and do nothing if exists:

INSERT INTO invoices(invoiceid,billed)SELECT'12345','TRUE'

WHERE NOT EXISTS(SELECT 1 FROM invoices WHERE invoiceid='12345');

-----------------------------

command = ("""

INSERT INTO test3(dateTime, random) SELECT %s, %s WHERE NOT EXISTS (SELECT 1 FROM test3 where random='dddddddddd') # Keep in mind no parenthesis around %s

""")

a = "dddddddddd"

try:

cur.execute(command, (dt_utc,a))

Today I have really learned a lot, including working with postgis, how to create geometry column, how to make point, add point to specific geo column.


To install the postgis extension, you might need owner privilege to do so:

CREATE EXTENSION postgis;

Therefore, you will need to grant access to the user:

GRANT ALL PRIVILEGES ON TABLE stations TO janton;

To drop primary key:

ALTER TABLE sample.public.employee DROP CONSTRAINT employee_pkey

To delete all rows from table:

DELETE FROM (tbl_name)

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • SQL SELECT 语句 一、查询SQL SELECT 语法 (1)SELECT 列名称 FROM 表名称 (2...
    有钱且幸福阅读 5,777评论 0 33
  • 1. Java基础部分 基础部分的顺序:基本语法,类相关的语法,内部类的语法,继承相关的语法,异常的语法,线程的语...
    子非鱼_t_阅读 31,802评论 18 399
  • 一. Java基础部分.................................................
    wy_sure阅读 3,846评论 0 11
  • fs119阅读 167评论 0 1
  • 只是想给自己写博客找个理由。 我是一个有严重拖延症的人,曾今学习的时候就想开个博客,纪录一下我学习的过程。后来也坚...
    Iris_lin阅读 267评论 0 0