0TunYekJ

1import mysql.connector
2
3# An example of a single table creation
4sql_articles = """CREATE TABLE IF NOT EXISTS articles (ID int AUTO_INCREMENT,
5                                                       doi_link varchar(255) NOT NULL,
6                                                       title varchar(255),
7                                                       abstract TEXT,
8                                                       publication_date varchar(255),
9                                                       citations int,
10                                                       UNIQUE (doi_link),  
11                                                       PRIMARY KEY(ID));"""
12all_sql_tables.append(sql_articles)  # list of all sql tables creation
13
14
15def build_database(db_name, host_name, user_name, password, all_sql_tables):
16    my_db = mysql.connector.connect(host=host_name, user=user_name, passwd=password, database=db_name)
17    cursor = my_db.cursor()
18    for sql_q in all_sql_tables:
19        cursor.execute(sql_q)
20    my_db.commit()
21    logger.info("\n*** Database was created successfully. ***\n")
22    except mysql.connector.Error as error:
23        my_db.rollback()  # rollback if any exception occured
24        logger.critical("Failed creating database {}.".format(error))
25    finally:
26        # closing database connection.
27        if my_db is not None and my_db.is_connected():
28            cursor.close()
29            my_db.close()
30            logger.info("MySQL connection is closed.")
31        else:
32            logger.info("connection to MySQL did not succeed.")