NewsClusters/sql.py at master · JakeStratton/NewsClusters · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
import psycopg2 as pg2
import sys


#connect to database
con = None
con = pg2.connect("host='localhost' dbname='newsclusters' user='jake' password='passwordjake'")
cur = con.cursor()

#add articles table
query = '''
        CREATE TABLE articles (junk varchar(10),
            byline_person_0_firstname varchar(50),
            byline_person_0_lastname varchar(50),
            byline_person_0_middlename varchar(50),
            headline_main varchar(500),
            keywords_0_value varchar(1000),
            keywords_1_value varchar(1000),
            keywords_2_value varchar(1000),
            pub_date varchar(50),
            section_name varchar(100),
            snippet varchar(500),
            source varchar(100),
            type_of_material varchar(100),
            web_url varchar(500),
            word_count varchar(10),
            author varchar(100),
            author_id varchar(100),
            article_id varchar(500),
            text varchar(1000),
            Document_No NUMERIC,
            Topic_Perc_Contrib NUMERIC,
            Keywords varchar(1000),
            topic_name varchar(100),
            topic_num NUMERIC,
            topic_num_0 NUMERIC,
            topic_num_1 NUMERIC,
            topic_num_2 NUMERIC,
            topic_num_3 NUMERIC,
            topic_num_4 NUMERIC,
            topic_num_5 NUMERIC,
            topic_num_6 NUMERIC,
            topic_num_7 NUMERIC,
            topic_num_8 NUMERIC,
            topic_num_9 NUMERIC,
            topic_num_10 NUMERIC,
            topic_num_11 NUMERIC,
            topic_num_12 NUMERIC,
            topic_num_13 NUMERIC,
            topic_num_14 NUMERIC,
            topic_num_15 NUMERIC,
            topic_num_16 NUMERIC,
            topic_num_17 NUMERIC,
            topic_num_18 NUMERIC,
            topic_num_19 NUMERIC,
            topic_num_20 NUMERIC,
            topic_num_21 NUMERIC,
            topic_num_22 NUMERIC,
            topic_num_23 NUMERIC,
            topic_num_24 NUMERIC,
            topic_num_25 NUMERIC,
            topic_num_26 NUMERIC,
            topic_num_27 NUMERIC,
            topic_num_28 NUMERIC,
            topic_num_29 NUMERIC,
            topic_num_30 NUMERIC,
            topic_num_31 NUMERIC,
            topic_num_32 NUMERIC,
            topic_num_33 NUMERIC
        );
        '''

cur.execute(query)
con.commit()


#import data from csv
query = '''
        COPY articles(junk,byline_person_0_firstname,byline_person_0_lastname,
        byline_person_0_middlename,headline_main,keywords_0_value,
        keywords_1_value,keywords_2_value,pub_date,section_name,snippet,
        source,type_of_material,web_url,word_count,author,author_id,
        article_id,text,Document_No,Topic_Perc_Contrib,Keywords,topic_name,
        topic_num,topic_num_0,topic_num_1,topic_num_2,topic_num_3,topic_num_4,
        topic_num_5,topic_num_6,topic_num_7,topic_num_8,topic_num_9,topic_num_10,
        topic_num_11,topic_num_12,topic_num_13,topic_num_14,topic_num_15,
        topic_num_16,topic_num_17,topic_num_18,topic_num_19,topic_num_20,
        topic_num_21,topic_num_22,topic_num_23,topic_num_24,topic_num_25,topic_num_26,topic_num_27,
        topic_num_28,topic_num_29,topic_num_30,topic_num_31,topic_num_32,topic_num_33)
        FROM '/home/jake/data_science/capstones/NewsClusters/data/articles.csv'
        DELIMITER ','
        CSV HEADER;
        '''

cur.execute(query)
con.commit()

con.close()