g5Q5ZRbr

1import mysql.connector
2from mysql.connector import errorcode
3import os
4from dotenv import load_dotenv
5
6load_dotenv()
7
8DB_NAME = os.getenv("MYSQL_DATABASE")
9
10TABLES = {}
11TABLES['valentine_tweet'] = (
12    "CREATE TABLE `valentine_tweet` ("
13    "  `id` int(11) NOT NULL AUTO_INCREMENT,"
14    "  `username` varchar(255) NOT NULL,"
15    "  `created_at` datetime NOT NULL,"
16    "  `tweet` text NOT NULL,"
17    "  `wakachi` text,"
18    "  `mecabed` enum('True','False'),"
19    "  `analyzed` enum('True','False'),"
20    "  PRIMARY KEY (`id`)"
21    ") ENGINE=InnoDB")
22TABLES['tweet_analysis'] = (
23    "CREATE TABLE `tweet_analysis` ("
24    "  `id` int(11) NOT NULL AUTO_INCREMENT,"
25    "  `tweet_id` int(11) NOT NULL,"
26    "  `part` varchar(10),"
27    "  `mecabed_data` text,"
28    "  PRIMARY KEY (`id`),"
29    "  CONSTRAINT `tweet_analysis_ibfk_1` FOREIGN KEY (`tweet_id`) "
30    "     REFERENCES `valentine_tweet` (`id`) ON DELETE CASCADE"
31    ") ENGINE=InnoDB")
32
33config = {
34    'user': os.getenv("MYSQL_USER"),
35    'password': os.getenv("MYSQL_PASSWORD"),
36    'host': 'db',
37    'port': '3307',
38    'database': os.getenv("MYSQL_DATABASE"),
39    'charset': 'utf8mb4'
40}
41
42cnx = mysql.connector.connect(**config)
43cursor = cnx.cursor()
44
45
46def create_database(cursor):
47    try:
48        cursor.execute(
49            "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8mb4'".format(DB_NAME))
50    except mysql.connector.Error as err:
51        print("Failed creating database: {}".format(err))
52        exit(1)
53
54
55if __name__ == '__main__':
56    try:
57        cursor.execute("USE {}".format(DB_NAME))
58    except mysql.connector.Error as err:
59        print("Database {} does not exists.".format(DB_NAME))
60        if err.errno == errorcode.ER_BAD_DB_ERROR:
61            create_database(cursor)
62            print("Database {} created successfully.".format(DB_NAME))
63            cnx.database = DB_NAME
64        else:
65            print(err)
66            exit(1)
67
68    for table_name in TABLES:
69        table_description = TABLES[table_name]
70        try:
71            print("Creating table {}: ".format(table_name))
72            cursor.execute(table_description)
73        except mysql.connector.Error as err:
74            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
75                print("already exists.")
76            else:
77                print(err.msg)
78        else:
79            print("OK")
80
81    cursor.close()
82    cnx.close()