Curso de Programación en Python/MySQL-5

• MySQL-5.py

MySQL-5.py

  1 #!/usr/bin/python3
  2 #-*-coding: utf-8 -*-
  3 
  4 import mysql.connector
  5 from mysql.connector import errorcode
  6 
  7 config = {
  8   'user': 'PythonClase',
  9   'password': 'Py800se',
 10   'host': '127.0.0.1',
 11   'database': 'PythonClase',
 12   'raise_on_warnings': True,
 13   'unix_socket': '/var/lib/mysql/mysql.sock'
 14 }
 15 
 16 try:
 17   Conn = mysql.connector.connect(**config)
 18 except mysql.connector.Error as err:
 19   if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
 20     print( "UsuarioNombre o Contraseña incorrecto" )
 21   elif err.errno == errorcode.ER_BAD_DB_ERROR:
 22     print( "Base de Datos no existe" )
 23   else:
 24     print( err )
 25 
 26 Cursor = Conn.cursor()   
 27 
 28 DB_NAME = 'PythonClase'
 29 
 30 TABLES = {}
 31 TABLES['employees'] = (
 32     "CREATE TABLE `employees` ("
 33     "  `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
 34     "  `birth_date` date NOT NULL,"
 35     "  `first_name` varchar(14) NOT NULL,"
 36     "  `last_name` varchar(16) NOT NULL,"
 37     "  `gender` enum('M','F') NOT NULL,"
 38     "  `hire_date` date NOT NULL,"
 39     "  PRIMARY KEY (`emp_no`)"
 40     ") ENGINE=InnoDB")
 41 
 42 TABLES['departments'] = (
 43     "CREATE TABLE `departments` ("
 44     "  `dept_no` char(4) NOT NULL,"
 45     "  `dept_name` varchar(40) NOT NULL,"
 46     "  PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)"
 47     ") ENGINE=InnoDB")
 48 
 49 TABLES['salaries'] = (
 50     "CREATE TABLE `salaries` ("
 51     "  `emp_no` int(11) NOT NULL,"
 52     "  `salary` int(11) NOT NULL,"
 53     "  `from_date` date NOT NULL,"
 54     "  `to_date` date NOT NULL,"
 55     "  PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`),"
 56     "  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) "
 57     "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
 58     ") ENGINE=InnoDB")
 59 
 60 TABLES['dept_emp'] = (
 61     "CREATE TABLE `dept_emp` ("
 62     "  `emp_no` int(11) NOT NULL,"
 63     "  `dept_no` char(4) NOT NULL,"
 64     "  `from_date` date NOT NULL,"
 65     "  `to_date` date NOT NULL,"
 66     "  PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`),"
 67     "  KEY `dept_no` (`dept_no`),"
 68     "  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) "
 69     "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
 70     "  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) "
 71     "     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
 72     ") ENGINE=InnoDB")
 73 
 74 TABLES['dept_manager'] = (
 75     "  CREATE TABLE `dept_manager` ("
 76     "  `dept_no` char(4) NOT NULL,"
 77     "  `emp_no` int(11) NOT NULL,"
 78     "  `from_date` date NOT NULL,"
 79     "  `to_date` date NOT NULL,"
 80     "  PRIMARY KEY (`emp_no`,`dept_no`),"
 81     "  KEY `emp_no` (`emp_no`),"
 82     "  KEY `dept_no` (`dept_no`),"
 83     "  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) "
 84     "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
 85     "  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) "
 86     "     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
 87     ") ENGINE=InnoDB")
 88 
 89 TABLES['titles'] = (
 90     "CREATE TABLE `titles` ("
 91     "  `emp_no` int(11) NOT NULL,"
 92     "  `title` varchar(50) NOT NULL,"
 93     "  `from_date` date NOT NULL,"
 94     "  `to_date` date DEFAULT NULL,"
 95     "  PRIMARY KEY (`emp_no`,`title`,`from_date`), KEY `emp_no` (`emp_no`),"
 96     "  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`)"
 97     "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
 98     ") ENGINE=InnoDB")
 99 
100 
101 for name, ddl in TABLES.items():
102     try:
103         print("Creating table {}: ".format(name), end='')
104         Cursor.execute(ddl)
105     except mysql.connector.Error as err:
106         if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
107             print("already exists.")
108         else:
109             print(err.msg)
110     else:
111         print("OK")
112 
113 Cursor.close()
114 Conn.close()

Resultado

[rrc@Pridd PythonClase]$ mysql -u PythonClase -pPy800se
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.0.22-MariaDB Mageia MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use PythonClase;
Database changed
MariaDB [PythonClase]> show tables
    -> ;
+-----------------------+
| Tables_in_PythonClase |
+-----------------------+
| TablaDePrueba         |
| departments           |
| dept_emp              |
| dept_manager          |
| employees             |
| salaries              |
| titles                |
+-----------------------+
7 rows in set (0.00 sec)