More on Thesis Sources

Patents, patents, patents!

I have been working a bit more on the patents side of my thesis. After the previous script that retrieved the data from the Wipo website on a patents query search, I have developed another script to import this data into a database. I have used a PostgreSQL DB although I have not been able to integrate it directly to Python, I have used ODBC connections instead. I guess that but the connection parameters it is suitable for any ODBC ready database.

It creates two tables. One with all the patents information and another one that relates this patents to the search terms that resulted on it. I paste here the definition of both tables:

-- Table: patents

-- DROP TABLE patents;

CREATE TABLE patents
(
p_id integer NOT NULL,
p_code character(20),
pub_dated date,
description character(512),
applicant character(1000),
intl_class character(15),
appl_number character(20),
url character(500),
abstract character(3000),
CONSTRAINT firstkey PRIMARY KEY (p_id)
)
WITH (OIDS=FALSE);
ALTER TABLE patents OWNER TO postgres;

-- Index: codeidx

-- DROP INDEX codeidx;

CREATE UNIQUE INDEX codeidx
ON patents
USING btree
(p_code);



-- Table: patent_query

-- DROP TABLE patent_query;

CREATE TABLE patent_query
(
recid integer NOT NULL,
pat_id integer,
query_terms character(256) NOT NULL,
CONSTRAINT patent_query_pkey PRIMARY KEY (recid),
CONSTRAINT patent_query_pat_id_fkey FOREIGN KEY (pat_id)
REFERENCES patents (p_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE patent_query OWNER TO postgres;

-- Index: queryidx

-- DROP INDEX queryidx;

CREATE UNIQUE INDEX queryidx
ON patent_query
USING btree
(pat_id, query_terms);

I have uploaded the second script to the site and can be found here, along with the previous one.