4whg220P

1# connect to MySQL:
2
3import mysql.connector	# for connecting to mysql servers using python
4from mysql.connector import errorcode	# for exception handling
5from datetime import date, datetime, timedelta
6
7try:
8
9	connect1= mysql.connector.connect(user= 'root', password= 'password', host= '127.0.0.1')	# 127.0.0.1= localhost connection
10
11	# for future connections after database is created: connect1= mysql.connector.connect(user= 'root', password= 'password', host= '127.0.0.1', database= 'database_name')
12
13	print("Database connection OK.")
14
15except mysql.connector.Error as connect_error:
16
17	if connect_error.errno == errorcode.ER_ACCESS_DENIED_ERROR:
18		print("User or password error, try again.")
19
20	elif connect_error.errno == errorcode.ER_BAD_DB_ERROR:
21		print("Database does not exist, try again.")
22
23	else:
24		print(err)
25
26
27
28db_cursor= connect1.cursor()	# data structure used for MySQL databases in python
29
30
31# create the "employees" database:
32
33DB_NAME= 'employees'
34
35def create_db(db_cursor):
36
37	try:
38		db_cursor.execute("CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))	# creates the database defined by DB_NAME as 'employees'
39	except mysql.connector.Error as db_err:
40		print("Failed creating database: {}".format(db_err))
41		exit(1)
42
43try:
44	db_cursor.execute("USE {}".format(DB_NAME))		# selects the just created database as the current database
45
46except mysql.connector.Error as db_err:
47	print("Database {} does not exist.".format(DB_NAME))
48
49	if db_err.errno == errorcode.ER_BAD_DB_ERROR:
50		create_db(db_cursor)	# creates the database stored in DB_NAME if the database does not exist
51		print("Database {} created successfully.".format(DB_NAME))
52		connect1.database= DB_NAME
53
54	else:
55		print(db_err)
56		exit(1)
57
58
59# create tables in the "employees" database after connecting to the MySQL database:
60
61
62TABLES = {}		# a dictionary data structure is created as an efficient way of storing the different tables in a database. The table name is the "key" and the "value" for each key is the mysql commands for creating the various rows and their data types
63
64TABLES['employees'] = ("CREATE TABLE `employees` (`emp_no` int(11) NOT NULL AUTO_INCREMENT, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M', 'F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`)) ENGINE=InnoDB")
65
66TABLES['departments'] = ("CREATE TABLE `departments` (`dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)) ENGINE=InnoDB")
67
68TABLES['salaries'] = ("CREATE TABLE `salaries` (`emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`), CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE) ENGINE=InnoDB")
69
70TABLES['dept_emp'] = ("CREATE TABLE `dept_emp` (`emp_no` int(11) NOT NULL, `dept_no` char(4) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`), KEY `dept_no` (`dept_no`), CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE, CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE) ENGINE=InnoDB")
71
72TABLES['dept_manager'] = ("CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL, `emp_no` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`), KEY `dept_no` (`dept_no`), CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE, CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE) ENGINE=InnoDB")
73
74TABLES['titles'] = ("CREATE TABLE `titles` (`emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL, `from_date` date NOT NULL, `to_date` date DEFAULT NULL, PRIMARY KEY (`emp_no`,`title`,`from_date`), KEY `emp_no` (`emp_no`), CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE) ENGINE=InnoDB")
75
76
77# iterate over the tables just created to add them to the 'employees' database:
78
79for table_name in TABLES:
80	table_description= TABLES[table_name]
81
82	try:
83		print("Creating table {}: ".format(table_name), end='')		# user friendly print function will display what tables have been created
84		db_cursor.execute(table_description)
85
86	except mysql.connector.Error as table_err:
87		if table_err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
88			print("Table already exists: {} ".format(table_name))
89		else:
90			print(table_err.msg)
91	else:
92		print("OK.")
93
94
95# insert data into the newly created tables:
96
97all_employees= {}	# initialize an employees dictionary to store all employees and salaries
98all_salaries= {}
99emp_no= 0
100
101all_employees['George Washington']= ('George', 'Washington', date(2000, 6, 14), 'M', date(1732, 2, 22))
102all_employees['John Adams']= ('John', 'Adams', date(2001, 1, 27), 'M', date(1735, 10, 30))
103
104all_salaries['George Washington']= {'emp_no': emp_no, 'salary': 50000, 'from_date': date(2000, 6, 14), 'to_date': date(9999, 1, 1)}
105all_salaries['John Adams']= {'emp_no': emp_no, 'salary': 45000, 'from_date': date(2001, 1, 27), 'to_date': date(9999, 1, 1)}
106
107add_employee= ("INSERT INTO employees (first_name, last_name, hire_date, gender, birth_date) VALUES (%s, %s, %s, %s, %s)")
108
109add_salary= ("INSERT INTO salaries (emp_no, salary, from_date, to_date) VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")
110
111
112
113
114for employee in all_employees:
115
116
117	try:
118		print("Adding employee {}: ".format(employee), end='')
119		db_cursor.execute(add_employee, all_employees[employee])
120		emp_no= db_cursor.lastrowid 	# cursor.lastrowid returns the AUTO_INCREMENT value for the last executed row. In this case it was "emp_no" from the employees table. We need this value as it is a primary key and is used in the next table "salaries"
121		db_cursor.execute(add_salary, all_salaries[employee])
122
123	except mysql.connector.Error as data_err:
124		print(data_err.msg)
125	else:
126		print("Employee and Salary OK.")
127
128
129
130
131
132
133
134
135
136
137
138
139connect1.commit()		# all data must be committed after being executed so that it is properly stored in the database
140
141
142
143
144
145
146
147
148
149
150db_cursor.close()	# close the MySQL cursor when finished inputting data into the database
151
152
153
154
155
156
157
158
159connect1.close()	# always close the connection when done with mysql