jfeugbgH

1import mysql.connector
2import logging
3logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(filename)s - %(levelname)s - %(message)s')
4
5
6DB_NAME = 'blah'
7connection = mysql.connector.connect(option_files='mysql.cfg') # using non root with basic read and write
8cursor = connection.cursor()
9
10
11def setup():
12    """Create database and tables if it does not exist"""
13    try:
14        logging.info("CREATING DATABASE > %s", DB_NAME)
15        cursor.execute("CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
16    except mysql.connector.Error as err:
17        if err.errno:
18            logging.error(err.msg)
19
20    try:
21        connection.database = DB_NAME
22    except mysql.connector.Error as err:
23        if err.errno == errorcode.ER_BAD_DB_ERROR:
24            create_database(cur)
25            connection.database = DB_NAME
26        else:
27            logging.error(err)
28
29    TABLES = {}
30    TABLES['users'] = (
31        "CREATE TABLE `users` ("
32        "  `u_id` int(7) NOT NULL AUTO_INCREMENT,"
33        "  `email` varchar(320) NOT NULL UNIQUE,"
34        "  `password` char(64) NOT NULL,"
35        "  `fname` varchar(25) NOT NULL,"
36        "  `lname` varchar(25) NOT NULL,"
37        "  PRIMARY KEY (`u_id`)"
38        ") ENGINE=InnoDB")
39    TABLES['workouts'] = (
40        "CREATE TABLE `workouts` ("
41        "  `w_id` int(7) NOT NULL AUTO_INCREMENT,"
42        "  `u_id` int(7) NOT NULL,"
43        "  `date` int(11) unsigned NOT NULL,"
44        "  `type` int(1) NOT NULL,"
45        "  `duration` int(4) NOT NULL,"
46        "  `calories` int(4) NOT NULL,"
47        "  `distance` int(4) NOT NULL,"
48        "  `notes` text NOT NULL,"
49        "  PRIMARY KEY (`w_id`)"
50        ") ENGINE=InnoDB")
51
52    for name, cmd in TABLES.items():
53        try:
54            logging.info("CREATING TABLE > %s", name)
55            cursor.execute(cmd)
56        except mysql.connector.Error as err:
57            if err.errno:
58                logging.error(err.msg)
59    return None
60
61def addUser(user):
62    """Add single user"""
63    logging.info("ADDING USER > %s", user)
64    command = ("INSERT INTO users (email, password, fname, lname) VALUES (%s, SHA2(%s, 256), %s, %s)")
65    logging.info("EXECUTING SQL: %s", command)
66
67    try:
68        cursor = connection.cursor()
69        cursor.execute(command, user)
70        connection.commit()
71        cursor.close()
72    except mysql.connector.Error as err:
73        if err.errno:
74            logging.error(err.msg)
75            print(err.msg)
76    return None
77
78def getUser(email):
79    """Return user data for given email"""
80    print("GETTING USER DATA FOR > %s", email)
81    command = ("SELECT u_id, email, password, fname, lname FROM users WHERE email='" + email + "'")
82    print("EXECUTING SQL: %s", command)
83
84    try:
85        cursor = connection.cursor()
86        cursor.execute(command)
87    except mysql.connector.Error as err:
88        if err.errno:
89            print(err.msg)
90    user = cursor.fetchone()
91    cursor.close()
92    return user # return tuple
93
94def deleteUser(email):
95    """Delete user with given email"""
96    logging.info("DELETING USER DATA FOR > %s", email)
97    command = ("DELETE FROM users WHERE email='" + email +"'")
98    logging.info("EXECUTING SQL: %s", command)
99
100    try:
101        cursor = connection.cursor()
102        cursor.execute(command)
103        connection.commit()
104        cursor.close()
105    except mysql.connector.Error as err:
106        if err.errno:
107            logging.error(err.msg)
108    return None
109
110def addAllWorkouts(workouts):
111    """Add all workouts"""
112    logging.info("ADDING ALL WORKOUTS > %s", workouts)
113    command = ("INSERT INTO workouts (u_id, date, type, duration, calories, distance, notes) VALUES (%s, %s, %s, %s, %s, %s, %s)")
114    logging.info("EXECUTING SQL: %s", command)
115
116    try:
117        cursor = connection.cursor()
118        cursor.executemany(command, workouts)
119        connection.commit()
120        cursor.close()
121    except mysql.connector.Error as err:
122        if err.errno:
123            print(err.msg)
124    return None
125
126def getAllWorkouts(u_id):
127    """Return all workout data for given user id"""
128    logging.info("GETTING ALL WORKOUT DATA FOR > %s", u_id)
129    command = ("SELECT w_id, date, type, duration, calories, distance, notes FROM workouts WHERE u_id=" + u_id)
130    logging.info("EXECUTING SQL: %s", command)
131
132    try:
133        cursor = connection.cursor()
134        cursor.execute(command)
135    except mysql.connector.Error as err:
136        if err.errno:
137            print(logging.error(err.msg))
138    workouts = {}
139    for (w_id, w_date, w_type, w_duration, w_calories, w_distance, w_note) in cursor:
140        workouts[w_id] = {"date": w_date, "type": w_type, "duration": w_duration, "calories": w_calories, "distance": w_distance, "note": w_note}
141    cursor.close()
142    return workouts
143
144def deleteAllWorkouts(u_id):
145    """Delete all workouts for given user id"""
146    logging.info("DELETING ALL WORKOUTS FOR > %s", u_id)
147    command = ("DELETE FROM workouts WHERE u_id=" + u_id)
148    logging.info("EXECUTING SQL: %s", command)
149
150    try:
151        cursor = connection.cursor()
152        cursor.execute(command)
153        connection.commit()
154        cursor.close()
155    except mysql.connector.Error as err:
156        if err.errno:
157            logging.error(err.msg)
158    return None
159
160
161setup()