-
Creating databases and Tables
-
From __future__ import print_function
-
-
Import Mysql.connector
-
From Mysql.connector import errorcode
-
-
db_name = ' Employees '
-
-
TABLES = {}
-
tables[' Employees ' = (
-
"CREATE TABLE ' employees ' ("
-
"' Emp_no ' int (one) not NULL auto_increment,"
-
"' birth_date ' date not NULL,"
-
"' first_name ' varchar () not NULL,"
-
"' last_name ' varchar (+) not NULL,"
-
"' Gender ' enum (' M ', ' F ') is not NULL,"
-
"' hire_date ' date not NULL,"
-
"PRIMARY KEY (' Emp_no ')"
-
") Engine=innodb")
-
-
tables[' Departments ' = (
-
"CREATE TABLE ' departments ' ("
-
"' Dept_no ' char (4) Not NULL,"
-
"' dept_name ' varchar (+) not NULL,"
-
"PRIMARY key (' Dept_no '), UNIQUE key ' dept_name ' (' Dept_name ')"
-
") Engine=innodb")
-
-
tables[' salaries ' = (
-
"CREATE TABLE ' salaries ' ("
-
"' Emp_no ' int (one) is not NULL,"
-
"' Salary ' int (one) is 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")
-
-
tables[' dept_emp ' = (
-
"CREATE TABLE ' dept_emp ' ("
-
"' Emp_no ' int (one) is 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 the DELETE CASCADE,"
-
"CONSTRAINT ' dept_emp_ibfk_2 ' FOREIGN KEY (' Dept_no ')"
-
"REFERENCES ' departments ' (' Dept_no ') on DELETE CASCADE"
-
") Engine=innodb")
-
-
tables[' Dept_manager ' = (
-
"CREATE TABLE ' Dept_manager ' ("
-
"' Dept_no ' char (4) Not NULL,"
-
"' Emp_no ' int (one) is 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 the DELETE CASCADE,"
-
"CONSTRAINT ' dept_manager_ibfk_2 ' FOREIGN KEY (' Dept_no ')"
-
"REFERENCES ' departments ' (' Dept_no ') on DELETE CASCADE"
-
") Engine=innodb")
-
-
tables[' titles '] = (
-
"CREATE TABLE ' titles ' ("
-
"' Emp_no ' int (one) is not NULL,"
-
"' title ' varchar (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")
-
-
CNX = Mysql.connector.connect (user= ' Scott ')
-
cursor = Cnx.cursor ()
-
-
def create_database (cursor):
-
Try
-
Cursor.execute (
-
"CREATE DATABASE {} DEFAULT CHARACTER SET ' UTF8 '". Format (db_name))
-
Except Mysql.connector.Error as err:
-
Print ("Failed Creating database: {}". Format (ERR))
-
Exit (1)
-
-
Try
-
Cnx.database = db_name
-
Except Mysql.connector.Error as err:
-
if Err.errno = = ErrorCode. Er_bad_db_error:
-
Create_database (cursor)
-
Cnx.database = db_name
-
Else
-
Print (ERR)
-
Exit (1)
-
-
-
For name, DDL in Tables.iteritems ():
-
Try
-
Print ("Creating Table {}:". Format (name), end= ")
-
Cursor.execute (DDL)
-
Except Mysql.connector.Error as err:
-
if Err.errno = = ErrorCode. Er_table_exists_error:
-
Print ("already exists.")
-
Else
-
Print (ERR.MSG)
-
Else
-
Print ("OK")
-
-
Cursor.close ()
-
Cnx.close ()
-
From __future__ import print_function
-
From datetime import Date, datetime, Timedelta
-
Import Mysql.connector
-
-
CNX = Mysql.connector.connect (user= ' Scott ', database= ' employees ')
-
cursor = Cnx.cursor ()
-
-
Tomorrow = DateTime.Now (). Date () + Timedelta (Days=1)
-
-
Add_employee = ("INSERT into Employees"
-
"(First_Name, last_name, hire_date, Gender, birth_date)"
-
"VALUES (%s,%s,%s,%s,%s)")
-
Add_salary = ("INSERT into salaries"
-
"(Emp_no, Salary, from_date, to_date)"
-
"VALUES (% (emp_no) s,% (salary) s,% (from_date) s,% (to_date) s)")
-
-
Data_employee = (' Geert ', ' Vanderkelen ', Tomorrow, ' M ', Date (1977, 6, 14))
-
-
# Insert New Employee
-
Cursor.execute (Add_employee, Data_employee)
-
Emp_no = Cursor.lastrowid
-
-
# Insert Salary Information
-
Data_salary = {
-
' Emp_no ': emp_no,
-
' Salary ': 50000,
-
' From_date ': Tomorrow,
-
' To_date ': Date (9999, 1, 1),
-
}
-
Cursor.execute (Add_salary, Data_salary)
-
-
# Make sure data are committed to the database
-
Cnx.commit ()
-
-
Cursor.close ()
-
Cnx.close ()