XwamNS8r

1def create_table():
2    """ create table in the MySQL database"""
3    commands = (
4                """
5                    CREATE TABLE IF NOT EXISTS url
6                    (
7                      url_id INT NOT NULL AUTO_INCREMENT,
8                      url VARCHAR(255) NOT NULL,
9                      title VARCHAR(255) NOT NULL,
10                      PRIMARY KEY (url_id)
11                     );
12                 """,
13                 """
14                     CREATE TABLE IF NOT EXISTS word
15                     (
16                       wurl_id INT NOT NULL,
17                       word VARCHAR(255) NOT NULL,
18                       count INT NOT NULL,
19                       PRIMARY KEY (wurl_id, word),
20                       CONSTRAINT fk_word
21                       FOREIGN KEY (wurl_id) REFERENCES url (url_id)
22                          ON DELETE CASCADE
23                          ON UPDATE CASCADE
24                      );
25                  """,
26                  """
27                      CREATE TABLE IF NOT EXISTS refer_url
28                      (
29                        rurl_id INT NOT NULL,
30                        ref_url VARCHAR(255) NOT NULL,
31                        PRIMARY KEY (rurl_id, ref_url),
32                        CONSTRAINT fk_refer_link
33                        FOREIGN KEY (rurl_id) REFERENCES url (url_id)
34                           ON DELETE CASCADE
35                           ON UPDATE CASCADE
36                       );
37                   """,
38                   """
39                       CREATE TABLE IF NOT EXISTS request
40                       (
41                         id INT NOT NULL AUTO_INCREMENT,
42                         ip_addr VARCHAR(23) NOT NULL,
43                         word VARCHAR(255) NOT NULL,
44                         uri VARCHAR(255) NOT NULL,
45                         request_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
46                         PRIMARY KEY (id)
47                        );
48                    """
49                   )
50    conn = None
51    try:
52       # read the connection parameters
53       params = config(section='mysql')
54       # connect to the MySQL server
55       conn = MySQLConnection(**params)
56       # create table one by one
57       cursor = conn.cursor()
58       for command in commands:
59           cursor.execute(command)
60       cursor.close()
61       # commit the changes
62       conn.commit()
63       print('Create table successfully.')
64    except Error as e:
65       print('Error:', e)
66    finally:
67       conn.close()
68if __name__ == '__main__':
69   connect()
70   create_table()