· 8 years ago · Jul 11, 2018, 04:30 AM
1#!/usr/bin/python3
2import mysql.connector
3from mysql.connector import errorcode
4from datetime import date, datetime, timedelta
5
6config = {
7 'user': 'root',
8 'password': 'root_mysql',
9 'host': '127.0.0.1',
10 'database': 'shoparu_01',
11 'raise_on_warnings': True,
12 'use_pure': True
13}
14
15TABLES = {}
16TABLES['employees'] = (
17 "CREATE TABLE `employees` ("
18 " `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
19 " `birth_date` date NOT NULL,"
20 " `first_name` varchar(14) NOT NULL,"
21 " `last_name` varchar(16) NOT NULL,"
22 " `gender` enum('M','F') NOT NULL,"
23 " `hire_date` date NOT NULL,"
24 " PRIMARY KEY (`emp_no`)"
25 ") ENGINE=InnoDB")
26
27TABLES['departments'] = (
28 "CREATE TABLE `departments` ("
29 " `dept_no` char(4) NOT NULL,"
30 " `dept_name` varchar(40) NOT NULL,"
31 " PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)"
32 ") ENGINE=InnoDB")
33
34TABLES['salaries'] = (
35 "CREATE TABLE `salaries` ("
36 " `emp_no` int(11) NOT NULL,"
37 " `salary` int(11) NOT NULL,"
38 " `from_date` date NOT NULL,"
39 " `to_date` date NOT NULL,"
40 " PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`),"
41 " CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) "
42 " REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
43 ") ENGINE=InnoDB")
44
45TABLES['dept_emp'] = (
46 "CREATE TABLE `dept_emp` ("
47 " `emp_no` int(11) NOT NULL,"
48 " `dept_no` char(4) NOT NULL,"
49 " `from_date` date NOT NULL,"
50 " `to_date` date NOT NULL,"
51 " PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`),"
52 " KEY `dept_no` (`dept_no`),"
53 " CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) "
54 " REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
55 " CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) "
56 " REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
57 ") ENGINE=InnoDB")
58
59TABLES['dept_manager'] = (
60 " CREATE TABLE `dept_manager` ("
61 " `dept_no` char(4) NOT NULL,"
62 " `emp_no` int(11) NOT NULL,"
63 " `from_date` date NOT NULL,"
64 " `to_date` date NOT NULL,"
65 " PRIMARY KEY (`emp_no`,`dept_no`),"
66 " KEY `emp_no` (`emp_no`),"
67 " KEY `dept_no` (`dept_no`),"
68 " CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) "
69 " REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
70 " CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) "
71 " REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
72 ") ENGINE=InnoDB")
73
74TABLES['titles'] = (
75 "CREATE TABLE `titles` ("
76 " `emp_no` int(11) NOT NULL,"
77 " `title` varchar(50) NOT NULL,"
78 " `from_date` date NOT NULL,"
79 " `to_date` date DEFAULT NULL,"
80 " PRIMARY KEY (`emp_no`,`title`,`from_date`), KEY `emp_no` (`emp_no`),"
81 " CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`)"
82 " REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
83 ") ENGINE=InnoDB")
84
85
86queries = {}
87queries['add_employee'] = ("INSERT INTO employees "
88 "(first_name, last_name, hire_date, gender, birth_date) "
89 "VALUES (%s, %s, %s, %s, %s)")
90
91queries['query_in_range'] = ("SELECT first_name, last_name, hire_date FROM employees "
92 "WHERE hire_date BETWEEN %s AND %s")
93
94
95data = {}
96data['add_employee'] = ('Geert', 'Vanderkelen', datetime.now().date() + timedelta(days=1), 'M', date(1977, 6, 14))
97data['query_in_range'] = ( date(1999, 1, 31), date(2018, 12, 31))
98
99
100try:
101 cnx = mysql.connector.connect(**config)
102 cursor = cnx.cursor()
103
104 for name, ddl in TABLES.items():
105 try:
106 print("Creating table {}: ".format(name), end='')
107 cursor.execute(ddl)
108 except mysql.connector.Error as err:
109 if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
110 print("already exists.")
111 else:
112 print(err.msg)
113 else:
114 print("OK")
115
116 #cursor.execute(queries['add_employee'], data['add_employee'])
117 #print(cursor.lastrowid)
118 cursor.execute(queries['query_in_range'], data['query_in_range'])
119 for (first_name, last_name, hire_date) in cursor:
120 print("{}, {} was hired on {:%d %b %Y}".format(
121 last_name, first_name, hire_date))
122 #print(cursor.execute("SELECT * FROM {table}".format(table=table)))
123except mysql.connector.Error as err:
124 if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
125 print("Something is wrong with your user name or password")
126 elif err.errno == errorcode.ER_BAD_DB_ERROR:
127 print("Database does not exist")
128 else:
129 print(err)
130finally:
131 cursor.close()
132 cnx.close()